I have an optional value prompt with some values.
We have a calculated column in the report in which we use this prompt.
If user selects any value...it's fine, as value gets pass to the conditon and we are getting output.
But if he don't select anything in prompt page...then while running the report it's asking to enter values, because this prompt/paremeter is part of a calculated column, is there any way to set like, user won't get prompted to select any value if he dont' select any values at prompt page.
I mean if user wont' select anything, we can pass null or something like that....
Many Thanks
Joys
Hi,
You can use a Macro Function. The sintax would be: #prompt('parameterName','dataType','defaultValue')#
Assuming the prompt is an integer and the calculation is "[A] + promptValue":
[calculation] = A + #propmt('pInputParam','integer','0')#
I would be able to provide you with more detail if you tell me the expression of the calculated column.
Regards,
Bark
Bark...this is the rough expression....
case when [region] not in ?PARAMETER1? and [LOCATION] not in ?PARAMETER2?) then
[AMOUNT] else 0 end
here user may or maynot select ?PARAMETER2?
Hi,
I am not sure what is what you are trying to achieve... based on your expression you want to zero the amount of those regions and locations the user has selected? That's extrange... In any case, what's the data type of [Location]? is it a cube/DMR? are they id's? literals?
CASE WHEN [region] not in ?PARAMETER1? AND [LOCATION] not in (#promptmany('PARAMETER2','string','dummyLocation')#) THEN [AMOUNT] ELSE 0 END
The "string" bit needs to chage to "integer" if it is an ID or to "memberuniquename" if it is a member from a cube/DMR.
Regards,
Bark
Thanks Bark...
is this expression reads like this...
if PARAMETER2 get values , it'll proceed with them, if not it'll use dummylocation ( which is not part of database values)
is it so....
Quote from: cognosun on 03 Dec 2012 05:39:55 AM
Thanks Bark...
is this expression reads like this...
if PARAMETER2 get values , it'll proceed with them, if not it'll use dummylocation ( which is not part of database values)
is it so....
Yes, that's what it does.
Did it return the expected results?
Regards,
Bark
As i said in the first instance of this topic....This parameter is already there in prompt page.
I'm using below report expression in a column...
CASE WHEN [region] not in ?PARAMETER1? AND [LOCATION] not in (#promptmany('PARAMETER2','string','dummyLocation')#) THEN [AMOUNT] ELSE 0 END
Expression is getting validated...but not report :-)
Hi,
When you say the report is not validated, is it running? sometimes when using the macro functions I encounter RS says it is invalid but it does work later. Could you please post the error(s)?
It might be that the list of values are not string or I am mistaking something.
I will try to mock it up later anyway and send you the spec.
Regards,
Bark
Yeah it's not giving any output.
After pressing finish button, it's getting stuck.
will cutomize my query and re-post it :-)
Hi cognosun,
I did the mock up and the issue was with the data types. Cognos was not recognising properly the "dummyLocation" as a string.
Try this: CASE WHEN [region] not in ?PARAMETER1? AND [LOCATION] not in (#promptmany('PARAMETER2','string',sq('dummyLocation'))#) THEN [AMOUNT] ELSE 0 END
#sq()# will add single quotes to the text so it can filter by it.
On the other hand, it took me a while unravel the logic and I still don't know if I got it right: "zero those locations and regions the user has selected in the prompt", is that ok? If so, and this is personal opinion, nothing to do with the functionality, I'd have coded: CASE WHEN [region] in ?PARAMETER1? or [LOCATION] in (#promptmany('PARAMETER2','string',sq('dummyLocation'))#) THEN 0 ELSE [AMOUNT].
Hope it works this time.
Regards,
Bark