CODEWITHSUNDEEP
reporting-servicessql-server-2012ssrs-2012
Concerned_Citizen
Concerned_Citizen

Reputation: 6835

Create a "Template" SQL in SSRS

I have a set of SQL queries built for SSRS. I am building reports for multiple customers and my queries apply to all of them - except I have to replace the company name in the WHERE clause.

Can SSRS create a "template" SQL query and automatically replace the company name and apply according to the appropriate company name?

Upvotes: 1

Views: 92

Answers (1)

tomdemaine
tomdemaine

Reputation: 758

A good way to achieve this is with an SSRS parameter. If you right click the parameters and add one I call mine "client" but you can use company name etc if you prefer.

add

If you only have a few options for companies you can add them in manually as "available values" but a good option if you have lots of posilbilities is to create a dataset (paramset) from new query that is simply

SELECT

distinct

[Company Name]

From {your table}

Then you can use the "get values from a query" option to automatically fill the parameter drop down box with all the potential options.

enter image description here

After that all you need to do is add

Where [Company Name] = (@Client) or @{whatever your parameter is called}

to your query. When you run the report you should get a dropdown box with all the company names in it and from there you can pick one and it should apply the filter to your data automatically. That way you can build one report and run it for as many companies as you like.

Upvotes: 1

Related Questions