• Replies 7 replies
  • Subscribers 337 subscribers
  • Views 21 views
  • Users 0 members are here
Related

Fields not display on the dataset when using SQL Cursor in SSRS

Harry Hoang
$organization

Hello,

I am doing a custom report on MS visual Studio. I am writing a stored procedure that contains cursor to fetch data from two SQL temp table #t1 and #level_statistics to display into the SSRS report. However, when I setup the stored procedure in MS visual studio, then I don't get any fields from the dataset to setup on report layout.

Anyone having any idea for this? any help will be appreciated. Thanks,

-Harry

Here is what I am doing:

+---------------

| Month 1

+---------------

 | Here to display membership statistic for this month

then continue for Month2, Month3 .... upto whatever month user want to display from report and utilities box.

Here is my cursor: 

declare display_statistic_cursor cursor for
select month_cur, month_pre, renewed_pre, not_renewed_pre,total_pre,percent_renewed_pre,percent_not_renewed_pre,
renewed_cur,not_renewed_cur,total_cur,percent_renewed_cur,percent_not_renewed_cur
from #t1
open display_statistic_cursor
fetch from display_statistic_cursor into @month_cur, @month_pre, @renewed_pre, @not_renewed_pre,@total_pre,@percent_renewed_pre,@percent_not_renewed_pre,
@renewed_cur,@not_renewed_cur,@total_cur,@percent_renewed_cur,@percent_not_renewed_cur

while @@FETCH_STATUS = 0
begin
-- this here to display month and year of each month
select
case @month_pre - (@month_pre / 12) * 12
when '0' then 'Dec'
when '1' then 'Jan'
when '2' then 'Feb'
when '3' then 'Mar'
when '4' then 'Apr'
when '5' then 'May'
when '6' then 'Jun'
when '7' then 'Jul'
when '8' then 'Aug'
when '9' then 'Sep'
when '10' then 'Oct'
when '11' then 'Nov'
end
+ ' ' + case @month_pre - (@month_pre/12) * 12
when 0 then ltrim(str((@month_pre/12) - 1))
when 12 then ltrim(str((@month_pre/12) - 1))
else ltrim(str(@month_pre/12))
end pre_month_year,
@renewed_pre renewed_pre,@not_renewed_pre not_renewed_pre,@total_pre total_pre,@percent_renewed_pre percent_renewed_pre,
@percent_not_renewed_pre percent_not_renewed_pre,

case @month_cur - (@month_cur / 12) * 12
when '0' then 'Dec'
when '1' then 'Jan'
when '2' then 'Feb'
when '3' then 'Mar'
when '4' then 'Apr'
when '5' then 'May'
when '6' then 'Jun'
when '7' then 'Jul'
when '8' then 'Aug'
when '9' then 'Sep'
when '10' then 'Oct'
when '11' then 'Nov'
end
+ ' ' + case @month_cur - (@month_cur/12) * 12
when 0 then ltrim(str((@month_cur/12) - 1))
when 12 then ltrim(str((@month_cur/12) - 1))
else ltrim(str(@month_cur/12))
end cur_month_year,
@renewed_cur renewed_cur,@not_renewed_cur not_renewed_cur,@total_cur total_cur,@percent_renewed_cur percent_renewed_cur,
@percent_not_renewed_cur percent_not_renewed_cur
-- this here to display the total membership details based on membership levels of each month
select pre_renewal, pre_norenewal, mem_lvl, cur_renewal,cur_norenewal
from #level_statistics
where month_year_pre = @month_pre

fetch next from display_statistic_cursor into @month_cur, @month_pre, @renewed_pre, @not_renewed_pre,@total_pre,@percent_renewed_cur,@percent_not_renewed_pre,
@renewed_cur,@not_renewed_cur,@total_cur,@percent_renewed_cur,@percent_not_renewed_cur
end
close display_statistic_cursor



[edited by: Harry Hoang at 6:07 PM (GMT -6) on 1 Mar 2017]
Parents
  • $organization

    I have never tried using SSRS with output durect from a cursor r have multipel datasets come out of the Stored procedure as normally it only reads the first (or specified) one.

     

    I am assuming the eason you are using the cursor  is because t1 has totals and level_stats are individual levels, but you can do this in a single select and then use SRS to only show the t1 stats in the group header level and the details beneath.

    Also  the modulo (%) function and dateadd function can makes your case statements simpler (I tend to avoid typing in month names esp after taking on other peoples reprots where they did this but typo-ed month names)

    Your SQL would be something like this

    select month_pre=left(DATENAME(month, DATEADD(month, t1.month_pre-1, CAST('2000-01-01' AS datetime))),3) + CONVERT(varchar(20),t1.month_pre-(t1.month_pre%12)),
    t1.renewed_pre,
    t1.not_renewed_pre,
    t1.total_pre,
    t1.percent_renewed_pre,
    t1.percent_not_renewed_pre,
    month_cur=left(DATENAME(month, DATEADD(month, t1.month_cur-1, CAST('2000-01-01' AS datetime))),3) + CONVERT(varchar(20),t1.month_cur-(t1.month_cur%12)),
    t1.renewed_cur,
    t1.not_renewed_cur,
    t1.total_cur,
    t1.percent_renewed_cur,
    t1.percent_not_renewed_cur,
    ls.pre_renewal,
    ls.pre_norenewal,
    ls.mem_lvl,
    ls.cur_renewal,cur_norenewal
    from #t1 t1
    join #level_statistics ls on ls.month_year_pre = t1.month_pre

     

    Hope that helps

    Mark

  • $organization in reply to Mark Ridley

    Hello Mark,

    Thank you for your help. I'm able to join the two tables to get the output to SSRS and it will show all the data fields in the setup dataset. However, the thing I'd like to break the data into the month-year categories by displaying the month and year as a category title, then all the membership break-up after that. For example,

    Jul 2015 <---- This title displays only one time here. (this pulled from table #t1)

    all the membership break-up details here. (multiple records pulled from table #level_statistics) for July 2015

    Aug 2015 <---- this title display only one here.(this pulled from table #t1)

    all the membership break-up details here. (multiple records pulled from table #level_statistics) for Aug 2015

    Sep 2015 <----- this display only one here.(this pulled from table #t1)

    all the membership break-up details here. (multiple records pulled from table #level_statistics) for Sep 2015

    ....... then continue on Oct, Nov, Dec .... to whatever dates that user chose to...

    If I just joined that 2 tables then I am going to get Month-Year for each line in the report, that why I am writing the cursor to break them down into each month-year. I wish can be attached the sample report here for you to look at, so you understand what I mean here. Where can attach a document here?

    I am not sure the SSRS system can control this case in order to display the right fields in the report. Do you know anything that I can fix this case?

    Thank you for your help, 

    -Harry 

  • $organization in reply to Harry Hoang

    Sounds like to me you need some grouping in the report. This will allow you to have a seperate tab in the ouput for each month. I would just have a the month int value each row. I do this all the time. So each month could be its own tab or matirx or whatever. You should not have to break this up at all into multiple datasets, unless I am completely not following at all. It you share the entire thing I can take a look.

    Travis



    [edited by: Travis Armbuster at 12:01 PM (GMT -6) on 2 Mar 2017]
  • $organization in reply to Travis Armbuster

    Hello Travis,

    Thanks for the quick reply here. Yes, I can send you the stored procedure. How I can attach file here? I can't see any here to attach document.

    Thanks again,

    -Harry

Reply Children
No Data