I have a report with text box prompt for Account Name. The requirement is that when user enters Account name and runs the report, the report should show data for the account name entered and when user does not enter an account name, the 'Account Name' field on the report should display "ALL".
To achieve this, I wrote a case statement in the data item as follows:
case
when (?AcctName? is null)
then ('ALL')
else ([ns].
.[Account Name])
end
The problem is, this logic is making the account name prompt 'required'. How can I make it optional?
One option I have in mind is to use a space like ' ' as default selection and change the data item logic to ?AcctName? = ' '
I dont like this option, because in a scenario where the user first runs the report with an account name and like to rerun the report for all accounts, he/she would delete all text in the account name text box, which will again make the text box prompt required and disable the Run button and the user would not know why the run button is disabled.
any other ideas are appreciated.
VJ |
Hello vvasireddy9,
Try this approach. In your query, add a filter which I believe you probably have already, which would have an expression like
[ns].[Account Name]=?AcctName?. Make this filter "OPTIONAL".
Then on your report page where you display the prompt value selected, add a layout expression and the definition for the same should be:
CASE
WHEN ParamValue('AcctName') IS MISSING THEN 'ALL'
ELSE ParamValue('AcctName')
END
You do not need the data item then.
If this does not suffice, and you HAVE TO use a data item then prompt macro is your answer.
Thanks,
Cognos810
Thanks Cognos810!
I am displaying the account name as a column in the field, so I need to keep it as a data item. The prompt macro idea worked. Appreciate it.
VJ
Umm....even if you are displaying the value in a column, change the property of the column to use Report Expression rather than a data item and then put the expression as mentioned earlier in the report expression. It will always show one single value that was entered in the text box prompt correct?
Anyways, glad it worked out for you :)
Cheers!!
Cognos810