CODEWITHSUNDEEP
sqlsql-serverdynamicreporting-services
jr from Yaoundé
jr from Yaoundé

Reputation: 656

SSRS Dynamic query and shared dataset

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

Upvotes: 0

Views: 2467

Answers (1)

jr from Yaoundé
jr from Yaoundé

Reputation: 656

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.

Upvotes: 1

Related Questions