comparing values from a multi-value parameter to a dataset column in SSRS

1
votes

I have a dataset created from a stored procedure by passing it a multi-value parameter. What I now need is to check whether all values from the parameter came back in a specific column of my result set or not, and if not, then display those values in the report.

So, for example, if I'm passing values 'a', 'b', 'c' and 'd' to my parameter, and if my dataset column only contains 'a' and 'd', then I need a way to display 'b' and 'c' on the report.

Thanks, Pratik

2
stored-proceduresreporting-servicesparametersssrs-2012
What data type is you column? It could be done with custom code. – alejandro zuleta
Its a varchar. Even I thought of custom code, the place where I'm getting stuck is traversing through all the unique values of my column, I'm not ale to figure that part out. – PratikGandhi

2 Answers

1
votes

First, you'll need a query that is supplying the values for your parameter. The query might look something like this:

select 'a' as ParamValue
union all
select 'b' as ParamValue
union all
select 'c' as ParamValue
union all
select 'd' as ParamValue

Set you parameter values to be populated by this query: enter image description here

Now Add a table that can list your parameter values.

enter image description here

Next, you can check if each value exists in your main dataset using a Lookup function like this:

=IIf(IsNothing(Lookup(Fields!PARAMVALUE.Value,Fields!COLVALUE.Value,Fields!COLVALUE.Value, "MainDataSet")), True, False)

You can use this as a filter to just show the parameter values where this function doesn't return a value:

enter image description here

0
votes

Can you change the stored procedure (or make a new one based on the old one for this report)? If so, then you could change from an INNER JOIN to an OUTER JOIN and get that result. For example, if your stored procedure showed how much clients were billed last month and looked like:

SELECT ClientName, SUM(BillAmount) AS TotalBilled
FROM Clients
INNER JOIN Bills ON Clients.ClientId = Bills.ClientId AND Bills.BillDate >= DateAdd(m, -1, GetDate())
WHERE ClientId IN @ClientIds
GROUP BY ClientName
ORDER BY ClientName

then this would exclude any clients not billed. If you change to an OUTER JOIN like so:

SELECT ClientName, SUM(BillAmount) AS TotalBilled
FROM Clients
LEFT OUTER JOIN Bills ON Clients.ClientId = Bills.ClientId AND Bills.BillDate >= DateAdd(m, -1, GetDate())
WHERE ClientId IN @ClientIds
GROUP BY ClientName
ORDER BY ClientName

then clients with no bills would still show with a Null amount for the amount billed