Creating a drilldown grid report in SQLView Pro

With the amount of data we collect each year, sometimes it’s hard to present that data to your employees and users effectively. Drill downs provide a quick and effective way to let users peer into the more detailed data of a report.

In this tutorial we’re going to be going through a sample that ships with SQLView Pro.  You can find a working demo of this drilldown here. To get started on your own and run through the tutorial, first add a new instance of SQLView Pro to any page on your site. If you aren’t currently a DNNStuff  member and would like to follow along, download and install the SQLView Pro Trial.

Now that we have the module on the page, click on the module menu and select ‘Browse Repository’. Choose the ‘SampleDrilldownYearlyBudgets’ item and click ‘Import Template’. You should now have a running drilldown on your page.

Setting up a drilldown is very easy to do.

Step 1: Create your parent report

The first report (parent) is the report that will contain the hyperlinks to the second report (child). In our example report, we’ll be clicking on the ‘Year’ column to drilldown into department budgets for that year.

Here is the sql for the parent report. Nothing ground breaking in this sql, it just selects the year and sums the budget and groups by year. By the way, when you install SQLView Pro there are 3 sample data tables created DNNStuff_SQLViewPro_Sample_Budgets, DNNStuff_SQLViewPro_Sample_Departments and DNNStuff_SQLViewPro_Sample_Employees that are used for many of the sample repository reports.

   1: SELECT Year, '$' + CONVERT(VARCHAR,SUM(Amount),1) [Total Budget] 

   2: FROM {oq}DNNStuff_SQLViewPro_Sample_Budgets

   3: GROUP BY Year

Step 2: Create your child report

The second report (child) is the report that will be shown after you click on a ‘Year’ link in the parent report. To turn the child report into a report that is drilled into you need to set two properties. These two properties are named ‘Drilldown from report’ and ‘Drilldown from fieldname’. In the ‘Drilldown from report’ property we’ll select the parent report and from the ‘Drilldown from fieldname’ we’ll enter ‘Year’. This tells SQLView Pro to add a link to each piece of ‘Year’ data from the parent report and send it to the child report. Because we are drilling into the child report, we also need to filter this report by the value that was clicked, the ‘Year’. This is accomplished by adding a parameter to the sql of our child report. In this case because we want to filter by the ‘Year’ we add [PARAMETER:Year] into our sql.

Here is the sql for the child report. As you can see in this sql code, we’re adding [PARAMETER:Year] to our WHERE clause to filter out only those records that are for our selected year.

   1: SELECT D.Name, '$' + CONVERT(VARCHAR,B.Amount,1) Amount 

   2: FROM {oq}DNNStuff_SQLViewPro_Sample_Departments D 

   3:    INNER JOIN {oq}DNNStuff_SQLViewPro_Sample_Budgets B 

   4:      ON D.DepartmentId = B.DepartmentId

   5: WHERE B.Year = [PARAMETER:Year]

   6: ORDER BY Amount DESC

Step 3: The finished report

If you made it this far you should have a full functional drilldown on your page now.

Feel free to play around with the sample to add some embellishments such as:

• In the child report, add a heading to your report that includes the year selected. Enter ‘Department Budgets for [PARAMETER:Year]’ into the report header
• You can also add any value from the drilldown row, try changing the report heading to ‘Department Budgets for [PARAMETER:Year], Total is [PARAMETER:Total_Budget]’ (Spaces are converted to underscores in fieldnames)
• You can create a second drilldown on another column in the parent report to go to a totally different report, or better yet you could add a text link column in the parent report called ‘Chart’ that drilled into a chart report showing the budget breakdown of the departments.
• You can create a third report that drills down from the department name of the second report to show all of the employees in that department or other interesting information about the department. You just set the third report up like you did the second report, referencing the second report and the column you want to be hyperlinked.

As you can see the possibilities are endless!