creating a report with 2 data sets in business intelligence studio
I am trying to create a report in Business Intelligence Studio.
I have two datasets (stored procedures).
The first is a query with technicians jobs called CALLS.
Select * from
CALL
left outer join Opteams on call.call_operatorid = opteams.ot_operatorid
WHERE CALL_STATUS <> 'Closed' and (CALL_OPERATORID = 'IT Helpdesk' or ot_team = 'IT Helpdesk')
The First few fields of query are:
CALL_CODE, CALL_USERNAME, CALL_TITLE, CALL_PROBLEM
200 Peter S landline faulty phone
851 Thomas PC faulty PC
The second query is based on Events of those jobs
The query for the events is:
Select * from event
Inner join CALL on event.event_callcode = call.call_code JOIN OPTEAMS on call.call_operatorid = opteams.ot_operatorid
WHERE (CALL_operatorid = 'IT Helpdesk' or ot_team = 'IT Helpdesk') and CALL_STATUS <> 'Closed'
The first few fields are:
EVENT_CODE, EVENT_CALLCODE, EVENT_ADDEDBY, EVENT_COMMENT
1213 361 admin Call logged by Email
1477 851 admin Call logged by email
1479 851 admin Call Logged by Email
I would like to join both datasets so that i can create a report for each job with all the events associated with each job.
There are many jobs. I would like a user to be able to click on view report and then be able to click print...so that they can print all
the jobs off...without having to select a parameter for each job.
So the report would look the following:
CALL_CODE, CALL_USERNAME, CALL_TITLE, CALL_PROBLEM
851 Thomas PC faulty PC
Events
EVENT_CODE, EVENT_CALLCODE, EVENT_ADDEDBY, EVENT_COMMENT
1477 851 admin Call logged by email
1479 851 admin Call Logged by Email
Thanks in advance for your help.
July 26th, 2012 3:32pm
Hi,
which version you are using 2008 r2 ? if yes then you can use lookup function
for more detail see this link
http://beyondrelational.com/modules/2/blogs/81/posts/11266/lookup-in-ssrs-2008-r2.aspx
Hope this will help you !!!
Sanjeewan
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2012 3:46pm
Thanks for reply.
I understand now how to create Lookups using multiple data sets. If i use your suggestion with creating an extra field at the end
I am a little confused as to how i would create the report in the my layout below
How could i get each report off to print of on seperate pages for each Call_CODE. There will be many call_codes.
CALL_CODE, CALL_USERNAME, CALL_TITLE, CALL_PROBLEM
851 Thomas PC faulty PC
Events
EVENT_CODE, EVENT_CALLCODE, EVENT_ADDEDBY, EVENT_COMMENT
1477 851 admin Call logged by email
1479 851 admin Call Logged by Email
July 26th, 2012 5:39pm
Is this possible to achieve?
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2012 5:58pm