locked
creating a dynamic crystal report with asp.net 2.0 i.e., VS 2005 RRS feed

  • Question

  • User-591396027 posted

    Hi,

    I have a procedure where the number of columns in not constant. It changes based on the search criteria.

    Now i need to build a crystal report based on the result of that stored procedure.

    I am never created dynamic report. Can someone help me in this

    Thanks in Advance

    Wednesday, February 15, 2012 6:17 PM

All replies

  • User803630265 posted

    hi u see this link

    http://www.c-sharpcorner.com/UploadFile/rsubhajit/CrystalReportwithDataSet03012006060655AM/CrystalReportwithDataSet.aspx

    Wednesday, February 15, 2012 10:29 PM
  • User-591396027 posted

    Thanks for your reply , But my question is that i don't have a fixed column to select . The number of column changes as per the input parameters of SP.

    PFB for the SP which i am talking about

    -----------------------------------------------

    USE [remedy]
    GO
    /****** Object: StoredProcedure [dbo].[usp_get_ticket_aging] Script Date: 02/15/2012 18:55:18 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO

    ALTER procedure [dbo].[usp_get_ticket_aging]
    (@ACCOUNT varchar(255) = '*'
    , @UCI varchar(255) = '*'
    , @ORIGIN varchar(255) = 'Telepacific'
    , @DATE_FROM varchar(255) = ''
    , @DATE_TO varchar(255) = ''
    , @SUMMARY int = 0
    , @CATEGORY varchar(255) = ''
    , @VIP_CODE varchar(255) = ''
    )
    as

    set nocount on
    declare @sql varchar(8000)
    declare @sum_fields varchar(8000)
    select @sum_fields = ''

    if @category <> '' begin
    select @category = replace(@category, ';', ',')
    select @category = @category + ','
    end

    if @VIP_CODE <> '' begin
    select @VIP_CODE = replace(@VIP_CODE, ';', ',')
    select @VIP_CODE = @VIP_CODE + ','
    end

    if @ACCOUNT='0' or @ACCOUNT='*' begin
    SELECT @ACCOUNT = 'all'
    end

    if @UCI='0' or @UCI='*' begin
    SELECT @UCI = ''
    end
    SELECT @UCI = '' + REPLACE(@UCI, '*', '%') + '%'

    DECLARE @DATE_FROM_IN datetime, @DATE_TO_IN datetime

    if @date_from = '' or @date_from = '*' begin
    select @date_from_in = (select min(arrival_time) from troubleticket
    where account_type = 'Premier Account'
    and (uci like @uci) and (charindex(category + ',', @category) > 0 or @category = '')
    and charindex(convert(varchar(255), vip_code) + ',', @vip_code) > 0 or @vip_code = '')
    end else begin
    SELECT @DATE_FROM_IN = CONVERT(DATETIME, @DATE_FROM)
    end

    if @date_to = '' or @date_to = '*' begin
    select @date_to_in = (select max(arrival_time) from troubleticket
    where account_type = 'Premier Account'
    and (uci like @uci) and (charindex(category + ',', @category) > 0 or @category = '')
    and charindex(convert(varchar(255), vip_code) + ',', @vip_code) > 0 or @vip_code = '')
    end else begin
    SELECT @DATE_TO_IN = CONVERT(DATETIME, @DATE_TO)
    end


    create table #filler (record_id int)
    create table #filler2 (record_id int)
    insert into #filler (record_id) values(0) insert into #filler (record_id) values(1) insert into #filler (record_id) values(2) insert into #filler (record_id) values(3) insert into #filler (record_id) values(4) insert into #filler (record_id) values(5) insert into #filler (record_id) values(6) insert into #filler (record_id) values(7) insert into #filler (record_id) values(8) insert into #filler (record_id) values(9) insert into #filler (record_id) values(10) insert into #filler (record_id) values(11) insert into #filler (record_id) values(12) insert into #filler (record_id) values(13) insert into #filler (record_id) values(14) insert into #filler (record_id) values(15) insert into #filler (record_id) values(16) insert into #filler (record_id) values(17) insert into #filler (record_id) values(18) insert into #filler (record_id) values(19) insert into #filler (record_id) values(20) insert into #filler (record_id) values(21) insert into #filler (record_id) values(22) insert into #filler (record_id) values(23) insert into #filler (record_id) values(24) insert into #filler (record_id) values(25) insert into #filler (record_id) values(26) insert into #filler (record_id) values(27) insert into #filler (record_id) values(28) insert into #filler (record_id) values(29) insert into #filler (record_id) values(30) insert into #filler (record_id) values(31) insert into #filler (record_id) values(32) insert into #filler (record_id) values(33) insert into #filler (record_id) values(34) insert into #filler (record_id) values(35)
    select @sql = 'insert into #filler2 select top ' + (convert(varchar(255), datediff(mm, @date_from_in, @date_to_in) + 1 )) + ' * from #filler'
    exec(@sql)

    select
    uci,replace(replace(replace(replace(replace(upper(isnull(service_street1, '')), ' ', ' '), ' ', ' '), '.', ''), 'STREET', 'ST'), 'ROAD', 'RD') as service_street1
    , replace(right(convert(varchar(255), arrival_time, 6), 6), ' ', '_') as mmm_yy
    , year(arrival_time) as yyyy
    , count(*) as tickets
    into #tmp_tickets
    from troubleticket
    where account_type = 'Premier Account'
    and ((uci like @uci)
    and (charindex(category + ',', @category) > 0 or @category = '')
    and (charindex(convert(varchar(255), vip_code) + ',', @vip_code) > 0 or @vip_code = ''))
    and arrival_time between @date_from_in and @date_to_in
    group by uci, replace(replace(replace(replace(replace(upper(isnull(service_street1, '')), ' ', ' '), ' ', ' '), '.', ''), 'STREET', 'ST'), 'ROAD', 'RD')
    , replace(right(convert(varchar(255), arrival_time, 6), 6), ' ', '_')
    , left(convert(varchar(255), arrival_time, 11), 5)
    , year(arrival_time)
    order by left(convert(varchar(255), arrival_time, 11), 5)


    declare @service_street1 varchar(255), @mmm_yy varchar(255), @tickets varchar(255)

    create table #tmp (
    uci varchar(255) default 0
    , MRC int default '0'
    , locations int
    , location_name varchar(255)
    , position_index varchar(5) default 0
    )
    declare @recordid int

    DECLARE CURSOR_DATE CURSOR FOR
    SELECT * FROM #filler2 order by record_id desc
    OPEN CURSOR_DATE
    FETCH NEXT FROM CURSOR_DATE INTO @recordid
    WHILE (@@fetch_status=0) BEGIN
    select @sql = 'alter table #tmp add [' + replace(right(convert(varchar(255),dateadd(mm, -@recordid, @date_to_in), 6), 6), ' ', '_') + '] int default 0'
    SELECT @sum_fields = @sum_fields + ', sum(convert(int, [' + replace(right(convert(varchar(255),dateadd(mm, -@recordid, @date_to_in), 6), 6), ' ', '_') + '])) as [' + replace(right(convert(varchar(255),dateadd(mm, -@recordid, @date_to_in), 6), 6), ' ', '_') + ']'
    if month(dateadd(mm, -@recordid, @date_to_in)) = 12 and year(dateadd(mm, -@recordid, @date_to_in)) <> year(@date_to_in) begin
    SELECT @SQL = @SQL + ', [Total_' + convert(varchar(4), right(year(dateadd(mm, -@recordid, @date_to_in)), 4)) + '] int default 0'
    SELECT @sum_fields = @sum_fields + ', sum(convert(int, [Total_' + convert(varchar(4), right(year(dateadd(mm, -@recordid, @date_to_in)), 4)) + '])) as [Total_' + convert(varchar(4), right(year(dateadd(mm, -@recordid, @date_to_in)), 4)) + ']'
    end
    -- print @sql
    exec(@sql)
    FETCH NEXT FROM CURSOR_DATE INTO @recordid
    END
    CLOSE CURSOR_DATE
    DEALLOCATE CURSOR_DATE
    SELECT @SQL = 'alter table #tmp add [Total_' + convert(varchar(4), right(year(dateadd(mm, 0, @date_to_in)), 4)) + '] int default 0'
    SELECT @sum_fields = @sum_fields + ', sum(convert(int, [Total_' + convert(varchar(4), right(year(dateadd(mm, 0, @date_to_in)), 4)) + '])) as [Total_' + convert(varchar(4), right(year(dateadd(mm, 0, @date_to_in)), 4)) + ']'
    exec(@sql)

    --select * From tbl_test_rpt

    --select 'hi' as hi
    --return
    declare @iuci varchar(255)

    insert into #tmp
    (uci, locations, location_name, position_index)
    select uci, 0, service_street1, 1 as position_index from #tmp_tickets group by uci, service_street1

    DECLARE CURSOR_DATE CURSOR FOR
    SELECT uci, service_street1, mmm_yy, tickets FROM #TMP_TICKETS

    OPEN CURSOR_DATE
    FETCH NEXT FROM CURSOR_DATE INTO @iuci, @service_street1, @mmm_yy, @tickets
    WHILE (@@fetch_status=0) BEGIN
    select @sql = 'UPDATE #TMP set [' + @mmm_yy + '] = ' + @tickets + ' where uci = ' + '''' + replace(@iuci, '''', '''' + '''') + '''' + ' and location_name = ' + '''' + replace(@service_street1, '''', '`') + '''' + ''
    -- print(@sql)
    exec(@sql)
    FETCH NEXT FROM CURSOR_DATE INTO @iuci, @service_street1, @mmm_yy, @tickets
    END
    CLOSE CURSOR_DATE
    DEALLOCATE CURSOR_DATE
    declare @yyyy varchar(255)

    DECLARE CURSOR_DATE CURSOR FOR
    SELECT uci, service_street1, yyyy, sum(tickets) as tickets from #tmp_tickets group by uci, service_street1, yyyy
    OPEN CURSOR_DATE
    FETCH NEXT FROM CURSOR_DATE INTO @iuci, @service_street1, @yyyy, @tickets
    WHILE (@@fetch_status=0) BEGIN
    select @sql = 'UPDATE #TMP set [Total_' + RIGHT(@yyyy, 4) + '] = ' + @tickets + ' where uci = ' + '''' + replace(@iuci, '''', '''' + '''') + '''' + ' and location_name = ' + '''' + replace(@service_street1, '''', '`') + '''' + ''
    -- print(@sql)
    exec (@sql)

    FETCH NEXT FROM CURSOR_DATE INTO @iuci, @service_street1, @yyyy, @tickets
    END
    CLOSE CURSOR_DATE
    DEALLOCATE CURSOR_DATE

    select @sql = '
    insert into #tmp
    select uci, mrc, count(*) as locations, null as location_name, 0 as position_index' + @sum_fields + '
    from #tmp
    group by uci, mrc
    '
    exec(@sql)

    select
    distinct b.uci, a.account_no
    into #tmp_acct
    from troubleticket a, #tmp b
    where a.account_type = 'premier account'
    and a.uci = b.uci
    group by b.uci, a.account_no

    select
    a.uci, sum(c.mrc) as mrc
    into #tmp_mrc
    from #tmp_acct a, (select account_no, max(mrc) as mrc from dwfx.dbo.current_mrc_tp group by account_no) c
    where c.account_no = a.account_no
    group by a.uci

    update #tmp
    set #tmp.mrc = #tmp_mrc.mrc
    from #tmp_mrc
    where #tmp.uci = #tmp_mrc.uci

    update #tmp set mrc = -99999, locations = -99999 where position_index = 1
    delete from #tmp where position_index <> 0
    select * from #tmp
    where position_index = @summary
    order by uci, position_index, location_name, locations

    ---------------------------------------------------------------------------

    I hipe you got my problem ...

    Thursday, February 16, 2012 12:46 AM
  • User-591396027 posted

    May be i can make the things simpler , The report which need to be displayed will have the count of tickets for each month and maximum it will display the records for 36 months.

    Now if i serach the tickets from Jan 2012 to Feb 2012 then the out put will be 

    UCI , MRC , Location , Jan_12 , Feb_12 , Total_2012

    Now if i serach the tickets from June 2011 to Feb 2012 then the out put will be 

    UCI , MRC , Location , Jun_11 , jul_11 , Aug_11 , Sep_11 , Oct_11 , Nov_11 , Dec_11 , Total_2011 , Jan_12 , Feb_12 ,  Total_2012

    I get these outputs in the procedure , Now i want to display by using crystal report . Can someone help me in this please. Its very urgent.

    Your immidiate help will rescue me.

    Thursday, February 16, 2012 7:36 PM
  • User-37275327 posted

    I think you need to go for crosstab in CR,

    http://www.youtube.com/watch?v=Vp3_Vy1eSXo

    http://diegworld.blogspot.com/2009/11/cross-tab-in-crystal-reports.html

    Thursday, February 16, 2012 10:50 PM
  • User-591396027 posted

    Hi,

    Thanks for your reply , Can you please provide some kind of code/method as i am very much new to the crystal report and the link didn't help much in case of my problem.

    Thanks

    Saturday, February 18, 2012 9:39 PM
  • User803630265 posted

    u may be use this code

    Dim crossReportWithHead1 As New _
      crossReportWithHeadcrossReportWithHead1.GroupColumnName = "Month"
    crossReportWithHead1.HeaderColumnName = "Year"
    crossReportWithHead1.RepeatColumnName = "Price"
    crossReportWithHead1.ItemColumnName = "Product"
    crossReportWithHead1.AvgColumn = True
    crossReportWithHead1.SourceTable = tempTable()
    
    Dim dt As DataTable = crossReportWithHead1.CreateReport
    Dim dr As DataRow = dt.NewRow() dt.Rows.Add(dr)
    DataGrid1.DataSource = dt DataGrid1.DataBind()
    Dim DataGridGrouper1 As New DataGridGrouper DataGridGrouper1.GroupColumn(DataGrid1, 0)

    and if u see full detail than go this link

    http://www.codeproject.com/Articles/12045/Cross-Tab-Reports#

    Saturday, February 18, 2012 10:43 PM
  • User-591396027 posted

    Thanks you very much for your reply, But my problem here is that i can not have month in a column and year in a row, I need the report having column as 'dec_11' , 'total_2011' , 'jan_12' , 'feb_12' , 'total_2012' , if the user searched from dec - 2011 to feb -2012

    PFA for the screen shot the view of the report which i wanted , but i have no idea how this can be done as the column names do change also it have both month and year . 

    http://s17.postimage.org/ycxy2fo7j/sample.jpg

    Saturday, February 18, 2012 11:34 PM
  • User972873971 posted

    you can pass SQL query and make report according to the query , chekc this one..

    http://csharp.net-informations.com/crystal-reports/csharp-crystal-reports-sql-query.htm

    gail.

     

     

     

    Tuesday, May 14, 2013 5:08 AM