Create view of Data Set in SSRS

Question

I hope this isn't too simple, but I have a report retrieves rows using this layout:

AccountType    AccountHolder    AccountBalance
-----------    -------------    --------------
SVG            A                      $ 100.00
LNS            A                      $ 300.00
HEL            A                      $ 150.00
SVG            B                      $  50.00
SVG            C                      $ 900.00
HEL            C                      $ 350.00

The report summarizes the data above in a variety of ways, however I've been asked to provide a Sum of AccountBalances by AccountHolder for all of their accounts, essentially resulting in the following output:

AccountHolder    AccountBalance
-------------    --------------
A                      $ 550.00
B                      $  50.00
C                      $1250.00

Since the AccountType differs between each row, its not possible to group the rows in the way I want.

I would really prefer not to create another dataset to retrieve identical data without the column, mainly because the original stored procedure takes 40 minutes to run and I don't want to double the amount of time it takes to run this report.

Can I force SSRS to ignore the AccountType column? Or create a view of the dataset without requiring an extra trip to the database?

Answer 1

You can group by anything you like in the Group By bands of the table. It's been a while and I don't currently have SSRS installed on this machine - but if memory serves - just click on the left boarder of your layout table and the bands will show up. Then you may have to right click on them and do properties or somehting similar and set the grouping properties. You can even have grouping levels (groups within groups).

Also - if the sproc takes 40 minutes you may want to look at optomizing your query. Indexes may be indictatd on join columns, etc. When I was developing SSRS - reports were not allowed to take over a minute to show up.

Answer 2

you need a drill down report How To Make a Drilldown Report

Also, if it takes 40 minutes to execute, there's something majorly wrong with your sql or yoru table/s. could you please post your code?