HI, I am a new for reporting, I have a assignment that on the report i have to compare data from 2 database Prod and Test, i have 2 data sets from both database, and i have 2 parameters here is my query /******** this from Prod database*************/SELECT permitTypeId, description, seasonFROM PermitsWHERE (season = @season) AND (permitTypeId = @permitTypeId)/*************from Test database ***************/SELECT permitTypeId, description, seasonFROM PermitsWHERE (season = @season) AND (permitTypeId = @permitTypeId)/***********************************************/

purpose of this report to check Requirements for Configuration Compare between Prod environment and Test environment.So i to get both of database to display data in the same table in the report , Is a way to accomplish this task. ?Thank you.

That another option that i'm consider, i have tried to created separate tables side by side. one table have data display but another that use different dataset( different database) was have nothing display.I don't know how to binding 2 different database in single report.

I have try to separate tables again , I have figure out that i set something wrong in the dataset properties that why the another table was not display anything. I put them into the same table because i have to check between 2 database if something column is different, so i will highlight the row that is different.

create a dataset with query like select 1 as key. Map table to the dataset. the other expressions inside should be specified with scope as dataset name ie suppose if you want field1 of dataset1 use First(Fields!Field1.value,"dataset1") etc

but one thing to note here is that you cant select individual values. you need to apply some kind of aggregation like MIN(),SUM(),First() etc

for getting all detail value best way is to bring them in same dataset if possible or Lookup() function if using 2008 R2 and above

Assuming you want to compare the results of a query from your prod database to the same query on your test database the easiest option would be to do all the work in a single dataset, then your report is a simple, single table.

I think this is what visakh16 was trying to get to.

So create a dataset with a query something like this.

Note: This is assuming the code is running from your PROD database and both databases are on the same server. If they are on different servers, make sure there is a linked server setup between the two servers and then prefix the table names in the query with the server too (e.g. TEST.dbo.Permits would become DEVSERVER.TEST.dbo.Permits)

SELECT
p.PermitID AS Prod_PermitID,
p.Description AS Prod_Description,
p.Season AS Prod_Season,
t.PermitID AS Test_PermitID,
t.Description AS Test_Description,
t.Season AS Test_Season
FROM
(SELECT PermitID, Description, Season FROM Permits
WHERE Season = @Season and PermitTypeID = @PermitTypeID) p
JOIN
(SELECT PermitID, Description, Season FROM TEST.dbo.Permits
WHERE Season = @Season and PermitTypeID = @PermitTypeID) t
ON p.PermitID = t.PermitID
-- optionally add a where clause to filter to just the differences
AND ((p.Description != t.Description) or (p.Season != t.Season))

Well you get the idea, you can change the query to give you your required results such as if you wanted to test if some records don't exist on either side then you could FULL JOIN the tables

Anyway, once you have this dataset it's just a case of dropping the results into a simple table on your report.