Exclude certain rows from the grand total

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.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Exclude certain rows from the grand total

Author

Topic

smac1979
Starting Member

2 Posts

Posted - 2010-01-07 : 20:43:29

Hi!

I need to exclude some rows from the totals that SSAS do.

Letra Factura Valor
1 1 100
2 200
3 300
Total 600
2 2 10
Total 10
5 5 20
Total 20
Grand Total 630

What I want is to have only the sums of the "Facturas" equal to "Letra", but preverse all the "Facturas".

Letra Factura Valor
1 1 100
2 200
3 300
Total 100
2 2 10
Total 10
5 5 20
Total 20
Grand Total 130

How can I do this in MDX, or in SSAS 2005?

Thanks,
Sérgio Cardoso

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-08 : 13:04:02

I got this from another site

One way around this would be to add a derived Boolean column to your dataset (assuming your data source is SQL Server, using a CASE statement or similar) showing whether the row should be excluded from the table where the filter is required.
You can then change your table filter to check the indicator, and use a expression to carry out a conditional sum to aggregate the correct total. Something like

=SUM(Iif(Fields!ExcludeRow.Value = True,0,Fields!ValueToSum.Value))

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 13:14:09

quote:
Originally posted by yosiasz

I got this from another site

One way around this would be to add a derived Boolean column to your dataset (assuming your data source is SQL Server, using a CASE statement or similar) showing whether the row should be excluded from the table where the filter is required.
You can then change your table filter to check the indicator, and use a expression to carry out a conditional sum to aggregate the correct total. Something like

=SUM(Iif(Fields!ExcludeRow.Value = True,0,Fields!ValueToSum.Value))

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Yosiaz OP is asking for MDX expression. The one you've given works fine in report but its not MDX expression

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-08 : 13:19:37

oops sorry! But you get the idea that you can use the same filtering on some MemberProperty to filter out the stuff you do not want.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

smac1979
Starting Member

2 Posts

Posted - 2010-01-09 : 14:42:57

Thanks for the help.
We talk to the client and we agree to do it in another way (changing the datamodel).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources