Dynamic parameters in Reporting Services

Question

Dynamic parameters in Reporting Services

Navigation

#1 by (0 votes)

1

I am currently generating a report using the SWL Server Business Intelligence Development Studio 2005 since it is the reporting version used by my client.

Your request is a report with 2 filters, one per region, and another one for stores, but you want that when you select region X the store filter loads only the stores that belong to that region.

The filter of regions I charge with the following query

select distinct(sitio), valor 
from cat_tiendas
order by valor

And the store has the following query

SELECT idTienda + ' ' + Observaciones AS descTienda
FROM cat_tiendas
WHERE (valor IN (SELECT Value FROM dbo.FnSplit(@valor, ',')))
ORDER BY valor

But at the time of executing the report, it shows me that

la funcion o el proecidimiento dbo.FnSplit tiene demasiados argumentos

But if I execute it manually the query returns me without problems the fields of the select

Someone can support me to know where my query is going wrong.

sql sql-server-2005 ssrs

asked by Rodrigo Jimenez 01.03.2017 в 18:12

source

1 answer

Generate Excel document from Java Fatal error: Call to a member function query () on null

score 0 · Accepted Answer

If your @valor parameter is multivalue, then you do not need to use the FnSplit function to separate the values. In SSRS (and only in this environment), just use IN :

SELECT idTienda + ' ' + Observaciones AS descTienda
FROM cat_tiendas
WHERE valor IN (@valor)
ORDER BY valor