sqldata-manipulationcognoscognos-11

How to implement Current Month and Previous Month as a Calculation using Cognos SQL

I want to create a calculation that adds the dimensions current month and previous month to a Cognos Data Module. The Month format is 2022/11. This is what I tried. I do not get an error message, but the calculation does not return a result.

Case 
when (Month_Adj = #timestampMask(_add_months($current_timestamp,0),'yyyy')+'/'+timestampMask(_add_months($current_timestamp,0),'mm')#) then 'Last Month'
when (Month_Adj = #timestampMask(_add_months($current_timestamp,-1),'yyyy')+'/'+timestampMask(_add_months($current_timestamp,-1),'mm')#) then 'Previous Month'
else null
end

Please find a screenshot for reference.enter image description here

Solution

  • You are comparing a decimal result from your macro to a character value in your data. You don't have syntax errors because SQL implicitly casts the decimal value for the comparison. But the values are unlikely to match.

    Using today's date, your macro code...

    #timestampMask(_add_months($current_timestamp,0),'yyyy')+'/'+timestampMask(_add_months($current_timestamp,0),'mm')#

    ...should produce...

    CAST(2022 AS DOUBLE PRECISION) / 11

    So the resulting SQL is effectively...

    Case 
when (Month_Adj = 183.818181812) then 'Last Month'
when (Month_Adj = 202.2) then 'Previous Month'
else null
end

    Use the sq() function to put quotes around the values returned by the macro functions before concatenating the three parts of the expression.

    #sq(timestampMask(_add_months($current_timestamp, 0),'yyyy'))# + '/' + #sq(timestampMask(_add_months($current_timestamp, 0),'mm'))#

    You can see the SQL that Cognos is producing by...