If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

prompt should not prompt !

Started by cognosun, 03 Dec 2012 03:32:38 AM

Previous topic - Next topic

cognosun

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

Bark

#1
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

cognosun


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?

Bark

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

cognosun

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....

Bark

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

cognosun

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 :-)



Bark

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

cognosun

Yeah it's not giving any output.

After pressing finish button, it's getting stuck.


cognosun

will cutomize my query and re-post it :-)

Bark

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