Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Report problem in Analysis Services


Report problem in Analysis Services

Author
Message
Viswanath S. Dhara
Viswanath S. Dhara
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
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
denglishbi
denglishbi
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1362
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 helpsSmile

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viswanath S. Dhara
Viswanath S. Dhara
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
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
denglishbi
denglishbi
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1362
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search