InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 22:56:12 GMT20http://www.sqlservercentral.com/Forums/Topic524097-147-1.aspxIt 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 rowsto this:non empty({ [first twelve], [Accounting Date].[Month].[Remaining Months]}* {[Procedures].[Billing Groups].[Billing Groups].ALLMEMBERS}) on rowsThu, 03 Jul 2008 07:57:56 GMTdenglishbihttp://www.sqlservercentral.com/Forums/Topic524097-147-1.aspxHi 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] asAggregate([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 rowsfrom [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?ThanksVishyWed, 02 Jul 2008 06:32:33 GMTViswanath S. Dharahttp://www.sqlservercentral.com/Forums/Topic524097-147-1.aspxIf 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:)Fri, 27 Jun 2008 12:36:51 GMTdenglishbihttp://www.sqlservercentral.com/Forums/Topic524097-147-1.aspxHi, 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!VishyThu, 26 Jun 2008 06:51:23 GMTViswanath S. Dhara