1

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.

SMM
  • 2,225
  • 1
  • 19
  • 30
SnowXTC
  • 19
  • 5
  • There are ways to do it. We would need to see all the SQL and parameter options in order to help. – Wes Palmer Dec 12 '16 at 20:19
  • @WesPalmer what I would really like to do is Select a.remark from remarks a where :rpt_parm But I can get away with the above. The report type would be POU Remarks, POD Remarks, etc.... and would have a value of ('P100', 'P105', P0110') or ('P200', 'P205', 'P210') etc.... How do I get that list properly into the value field for the query to use it? – SnowXTC Dec 12 '16 at 20:47

4 Answers4

0

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.

Wes H
  • 4,186
  • 2
  • 13
  • 24
0

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      ...;
SMM
  • 2,225
  • 1
  • 19
  • 30
  • Do you have to do a seperate stored procedure for the query? – SnowXTC Dec 12 '16 at 23:03
  • Only if you want. The query can just be embedded in the dataset within the report. We generally create a procedure per report to make it easier to give to the dba if we find tuning is required but lots of people just embed their queries. Use a shared data set when multiple reports will use the same query. – SMM Dec 13 '16 at 00:04
0

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

SnowXTC
  • 19
  • 5
0

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.