Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Advanced Ranking and Grouping with SQL Server Reporting Services : Page 4

Learn how to flexibly define a SQL Server Reporting Services report for ranking purposes. This article provides a workaround for custom grouping totals for top items and for jazzing the table up with some visual chart indicators.


advertisement
Jazzing Up the Report
You can add one last visual cue to note the difference between top sellers and all others with a simple inline bar chart that grows relative to the difference. Place one chart in the footer row for the "Top" total and one in the row for "All Others." Set the dataset to the same as your first table, AW_Sales. For the first chart, set it to Simple Bar and add a new Data Value. The Value should be set to =ReportItems!TOP_COL1.Value, which is your total for the ranked set. On the Edit Chart Value property page, click Point Labels and enable "Show point labels." Then enter the data label as follows:

=IIF(ReportItems!GT_COL1.Value<>0,ReportItems!TOP_COL1.value/IIF(ReportItems!GT_COL1.Value<>0,
ReportItems!GT_COL1.Value,0),0)

Set the "All Others" row chart to Simple Bar as well. Add a value with the expression =ReportItems!NOTTOP_COL1.Value. On the Edit Chart Value property page, click Point Labels and enable "Show point labels." Then enter the data label as follows:



=IIF(ReportItems!GT_COL1.Value<>0,ReportItems!NOTTOP_COL1.value/IIF(ReportItems!GT_COL1.Value<>0,
ReportItems!GT_COL1.Value,0),0)

In the Chart Properties for both charts, select the Y axis tab and set the scale minimum to 0 and scale maximum to =ReportItems!GT_COL1.Value. You need to set the maximum to the total number of sales for the entire set so there is some consistent perspective.

Add a little gradient coloring to the Value (Edit button → Appearance tab → Series Style button → Fill tab), and you now have a visual indicator for the relative difference in sales. You now have a flexible report that limits the grouping, while providing end users with the flexibility to drill into detail. Figure 8 and Figure 9 show some of the options users have in crafting reports and the preservation of ranked values when sorted on a different column.


Figure 8. Custom Grouping by State: You now have a flexible report that limits the grouping, while providing end users with the flexibility to drill into detail.
 
Figure 9. Custom Grouping by Customer: You now have a flexible report that limits the grouping, while providing end users with the flexibility to drill into detail.

A Scalable Report
It's often important for report developers to look beyond users' immediate needs and create solutions that scale to accommodate many requirements. Without getting into overkill, this article presented a good example of providing user inputs so a wider audience can get value from your reports, and it is easily scalable when users need a different perspective of their data.



David Leibowitz is Practice Manager for Computer Generated Solutions, helping customers realize value from enterprise portals and analytics. For over a decade he's architected solutions for retail, wholesale, and distribution companies ranging from startups to Fortune 500 firms.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap