Matrix Subtotals

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 percent of total sales and percent of total category sales?

Q1 Q2 Total

Beverages

Lemonade

Sales $ 100 150 250

Percent of Bev 44%

Percent of Total 19% ?How do I do these Percentages in a Matrix?

Soda

Sales $ 125 125 250

Percent of Bev

Percent of Total

Total Beverages 225 275 500

Snacks

Peanuts

Sales $ 100 150 250

Percent of Snacks

Percent of Total

Cookies

Sales $ 200 250 450

Percent of Snacks

Percent of Total

Total Snacks 300 400 700

Total Sales 525 675 1300

In 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?

Unfortunately, this is still a limitation of the matrix. While we would like to have addressed it for SQL 2005, we did not have enough time. It is very high on the list of items for the post 2005 release.|||Thanks for your reply.
I did get a work around, thanks to Brian Larson.
Maybe in 2005 SP1?|||Can you please tell us how?|||My work around did not work as well as I had hoped. I Did get an E-mail from Brian Welker that the Aggregates of Aggregates issue is about 3 on the priority list. He said that they were currently working on the reporting engine. I did not get an ETA. Having this functionality will be a huge time saver for me over what I do in Crystal Today.|||

OK I can give a partial work around for this.First of all let’s assume that the column group name is “Quarter”.

Getting the 19% figure is easy, it is Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, “Quarter”)

Getting the 44% is easy but you have to hard code some things.

Create another field called “Beverages”.In the calculated expression put in something like: IIF(Fields!Group.Value = “Beverages”, Fields!Sales.Value, 0).You may need to do some conversion to Decimal or etc.

Then you can use this formula to get the 44%: Sum(Fields!Sales.Value) / Sum(Fields!Beverages.Value, “Quarter”)

This is the best I can come up with for a work around until Microsoft can get the Aggregates issue on the drawing board.(They will in the coming year I would expect)

sql

Matrix Subtotals

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 percent of total sales and percent of total category sales?

Q1 Q2 Total

Beverages

Lemonade

Sales $ 100 150 250

Percent of Bev 44%

Percent of Total 19% ?How do I do these Percentages in a Matrix?

Soda

Sales $ 125 125 250

Percent of Bev

Percent of Total

Total Beverages 225 275 500

Snacks

Peanuts

Sales $ 100 150 250

Percent of Snacks

Percent of Total

Cookies

Sales $ 200 250 450

Percent of Snacks

Percent of Total

Total Snacks 300 400 700

Total Sales 525 675 1300

In 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?

Unfortunately, this is still a limitation of the matrix. While we would like to have addressed it for SQL 2005, we did not have enough time. It is very high on the list of items for the post 2005 release.|||Thanks for your reply.
I did get a work around, thanks to Brian Larson.
Maybe in 2005 SP1?
|||Can you please tell us how?|||My work around did not work as well as I had hoped. I Did get an E-mail from Brian Welker that the Aggregates of Aggregates issue is about 3 on the priority list. He said that they were currently working on the reporting engine. I did not get an ETA. Having this functionality will be a huge time saver for me over what I do in Crystal Today.|||

OK I can give a partial work around for this.First of all let’s assume that the column group name is “Quarter”.

Getting the 19% figure is easy, it is Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, “Quarter”)

Getting the 44% is easy but you have to hard code some things.

Create another field called “Beverages”.In the calculated expression put in something like: IIF(Fields!Group.Value = “Beverages”, Fields!Sales.Value, 0).You may need to do some conversion to Decimal or etc.

Then you can use this formula to get the 44%: Sum(Fields!Sales.Value) / Sum(Fields!Beverages.Value, “Quarter”)

This is the best I can come up with for a work around until Microsoft can get the Aggregates issue on the drawing board.(They will in the coming year I would expect)

Matrix Subtotals

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 percent of total sales and percent of total category sales?

Q1 Q2 Total

Beverages

Lemonade

Sales $ 100 150 250

Percent of Bev 44%

Percent of Total 19% ?How do I do these Percentages in a Matrix?

Soda

Sales $ 125 125 250

Percent of Bev

Percent of Total

Total Beverages 225 275 500

Snacks

Peanuts

Sales $ 100 150 250

Percent of Snacks

Percent of Total

Cookies

Sales $ 200 250 450

Percent of Snacks

Percent of Total

Total Snacks 300 400 700

Total Sales 525 675 1300

In 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?

