Display numeric parameter results in Report Header with condition

[Former Member]

My database has 9 employees each with a numeric ID number.  I have created a Number parameter using Allow multiple values. i have a text box in the report header which says "Employees selected: @displayEmpId". When I run the parameter the results are perfect.   I see only the employee Id's l selected.  e g. "Employees selected: 3, 7, 9"

What l would like to do is instead of having all 9 numbers display when Il run the parameter  and get the results of all  e.g. Employees selected:  1, 2, 3, 4, 5, 6, 7, 8, 9 is the message to read "Employees selected: ALL". As the number of employees grow in the future there would not be enough space in the Report Header to display them all.

Would really appreciate ate any help on this problem, I am fine with string fields, its trying to get the string result of ALL when the parameter is numeric.

[abhilash_kumar · Accepted solution]

Hi Mary,

Please see if this works:

1) Go to the Field Explorer > Right click SQL Expression Fields > New > use this code:

(

Select Count(Distinct "T.EmployeeID") From Table T

)

The SQL needs to be enclosed in round braces and the database field needs to be surrounded by double quotes.

2) Edit the formula on the header and add this part to the existing code:

If {%Sql expression name} = ubound({?Prompt}) then

'ALL'

Else

(

<your existing code here>

)

Another way to do this is to create a SQL query via the add command option that only returns the distinct count of employees as its result. You'd then leave the command sql unjoined to the other tables and replace the SQL expression field with the field from the SQL in the formula above.

-Abhilash

[Former Member]

Thank you so much Abhilash for a great solution to my query, much appreciated and I thank you for the time you took to answer my query as it has saved me so much time.  Mary