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

Regarding Analysis services Expand / Collapse
Author
Message
Posted Tuesday, October 14, 2008 6:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 02, 2010 1:31 AM
Points: 24, 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



Post #585392
Posted Tuesday, October 14, 2008 6:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 30, 2012 7:15 PM
Points: 22, Visits: 121
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
Post #585883
Posted Tuesday, October 14, 2008 9:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 02, 2010 1:31 AM
Points: 24, 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
Post #585922
Posted Tuesday, October 14, 2008 9:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 30, 2012 7:15 PM
Points: 22, Visits: 121
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
Post #585928
Posted Tuesday, October 14, 2008 10:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 02, 2010 1:31 AM
Points: 24, 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


Post #585946
Posted Wednesday, October 15, 2008 12:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 30, 2012 7:15 PM
Points: 22, Visits: 121
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
Post #585974
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse