SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 SQL Reporting use 2 different database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ampmy123
Starting Member

USA
5 Posts

Posted - 02/15/2013 :  14:41:35  Show Profile  Reply with Quote
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, season
FROM Permits
WHERE (season = @season) AND (permitTypeId = @permitTypeId)
/*************from Test database ***************/
SELECT permitTypeId, description, season
FROM Permits
WHERE (season = @season) AND (permitTypeId = @permitTypeId)
/***********************************************/

here is my result
Permit ID | Description | Season | | PermitID | Description | Season|
111 | Dove |2011/2012|| 111 | Dove |2010/2012
/*****************************

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.

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/15/2013 :  14:47:13  Show Profile  Reply with Quote
why not put them onto separate tables side by side?

or bring them as a single resultset?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ampmy123
Starting Member

USA
5 Posts

Posted - 02/15/2013 :  14:51:35  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/15/2013 :  15:00:15  Show Profile  Reply with Quote
to bind two different datasets onto same table you've apply a dummy dataset to table and inside expression explicitly call required datasets in scope

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ampmy123
Starting Member

USA
5 Posts

Posted - 02/15/2013 :  15:00:43  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/16/2013 :  02:46:59  Show Profile  Reply with Quote
ok...if you've to relate between rows in dataset its always best to combine and bring them in same dataset itself.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ampmy123
Starting Member

USA
5 Posts

Posted - 02/19/2013 :  10:32:40  Show Profile  Reply with Quote
HI ViaKH16, Can you explain more how to create dummy data set and apply to the table? Thank you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/19/2013 :  12:43:05  Show Profile  Reply with Quote
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

http://msdn.microsoft.com/en-us/library/ee210531.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ampmy123
Starting Member

USA
5 Posts

Posted - 02/19/2013 :  14:43:01  Show Profile  Reply with Quote
Thanks for the reply visakh16.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/20/2013 :  01:26:00  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Alan Schofield
Starting Member

United Kingdom
23 Posts

Posted - 02/26/2013 :  22:03:31  Show Profile  Reply with Quote
Hi,

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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000