Unfortunately, this is still a limitation of the matrix. While we would like to have addressed it for SQL 2005, we did not have enough time. It is very high on the list of items for the post 2005 release.|||Thanks for your reply.
I did get a work around, thanks to Brian Larson.
Maybe in 2005 SP1?|||Can you please tell us how?|||My work around did not work as well as I had hoped. I Did get an E-mail from Brian Welker that the Aggregates of Aggregates issue is about 3 on the priority list. He said that they were currently working on the reporting engine. I did not get an ETA. Having this functionality will be a huge time saver for me over what I do in Crystal Today.|||

OK I can give a partial work around for this.First of all let’s assume that the column group name is “Quarter”.

Getting the 19% figure is easy, it is Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, “Quarter”)

Getting the 44% is easy but you have to hard code some things.

Create another field called “Beverages”.In the calculated expression put in something like: IIF(Fields!Group.Value = “Beverages”, Fields!Sales.Value, 0).You may need to do some conversion to Decimal or etc.

Then you can use this formula to get the 44%: Sum(Fields!Sales.Value) / Sum(Fields!Beverages.Value, “Quarter”)

This is the best I can come up with for a work around until Microsoft can get the Aggregates issue on the drawing board.(They will in the coming year I would expect)

Matrix Report Calculation Issue.

Hi There,
I have this matrix report that shows sales for last couple of years and is
grouping on 5 different regions and separate years on different columns, I
want to calculate the % Sales for each region in another column. how do I
accomplish this in Reporting Services? I think the static cell on a matrix
don't allow this, I have been trying to somehow call the single cell name
from a Expression but no luck. I think this could be accomplish with some
code, if it is, can you please give some samples or direct me to a place
where I could research this topic a bit more?
any advices would be really appriaciated.
Thanks very much in advance.
MannyHi Manny:
There is a way to achieve this using "IIF" and "InScope" function. I did it
on a Matrix Report and it will dynamically calculate the percentage based the
Row/Column grouping.
Manny wrote:
>Hi There,
>I have this matrix report that shows sales for last couple of years and is
>grouping on 5 different regions and separate years on different columns, I
>want to calculate the % Sales for each region in another column. how do I
>accomplish this in Reporting Services? I think the static cell on a matrix
>don't allow this, I have been trying to somehow call the single cell name
>from a Expression but no luck. I think this could be accomplish with some
>code, if it is, can you please give some samples or direct me to a place
>where I could research this topic a bit more?
>any advices would be really appriaciated.
>Thanks very much in advance.
>Manny
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200612/1|||Can you give us a sample on how you accomplished this please?
Thanks,
Manny
pmak wrote:
>Hi Manny:
>There is a way to achieve this using "IIF" and "InScope" function. I did it
>on a Matrix Report and it will dynamically calculate the percentage based the
>Row/Column grouping.
>>Hi There,
>[quoted text clipped - 12 lines]
>>Manny|||Here is the example:
IIF(InScope("matrix1_RowGroup1"), IIF(InScope("matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup2")),IIF
(InScope("matrix1_Program"),IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!
Funding.Value,"matrix1_Program")/Sum(Fields!Funding.Value,
"matrix1_ColumnGroup1"),Sum(Fields!Funding.Value,"matrix1_Program")/Sum
(Fields!Funding.Value,"matrix1_ColumnGroup2")),""))IIF(InScope
("matrix1_RowGroup2"), IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup2")),IIF(InScope
("matrix1_RowGroup3"),IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value,"matrix1_Program")/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value,"matrix1_Program")/Sum(Fields!Funding.Value,
"matrix1_ColumnGroup2")),""))
Paul
Manny123 wrote:
>Can you give us a sample on how you accomplished this please?
>Thanks,
>Manny
>>Hi Manny:
>[quoted text clipped - 7 lines]
>>Manny
--
Message posted via http://www.sqlmonster.com

Matrix Rendering Problem?

