Solved

How to create a dynamic DataSource

Posted on 2013-01-08
7
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Suggested Courses

737 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