lookup tables are in a separate database

• jen33

  Newbie

  Points: 2

  More actions

  November 25, 2011 at 5:26 am

  #248548

  Hi,

  I'm using SQL Services Reporting Services 2008 R2.

  My main data is in ReportDB.tblReportHistory, but my lookup tables are in a separate database on the same server. (don't know why)

  I have used the Lookup function and grouping successfully, but now my lookup table is in a separate database with a primary key composed of 2 columns...(ie I need a more complex query than the Lookup function)

  I want to display ConfigDB.tblSelection1.Selection in my report where

  ConfigDB.tblSelection1.Prog = ReportDB.tblReportHistory.Program and

  ConfigDB.tblSelection1.SelID = ReportDB.tblReportHistory.Sel1

  and group by

  ReportDB.tblReportHistory.Program

  ConfigDB.tblSelection1.Selection

  ReportDB.tblReportHistory.WashType

  ConfigDB.tblSelection1 looks like this

  Prog,SelID,Selection

  4,1,prog4sel1

  4,2,prog4sel2

  4,3,prog4sel3

  5,1,prog5sel1

  5,2,prog5sel2

  5,3,prog5sel3

  6,1,prog6sel1

  6,2,prog6sel2

  6,3,prog6sel3

  I hope this makes sense.

  Any help would be gratefully appreciated.

  Thanks,

  Jen.

  PS I'm not allowed to modify database in any way

• Possinator

  Hall of Fame

  Points: 3422

  More actions

  November 25, 2011 at 5:08 pm

  #1413076

  Why don't you use fields from both databases by using the fully qualified names in your query/dataset.

  select

  c.Prog

  ,c.Selection

  ,c.SelID

  ,r.Program

  ,r.Sel1

  ,r.WashType

  from

  configDB.dbo.tblSelection1 c

  inner join ReportDB.dbo.tblReportHistory r

  on r.Program = c.Prog

  and r.Sel1 = c.SelID

  (replace dbo with correct schema)

  If you need a list from the ConfigDB for report parameters, create a data set specifically for that purpose - select from the configDB.

  [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

• jen33

  Newbie

  Points: 2

  More actions

  November 28, 2011 at 3:04 am

  #1413302

  Thank You!