Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Another Format String - Color style question

  1. #1
    Join Date
    Mar 2006
    Posts
    142

    Question Another Format String - Color style question

    Is it possible to specify a Format String / style based on a MDX expression. I am thinking using the expression= syntax might provide this. Here is what I am trying to do. I have a measure slicied by Time. If the CurrentMember is < the same member for the previous time period, then show the CurrentMember in RED. If the CurrentMember is > the same memeber for the previous time period, then show the CurrentMember in GREEN.

    So I envision having a grid such as the following:


    SALES Measure
    2005 2006 2007
    Product A $100 $200 $150
    Product B $200 $50 $100
    Product C $100 $100 $100


    I want the style colors to look at the prevois Time SLice for the same product to help indicate a upward (Green) or downward (red) trend by comparing the current vlaue to the value from teh previous year.

    For Product A: The cells would be:
    2005 $100 no color
    2006 $200 GREEN (because $200 > $100)
    2007 $150 RED (because $150 < $200)

    I am new to MDX but it seems like maybe the ParrallelPerios() or LastPeriods() function might give me what i want.

    Can I Put a MDX expression in the CalculatedMemberProperty such as my example?

    Does anyone have a sample of what this would look like?

    Thanks,
    Bill

  2. #2
    Join Date
    Mar 2006
    Posts
    142

    Default Soultion :

    I figured out the proper MDX to do this. here is th solution to my original question. Use the CurrentMember and PrevMember functions:

    In my .xaction file, I have a calculated Measure defined: GPUnit (Gross Prfit per Unit). When the value of this measure for one Year is greater than the previous year, the cell is colored green. When it's less, the cell is colored red.


    with member [Measures].[GPUnit] as '([Measures].[Actual Margin] / [Measures].[Shipped Units])', format_string = IIf((([Measures].[GPUnit],[Fiscal Time].CurrentMember) < ([Measures].[GPUnit],[Fiscal Time].CurrentMember.PrevMember)), "|$#,##0.00|style='red'", IIf((([Measures].[GPUnit],[Fiscal Time].CurrentMember) > ([Measures].[GPUnit],[Fiscal Time].CurrentMember.PrevMember)), "|$#,##0.00|style='green'","$#,##0.00"))


    - Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.