How to change data source of SQL report during runtime

Hello,
we are in teh process of migrating the reporting of our application to SQL reporting services.
We still need to solve teh following issue.
When we design a report, we do it it on a sample data Base.
Then this report is deployed with the application, and when the report is loaded into teh report viewer, teh application should be able to change the data source by .
the changes should apply only for this session, and not to all report, as another user or teh same user might run this report but based on another data dource.
any help?
zrodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

planoczCommented:
I would think that using Stored Procedures would be better.
The reports would not have to be changed. and you could have a set of SP to migrate to the customers SQL.
0
zrodAuthor Commented:
I do not get you.
Are you suggesting that we use a stored procedure to modify the data source of SQL reports during run time?
0
planoczCommented:
Your data source is your SP. No need to change at run time.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

zrodAuthor Commented:
I think there is a mis-understanding.
What we need to change is teh server name, data base name . In fact the connection parameters .
0
planoczCommented:
zrod  are you still having problems with this report?
0
zrodAuthor Commented:
yes,
i did not get any solution till know,
i was unable to deploy an Sql  report to customers within my application.
0
planoczCommented:
Have you tried to copy the file to the server reporting directly and then run it?
0
zrodAuthor Commented:
Lets us take an example:
you have a query in our application  that returns data from one or multiple data bases.
When we deply the application at teh clients site, The user chooses the anme of the server, credentials and data base names.
So the report has been designed on data base x,y,z and now we need to run it on another set of data base. I do not see how teh copy will solve this issue.
0
planoczCommented:
I see what you are talking about. There may be no easy way, but to just enter new conection data depending on which server you use. I don't know if setting up database conections as parameters would work.
0
zrodAuthor Commented:
But setting up the database as parameters, will expose teh passwords,e tc... to the report.
it is amzing because we faced similar problem in VS2010. I wonder how microsoft reporting tools pretends to compote with Crystal report
0
zrodAuthor Commented:
corrected
But setting up the database as parameters, will expose the passwords,etc... to the report.
it is amazing because we faced similar problem in VS2010. I wonder how microsoft reporting tools pretends to compete with Crystal report.
0
planoczCommented:
You could just setup  the connection string ,but the user has to know the password to use it.
0
zrodAuthor Commented:
This is not a solution for us, as in a multiuser situation the normal users does not have access to the data base except through the application
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.