sum multiples matrix SubTotals

Question

sum multiples matrix SubTotals

Marcelo.Pereira

SSC Enthusiast

Points: 138
More actions
August 7, 2008 at 5:08 pm

#195622

Hi,
I have many matrix in a report and each one have a Subtotal.
I need sum all SubTotals. How can I to do this?
thanks,
Marcelo.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 1

Sum of all individual items plus their sub-totals is always twice as large as the subtotals themselves. Sum everything and divide by 2.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 2

i need an example or a report to understand your problem. is this what trying to get?

[font="Courier New"] |jun |jul |aug |total

aaa | 10 | 20 | 15 | 45

bbb | 5 | 7 | 10 | 22

-----+----+----+----+-----

total| 15 | 27 | 25 | 67[/font]

Marcelo.Pereira SSC Enthusiast Points: 138 More actions · Answer 3

OK. Per example:

first matrix

|jun |jul |aug |total

aaa | 10 | 20 | 15 | 45

bbb | 5 | 7 | 10 | 22

-----+----+----+----+-----

total| 15 | 27 | 25 | 67

second matrix

|jun |jul |aug |total

aaa | 20 | 30 | 45 | 95

bbb | 5 | 7 | 10 | 22

-----+----+----+----+-----

total| 25 | 37 | 55 | 117

i need to do this:

1 - total| 15 | 27 | 25 | 67

2 - total| 25 | 37 | 55 | 117

-------------------------------

3 - total 40| 64 | 80 | 184

do you understand?

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 4

are the two matrices coming from 2 datasets or 1 dataset? if it's a single dataset, then create another matrix with a single row grouping that equates to whatever distinguishes matrix 1 from matrix 2.

if it's 2 datasets, it will be much more difficult.

Marcelo.Pereira SSC Enthusiast Points: 138 More actions · Answer 5

Marcelo.Pereira

SSC Enthusiast

Points: 138

August 8, 2008 at 8:45 am

#855259

there is one dataset for each matrix.

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 6

marcelo (8/8/2008)
there is one dataset for each matrix.

Oy! Can the datasets be combined?

Marcelo.Pereira SSC Enthusiast Points: 138 More actions · Answer 7

No, because my querys using MDX and differents hierarchys.

Any suggestion?

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 8

marcelo (8/8/2008)
OK. Per example:
first matrix
|jun |jul |aug |total
aaa | 10 | 20 | 15 | 45
bbb | 5 | 7 | 10 | 22
-----+----+----+----+-----
total| 15 | 27 | 25 | 67
second matrix
|jun |jul |aug |total
aaa | 20 | 30 | 45 | 95
bbb | 5 | 7 | 10 | 22
-----+----+----+----+-----
total| 25 | 37 | 55 | 117
i need to do this:
1 - total| 15 | 27 | 25 | 67
2 - total| 25 | 37 | 55 | 117
-------------------------------
3 - total 40| 64 | 80 | 184
do you understand?

Sure... like I said, divide the sum of the union of the two result sets, including the totals, by 2 and you're done.

The "proof"...

[font="Courier New"] |jun |jul |aug |total

+ aaa | 10 | 20 | 15 | 45 -- + bbb | 5 | 7 | 10 | 22 > Result set #1 including total

+ total| 15 | 27 | 25 | 67 --/

+ aaa | 20 | 30 | 45 | 95 -- + bbb | 5 | 7 | 10 | 22 > Result set #2 including total

+ total| 25 | 37 | 55 | 117 --/

-----+----+----+----+-----

SUM 80 128 160 368 --Unioned sum of everythin including totals

-----+----+----+----+-----

/2 40 64 80 184 --Divide by 2 to get final correct answer [/font]

The code...

SELECT SUM(Jun)/2 AS Jun, SUM(Jul)/2 AS Jul, SUM(Aug)/2 AS Aug, SUM(Total)/2 AS Total

FROM (--==== Derived table "d" creates sums for each result set

SELECT SUM(Jun) AS Jun, SUM(Jul) AS Jul, SUM(Aug) AS Aug, SUM(Jun+Jul+Aug) AS Total

FROM ResultSet1

UNION ALL

SELECT SUM(Jun) AS Jun, SUM(Jul) AS Jul, SUM(Aug) AS Aug, SUM(Jun+Jul+Aug) AS Total

FROM ResultSet2) d

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 9

marcelo (8/8/2008)
No, because my querys using MDX and differents hierarchys.
Any suggestion?

no simple ones. as you've discovered, you can't access the intermediate summaries of a matrix. (in the example, you can't access the monthly subtotals).

you can combine multiple MDX queries by creating a linked server to the analysis server and then using openquery() to get the results. of course, you won't be able to use the MDX query designer in reporting services to edit the actual query.

your sole dataset's query would then become:

[font="Courier New"]select 1 as batch, ...columns... from openquery( SSAS, 'MDX query 1' )

union all

select 2 as batch, ...columns... from openquery( SSAS, 'MDX query 2' )[/font]

from there, your first two matrices would be filtered by [batch] and the summary matrix would be grouped on [batch] only.

Marcelo.Pereira SSC Enthusiast Points: 138 More actions · Answer 10

Hi. I can't to do this.

Can you give me a pratice example using "union all" and "openquery" for MDX?

Can I to do this when my query use differents cubes?

thanks,

Marcelo.

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 11

Sorry Marcello... a bit of ignorance on my part showing... didn't realize you were doing it in MDX and I know just about nothing about MDX. My appologies.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)