none
Generate column field value for report.rdlc depend on dynamic Sql statment RRS feed

  • Question

  • Hi all,

    i am using a dynamic Sql statment to retrieve data

    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS OFF 
    GO
    
    
    
    
    
    -- exec dbo.N_Items_News_rep 1,0,'1/1/1900','1/1/1900',0,7703
    
    
    ALTER    PROCEDURE dbo.N_Items_News_rep
    @Main_class int,
    @Sub_class int , 
    @date_from datetime =NULL ,
    @date_to datetime =NULL, 
    @country int,
    @NewID int
    as
    Set noCount on
    Create table #Table1 (id int identity (1,1) ,Item_data varchar (50) default null ,Item_name varchar (50) default null,New_ID int,New_Date datetime)
    insert into #Table1
    SELECT   Items_News.Item_Data, Items.item_name,Items_News.New_ID,News.New_Date
    FROM     Items_News INNER JOIN Items ON Items_News.Item_ID = Items.Item_ID INNER JOIN
               News ON Items_News.New_ID = News.New_ID
    where   (Items.show_on_report = 1) and (Items.MainClass_ID=@Main_class or @Main_class=0) and (Items.SubClass_ID=@Sub_class or @Sub_class=0)
    	  and((News.New_Date between @date_from and @date_to) or (@date_from='1/1/1900' and @date_to ='1/1/1900'))
          --and (News.New_Date between @date_from and @date_to) or (@date_from = '1/1/1900' and @date_to = '1/1/1900')
          and (News.Src_Cntry_ID=@country or @country=0) and (News.New_ID=@NewID)
    
    --new add
    Declare @FirstCol varchar (50)
    Select top 1 @FirstCol=Item_name from #Table1 --where id=1--create queryto get first column
    Declare @sql3 varchar (1000)
    if(@FirstCol is not null)
    begin
    set @sql3='Create table table3 (id int identity (1,1),New_ID int,New_Date datetime,['+@FirstCol+'] varchar (50))'
    exec(@sql3)
    
    --till here
    Declare @sql varchar (1000)
    DEclare @colName varchar (50)
    DECLARE @ItemName CURSOR
    SET @ItemName = CURSOR FOR
    SELECT distinct Item_name FROM #Table1
     open @ItemName
    FETCH NEXT
    FROM @ItemName INTO @colName
    WHILE @@FETCH_STATUS = 0
    
    Begin
    --Work Area
    if(@colName!=@FirstCol)--change here
    Begin
    set @sql=' alter table table3 add ['+@colName+'] varchar (50) default null'
    
    exec(@sql)
    End
    FETCH NEXT
    
    FROM @ItemName INTO @colName
    END
    
    CLOSE @ItemName
    DEALLOCATE @ItemName
    
    
    insert into table3 (new_id)
    Select distinct new_id from #table1
    
    
    DEclare @colName1 varchar (50)
    DECLARE @ItemName1 CURSOR
    Declare @sql1 varchar (8000)
    SET @ItemName1 = CURSOR FOR
    SELECT distinct Item_name FROM #Table1
    open @ItemName1
    FETCH NEXT
    FROM @ItemName1 INTO @colName1
    WHILE @@FETCH_STATUS = 0
    Begin
     	set @sql1='update table3 set ['+@colName1+']= #table1.item_data from table3,#table1 where table3.new_id=#table1.new_id and #table1.item_name like '''+@colName1+''' '
      print (@sql1)
    	exec(@sql1)
    
    FETCH NEXT
    
    FROM @ItemName1 INTO @colName1
    End
    CLOSE @ItemName1
    DEALLOCATE @ItemName1
    
    Select distinct * from table3
    drop table table3
    end
    drop table #Table1
    
    
    
    
    
    
    
    
    
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    
    

    i want to make report.rdlc depend on this statment, i am using Asp.net2008 and sqlserver2000, make report using reportViewer. when i make the report i have to specify to the Table , values like 

    =Fields!val1.Value and i don't know what is the name of the column. How can i manage this

    i searched alot and i didn't find and the code i find i didn't understand it.

    could you help, as it is an urgent problem to me.

    Thanks

    Tuesday, February 22, 2011 12:13 PM

All replies