July 26, 2012 at 1:27 pm
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 26, 2012 at 1:46 pm
July 26, 2012 at 2:02 pm
Thought this might over complicate things...thats why i went for the two data sets.
July 27, 2012 at 9:37 am
In this case it might be easier to ahve two tablix controls one fetching the job info and the other fetching the event info grouped by Job it.
If you want to merge the two result sets , then conisder using the lookup function.
http://prologika.com/CS/blogs/blog/archive/2009/08/12/reporting-services-lookup-functions.aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply