Dynamic Pivot/Table Header Text

Comments

3 comments

  • Avatar
    Tri Anthony Situmorang

    Hi Ganesh,

    Are those three Value columns simple aggregation with a measured value (formula filter)?

    If so, instead of having three columns, you can have only one Value column (just the aggregation, remove the measured value), and add the 'custom_label' in the Columns panel.

    That way, the headers will be dynamic depending on the client you are looking at.

    1
    Comment actions Permalink
  • Avatar
    Ganesh Naikare

    Thank you, Tri! That solution worked! It's exactly what I wanted.

     

    0
    Comment actions Permalink
  • Avatar
    Ganesh Naikare

    The above solution helped me to get all required columns with dynamic labels show up in report as expected. However there is one last column that needs calculation based on previous columns and I couldn't figure out solution after trying multiple ways through Sisense SQL in data model object. 

    Last column in this report is 'Percentage Target Territory Developed' and formula for this value in each row is based on three columns in this report i.e. ((Active + Pending) / Target Clients in State)  x 100 . Her is how report looks now...

     

    Currently last percentage column is showing addition of Active and Pending (Contracted - organization custom label) based on SQL that I added. But I can't use value of Target Clients in State in formula as it's calculated dynamically based on state. Following image shows part of the query that calculates target clients and %. There is connected Dim Client State table/elasticube object, that allows data to be listed on separate rows per state.

     

    SELECT cl.[id], 'A - Target Clients in State' AS territory_development_columns

    FROM [client] cl

    UNION

    SELECT cl.[id], CONCAT('B - ', COALESCE(cl.status_custom_label, 'Active') ) FROM [client] cl

    WHERE cl.status = 'ACTIVE'

    .

    .

    UNION

    SELECT cl.[id], 'G - Percentage of Territory Developed' FROM [client] cl

    WHERE cl.status = 'ACTIVE' OR cl.status= 'PENDING'

     

    0
    Comment actions Permalink

Please sign in to leave a comment.