Regarding Analysis services

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » Regarding Analysis services

Regarding Analysis services

Rate Topic

Display Mode

Topic Options

Author

Message

alokkumar_srivastava

Posted Tuesday, October 14, 2008 6:18 AM

Grasshopper

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

Boyan Penev

Posted Tuesday, October 14, 2008 6:05 PM

Grasshopper

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

alokkumar_srivastava

Posted Tuesday, October 14, 2008 9:25 PM

Grasshopper

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

Boyan Penev

Posted Tuesday, October 14, 2008 9:41 PM

Grasshopper

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

alokkumar_srivastava

Posted Tuesday, October 14, 2008 10:44 PM

Grasshopper

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

Boyan Penev

Posted Wednesday, October 15, 2008 12:20 AM

Grasshopper

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 »

Permissions