Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-06-20 : 07:33:31
|
| Mark writes "We created a complex Business Intelligence system long before BI was a buzz word. The whole structure has been built around Crystal Reports (Sorry I know that is a bad word around here).I looked at RS a couple years back, around the release of SP1. The biggest issue I had was the lack of Aggregates of Aggregates. This still seems to be the case in RS 2005, is that correct?So help me with a work around.In a Matrix report how can I?Have a calculation (Aggregate) that has a scope that is for both the Row and the Column.Example:What is the average sale per unit by product line, as well as the totals? Q1 Q2 TotalPeanuts Sales $ 100 150 250 Sales # 10 10 20 Per Sale Avg 10 15 12.5Cookies Sales $ 200 250 450 Sales # 10 10 20 Per Sale Avg 20 25 22.55Total 300 400 700 20 20 40Per Sale Avg 15 20 17.5In RS 2000 you could not have an Aggregate of an Aggregate, nor could you have a “compound scope”. Because of this I went back to that other software.I would love a way to deal with this, I have many reports that use this functionality, and would like to move to RS. If I could use the Matrix, I could author reports in 10% of the time.Ideas?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-20 : 07:36:27
|
| Transact-SQL has the CUBE and ROLLUP operators that will generate additional summary rows for things like subtotals and grand totals. Take a look in Books Online under "CUBE" and "ROLLUP". With a tiny amount of query twiddling you can format these rows with a value of "Total", and they should pivot in a Matrix report exactly as you want them to. |
 |
|
|
|
|
|
Aggregates in Reporting Services