CODEWITHSUNDEEP
reporting-servicesdynamic-sql
Brtrnd
Brtrnd

Reputation: 196

dynamic queries in SSRS

I would like to write a query and allow for a grouping on different colums; for example the end user can group either on country, region, city, or no grouping.
The result is to be used in an ssrs report.

so I would write a query like this:

@value = CASE WHEN @dropdown=1 THEN ', foo.country' ELSE ', foo.region'

@sqlquery = 'select name '+@value+' from foo group by field1'+ @value + ';'

EXEC(@sqlquery);

This does work as expected in management studio (if i define variables and assign something to @dropdown);

I'm a bit at loss on how to implement this in an ssrs report: I want the user to be able to choose no grouping (leave the @value empty)
Visual studio will not be able to 'see' the field and therefore allow to add it in a tablix

Upvotes: 1

Views: 2897

Answers (3)

William Xu
William Xu

Reputation: 261

Try this:
1. Create a parameter GroupBy
enter image description here

  1. Create your data set. Don't directly write your query in to box, use expression instead.
    enter image description here

  2. Write your query like this:
    enter image description here

I think you probably need to try and adjust your query expression for a while, but it should be the way to go. Good luck.

Upvotes: 1

alejandro zuleta
alejandro zuleta

Reputation: 14108

Avoid users type the grouping (see the @NeilP recommendation), just create a parameter called Grouping, in Available Values tab you can specify the dropdown selector values.

enter image description here

In the Available Values tab use these settings:

enter image description here

Then in your dataset properties map the SSRS parameter to a T-SQL parameter.

enter image description here

Now you can use @Grouping in your dataset:

set @value = CASE WHEN @Grouping = 1 THEN ', foo.country' 
                  WHEN @Grouping = 2 THEN ', foo.region'
                  WHEN @Grouping = 3 THEN ', foo.city'
                  ELSE ''
             END

set @sqlquery = 'select name '+@value+' from foo group by field1'+ @value + ';'

The user will be prompt to select one value in the dropdown list:

enter image description here

You can define a default value used in the report, which lets users run the report without specify any value.

Go to Default Values tab in Parameter Properties and add =0 if you want to your report runs without any group by default.

Let me know if this helps.

Upvotes: 2

Neil P
Neil P

Reputation: 3190

I would wrap this in a stored procedure and then call that from the dataset

create proc report_1 
@value varchar(200)
as
@sqlquery = 'select name '+@value+' from foo group by field1'+ @value + ';'
EXEC(@sqlquery);

Be weary of SQL injection when using dynamic sql, it might be advisable to set a defined list of values for your drop down parameter, either statically or by querying the information schema for column names.

Upvotes: 0

Related Questions