June 26, 2008 at 6:51 am
Hi,
I am new to Analysis services. I have a problem with a report.
1. The report displays amounts by month for a period the user inputs.
2. The user wants only the first twelve records to show the amount (without summation) and a single record summing up the amount for the rest of the records.
I looked at the MDX query, measures and dimensions but am unable to figure it out.
The report uses time dimension.
Thanks for the help in advance!
Vishy
June 27, 2008 at 12:36 pm
If I am following what you are getting at then you want to display the first twelve months of data broken out by each month and then the remaining months. Not sure at what point you want to start with, but you would want to use named sets to create your groupings of months and then aggregate the remaining ones into a single member. Here is an example against the AdventureWorks sample SSAS cube.
with set [first twelve] as head([Date].[Month Name].[Month Name], 12)
set [remaining] as except({[Date].[Month Name].[Month Name]}, [first twelve])
member [Date].[Month Name].[Remaining Months] as Aggregate([remaining])
select measures.defaultmember on columns,
non empty ({[first twelve], [Date].[Month Name].[Remaining Months] }) on rows
from [analysis services tutorial]
So the first set is creating the individual months that you want to view, the second set is excluding the first set and summing up the remaining months, and then the member is aggregating the remaining months into a single member to display.
This is one way to get at what you want. Hope this helps:)
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
July 2, 2008 at 6:32 am
Hi Dan,
That worked. Thanks a lot. However we have another problem.
Here's our qury based on the query you sent.
/*
with set [first twelve] as head([Accounting Date].[Month].[Month].AllMembers, 12)
set[remaining] as except({[Accounting Date].[Month].[Month]},
[first twelve])
member [Accounting Date].[Month].[Remaining Months] as
Aggregate([remaining])
select {[Measures].[Procedure Charge Amt],
[Measures].[Transaction Amount],
[Measures].[Payment Trans Amount]
} ON COLUMNS,
non empty({
[first twelve], [Accounting Date].[Month].[Remaining Months]
--* [Procedures].[Billing Groups].[Billing Groups].ALLMEMBERS
}) on rows
from [VCube]
*/
Now I want to display the Billing Groups. See the last line commented.
When I tried to execute I am getting an error message "Dimensionality Exceded". Is this becuase of the way [first twelve] is declared?
Is there a workaround for this?
Thanks
Vishy
July 3, 2008 at 7:57 am
It appears that you are missing some curly braces within your crossjoin:
change this section:
non empty({
[first twelve], [Accounting Date].[Month].[Remaining Months]
--* [Procedures].[Billing Groups].[Billing Groups].ALLMEMBERS
}) on rows
to this:
non empty({ [first twelve], [Accounting Date].[Month].[Remaining Months]}
* {[Procedures].[Billing Groups].[Billing Groups].ALLMEMBERS}) on rows
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply