Hi, I am a beginner of Reporting Service. I am trying to create a report using multiple databses. For example, I want to create a report called RevenueByCustomer, so I need to get data from the Customer Table of CRM database, which contains customer information, and I also need to get data from Transaction table of Sales database, which contains all the revenue information. In order to get data from both database, I have created two dataset. One is Customer dataset, which get all required customer data from CRM database, and the other is Revenue dataset, which gets data from Sales database, they used seperate datasource (because each datasource only contains one database connection). Now my problem is how can I make them be displayed in one report ? It seems to be like a Master-Detail report, I need to sum up all trasactions for a particular customer and also need to display the customer name with each TotalAmount, but how can I make these two dataset can be merged together or used an extra query to do it?
Please help me, thanks a lot
This Query below is used to calculate the total amount for each customer in Revenue dataset:
SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @.Start) AND (Updated <= @.End)
Group By CustomerID
the other one here is used to get customer code and name in Customer dataset:
SELECT ID, Code, Name
FROM Customer
SELECT ID, Code, Name
FROM Customer
UNION ALL
SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @.Start) AND (Updated <= @.End)
Group By CustomerID
|||
I think your easiest solution would be to join the two recordset's together via the CustomerID and put it in one dataset. Then you can have Reporting Services do the grouping.
SELECT c.Code, c.Name, sum(t.TotalAmount - t.TotalTaxAmount) AS TotalExcTax, sum(t.TotalAmount) AS TotalIncTax
FROM CustomerDB.dbo.Customer c
inner join SalesDB.dbo.TransactionMaster t on c.ID = t.CustomerID
WHERE (t.Updated >= @.Start) AND (t.Updated <= @.End)
Hope this helps.
Jarret
|||
Hi,
You might be able to do that on the SQL server and return that as a view, but you can't access two separate database connections in the SQL statement in the RS dataset designer. (unless this has changed..)
If you want to create a master - detail report in BIDS, try creating two connections, two seperate datasets and then create a 'master' report on one dataset and a subreport for the detail on the other dataset. Then pass that subreport the current master record's ID field using parameters..
pl
|||
pl,
You can join from other databases as in my example. I think I mis-worded it, instead of '...join the two recordset's together via...', I should have said '...join the two table's together via...'.
Sorry for the confusion. I still think this would be the easiest solution, rather than creating sub-reports.
Jarret
|||Hi Jarret,
I don't believe you can join two tables from different databases in SQL dataset editor in BIDS. Have you ever tried?
pl
|||pl,
Yes, I have tried, and it works (as long as the user account has access to both databases). I have a few reports in production that link between databases.
Jarret
|||Hi Jarret,
Really? Which version of RS are you using? I have RS 2005 (no upgrades to sp1..) and BIDS (SQL Server Business Intelligence Development Studio). Even though I create two connections to two databases in a solution using BIDS, I cannot refer to 'the other database' in the SQL view designer when I'm creating a new dataset.
Is this post correct: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177599&SiteID=1
Here is what I do:
1) Create a new Report Server Project in BIDS 2005
2) Add a datasource to the Shared Data Sources. Hook it up to database1.
3) Add another datasource to the Shared Data Sources. Hook it up to database2. Now you have two datasources in one report project.
4) Add a report.
5) Create a new dataset. (Click on the "Data" tab, hit the drop-down list and choose <new dataset>) Notice that when that dataset dialog opens up, you can only specify one datasource for it. Specify the datasource.
6) In the dataset designer, try to access the other datasource that you did not specify in the dataset's connection (ie: select * from database2.dbo.table1) . It doesn't work. You get an error message that says "Invalid object name... Error: 208"
You are saying that this works for you? Are you sure you aren't maybe doing this in query analyzer on the server and producing a view which you then use to populate a datasource in RS using only one connection?
pl
|||
pl,
I think you misunderstand what I have done (and suggested) here. I am using RS 2005 SP1 and BIDS. In my example, the code I pasted (below) was the query for 1 single dataset. Of course, if these databases are on separate servers, this wouldn't be possible without a linked server. However, Fern.Andy didn't mention anything about the DB's being on separate servers, so I assumed they weren't.
SELECT c.Code, c.Name, sum(t.TotalAmount - t.TotalTaxAmount) AS TotalExcTax, sum(t.TotalAmount) AS TotalIncTax
FROM CustomerDB.dbo.Customer c
inner join SalesDB.dbo.TransactionMaster t on c.ID = t.CustomerID
WHERE (t.Updated >= @.Start) AND (t.Updated <= @.End)
I am not saying that I can link two dataset's together from within BIDS. In short, I joined the two tables together in the query, rather than trying to get RS to join two different datasets.
1. Create a new report
2. Create the connection string to the server
3. Paste the above query into the query string
Jarret
|||Hey Jarret,
I've got it working now. Thank you very much. I appreciate your patience.
Seems I had a spelling mistake in the database name. Once I had that fixed, I had to give access to the same user to the second database. But, it does work now. And, I only needed one connection in RS. This is going to be a very handy. Much better than using sub-reports.
Thanks again.
pl
No comments:
Post a Comment