sql server reporting services: Divide by a first row of resultset in a matrix -- sql server reporting services

You're in the

sql server reporting services

group:

Divide by a first row of resultset in a matrix

Divide by a first row of resultset in a matrix

Kaisa M. Lindahl
1/31/2005 4:54:27 PM

sql server reporting services:

I'm creating my first matrix based report, and find that some things that
work with tables doesn't work the same way with matrix.
My resultset comes from an MDX statement. It looks something like this

........................................Registered hours .............Amount
..................Total...........................10.........................
1000
Month1.....Group 1........................6...........................600
..................Group
2........................4...........................400

..................Total...........................12.........................
1200
Month2.....Group 1........................8...........................800
..................Group
2........................4...........................400

The customer wants to display the values for the months next to each other,
for each value. Now, I've managed to get this with a matrix, having the
[static group] ordered on top. (Thanks to Ohjoo Kwon).

BUT one of my columns is supposed to be "Group Result as % of total result."
Meaning for Group1 in Month1 I need to divide 600 on the Total Amount. The
first row in my result set is always a calculated total for that month. So,
when using tables, I usually just do =Amount/First(Amount), and that works
like a charm. When I try to do that in a matrix, all I get in return is
"100%". So my guess is that in a matrix, I can't use First() the same way as
in a table. But how can I do the division? How to divide "Any Row" by "First
Row"?

All help appreciated.

Kaisa M. Lindahl

Re: Divide by a first row of resultset in a matrix

Robert Bruckner [MSFT]
2/1/2005 8:56:14 PM

You have to provide the "right" scope for the aggregate function. Try one of
the two following expressions to divide by the first row:
=Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "DataSetName")
=Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "MatrixName")

--
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

Re: Divide by a first row of resultset in a matrix

Kaisa M. Lindahl
2/3/2005 2:02:29 PM

Thanks.
It worked with
=Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "DataSetName")

Kaisa

[quoted text, click to view]

Re: Divide by a first row of resultset in a matrix

Kaisa M. Lindahl
2/9/2005 2:44:37 PM

I just realized an error in my original code. What Robert suggested (below)
works for dividing by the very first row of the result set.
Unfortunately, I need to divide the right number with the right first row...
I'll explain

My result looks like this:
...............................Measures_Amount ...
................AllGrps....100
................Grp1..........30
200409...Grp2..........30
................Grp3.........40
................AllGrps.....120
................Grp1..........40
200309...Grp2..........40
................Grp3..........40

My report looks someting like this

..................Measures_Amount......%ofTotalAmt
Group..........200309|200409....200309|200409
Total..................120|...100..........100%|...100%
Grp1....................40|.....30............33%|.....30%
Grp2....................40|.....30............33%|.....30%
Grp3....................40|.....40............33%|.....40%

Today I realized that my statement
=First(Fields!Measures_Amount.Value)/ First(Fields!Measures_Amount.Value,
"OmsetningHT")
doesn't work quite as planned. This will divide all rows with the very first
row, which is the total for 200409. Suddenly all results from 200309 are
divided by the total for 200409, which is wrong.

So I need a way to divide a group result by the correct total in my matrix.
The months are organized in a column group.

All help appreciated!

Kaisa M. Lindahl

[quoted text, click to view]

Re: Divide by a first row of resultset in a matrix

Chris McGuigan
3/7/2005 8:09:35 AM

If you can group on year or whatever is relevant in your scenario, then
within that group, =First(Fields!Amount.Value) should refer to the
first value in that group. As Robert said, it's all about scope with
aggregate functions. By grouping the data, you are limiting the scope,
which is what you want.

Chris

[quoted text, click to view]

Re: Divide by a first row of resultset in a matrix

Kaisa M. Lindahl
3/7/2005 3:47:17 PM

This problem just resurfaced.
Anyone want to give it some thought?

Is it possible to check for something like
if month = Parameters!FirstPeriod.Value then
'use this row
end if ?

Kaisa

[quoted text, click to view]

Re: Divide by a first row of resultset in a matrix

Chris McGuigan
3/8/2005 1:47:27 AM

Kaisa, in general, leaving the scope out lets RS decide for you.
Generally this will work the way you want.

Chris

[quoted text, click to view]

Re: Divide by a first row of resultset in a matrix

Kaisa M. Lindahl
3/8/2005 9:20:51 AM

OK, thanks to both. I finally figured it out. :)

I'm still getting confused by when to use what scope. The final solution was
to change from
First(Fields!Amount.Value, "Dataset") to First(Fields!Amount.Value,
"MatrixColumnGroupName").

Kaisa M. Lindahl

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn