Setting a Data Source at Runtime on SQL Reporting Services

Posted by beakersoft | Posted in SQL Reporting Services | Posted on 22-06-2008

15


I am in the process of writing a reporting service that will be used across our whole company. The problem I have is there is not one central database all the reports will run of, each division has its own separate one. Lucky the database schema’s are consistent, but I needed a way of:

a) setting the report data source at run time and
b) letting the users control the database they want to connect to. Some high level user need to be able to access multiple databases, while others will just need access to their local one

So, here’s how I went about doing it.

Setting Data source at Run time

Normally, when you setup a reports data source in reporting services, you just point it to the database you want it to use (probably using a shared data source) give it some credentials and off you go. You would probably end up with a connection string looking something like this:

Normal Data Source

Under normal circumstances that would work fine. But not this time. I need an easy way to replace the information held in the connection string with some sort of variable.

Turns out the easiest way to do it is by using a parameter in the connection string. You can create the connection string as an expression as if you were working with any other part of the report. However, before doing this you first need to create the parameters and fill them with some data.

Data Source Table

In the reports database, I created two new tables, one that will hold the name of the reports servers, database names and Active directory group with access to this data source. The other one will contain all the user names and what Database they are currently using. Download the SQL scrip to create the databases below

Download Table Creation Script

Download the script: create_tables.sql

hello

Note: You will need to put something in place (i use a webpage) to allow the users to control the content of these tables, this article only looks at the reporting services side of things.

Run this script on your reports sever database, and you will have two new tables, database_details and user_details. In order to do the tests you will need to create an entry in the database table (database_details) for your database, and an entry in the user table (user_details) for your username (include the domain name so it should read dom\joe.bloggs) and point it using the site_id filed to the database. You are now ready to create the SQL in the report that will populate the parameters

Dataset Containing the database connection details

Create yourself a test report, and in that report create a new dataset that points at your reports server database. The SQL needs to be the following:

SELECT     jp_user_details.username, jp_database_details.server_name, jp_database_details.database_name
FROM         jp_user_details INNER JOIN
jp_database_details ON jp_user_details.site_id = jp_database_details.site_id

This will return all the names in your database, what we need to do now is make sure at run time it only returns data for the user running the report. To do that we need to put a filter on the report. The filter will be:

Fields!username.value=User!UserID

and should look like this in your Dataset properties

Filter Propertiess

The Parameters

Once you have done this dataset, you need to create a couple of parameters. One will be for the server name, one for the database name. So go into your report parameters and create the following, with these options:

Name – ServerName
Datatype - String
Hidden - Yes
Available Values from Query: Select your dataset , then both values fields need to be server_name
Default Values From Query – Select your dataset, value field is server_name

Name – DatabaseName
Datatype - String
Hidden - Yes
Available Values from Query: Select your dataset , then both values fields need to be database_name
Default Values From Query – Select your dataset, value field is database_name

What does this do? Well, when you run your report now you will have the two parameters you have just created filled in with the server name and database name the person running the report wants to report on. The parameters are set to hidden so the user knows nothing about them.

Final Step, create your dynamic dataset

Now we have everything we need in place, the last step is to create a dataset that will look into the server held agaist the user, and retrive us the actul information we want. Create a new dataset, and in the connection string of this data source put the following:

= “data source=” & Parameters!ServerName.Value & “;initial catalog=” &  Parameters!DatabaseName.Value

It should look like this in the window

dataset with Params

Now when you run the report, you data source will be based on the users choice held in the table

A couple of things to note

You will find it hard (probably impossable) to write the report with the parameterized data source. You’ll need to work with your local copy of the database hardcodded into the connection string first, then when you are happy with it change it to the parameter driven one. If you dont do this the report designer wont be able to read the dataset.

You also need to be carful the order in witch you put your user paramters on the report. I found that if i did’nt put data driven parameters (ie a list of sales areas the user can select) at the top of the list, they were greyed out when you tried to run the report. Not sure why, but i asume its down the the order the datasets are exceuted in

If you have any problems with this, or have any different ways of doing it let me know!

Comments (15)

I loved this information, thank you. However, I ran into a bit of trouble and was wondering if you had seen the same thing. When I do all of this and run in debug mode, it works like a champ and the selection I make on the first page (specifying database, etc) is then passed on to the next page, which retrieves data from the specified database.
However, once I deploy the reports, only the first page (the one that is hard-coded) works. I get this error on the second page:

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database

The information that I’ve found on that error suggest I need to store the credentials along with the datasource, which sould defeat the purpose of this handy method you’ve got here.

Have you run into a similar problem when you deploy the reports?

Hi Scott, when i deployed my reports each database server already had a reporting user on it, so i just hard coded it into the datasource.

I did have a quick play with using pass through authentication and i had the same problem, but i cant see why it should’nt work. I will try and delve further into why its happening, but in the meantime if possable just build a sql user on the servers with the same username/password and put that in the datasource.

when i wrote the connection string like above it doesn’t work it work with the next format
=”data source=” +Parameters!ServerName.Value+ ” ;initial catalog= “+Parameters!DatabaseName.Value

Tried the above steps but it prompted me with “Error during processing of the connectstring expression of datasource ‘DataSourceTes’

Hi
Its a great stuff and cleared all of my problems
but one thing i didnt get is about getting the user who run the report.
could you please clear my doubt
in the creation of dataset you made a filter like
“Fields!username.value=User!UserID”
here i dint understand how u will get and where we get “User!UserID”. im calling reports from web interface so i need to get the login user from web application and passed to report server?
please tell me how will we do this..?
Advance Thans

Madhu,

“Fields!username.value=User!UserID” is the current logged on user, (so it would return domain\username)

It uses this to go and get the users info out of the DB, without this you would not be able to tie a user and a database together

Thanks Beakersoft For ur quick reply

yes same i mapped in dataset filter but its not working.i think we should have entry of login username in reportserver.users table?

Sorry to say i didnt understand ur point could you please explain me in detail.

let me explain u my problem is:

from my web application i login with username “madhu”,so for this username we have to maintain any entries in any table?

some where im missing the flow please help me out to solve this,im breaking my head from last weak

Thanks
madhu

madhu, yes you need an entry for the username in the users table, this is how it maps to the database

thanks for ur effort , I did as u indicate, but the dataset couldn’t be fill with field, so any solution avaliable

You mentioned in the beginning about allowing high end users access to multiple databases. I assume you would just un-hide the database parameter?

I can handle the interface to build a way to undate the tables used in this article. Only problem is the article isn’t clear on providing the report user a way to control which database is available. Is there a query to the database to check which databases the current user is ALLOWED access to? Otherwise, thanks for the article. I am sure we will be able to use it in some form or fashion. Cheers.

Drew, i used active directory groups to control the access to the database. I have a table that maps databases to groups, and as a user access’s the site i only allow them to connect to the databases based on their group membership.

Hope that helps

The site SQL Reports has a great SQL tutorial.
SQL Reports:http://www.sql-reports.net/

Highly recommended for people just getting started on SQL selects.
Sql tutorial:http://www.sql-reports.net/2011/03/sql-select-tutorials.html

Even i have to pass username and password for the datasource SSRS how can i pass the those values from asp.net

Write a comment

Comments links could be nofollow free.