Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Rock Your Reports Series 1 of 3

  • 11 March 2011
  • Author: SMcDonald
  • Number of views: 12176

Hello everybody,  I want to thank all of you who attended my webinar that I did on March 10th, called Rock your Reports.  I had a lot of requests to post the information that I used to create the reports so that is why I am posting it on BIDN!  Below are the step by step instructions and my story line :-).

ABC company is trying to solicit some investors because they are having some financial troubles.  The investors asked us for some reports that is going to give them a comparison of our Sales Amount, Product Cost and our Gross Margin. As well as some reports on the locations of our resellers.  What we are going to do first is create a query that brings back the Reseller's SalesAmt, Total Product Cost, the FiscalYr and Qtr. 

1.       Create report

2.       Create a shared data source to AWDW2008

3.       Create data source under Report Data tab called ResellerSalesbyDate

4.       Create data set using the below query:

SELECT        SUM(FRS.SalesAmount) AS SalesAmount, SUM(FRS.TotalProductCost) AS TotalProductCost, T.FiscalYear, T.FiscalQuarter, 'Qtr ' + CONVERT(char(1), T.FiscalQuarter) AS FiscalQuarterLb1

FROM            FactResellerSales AS FRS INNER JOIN

                         DimDate AS T ON FRS.OrderDateKey = T.DateKey

GROUP BY T.FiscalYear, T.FiscalQuarter

ORDER BY T.FiscalYear, T.FiscalQuarter


5.       While in the data set, Click on Fields and add a Calculated Field called Gross Margin


6.       Drag out Chart from the Toolbox Items and select Columns

7.       Drag FiscalYr and FiscalQtr to Category Group under the Chart Data box

8.       Drag Sales Amt, TotalProductCost to Value under the Chart Data box

Preview to make sure it's running properly.

We've charted the Reseller  Sales Amt and Total Product Cost as a column chart with the Fiscal Yr and Qtr on the X axis.  We also have Sales Amt vs. Total Product Cost on the Y Axis.

Now if we look at the Fiscal Qtr label that's displayed at the bottom, it isn't very informative.  So what we are going to do to help out here is change the label.  In the data set that we brought in we created a column called Fiscal Qtr Label1. We'll use this because it's a little more intuitive.

8.  So to change that label, Right Click in Category Groups on Fiscal Quarter and change the label to FiscalQuarterLb1


9.  Since we want to show each quarter for the year and it currently is showing every other one, we need to Right Click on Horizontal Axis then select Properties (Qtr & Yr) and change the Interval to 1, Click ok

10.  Drag Sales Amount over to the Values section on your Chart Data box. Next what we are going to do is create a 3rd data series  for Gross Margin using the Line with Markers chart type and plots this series on a secondary Y-axis using a percentage number format scale.

11.  Right Click and select Series Properties, change the value field to [Sum(GrossMargin)]


13. While still in the Series Properties, Select Axes & Chart Area and choose Secondary for your Vertical Axis / Click ok

14.Right Click chart body column for Gross Margin and select Change Chart Type to Line with Markers.

Preview :Now we want to give the user of the report a more detail of what is happening in the Chart

15.  Drag out a Text Box below your chart and Label it View Details underlined and color is blue, then right click on text box and select properties tab Change the textbox Name to ToggleDetails.

16.  Drag out a table and group on FiscalYr and FiscalQtr.  Delete details from Row Groups (group only).

16.  Drag out Total Product Cost, Sales Amount and GrossMargin (should sum it for you)

18.  Now format the product cost and sales amt to currency / gross margin will be percentage w 2 dec.

19.  Create a new column to the Right of Gross Margin & drag Indicators over, choose Directional

20. Right Click on Indicator to go to Properties, Value & States

21. Change Value to Sum(GrossMargin) & State Measurement to

                Numeric R = -.12 - 0 / 0.001 - 0.03 / .003 - 1


22.  Add some color for the column heading (silver) and then click on the details box and go background color in the properties tab and add the below expression.

=IIF(RunningValue(Fields!FiscalQuarter.Value,COUNTDISTINCT,NOTHING) MOD 2 = 0,IIF(ROWNUMBER(NOTHING) MOD 2=0,"LightGrey","White"),IIF(ROWNUMBER(NOTHING) MOD 2=1,"White","LightGrey"))            

23.  Go to properties tab for your table and change the visibility Hidden To True and Toggleitem is ToggleDetails


This is part 1 of a three part article series.  I hope you enjoyed the level of detail in the instructions.  To view the recording click here.  You will need to register if you haven't already to view it.

Once you have mastered this, try practicing with some of your data.  Have a great weekend.

Categories: Reporting Services
Rate this article:


Other posts by SMcDonald

Please login or register to post comments.