• Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About
Microsoft Data & AI

SQL Server Analysis Services

Time Calculations: Formatting percentage calculations in multidimensional cubes

1/17/2016

1 Comment

 
I received this little tip from a colleague and thought I'd pass it on hoping to be of help to someone else.

Problem: Dynamic time calculations in multidimensional cubes are not by nature formatted.  It would be nice to at least return percent time calculations as percentages.

Problem Illustrated: In the screen print below, [Prior Period % Change], [YTD % Change] ... columns are presented in a pivot table by default without a percentage sign.
Picture
Desired Result: Automatically format percentage calculations appearing in a pivot table with a percentage sign.

Assumption: You are familiar with creating MDX time calculations that can be applied against any measure in a multidimensional cube.

Solution: Scope for your percentage time calcs at the end of your MDX script, and format them as a percent.
--format percentages
SCOPE(
    {[Date Calculations].[Date Calculation Name].[Prior Period % Change],
     [Date Calculations].[Date Calculation Name].[YTD % Change],
     [Date Calculations].[Date Calculation Name].[QTD % Change from Prior Year],
     [Date Calculations].[Date Calculation Name].[QTD % Change from Prior Quarter],
     [Date Calculations].[Date Calculation Name].[MTD % Change from Prior Year],
     [Date Calculations].[Date Calculation Name].[MTD % Change from Prior Month],
     [Date Calculations].[Date Calculation Name].[WTD % Change from Prior Year],
     [Date Calculations].[Date Calculation Name].[WTD % Change from Prior Week]}
    );
    THIS = ([Measures].currentmember);
    FORMAT_STRING(this)="Percent";
END SCOPE;

Result Illustrated:  Without having to format the percentage calculation columns or rows in Excel, they will come in formatted when selected from PivotTable Fields. 
Picture
Important Note!!  This only works with NEW  pivot tables.  If your pivot table or chart has already been created, doing a <Data Ribbon --> Refresh> in Excel will not update the formatting.  You'll see your new MDX work when you <Insert> new.

Detail MDX Script Illustrated:
SCOPE ([Date Default].[Y-Q-M-D Hier].MEMBERS);  
--YEAR CALCULATIONS
    --Year To Date


    [Date Calculations].[Date Calculation Name].[YTD] =
     AGGREGATE
     (
        {[Date Calculations].[Date Calculation Name].[Current Period]} *
          PERIODSTODATE
          (
            [Date Default].[Y-Q-M-D Hier].[Year],
            [Date Default].[Y-Q-M-D Hier].CURRENTMEMBER
          )
     );           


    --Year to Date Prior Year
    [Date Calculations].[Date Calculation Name].[YTD Prior Year] =
     AGGREGATE
     (
        {[Date Calculations].[Date Calculation Name].[Current Period]} *
        PERIODSTODATE
        (
           [Date Default].[Y-Q-M-D Hier].[Year],
           PARALLELPERIOD
           (
              [Date Default].[Y-Q-M-D Hier].[Year],
              1,
              [Date Default].[Y-Q-M-D Hier].CURRENTMEMBER
        )
     )
     );  

END SCOPE; 

--PRIOR PERIOD & YEAR

    --Prior Period
    [Date Calculations].[Date Calculation Name].[Prior Period]=
    AGGREGATE
        ([Date Default].[Y-Q-M-D Hier].CurrentMember.PrevMember,
        [Date Calculations].[Date Calculation Name].[Current Period]);  


​--calculate change
[Date Calculations].[Date Calculation Name].[Prior Period Change] = 
    [Date Calculations].[Current Period] - [Date Calculations].[Prior Period];           

[Date Calculations].[Date Calculation Name].[YTD Change] = 
    [Date Calculations].[YTD] - [Date Calculations].[YTD Prior Year];  



--calculate % change
[Date Calculations].[Date Calculation Name].[Prior Period % Change] = 
    DIVIDE (([Date Calculations].[Date Calculation Name].[Current Period] - [Date Calculations].[Date Calculation Name].[Prior Period]), 
         [Date Calculations].[Date Calculation Name].[Prior Period],0);            

[Date Calculations].[Date Calculation Name].[YTD % Change] = 
    DIVIDE (([Date Calculations].[Date Calculation Name].[YTD] - [Date Calculations].[Date Calculation Name].[YTD Prior Year]), 
         [Date Calculations].[Date Calculation Name].[YTD Prior Year],0);         


--format percentages
SCOPE(
    {[Date Calculations].[Date Calculation Name].[Prior Period % Change],
     [Date Calculations].[Date Calculation Name].[YTD % Change]}
    );
    THIS = ([Measures].currentmember);
    FORMAT_STRING(this)="Percent";
END SCOPE;
1 Comment
Sadrul Jamali
7/7/2017 11:33:46 am

Thank You for the solution Delora!! I needed to format the % change in Time Calculation. Couldn't have done it without this. Worked like a charm!!!!!!!!!

Reply



Leave a Reply.

    Categories

    All
    Cache
    DBA
    Excel
    Extended Events
    HOLAP
    MOLAP
    Multidimensional
    ROLAP
    Server Mode
    SSAS
    Tabular

    RSS Feed

    Tweets by @SqlServerSME
Powered by Create your own unique website with customizable templates.
  • Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About