Search code examples
sqldatabasereportcognoscalculation

How to convert SQL statement to equivalent Cognos query calc?

I was building a crosstab like so: enter image description here

and need to instead convert into a list. The part that's giving me trouble is converting the grouping of v_IT or BU which feeds down into Act and Direct Cost into 4 separate fields for the list which would be 'IT Act', 'IT Direct Cost', 'BU Act', and BU Direct Cost.' I'm not sure how to make a calculation for these fields using those two data items.

I think the SQL would be something like:

Select Act$ from 'mytable' where v_ITorBU = 'IT'

for the first 'IT Act' data item at least.

v_IT or BU has two values 'IT', and 'BU'.

I tried 

Case
When v_ITorBU = 'IT'
then act$
end

but this only gives me the overall act values, not those specifically for IT. I need the specific IT actuals as well as the BU actuals.

Does anyone have any insight on how to make this into Cognos query calculations?

Solution

  • The grouping of v_IT or BU which feeds down into Act and Direct Cost into 4 separate fields for the list which would be 'IT Act', 'IT Direct Cost', 'BU Act', and BU Direct Cost.

    Go to the query add data items from the tool box

    Create the definition expression, for example this would be for [IT Act]

    IF( [v_IT or BU]  = 'IT Act' )Then([Act$])Else(0)

    Repeat this process for

    • IT Direct Cost
    • BU Act
    • BU Direct Cost

    Also repeat these 4 data items for the cost metric

    Remember to check the properties in order to achieve the correct aggregation