Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.
Looking back at the report we generated in yesterdays post, there are some things we can do to further enhance its appearance. We can format the look of the total amount, and we can also hide the subcategory details. If you don’t have the report open already, open it and go to the Design tab.
Step 1. Format numeric values.
Right click on the Total Amount area of the details line, and pick “Text Box Properties” on the menu.
In the properties window, click the Number menu option on the left. Then change the Category to “Currency”. Now check on the “Use 1000 separator” option.
When we work with values as big as those that will be on the report, only dollars are typically shown. Reduce the decimal places to zero. You can also change the symbol to one appropriate to your location, put a space after it, or move it behind the dollar field. In this case we’ll just click OK.
Repeat the above steps for the SUM(TotalAmount) text boxes on the ProductCategoryName and FiscalYear lines.
Step 2. Add interactivity to hide sections of the report.
Next we can add interactivity to the report by allowing the user to essentially hide the subcategories, collapsing them into the categories.
Click on the grid, to bring up the grid row and headers. Then right click on the row selector, as pointed to by the red arrow in the following figure:
From the pop up menu, pick “Row Visibility”.
Click the Hide option. Then check on the “Display can be toggled by this report item” and pick “ProductCategoryName’ in the drop down, and click OK.
Step 3. Preview your work.
Save your work then click on the Preview tab. Your should now see a report where the Subcategories are rolled up into the Categories. Each category should have the + symbol to reveal the details behind it.
Click on one of the + signs to expand will reveal the details.
Arcane Code 