Hello
I have multiple databases in SQL server.
I want to include all these databases in the data sources in Report Builder 3.0, and have them all queried by the report.
What do i put in the connecting string as shown bellow ?
The databases look like this:
Do you currently have an SSRS report SERVER?
Yes, i have a report server.
the databases on the second pictures are in the report server.
but from report builder i can access them one by one, i want to add them as shown in the pic below:
I could recommend you create a data source on your reporting server and use that instead. will make your life much easier. in each environment each SSRS Server will have a data source. each data source will have it's own connection string. Your reports point to these shared connections (the checkbox above Use a connection embedded in my report
That way when you deploy you don't have to manually change this connection string (unless you are changing it through some automation script such as powershell that changes the embedded connection)
Here is a sample shared data source on SSRS report server
i understand that , but my acess to the reporting server is secondary. all i have is a server name and few databases that i have access to. So if i can concatenate those databases in Report Builder 3.0 it will be just fine.
in that case click on the Build... button on the right hand side.
how do i build for multiple databases?so far i can build for one database.
That is my main question.
you cant in that one entry.
What is the work arround
Create a database called Shared that will host all of your stored procedures used by the reports
The stored procedures in this new db will make calls to the rest of your databases
Create proc employees_sp
As
Begin
Select name, age, phone
From
aag_simmons.dbo.employees
End
Then in connection string you add this new db that has all procs that call all databases.
Or better yet create a data set on ssrs server
You can add additional data sources in Report Builder - and then create datasets from those data sources.
In a single report - you could then add a tablix or chart based on one of the datasets - then add another tablix or chart based on the other dataset.
What you cannot do is combine the data in a single dataset directly, but there are ways to get related data from one dataset into another. You can create additional columns that utilize an expression - and in that expression use a Lookup or Join or other function to pull data from the other dataset.
In your report - you can tie each tablix/chart to a specific parameter - so the data shown is related by that parameter value.
For more complex relationships - you would want a stored procedure that returned the related data from one of your available databases. You can then use that stored procedure as the source for your dataset.
You can also use embedded queries to query multiple databases...as in:
SELECT ...
FROM db1.dbo.table1 t1
INNER JOIN db2.dbo.table2 t2 ON t2.key = t1.key
WHERE ...
There are many possibilities...
1 Like
This was helpful and it worked. I did it like this:
But however, when i export the data from multiple tablix into ecxel, it is cannot bePivoted.
So wee need to combine all the data sets into one tablix as shown in the pic bellow:
How do we combine the datasets into a single tablix?
you would have to use Lookup expressions and/or Custom code within the report. I would highly discourage this approach especially with the amount of dataSets you have.
It will make your report not maintainable, scalable nor sustainable
Are all the databases you want to query on the same SQL Server instance? If so can you create a master view querying each database? Once that is in place just create one SSRS data source to the database that the view is in, you can just query the one view.
he mentioned that he has no perms to create any artifacts on the server.
As others have pointed out - if you need the data combined in a single tablix you can combine that data using a lookup. If the databases all exist on the same server - then you can also create a dataset that queries multiple databases using 3-part naming:
SELECT ...
FROM db1.dbo.table1 t1
INNER JOIN db2.do.table2 t2 ON t2.key = t1.key
WHERE ...
Your datasets can be created from embedded code, directly from a single table - or from a stored procedure. In your case, since you don't have access to create a stored procedure - use embedded code and join the data as needed.
If your goal is to combine the data from all datasets into a single dataset - and every dataset is the same...
SELECT ...
FROM db1.dbo.table1
WHERE ...
UNION ALL
SELECT ...
FROM db2.dbo.table1
WHERE ...
UNION ALL
SELECT ...
FROM db3.dbo.table1
WHERE ...
This will create a dataset where all of the data has been combined. You can then use that dataset as the source for your tablix - export it to Excel - and pivot the data in Excel.
With that said - you can also pivot the data directly in Report Builder using a matrix so you don't have to export to Excel to get the results.
1 Like
This solved mz problem. How do i mark it as an answer?