Hello,
I have a matrix rendering problem that drives me crazy. Can't figure out
what it is for a couple of days. The problem is illlustrated below - note
that starting from "Min Value" all matrix rows are shifted down. "Max Time"
row shows minimum values, "Max Value" row shows time of maximum values, and
maximum values are displayed in the extra row that does not exist at all. The
strange thing is that this only happens when report is rendered as HTML via
url access. It works just fine in the Report Designer (both Preview and Debug
Local), and it renders correctly if exported to a different format such as
PDF or IMAGE. If anyone knows how to fix this, please advise. Thanks in
advance.
M T
W
Depth Vel Quant Depth Vel Quant Depth
Vel Quant
Total 0.255 0.239
0.232
Avg 2.85 1.88 0.255 2.79 1.83 0.239 2.74
1.82 0.232
Min Time 5:30 4:30 4:30 3:00 5:15 5:15 5:15 2:45
2:45
Min Value
Max Time 1.66 0.50 0.033 1.66 0.51 0.032 1.63 0.49
0.033
Max Value 17:00 20:15 20:15 9:45 9:45 9:45 8:30 7:30
9:15
3.77 2.53 0.449 3.77 2.23 0.410 3.65
2.32 0.384
--
Regards,
Alex Korygin.Sorry for the formatting. Hopefully this one will show better.
M T W
Depth Vel Quant Depth Vel Quant Depth Vel Quant
Total 0.255 0.239 0.232
Avg 2.85 1.88 0.255 2.79 1.83 0.239 2.74 1.82 0.232
Min Time 5:30 4:30 4:30 3:00 5:15 5:15 5:15 2:45 2:45
Min Value
Max Time 1.66 0.50 0.033 1.66 0.51 0.032 1.63 0.49 0.033
Max Value 17:00 20:15 20:15 9:45 9:45 9:45 8:30 7:30 9:15
3.77 2.53 0.449 3.77 2.23 0.410 3.65 2.32 0.384
"Alex Korygin" wrote:
> Hello,
> I have a matrix rendering problem that drives me crazy. Can't figure out
> what it is for a couple of days. The problem is illlustrated below - note
> that starting from "Min Value" all matrix rows are shifted down. "Max Time"
> row shows minimum values, "Max Value" row shows time of maximum values, and
> maximum values are displayed in the extra row that does not exist at all. The
> strange thing is that this only happens when report is rendered as HTML via
> url access. It works just fine in the Report Designer (both Preview and Debug
> Local), and it renders correctly if exported to a different format such as
> PDF or IMAGE. If anyone knows how to fix this, please advise. Thanks in
> advance.
> M T
> W
> Depth Vel Quant Depth Vel Quant Depth
> Vel Quant
> Total 0.255 0.239
> 0.232
> Avg 2.85 1.88 0.255 2.79 1.83 0.239 2.74
> 1.82 0.232
> Min Time 5:30 4:30 4:30 3:00 5:15 5:15 5:15 2:45
> 2:45
> Min Value
> Max Time 1.66 0.50 0.033 1.66 0.51 0.032 1.63 0.49
> 0.033
> Max Value 17:00 20:15 20:15 9:45 9:45 9:45 8:30 7:30
> 9:15
> 3.77 2.53 0.449 3.77 2.23 0.410 3.65
> 2.32 0.384
>
> --
> Regards,
> Alex Korygin.

Matrix Questions

I have been running into a couple of problems with my Matrix style reports:

First my Matrix is a drillthrough that looks like this

Year Group QuarterGroup Detail name Detail name Group1 Group2 Group3 Detail data Detail Data

1. How do I interactively sort by a detail column name, right now I'm using a switch statement in the Group1-3 sorts to allow the user to sort, the problem is that the user has to choose how to sort before running the report and I can't set the sort direction. Sort direction doesn't take an expression.

2. How do I collapse a Detail name column, when I set the visible property to false on the text boxes in the column all they disappear but the Year and Quarter Group textboxes don't resize.

3.

I would also like to have an drillthrough matrix report that looks like this

Year Group QuarterGroup Detail % Change over previous time period Group1 Group2 Group3 Detail data % of change from last quarter or year

What expression should I place in the % of change from last quarter or year box?

Anybody?

Matrix Queries

I've just started using report martix's (RS2005) and have a couple of
questions.
Is it possible to change the way a summary for a column calculates. By
default it does a sum but on a certain row I want an average. can this been
done or am I stuck with a sum.
Also i would like to be able to have a static heading right at the top of
the matrix, at the moment i'm using the top left cell but this isn't ideal
--
Cheers
Luke
http://beakersoft.blogspot.comOfcourse you can use "Average", Give a title which is not changing, I mean
different values. then it will display single value. If you want the title to
be same then might as well you can think of table instead of matrix.
Amarnath, MCTS.
"lukethepunk" wrote:
> I've just started using report martix's (RS2005) and have a couple of
> questions.
> Is it possible to change the way a summary for a column calculates. By
> default it does a sum but on a certain row I want an average. can this been
> done or am I stuck with a sum.
> Also i would like to be able to have a static heading right at the top of
> the matrix, at the moment i'm using the top left cell but this isn't ideal
> --
> Cheers
> Luke
> http://beakersoft.blogspot.com