CODEWITHSUNDEEP
reporting-servicesoracle11gssrs-2008ssrs-2016
SnowXTC
SnowXTC

Reputation: 19

SSRS 2016 - Change a portion of SQL based upon parameter choice

I have 7 reports that all use the same query except one line in the where clause. How can I combine these into 1 report and change the line based on the report type selected.

Code example Select a.remark from remarks a where a.code in (:rpt_parm)

:rpt_parm has 7 options, but I cannot seem to get the query to accept the list of values I put in the value or to use a dataset that returns a list.

Note I am using SSRS 2016 with VS2015 and an Oracle Database connection.

Upvotes: 1

Views: 127

Answers (4)

user5683823
user5683823

Reputation:

I am not sure I understand the question properly, but I'll give it a shot.

I think you could have a table helper with two columns, option and value. For each option, you have as many rows as you have values for that option. (So a value may appear more than once - as many times as there are options using that value.)

Then, you only input the option number, say :option. Your query should be:

... where a.code in (select value from helper where option = :option)

If performance is an issue, you may put an index on option. This should work better than any solution based on comma-separated strings.

Upvotes: 0

SnowXTC
SnowXTC

Reputation: 19

https://dataqueen.unlimitedviz.com/2013/10/how-to-use-a-delimited-string-in-a-multi-valued-parameter/

This worked awesome. Comma delimited string in the value for each option in the parameter and then on the parameter portion of the query, you use the split function.

I will admit I am fairly new to SSRS and we are under a serious time crunch to convert 100 Discoverer reports to SSRS before the end of the year. But my programming OCD makes me want to do it as correctly as I possibly can the first time.

I much appreciate the help.

NEGATE THE ABOVE, IT ONLY RETURNED THE FIRST VALUE IN THE LIST

Upvotes: 0

SMM
SMM

Reputation: 2235

You can change your query to simply accept the report type something like this (MS-SQL...sorry don't have an Oracle instance kicking around):

declare @report_type int;
select  *
from    remarks a
where   (@report_type = 'POU Remarks' and a.remarks in ('P100', 'P105', 'P0110'))
or      (@report_type = 'POD Remarks' and a.remarks in ('P200', 'P205', 'P210'))
or      ...;

Upvotes: 0

Wes H
Wes H

Reputation: 4439

I assume by list you mean a comma separated list?

From an SSRS report, the best way to do this is to pass the parameter list into a stored procedure. It will be accepted as a single valued string. Within the stored procedure, you'll need to split the string into an table of strings and than use the table to join to the rest of your query based on the string value fields.

Upvotes: 0

Related Questions