Solved

How to create a dynamic DataSource

Posted on 2013-01-08
7
252 Views
Last Modified: 2013-01-14
Hi Experts,
I am trying to figure out how to use a dynamic data source in my report file? I use 2 different databases which are on the current server. But I want a variable to drive my data source so that on my query I don't have to mention the name of the database. Your help will be highly appreciated
0
Comment
Question by:Josh2442
7 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 38757691
Ask user to input the servername, databasename and create the data source using the parameters dynamically.
http://stackoverflow.com/questions/2101456/using-dynamic-connection-strings-in-ssrs2008
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 305 total points
ID: 38757935
If you use an embedded data source, you'll have the famous fx button on the Data Source Properties window which can then be used to create an expression such as:

="Data Source=YourServer\YourInstance;Initial Catalog=" & Parameters!YourDatabase.Value

Open in new window

The expression assumes that a parameter called YourDatabase of type Text exists, and contains a valid name of a database on the YourServer\YourInstance server.

Please note that the fx button is only available for embedded sources, not shared ones...

And also note that you may want to build your datasets first, prior to making the data source dynamic.  This will avoid some annoying popups...
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38758062
In First Data Source get the Database names.

Create Parameter for that Database name.


Create Second datasource in that you Database name as parameter.


Hope you do the way.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 38771910
Josh2442, would you mind explaining the B grade?
0
 

Author Comment

by:Josh2442
ID: 38771941
I have done the above steps. So far it looks good but still has been giving errors for
My first dataset where I have had my dynamic datasource. I will have to see further on that issue but your post was helpful for me to set these up. I wish I could change the grade to A but it is too late. Thanks ValentinoV.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38773639
Thank you Josh2442 for responding, I appreciate that.

If you think the error is directly related to the dynamic data source, please go ahead and click the Request Attention link (the yellow warning sign located under your question) to have the question re-opened.  You're welcome to interact with the experts, with that I mean if something isn't clear or you need some additional information to get something done 100%, you're free to post additional follow-up comments in the same question thread.  In fact, the experts appreciate that more than a question that gets closed with a less than A grade.

If you think the error is not directly related to the dynamic data source and thus not to this question, don't hesitate to open a new one.  I'm sure someone will be glad to help you out :)
0
 

Author Comment

by:Josh2442
ID: 38774126
Thanks ValentinoV. I too am thankful for your expert opinions not only in this question but my previous questions as well. I will also keep the above listed comments by you in mind before jumping into conclusion and grading it right away from now on. Thankyou once again
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question