How to restrict duplicate value in prompt display value

[inu]

Hi Team,
I have one value prompt, i used used value contain 1,2,3.
For display value i wrote case statement as
case when dataitem1 in (1,2,3) then 'Client Demise' else 'Non Demise' end

when i run the report three client demise is displayed.
Can you please suggest, what should i do.

Regards,
Inam

[BigChris]

I might be missing something, but it looks like it's doing exactly what you asked it to do. The three values that you've given it (1,2 and 3) ALL equate to 'Client Demise'...so the display value only has one thing to display. Do you need to see something like '1 - Client Demise', '2 - Client Demise' etc.? If so then you need to concatenate the dataitem1 and the case statement to give you a new field to use as your display value.

[inu]

I might be missing something, but it looks like it's doing exactly what you asked it to do. The three values that you've given it (1,2 and 3) ALL equate to 'Client Demise'...so the display value only has one thing to display. Do you need to see something like '1 - Client Demise', '2 - Client Demise' etc.? If so then you need to concatenate the dataitem1 and the case statement to give you a new field to use as your display value.

How to do that , can you please write the statement..

Regards,
Inam

[BigChris]

You've been around a while...you've been on this board for three years...I'd have thought you'd know how to bring to fields together into a calculated field by now. Have a go and let me know if you get any problems.

[AnalyticsWithJay]

I believe what Inam meant was that although they're selecting 3, all three values are displayed. In other words, filtering is not being applied.

If the above is true, then what you're doing is not a filter -- it's a display item. You will need to add a filter to your query to bring back records where it's equal to your prompt parameter value.

Respectfully, I think you will feel much more comfortable if you studied the Report Studio course. There are some fundamental reporting concepts that you don't appear to be comfortable with yet, but will make a significant difference in your knowledge.

Jay

[inu]

I believe what Inam meant was that although they're selecting 3, all three values are displayed. In other words, filtering is not being applied.

If the above is true, then what you're doing is not a filter -- it's a display item. You will need to add a filter to your query to bring back records where it's equal to your prompt parameter value.

Respectfully, I think you will feel much more comfortable if you studied the Report Studio course. There are some fundamental reporting concepts that you don't appear to be comfortable with yet, but will make a significant difference in your knowledge.

Jay

Thanks Jay,
I used filter as usedvaluequeryitem=1, then in the prompt 'Client Demise' is displaying once, but when user selects Client Demise, only one value will pass , right?.
Now the question is , what statement should i write in the main query filter to pass all three value.

Thanks
Inam

[HalfBloodPrince]

why cant you take the reverse way, create calculation in the main query in which you want filter out as

If ([Data item ]in (1,2,3) )then ('Client Demise') else ('Non Demise' )

and then create static value prompt with 2 values  'Client Demise'&  'Non Demise'  and add filter on the new calculated column in main query

[inu]

why cant you take the reverse way, create calculation in the main query in which you want filter out as

If ([Data item ]in (1,2,3) )then ('Client Demise') else ('Non Demise' )

and then create static value prompt with 2 values  'Client Demise'&  'Non Demise'  and add filter on the new calculated column in main query

Thanx HalfBloodPrince, I am reaching out to the solution.. I tell you what i did.
1. Created a static value prompt (?Par_Demise?) and set use value as 1 and 0, display value 'Client Demise' and 'Non Demise'.
2. Created a query calculation as per your suggestion as
     If ([Data item ]in (1,2,3) )then ('Client Demise') else ('Non Demise' )
Now i have to add filter in main query using ?Par_Demise?. Thats i wanted to know...
Can you please tell the last step..
I really appreciate you response.

Regards,
Inam

[HalfBloodPrince]

Created a static value prompt (?Par_Demise?) and set use value also 'Client Demise' and 'Non Demise'. display value 'Client Demise' and 'Non Demise'.

[inu]

Created a static value prompt (?Par_Demise?) and set use value also 'Client Demise' and 'Non Demise'. display value 'Client Demise' and 'Non Demise'.

Thanks, I will do that... But query calculation, which i created in main query is not being used in chart report. and we dont have option to hide query item in chart.
Now please tell me what should i do?

One more thing, if use value is 'Client Demise' and 'Non Demise' these are our own string, right..not in Database.
So we used 'Client Demise' and 'Non Demise' in use value, then what value it will pass in the query...Can you please explain..

Apart from this, when i use "calculated_query=?Par_Demise?" in main query filter, it throws error.
actually it should throw error due wrong statement.





Regards,
Inam