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 forselect 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_curfrom #t1 open display_statistic_cursorfetch 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 = 0begin-- 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_norenewalfrom #level_statisticswhere 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_curendclose display_statistic_cursor
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_norenewalfrom #t1 t1join #level_statistics ls on ls.month_year_pre = t1.month_pre
Hope that helps
Mark
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,
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
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,
When you set upnthe SSRS report add a Group to your tablix control wth a header.
This will allow youto display the t1 values in the header and then the level statistics values in the detal section.
Below you may be able to see an example of this where I show a production Name and production details then performance date with performance details and finaly details rows for the price types for each performance
URLadded in case image doesn't show
http://www.tessituranetwork.com/Community/members/markridley2246/files/ssrs_5F00_grouping.png.aspx
Attach files go to you public profile and in the right hand sideclick the view all files link and you should get option to add a file there
Thanks Mark for your info. it's very helpful. I will try it and let you know. Thanks again,
Mark,
Thank You So Much for your time to assist me to be able to control the groups in the report. I just added 2 groups into tablix. One group is for t1 table to control month-year and one group inside the previous group helps to control all the details in level_statistics table. It's working as expected.