Dynamically set Data Source values 1

[tshad]

I am trying to run my SSRS reports on different servers that point to different databases (one for development, one for staging and one for production).

If I create the report and set up a data source I will be pointing at a particular server and cube. It will be a different server for each (development, staging and production) and would have to have a different connection string for each.

I am not going to change the report for each (dimension tables and fact tables would still be the same) just the connection and possibly the Cube (The cube could be appended with Dev, Stage, Prod).

Is there a way to use a configuration file to get the cub and database (connection string) at run time?

Thanks,

Tom

 

[xlbo]

If you run the report using ASP.NET then you can set the connection easily at runtime

If you want the report to be run through the report manager then you have 3 options:

1: Have a static server/database which you can read a connection string from - use this in user code / expression in the datasource configuration
2: Hard code the various connection strings in an expression in the datasource configuration - let it choose which one to use based on passing an "Environment" parameter to the report
3: Similar to the 2 above but use custom code to query somewhere (text file / database) to return the connection string and again, assign in the datasource configuration

The basic element that allows this to work is that the connection string in the datasource can be an exression so as long as you can pick up the appropriate string based on some form of environment parameter passed to the report, you can calc th connection string and pass it in

In its most basic format

=iif(Parameters!Env.Value="DEV","DEV STRING",iif(Parameters!Env.Value="STG","STG STRING","PROD STRING"))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question