Solved

How to create a dynamic DataSource

Posted on 2013-01-08
7
255 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 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