Parameter with Show All Values enabled doesn't work
Hi team,
I am trying to write a report where I have 2 queries:
- Query 1 is a list of active Clients (Client ID is the identifier)
- Query 2 is a list of Purchases (created using manually edited SQL and has a where clause: WHERE CLIENTID in :ClientID)
In the report, I have created a Parameter called Client ID, and it has LookupSettings enabled, which points to the Clients datapipeline and ClientID value filed. This parameter uses an InList search operator.
The idea is that a user can run the report and select a list of IDs from the UI, and the report will return purchases relevant to the selected IDs. This works fine.
However, when the user selects "Show All Values" on the UI, the report returns a syntax error: Incorrect syntax near ')'
I investigated further using SQL Profiler and found that no values were passed in, and the SQL code shows the where clause below, which explains the error:
WHERE ClientID IN ()
Is there a way to pass in all ClientID when "Show All Values" is selected?
Your help will be much appreciated.
Thank you,
Tracey
I am trying to write a report where I have 2 queries:
- Query 1 is a list of active Clients (Client ID is the identifier)
- Query 2 is a list of Purchases (created using manually edited SQL and has a where clause: WHERE CLIENTID in :ClientID)
In the report, I have created a Parameter called Client ID, and it has LookupSettings enabled, which points to the Clients datapipeline and ClientID value filed. This parameter uses an InList search operator.
The idea is that a user can run the report and select a list of IDs from the UI, and the report will return purchases relevant to the selected IDs. This works fine.
However, when the user selects "Show All Values" on the UI, the report returns a syntax error: Incorrect syntax near ')'
I investigated further using SQL Profiler and found that no values were passed in, and the SQL code shows the where clause below, which explains the error:
WHERE ClientID IN ()
Is there a way to pass in all ClientID when "Show All Values" is selected?
Your help will be much appreciated.
Thank you,
Tracey