Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Report problem in Analysis Services Expand / Collapse
Author
Message
Posted Thursday, June 26, 2008 6:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 3, 2008 7:06 PM
Points: 52, Visits: 71
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
Post #524097
Posted Friday, June 27, 2008 12:36 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
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
Post #525279
Posted Wednesday, July 2, 2008 6:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 3, 2008 7:06 PM
Points: 52, Visits: 71
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
Post #527241
Posted Thursday, July 3, 2008 7:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
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
Post #528075
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse