July 5, 2012 at 7:41 am
I am working on a new report and I "had" it working with my first pass, shown below:
declare @events varchar(40)
select @events = case @TeamID
when 15 then '215,164,165,133,135,137,141,146,154'
when 14 then '50,52,54,58,61,69'
when 11 then '30,33,189,182'
when 12 then '30,33,189,182'
end
declare @sql nvarchar(4000)
set @sql = 'select p.firstName + '' '' + p.lastName ''Employee'', pa.shortname, COUNT(*) ''Count''
from People p with (nolock)
right outer join ActionLog al with (nolock) on p.personID = al.personID
right join events ev with (nolock) on al.eventID = ev.eventID
inner join hl.dbo.xref_predictiveactions pa with (nolock) on ev.eventName = pa.vc_action and pa.teamid = ' + @TeamID + '
where p.teamID = ' + @TeamID + ' and p.businessType =''' + @business + ''' and dateRecorded >= ''2012-01-01'' and subtract = 0 and ev.eventID in (' + @events + ') --and p.active = 1 and p.deleted = 0
group by eventname, p.firstName, p.lastName,pa.shortname, pa.sortorder
order by p.lastName,pa.sortorder'
exec sp_executesql @sql
But I need to finish it off now by adding in another conditional level at the top before it runs my dynamic SQL. I have it working in QE but when I make the change in my Report Dataset, it doesn't have any fields for use in the report, so I can't save or go forward.
This is the change, I take out the "select @events" case statement and replace it with:
if @business = 'business'
begin
select @events = case @TeamID
when 15 then '215,164,165,133,135,137,141,146,154'
--when 15 then '103,113,205'
when 14 then '50,52,54,58,61,69'
when 11 then '30,33,189,182'
when 12 then '30,33,189,182'
end
end
if @business = 'leader'
begin
select @events = case @TeamID
--when 15 then '215,164,165,133,135,137,141,146,154'
when 15 then '103,113,205'
when 14 then '50,52,54,58,61,69'
when 11 then '30,33,189,182'
when 12 then '30,33,189,182'
end
end
I am not sure why this is happening, I am new to SSRS. TIA.
Tim
July 5, 2012 at 1:33 pm
I wrote a blog on this subject that should help. Also read the comments below for additional strategies to solving the problem.
July 5, 2012 at 2:28 pm
Thanks Daniel! I will give this a go.
Edit: Worked like a charm! Thank you very much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply