I have the following report expression returning part of a date (year)
substring(ParamDisplayValue('Selected_Month'),character_length(ParamDisplayValue('Selected_Month'))-4,5)
I need to do a calculation based on the expression above that would give me the previous year. In order to do so, I have tried to Cast the output of the expression above to an integer but I am getting a parsing error:
A parsing error was found at or near the position 118 in the expression "Cast(substring(ParamDisplayValue('Selected_Month'),character_length(ParamDisplayValue('Selected_Month'))-4,5), integer)
Can anyone point out how I should go about getting the output I need?
More information would be helpful, like a sample of the data returned by Selected_Month.
Are your years 4 digits long or 5 digits long?
Thank you for your reply dougp
The value returned by selected_month is Dec, 2014. The substring returns 2014
are you certain your substring returns "2014" rather than " 2014"? the length you are specifying for the substring's return is 5, rather than 4, and you're starting one too early - should be length-3 rather than length-4. And the extra character at the end might introduce issues when casting, depending on what it is.
Thank you hespora,
changing the substring to 4 instead of 5 and length-3 instead of -4 gives me 2014, but I am still getting the same error when it comes to casting.
Oh I'm dumb... you said the magic words right in your original post.
casting is not available in report expressions. Instead, you have different functions per conversion. (in the expression editor under Functions -> Report Functions -> Data Type Casting Functions)
Try
string2int32 (
substring ( ParamDisplayValue('Selected_Month'),character_length(ParamDisplayValue('Selected_Month'))-3,4 )
)
<facepalm>
That makes sense. Layout calculations are processed locally, and cast is not available locally.
Thank you hespora! That did the trick.
I appreciate the help.
Glad I could help! :)
How about using the YEAR column in your DATE dimension...? 8)
Ahem. He's not referencing data, but a parameter.
Ahem, thanks for pointing that out. I look for the original that needs to be achieved. His current issue seems like an intermediate part of getting from problem to solution. Usually with dates you have the date dimension to reference, or you use a cube with his special possibilities for that (as my new external colleagues at my current client so graciously point out). Consider that earlier in the road to the solution you can use the date dimension or switch to a cube. Do with it what you like.
If the parameter value is coming from a prompt, there are two options:
- Parse it using SQL functions, Cognos functions, or Cognos macros.
- Look up the year using a query against the "date" query subject and using the parameter for filtering.
It's a choice of which complexity you want to support in the long-term.