SSRS Tablix report, handling column period totals and subreport comparisons

0
votes

I'm new to SSRS (2008) and am trying to replicate an existing Access report. The report lists sales totals by month, and I've not had any issue resolving the basics into a tablix. However the original Access report then totals columns by quarter, 6 month and yearly values, and moreover applies incorporates subreports to compare these with previous year totals and targets. Schematically thus

 Sale           Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
 Customer 1       1  11  10   8   1   2   0   0   0   1   3   4    40
 Customer 2       0   1   3   1   0   0   0   1   1   0   2   1    10
 MonthlyTotals    1  12  13   9   1   2   0   1   1   1   5   5    50
 Quarterly               26          12           2          11
 6 Monthly                           38                      13
 Yearly                                                      51

 Prev Yr Totals   2  10  10   5   5   0   0   0   0   0   0  10
 Monthly Diff    -1   2   3   4  -4   2   0   1   1   1   5  -5
 Quarterly Diff           4           2           0          10

And so forth. Note that the parameters are set so that the report can start at any month to lists the columns (for different financial years)

I have everything working fine for the first 4 lines (sale, customer 1, customer 2 and monthly totals) in the above, but cannot see the best way of

  1. Displaying the quarterly etc totals
  2. Displaying the subreports to show the previous years and target values with the differences between them and the current values.

I have full access to the SQL Server and am comfortable with complex queries and stored procedures, so was inclined to generate the values in a table and display out that, but is there a better way? In particular handling the quarterly etc totals in SSRS would be advantageous.

1
sql-server-2008reporting-services

1 Answers

1
votes

I think it is not possible to caclulate the quarterly totals with the help of Tablix alone, unless your SQL Table has a separate column named "Quarter".

So to achieve your requirement, you have to write simple stored-procedure which returns the resultant table along with one more additional column named "Quarter" which contains the values from "Q1..Q4" which is calculated based on the sales month field.

Then you can add this new column (Quarter) as ColumnGroup on top of your existing group in the tablix.

Hence the whole idea is to create the result set with all possible columns for which you want to group.