SSRS – Drilldown Reports

What are we going to Learn?

1. How to create simple Pie chart
2. How to create simple table data
3. How to use report parameters (a brief demo)
4. What is drilldown in reporting terms
5. How to implement drilldown feature.
6. Completing one sample example.

Some part of this article is based on my previous article SSRS – Working with Column Charts. I would request before proceeding forward please have a look on this article not to find yourself in middle of no-where!!

1. We shall continue with our report created in our previous article SSRS – Working with Column Charts and will extend it to demonstrate drill down feature.
2. We will now add a chart on the bottom left section of our report.
3. As described earlier, on inserting chart control you will see a dialog to select chart type. From that window select pie chart.
4. We will select following pie chart for this example
   
   
    
5. Place the pie chart at appropriate location by clicking and dragging the cross-hair icon at top left corner of the selected chart.
6. Please note that, chart controls are actually composed of many small controls (like textbox for axis, chart body, major and minor ticks etc), please make sure you select right element you intend to work upon. I personally have messed couple of times while selecting control in some complex structure.
7. Your report should now look something like this

   

8. Just like bar chart report which we saw in my previous article mentioned above. On clicking on pie chart body also pops up a new context strip which allows you to quickly configure your chart what field it needs to represent.
9. But before configuring chart, we must configure dataset for the chart report. For this right click on Datasets folder and click "Add Dataset...", following window pops up

   

10. Configure your dataset as shown above with following query in the query box
    SQL

    SELECT EPO.SalesPersonId as EmployeeID, OrderPrice TotalRevenue,
    p.ProductId, p.ProductName, p.ProductPrice, EPO.Location, CONVERT(int,(OrderPrice/ProductPrice)) as Quantity
    FROM EmployeeProductOrder EPO
    INNER JOIN Product P on EPO.ProductId = P.ProductId
    WHERE (@EmpId is null and 1=1) or (SalesPersonId = @EmpId)

11. In the above query, the where condition has been specifically written to return data for all employees when @EmpId variable is null.
12. Below is our database diagram based on which above query was built

    

13. So here we are trying to find employees with their Total revenue along with detail of products sold and what fraction of product contributed in Total Revenue. For simplicity I have kept the SQL query very simple and didn’t want to get deep into accurate business logic, in real world we would rather have complex SQL queries which would then be formed as Stored procedures and used in reports rather than simple query.
14. We will then check the fields section on dataset pop up, and in parameters sections we will see on left side there is a parameter required for the query which is @EmpId

    

15. But its Parameter Value column doesn’t have anything in it. Here comes the power of BI tool, if you simple click OK at this moment, the tool will create a new parameter in the Parameters folder in Report Data window as below

    

16. Don’t worry about other datasets at the moment we would gradually learn them as well in this article.
17. Now our Dataset is ready, and we are ready to configure our chart to render data from this dataset in a pie chart format
18. Go back to the chart which was previously created on report body, right click on chart and select "Chart Properties…" the following pop up will open where we need to introduce our chart to the dataset so created

    

19. Select dataset as show above, I have not talked about data source in this example as I have already covered that in my previous article. Kindly refer that article in case there’s any doubt regarding data source creation.
20. Once you click OK button on above window, it is time to actually set up pie chart and tell it which field it needs to work upon to display data
21. Simply click on the dummy chart area already shown by BI tool as sample, a smart token window named "Chart Data" shall open up where you can set up which fields chart need to look for data.

    

22. As shown above, select ProductName under Category Groups by clicking on the + (plus) sign next to Category Groups text. And select Quantity under Values section. BI tool will automatically puts up Sum(Quantity) as you values, this is default behavior which can be changed by either clicking on the dropdown option next to Sum(Quantity) and select Aggregates and then appropriate option. Otherwise use dropdown of TotalRevenue column select "Series Properties…" option which will bring up following window

    

23. From this window clicking on function button next to Value field text box will allow you to provide you custom expression for values to be shown in report.
24. This window also allows various other options like
    1. Whether to show /hide chart under certain condition
    2. Toggle legend to be shown or even customize legends for this series
    3. Any activity to be performed when user click on this series - this would be described later while explaining report drill down feature.
    4. Fill series with certain color or use system defined
    5. Border and Shadow are self explanatory
25. After providing appropriate settings close this window and run your report, you report should look like below

    

26. Since we haven’t passed any value to @EmpId report parameter the chart above shows data for all employees. Providing Category as ProductName in chart configuration has also create chart legends under it.
27. To show % marking on the chart, use Marker settings on chart body, right click on chart body and select "Show Data Labels" this will show your data, for further changing the settings like adding % symbol, again right click on any one of the data labels on the chart and click "Series Lable Properties…"

    

