November 5, 2010 at 8:55 am
I have a calculated member that shows total of a selected date range that I use as a denominator for an average calc. However, excel seems to not recognize the "existing" function. Does anyone have a work around?
Here is my current script which returns the following result in SSMS:
with
set [dates] as
{existing([Dim Date Time].[Calendar Date].[Calendar Date])}
member [total] as
sum([dates]
,[Measures].[Test Volume])
select {[Test Volume],[total]} on 0,
[Dim Date Time].[Calendar Date].[Calendar Date]
on 1
from [Test_DW]
where
{filter([Dim Date Time].[Year-Month-Day].members,
cstr([Dim Date Time].[Year-Month-Day].member_caption) = "2010-10-01 00:00:00").item(0)
:filter([Dim Date Time].[Year-Month-Day].members,
cstr([Dim Date Time].[Year-Month-Day].member_caption) = "2010-10-09 00:00:00").item(0)
}
Test Volume total
2010-10-01 00:00:00916169482
2010-10-02 00:00:00625969482
2010-10-03 00:00:00422569482
2010-10-04 00:00:001032169482
2010-10-05 00:00:00881769482
2010-10-06 00:00:00901069482
2010-10-07 00:00:00908269482
2010-10-08 00:00:00767069482
2010-10-09 00:00:00493769482
In excel the total column is pulling the total of all dates regardless of the filter selection by the user.
Any ideas would be most appreciated.
November 11, 2010 at 4:54 am
Jason,
assuming that you use the Pivot Table functionality to retrieve the data in Excel, you can control whether or not the totals include all values.
Just right-click the pivot table and select 'Table Options'; there you'll find the option to display only totals of members shown.
WM_JUSTMY2CENTS
Guenter
November 11, 2010 at 6:18 am
Guenter,
thanks for the reply.
I am using a pivot table to display the result set, but what I am trying to display is a calculated member from the cube based on the mdx that I posted... it is a total of all selected members that should appear against each member of the table. The sum totals that the pivot generates are not related to my problem.
regards,
Jason
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply