Sign In






Prism Business Intelligence Tools - Tips and How-to's

This is where we post tips and how-to's in response to questions we get from Prism users.

January 2010 - Posts

  • Exception Highlighting / Conditional Formatting in the Pivot

    Having a pivot table with data in it is not always enough. Sometimes, you want to display different colors for different value ranges.

    In order to see coloring by values (Threshold), I will create a new custom string field [Indicator] in the ElastiCube Manager.

    ifstring(([Amount]>50), 'Green', 'Red')

     

    Now open PrismCubed, create a new Pivot and add Customer and Indicator dimensions on the rows and Amount measure on the columns.

     

    Using the span selection on a member, select the Red member such that all Red members and Amount values will be selected and set its back color to red.
    Do the same for the Green member.

     

    Final step is to minimize the Indicator field such that it won't be visible.

     

    Posted Jan 21 2010, 09:21 PM by Adi with no comments
    Filed under: , , ,

  • Converting seconds field to HH:MM:SS format in ElastiCube

    We often receive questions from users who need to create special/custom fields. Here, I am happy to share with you the way you can convert an INT field [Seconds] into HH:MM:SS string format

    First, we will need to add three custom INT fields (Hour, Min, Sec) to our table:

    1. Hour Field = div([Seconds],3600)

    2. Min Field  = div(Mod([Seconds],3600),60)

    3. Sec Field = Mod([Seconds],60)

    Finally, we will create a fourth field to contain the final HH:MM:SS string (HourRep). We do this by concatenating the three values, separated by colons (:) and we also include a '0' before any single-digit values. This is the code:

    concat(
        concat(
            concat(
        -- Get hours indication depending on number of resulting digits.
        ifstring(([Hour]< 10), concat('0',ToString([Hour])),ToString([Hour])),':'),
            concat(
        -- Get minutes indication depending on number of resulting digits.
        ifstring(([Min]< 10), concat('0',ToString([Min])),ToString([Min])),':')),
        -- Get seconds indication depending on number of resulting digits.
        ifstring(([Sec]< 10), concat('0',ToString([Sec])),ToString([Sec])))

     


     

    Good luck!

     

    Posted Jan 20 2010, 10:42 PM by Adi with no comments
    Filed under: , ,