SSRS Dataset Issue?

  • 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

  • I wrote a blog on this subject that should help. Also read the comments below for additional strategies to solving the problem.

    http://www.bidn.com/blogs/Daniel/ssas/1908/ssrs-dataset-fields-disappear-when-using-a-stored-procedure-with-dynamic-sql-%E2%80%93-how-to-get-them-back-and-save-your-report

  • 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