Following is the followup post to the previous Help Tickets post located here. In this post I will discuss creating a simple SQL Server Reporting Services report the display the results generated by one of the key Total Ticket Count queries. Following is the query that will be used in the report.


This procedure assumes some familiarity with SSRS and BIDS (Business Intelligence Development Studio). I used SQL Server 2008 Developer Edition for this sample.

Step #1: Launch BIDS.
Step #2: Create a new Report Server Project Wizard Project. Choose a meaningful name like Tickets.
Step #3: The Report Wizard should automatically start. Create a new Data Source pointed to the SQL Server instance and DBTickets database. Choose a meaningful name like DBTickets.
Step #4: Copy and paste the above query into the Design the Query window. See screenshot below.



Step #5: Select Report Type: Tabular. Step #6: In the Design the Table (grouping) window move Section into Group and Department and TicketCount into Details. See screenshot below.



Step #7: In the Choose the Table Layout window select Stepped, check Include subtotals, uncheck Enable drilldown. See screenshot below.



Step #8: Choose a Table Style for the table. I chose Slate.
Step #9: Enter the Report server and Deployment folder.
Step #10: Choose a meaningful Report name such as Tickets. Check the Preview report to see initial report output.
Step #11: Enter 12/01/2009 for Start Date and 12/07/2009 for End Date. Click View Report. Output should be something like the following screenshot. Numbers are probably different because they are randomly generated.



Step #12: Switch from Preview mode back to Design. In the Report Data Treeview on the left expand the Parameters folder and right click on the StartDate and select Parameter Properties. Change the Data type to Date/Time. Repeat for the EndDate parameter. See the screenshot below.



Step #13: Save the Report and Preview. The StartDate and EndDate parameter inputs have changed from textboxes to date pickers.
Step #14: Switch back to Design mode. In the Row Groups section at bottom right click on the "table1_Section" label and select Add Total After. See screenshots below.


New row added:


Step #15: In the Section column of the newly added row add the text "Overall Total". Highlight the newly added row and in Properties change the Font to "Tahoma, 10pt, Default, Bold, Default", the Color to White, and the Background color to #6e9eca. This will make it make the Slate theme of the table.
Step #16: Change the "Tickets" header to something more meaningful like "Ticket Counts By Section and Department".
Step #17: Expand the Column widths of the table's columns especially the Department column. You will likely need to expand the overall with of the report.< br/> Step #18: Higlight the table and move it on row width lower. Insert 4 textboxes onto the report in between the "Ticket Counts By Section and Department" and the table. The left-most textbox will contain the text "Start Date". The second textbox will contain the expression "=[@StartDate]". The third textbox will contain the text "End Date". And the fourth will contain the expression "=[@EndDate]". Change the Properties of the 4 textboxes: Font: Tahoma, 10pt, Default, Bold, Default; Color: SteelBlue; Background Color: White
Step #19: Change the Format of the values contained within the @StartDate and @EndDate textboxes by doubleclicking on the values until the Placeholder Properties window appears. Select the Number tab and change the Number Category to Date. Do this for both StartDate and EndDate. See screenshot below. Also on the Alignment tab change Horizontal to Left for both Start Date and End Date.



Step #20: View the results. See screenshot below. Test and tweak as necessary. Deploy. Collect paycheck.