locked
Generate Dynamic fields for report.rdlc RRS feed

  • Question

  • User137014332 posted

    Hi all,

    i am making a report.rdlc which depend on the statment

     

    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
    

     

    which doesn't have a specific column name

    how can i fill the table in the report

    column value in table  =Fields!columnname.Value (how can i get columnname, it is dynamic)

    thanks

     

    Monday, February 21, 2011 6:51 AM

Answers

  • User1471008070 posted

    I agree with Nishantg totally, this requirement is not a build-in feature, so
    here I would recommend you submit a wish to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback
    Your feedback enables Microsoft to make software and services the best that they can be.<o:o:o:p></o:o:o:p>

    Regards,
    Challen Fu<o:o:o:p></o:o:o:p>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 25, 2011 6:04 AM

All replies

  • User-830595639 posted

    Hi,

    If you have two different set of column in which would return by your sp, then you need to take two dataset in your report and these dataset first with inline query. Also take two different table in report two show result. Take a parameter in your SPs which will let you know that what set of columns are returned and based on this parameter value you can hide/show your table accordingly.

    Tuesday, February 22, 2011 2:16 AM
  • User137014332 posted

    errorHi,

    Thanks for the reply, what i want to show is the result of table3, when i make dataset depend on this sp, it give warning that it can't generate slect statment and unknown #table1. but the procedure is working right. i think this is because these tables are dynamic.

    so there is no columns to give it to the table in the report, and the column names changes when report parameters changes(the column name are specified at runtime).

    (I tried to create report  but if my stored procedure use temp table (ex: #Temp), I'll get the error "There is an error in the query. Invalid object name '#Temp'). But if the stored procedure doesn't include temp table, everything will be fine.)

    Thanks

    Wednesday, February 23, 2011 2:29 AM
  • User-830595639 posted

    Hi,

    First of all, I don't think its possible in SSRS to change the column name at runtime. And for your stored procedure, it seems like that SSRS doesn't allow temporary table. But my thought is that SSRS is nothing do what is happening in the stored procedure, it only take the output from the SP, so if SP is running correctly then it should also run in your SSRS.

    In your SP just try to remove the last statement (drop #Table1) and then try to use it in report.

    Wednesday, February 23, 2011 4:57 AM
  • User137014332 posted

    Hi,

    i removed  (drop #Table1),but there is nothing, how can i use this stored proceure in the report(there is no column name)

    how can i fill the table in the report

    this statment is a previous post for me on this Forum

    http://forums.asp.net/t/1644147.aspx/1/10

    and i need to connect this statment to report.rdlc

    Wednesday, February 23, 2011 5:01 AM
  • User-830595639 posted

    Hi,

    To add fields in your dataset, you can do using one of these two methods.

    1. Take a query which includes all your column names (if value is not availabe then take them as dummy column name). Use simple query and execute in your dataset, this will fill your dataset with the columns name.

    2. Other way is, take a dataset and goto its property. In property you can find "Fields" tab, in this you can add your field name and the source column name.

    Wednesday, February 23, 2011 5:13 AM
  • User137014332 posted

    Hi,

    actually i don't know the column names, it changes everytime i change the parameter.

    the query i use to return the data is

    select * from table3(which is temp table and i don't know the column names on it).

    Wednesday, February 23, 2011 5:19 AM
  • User-830595639 posted

    Hi,

    As per your requirement I am affraid that its not possible in SSRS. Because for creating a Report you must know the columns name in advance.

    Wednesday, February 23, 2011 5:29 AM
  • User1471008070 posted

    I agree with Nishantg totally, this requirement is not a build-in feature, so
    here I would recommend you submit a wish to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback
    Your feedback enables Microsoft to make software and services the best that they can be.<o:o:o:p></o:o:o:p>

    Regards,
    Challen Fu<o:o:o:p></o:o:o:p>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 25, 2011 6:04 AM
  • User137014332 posted

    Thank You, i will try to change on the design of the report

    Sunday, February 27, 2011 6:14 AM
  • User481172561 posted

    Take a look at www.rptgen.com

    Thanks

    Monday, July 4, 2011 6:09 PM
  • User481172561 posted

    Take a look at www.rptgen.com. With this you can change the columns dynamically.

    Thanks

     

    Friday, March 2, 2012 3:33 PM