Running Totals

[katams]

I am tryng to display totals on the top row of any view when browsing a cube in asp.

For example:

The first view is:
Count Sales
AllStores 100 $100,000

When you drill on stores, it looks like this:

Count Sales
Store A 25 $10,000
Store B 25 $10,000
Store C 50 $80,000


I would like the view to look like this:
Count Sales
AllStores 100 $100,000
Store A 25 $10,000
Store B 25 $10,000
Store C 50 $80,000

Any ideas? Thanks in advance for help!

 

[issahar]

Hi Katams!

basing on FoodMart 2000 demo you can try the next...

Select {[Measures].[Sales Count], [Measures].[Store Sales]} ON COLUMNS, {Descendants([Store],[Store].[Store Name]),[Store].[All Stores]} ON ROWS FROM Sales
WHERE ([Time].[1997])


Please notify me if it helped you...
Have a nice day.
Issahar
senior software engineer

http://www.softwatch.com

Israel

 

[katams]

Hi Issahar!

I tried what you said and it worked. I also tried the generate function:

SET [Rowset] as 'GENERATE ({[Sites].[MarketRegion].&[1]}, {[Sites].[MarketRegion].currentmember, [Sites].[MarketRegion].currentmember.children}) '

and that worked, as well. Do you use this function much?

I'm trying to make the totals always showing dynamic, since I have it in an asp browser. It looks like the third party tools used for cube browsing only retain the first view only, as well.

Thanks so much for your help!

 

[cltalbott3]

Katams,

Try this query. Only thing you need to do is format the totals to show it as the others.

With member [Store].[Total] as 'Sum({[store].members }) '
Select {[Measures].[Sales Count], [Measures].[Store Sales]} ON COLUMNS, non empty {[Store].[Total], Descendants([Store],[Store].[Store Name]) } ON ROWS FROM Sales
WHERE ([Time].[1997])

 

[katams]

Thank you!

I also tried this to make it dynamic:

In my code I check to see if a member has children, and if so, I make it a hyperlink. So, if it has children, I'm going to display that along with the parent level, like this...

Select {[measures].[store sales], [measures].[sales count]} on columns,
{ [store].[store country].[usa],
[store].[store country].[usa].children
} on rows
from [sales]