March 23, 2011 at 1:35 pm
I'm trying to create a report in BIDS 2008 where the data has many one to many relationships and I'm wondering how to handle this in the report.
Basically the database stores data from jobs that wipe hard drives, so each job can have one to N drives that have been wiped there also are seven custom fields that get populated per job. I wrote all the basic select statements out in SSMS to see if I got all the data I needed but now I'm having trouble getting it all together in the report.
Here is all the basic statements that need to go into the Report
Declare @p1 int
Set @p1 = 1 --1 = first job that was run
select * from Erasure
where ID = @p1
--one to many relationship
select * From CustomerField
where ErasureID = @p1
--one to many relationship
select
'Disk:' + ' ' + convert(nvarchar(10), M.ID)
,M.Model
,M.Serial
,M.MediaSize / 1073741824 as Capacity
,M.[BlockSize]
,M.MediaType
,M.ErasureStatus
,M.SmartStatus
,M.UnlockStr
,M.LockingType
from Media M
INNER JOIN [Partition] P ON M.ComputerID = P.MediaID
where m.ComputerID = @p1
--one to many relationship
select
P.PartitionType
,P.PartitionType
from [Partition] P
INNER JOIN Media M ON P.MediaID = M.ID
where m.ComputerID = @p1
--one to many relationship
select * from Computer
where ID = @p1
--one to many relationship
select * from Ram
where ComputerID = @p1
--one to many relationship
select * from cpu
where ComputerID = @p1
--one to many relationship
select * from Device
where ComputerID = @p1
March 24, 2011 at 6:57 am
The first thing I would probably do is to write all of this into one statement joining all your tables (Erasure to CustomerField to Media to Partition to Computer to Ram to CPU to Device) They all appear to have common keys so that should not present a problem. Add grouping to your statement to eliminate unnecessary redundancies. At that point, it should just be drag and drop fields onto your report.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply