I am extremely new to the COGNOS/Impromptu community and just getting into the swing of things. However, I am a little stumped and need help in one of I hope are a few stumbling blocks.
I have a report with columns from Jan-Dec. In this report, the query tells the case statement to end as "Jan-2009", "Feb-2009", etc.
...
sum(case when T1.ReqMonth = 1 then T1.Requests else NULL end ) "Jan-2009",
sum(case when T1."ReqMonth" = 2 then T1."Requests" else NULL end ) "Feb-2009" ,
sum(case when T1."ReqMonth" = 3 then T1."Requests" else NULL end ) "Mar-2009" ,
...
What I would like to do is make these CASE names dynamic because each column name need to match the input of one of my prompt variables "?ReportYear?". Now, ReportYear is a number field and not a string. In addition, I would like to have the ?ReportYear? concatenated after each 3 character month:
...
sum(case when T1.ReqMonth = 1 then T1.Requests else NULL end ) ("Jan-" + ?ReportYear?)
...
I know that each piece of the concatenation must be a string, however, I tried to CASE the ?ReportYear? with no luck. When I substitute it with one of my string prompt-variables, it verifies fine and I see the value in the header of the column (though I didn't concatenate the 3 character month).
So, can this actually be done and what would be the syntax to accomplish this?
Thanks,
John
Sirplaya,
If the value that is being brought back is a number then did you try the number-to-string( <numeric_exp> ) function as the end of your query?
Evorus
I think you dont go over the sql generated.
Do it at the report level with the available functions.
rather than the sql generated,, if you can explain the business need you want to implement, like what information you have and what you want to generate, we can go for the solution at the report level.