SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Regarding Analysis services


Regarding Analysis services

Author
Message
alokkumar_srivastava
alokkumar_srivastava
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 54
Hi All,

I want to get sector wise revenue in percentage form Measure & dimension I have mention below :

Measures:

[Measures].[Total Revenue]

Dimension data :

[Dimension Sector].[Sector Group].&[Energy]
[Dimension Sector].[Sector Group].&[N/A]
[Dimension Sector].[Sector Group].&[Services]
[Dimension Sector].[Sector Group].&[Utilities]

I am running this Query :


With Member [Revenue Energy]
As [Measures].[Total Revenue], [Dimension Sector].[Sector Group].&[Energy]

Select [Measures].[Revenue Energy]
On columns from [EUS Fin Mart]



It’s showing me execution error as

Executing the query ...
Query (2, 50) Parser: The syntax for '.' is incorrect.
Execution complete

At [Dimension Sector].

In the query mention below .


With Member [Revenue Energy]
As [Measures].[Total Revenue], [Dimension Sector].[Sector Group].&[Energy]

Select [Measures].[Revenue Energy]
On columns from [EUS Fin Mart]

Please help me .


Regards,
Alok
Boyan Penev
Boyan Penev
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: 125
Try:

With Member [Revenue Energy]
As ([Measures].[Total Revenue], [Dimension Sector].[Sector Group].&[Energy])

Select [Measures].[Revenue Energy]
On columns from [EUS Fin Mart]

Boyan Penev
alokkumar_srivastava
alokkumar_srivastava
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 54
Boyan,

Thanks to reply it's working now . But I am facing one problem , Can you please tell me how i can add this particular sector wise revenue in Cubes So i can directly drag it as Column for generation of the Reports .

Thanks & Regards,
Alok
Boyan Penev
Boyan Penev
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: 125
If you want to have this as a calculated measure in your cube you can create one from Visual Studio Business Intelligence Dev Studio.

You may find this useful:

http://msdn.microsoft.com/en-us/library/ms166568.aspx

Boyan Penev
alokkumar_srivastava
alokkumar_srivastava
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 54
Hi Boyan,
Thaks to reply , What i understand from Calculated member is that we can perform different operation on measure data as par the business requirement . But after reading that details mention in Calculated member i found that I can not perform any mathematical operation between measure & dimension directly because in dimension we are having only details like Dimension Sector I have : Energy ,Services , Utilities . and in measure like revenue i Have numeric data like revenue in some million value .

Like there is any other way so that after running this query (With Member [Revenue Energy]
As ([Measures].[Total Revenue], [Dimension Sector].[Sector Group].&[Energy])
Select [Measures].[Revenue Energy]
On columns from [EUS Fin Mart]

)so I can put directly it as measure value.

Regards,
Alok
Boyan Penev
Boyan Penev
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: 125
Well, if you want to get Energy Revenue - Utility Revenue you could write MDX to do it. You can either create [Energy Revenue], [Utility Revenue], and [Energy - Utility Revenue] which subtracts the two calculated measures, or you can alternatively just create the [Energy - Utility Revenue] and write the MDX to calculate it. In example:

WITH MEMBER [Energy - Utility Revenue]
AS
([Measures].[Total Revenue], [Dimension Sector].[Sector Group].&[Energy])-([Measures].[Total Revenue], [Dimension Sector].[Sector Group].&[Utility])
SELECT [Measures].[Energy - Utility Revenue] ON COLUMNS
FROM [EUS Fin Mart]

Boyan Penev
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