SSRS Dynamic query and shared dataset

Question

It's pretty simple to write dynamic query with embedded Dataset on SSRS 2008 R2.

For example, if i have a table named Employees and a Report Parameter named @Column wich contains either 'Name' or 'Surname' or whatever else, i could write the following query for my dataset :

="SELECT * FROM Employee WHERE " & Parameters!Column.Value & " =  " 
  & Parameters!Criteria.Value

I want to achieve the same thing with shared dataset. It seems that the Parameters collection aren't available in share dataset and i have no other way to access the parameter value to make my dynamic query.

Thanks in advance

This is more of a datasouce credentials mix-up. How are you supplying credentials to your shared datasource? — Ross Bush
@lrb I use Shared Datasource and i pass parameter to my shared dataset normally. The dynamic query works for embedded dataset not for shared one. — jr from Yaoundé

jr from Yaoundé jr from Yaoundé · Accepted Answer · 2013-10-21T10:51:49

I finally rewrote my query using CASE WHEN of T-SQL.

So the new query (not so dynamic) is like that :

SELECT *
FROM Employee
WHERE 
    CASE @Column
       WHEN 'Name' THEN Name
       WHEN 'Surname' THEN Surname
    END
    = @Criteria

Thanks.

SSRS Dynamic query and shared dataset

1 Answers