28. In the Label Data section, click on function button and provide following expression

    =CDec(Sum(Fields!Quantity.Value,  
    Chart3_CategoryGroup")/Sum(Fields!Quantity.Value, "dsPieChart"))

    The above expression find the Quantity percentage of each product for total revenue so generated.

29. "Chart3_CategoryGroup" is actually the scope of operation. In SSRS you can actually perform on run time operations on certain group of data. In this example "Chart3_CategoryGroup" is the name of the category group provide in chart settings. Whereas "dsPieChart" is the name of dataset and hence sum calculated with this scope will calculate summation of all Quantity values in the dataset.
30. The following picture highlights where to look for scope of category group item of the chart. Click on the Drop down provided with ProductName under Category Groups section and then go to "Category Group Properties…"

    

31. We shall talk about scoping more in upcoming articles. This feature will take of many of the loads from your query/stored procedure and move them to the report rending operation. So in a nut shell scope allows you to perform action on certain subset of entire database relative to your grouping defined in report. To know more about scope read this article from MSDN.
32. So once we have defined our series labels we can now click on preview button and see how our chart report looks like. It would look like something like this after you make certain changes in background color.

    

33. Moving ahead we will now try to create a tablix structure, displaying employee’s details based on Employee ID passed as parameter.
34. Tablix is a container control which has rows and columns, one can group either rows and columns and represent data accordingly. Tablix itself is a complex control in SSRS with lots of features which we shall discuss in our next article.
35. Moving ahead with our example, lets now draw first a Rectangle control on right bottom section of our report and then inside that place one matrix control (one type of tablix). Your report should now look something like this below

    

36. Before going forward with report design, let’s design dataset for this report. For that go to Datasets folder, right click and select "Add Dataset…". In the window, select your data source so created previously and the put down below SQL query in the query box

    SELECT        EmployeeId, EmployeeName, EmployeeDeptId, Region, Salary, Photo
    FROM            Employee
    Where EmployeeId = @EmpId

37. Here you would have noticed that I have a photo column as well containing employee photos. Furthermore, this query will also use the same report parameter EmpId previously created for pie chart.
38. You will notice that BI tool has automatically created a Row grouping for you when you inserted the table control inside the rectangle. For the moment we won’t require this row grouping so will go ahead and right click on the group definition below and click "Delete Group" as shown below. Once you click Delete Group a pop would ask if you want to delete only group or rows along with it, for the moment we will just delete the group so will select option 2.

    

39. Now will add two columns and couple of rows in the table which would create cells where we need to show our data. Right click on the first column to merge your 4 row cells. Your grid should now look something like this below

    

40. I have added one image control as well to the merged column where I shall display Employee’s photo.
41. Now as you can see in above image I have bind the Dataset fields to the cells in extreme right column
42. Now the question that arises here, where to get the @EmpId value from? Here comes our first drill down feature of SSRS
43. To implement this, go back to our bar chart section, right click on the series definition and click Series properties

    

44. Go to Action tab in the window that popus up and fill in details as shown below

    

45. As you can see, Action section defines what action to be executed when someone clicks on the series data, here we are asking the chart to render itself but this time render it with parameter EmpId (Which is our report parameter) with value from EmployeeId field. What this will do is it will bind this action to each series bar as each of our bar is for each employee, so clicking on any bar would re-render our chart but this time EmpId parameter populated with Employee ID.
46. So now go ahead and run this application, it would initially render like this

    

47. Now here the bar chart is rendering as expected for all employees showing Total sales they have acquired so far. Our pie chart on the other hand is also showing Total sales per product details for all the product this because of the kind of query that we have written for the data set of this report as mentioned in point 11 previously.
48. Now when you click on any of the bar in the bar chart this entire report re-render itself as below

    

49. So you can see details of individual employee on the pie chart and employee detiails section. Thus we drilled down to more consise information from a summary report which is one way of drilling down the report.
50. Moving ahead with another type of drill down, which we usually see in many excel sheet workbooks. Let me show you the end result first so that you get an idea what I am talking about

    

51. So here you can see, there is a small [+] mark against all major rows upon clicking on which expands the child rows underneath it. Initially all rows are collapsed showing our summary data (which in our example not much of data – I agree, but its different in real world) and then can be further drilled down to detail reports
52. Let’s see how to start up with this report. First thing first we will add up a new report in our project and will name it EmployeeOrder.rdl

    

53. Will then configure our data source and for our data set we will use following query
    SQL

    SELECT OrderId, SalesPersonId, OrderPrice, Location, ProductId
    FROM EmployeeProductOrder

54. After that will drag Table control on report body, and add Parent grouping for that Table control as shown below

    

55. Set group on SalesPersonId, then select remaining fields in other columns this would be part of detail report. Your grouping window would be as below

    

56. Now to create those [+] marks, go to Details section under the grouping so made at the bottom of the designer window, and select "Group Properties" this would open up a pop up window where in the Visibility tab set following configurations

    

57. Once you save this configuration and preview the report, it would show you our expected report behavior

    

With few coloring and decoration it would look exactly like what I have previously demonstrated.

I hope this articles would have enlighten your knowledge regarding SSRS tools a bit further, and was able to highlight the power bestows in SSRS when it comes to data analysis and reporting. Till next time, happy learning !!!