Search code examples
reporting-servicesssasmdx

Apply Sourcecode in SSAS

Direction:
Use the KPI [Measures].[Profit Margin] inside of SSAS:s tab named Calculation and then use it in my SSRS report and other user

Obstacle:
I tried applying the source code in "Named set" or "calculated set", in SSAS, but it didin't work at all. I don't know how to apply the source code inside of SSAS.

Information:
*I'm using SQL server 2012
*Today, I have the source code in my SSRS report in the query designer view, inside of dataset, but I want the code to be inside of SSAS that I can allow users to use the same source code in their report.
*The report's data source is SSAS in my SSRS. MDX code only.
*The source code can be used for AdventureWorks.

![enter image description here][1]

    WITH 
MEMBER
[Measures].[Profit Margin] AS 
([Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost] ) / [Measures].[Internet Sales Amount] ,
FORMAT_STRING="Percent"
SELECT 
{
    [Measures].[Internet Sales Amount], 
    [Measures].[Internet Total Product Cost], 
    [Measures].[Profit Margin] 
}
ON COLUMNS ,
{ 
    [Date].[Calendar].[Calendar Year].&[2007], 
    [Date].[Calendar].[Calendar Year].&[2008] 
}
ON ROWS
FROM [Adventure Works]
Solution

  • You can use the calculated measures to create the [Measures].[Profit Margin]. This option can be found in the Calculations tab in the cube project. Here is a sample:

    CREATE MEMBER
[Measures].[Profit Margin] AS 
([Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost] ) / [Measures].[Internet Sales Amount] ,
FORMAT_STRING="Percent"