I' m using Microsoft Reporting. Is there a way to create Report .rdlc file Dynamically on the fly? I' m using VB.net. Any help can be appreciated. Thanks Mythili

Its just and XML file so sure you can. You can use either the textwriter or xmltextwriter classes among other ways. I suppose the real question is what you want to do with it afterwards.

the problem I' m working on right now is that I have to run the query depending on the user selection on the .aspx page. So, I used Dynamic Sql to get the query results. Among other columns that are to be shown in the Report I have a collection of 7 columns where the user is allowed to select the ones he want to see in the report. I' m using a stored procedure. the query in which is set @ssql1='select Distinct sfc.fm_code+'' '' + sfc.customer as FMCustomer,rm.rptg_mark,rm.car_number,ssl.location_shop_name,ssl.Location_state,ssl.Location_city, DATEDIFF([day],ss.arrival_date,ss.ship_date) AS Days_In_Shop,ss.ship_date,ss.orig_entry_date,ss.arrival_date,sfpa.Description as plant_assignment,minPOD, maxPOD,csts.Cost,rsn.[Description] AS Reason_Description,ss.shp_CUST_SELECT_DISPO,c.Description as commodity,cc.Commoditycode,'+@fields+',sss.Description,cmcm.top_level_name Lessee ,'''+@NR+''' as numrowsfrom ariprdsql02.car_maintenance.dbo.shp_shopping_types sst,ariprdsql02.customer_master.dbo.cm_customer cmcm,ariprdsql02.car_maintenance.dbo.shp_shopping_status sss, ariprdsql02.commodity.dbo.commodities c, ariprdsql02.commodity.dbo.commoditycode cc,ariprdsql02.rm.dbo.rm_railcar rmr,ariprdsql02.car_maintenance.dbo.shp_fm_plant_assignments sfpa ,ariprdsql02.car_maintenance.dbo.shp_fm_codes sfc , ARIPRDSQL02.car_maintenance.dbo.shp_CarAnalysis_Custom_Defined_Fields cacdf,ariprdsql02.carmaster.dbo.rm_stenciling_history rm,ARIPRDSQL02.car_maintenance.dbo.shp_car_detail scd,ARIPRDSQL02.car_maintenance.dbo.shp_shopping ss left outer joinARIPRDSQL02.car_maintenance.dbo.shp_shop_locations ssl onss.shp_shop_locations_id = ssl.shp_shop_locations_id LEFT OUTER JOIN (SELECT shp_shopping_ID, MIN(POD_Date) minPOD, MAX(POD_Date) as maxPOD FROM ARIPRDSQL02.car_maintenance.dbo.shp_POD_DATES GROUP BY shp_shopping_ID) pod ON pod.shp_SHOPPING_ID = ss.shp_SHOPPING_ID LEFT OUTER JOINARIPRDSQL02.car_maintenance.dbo.shp_vw_Shopping_CostView_Glovia csts ON csts.c_shopping_ID = ss.shp_shopping_ID LEFT OUTER JOIN ARIPRDSQL02.car_maintenance.dbo.shp_vw_ReasonCodesView rsn ON rsn.shp_shopping_ID = ss.shp_shopping_IDwhere scd.rm_stenciling_history_id=rm.id and sfc.fm_code='''+@fmcode+'''and sfc.customer in (scd.owner_customer,scd.lessee_customer,scd.sublessee_customer) and scd.status=''A'' and scd.shp_car_detail_id = ss.shp_car_detail_id and ssl.location_shop_name IS NOT NULL and sfc.customer=sfpa.customer and sfc.customer=cmcm.customer and sfpa.customer=cmcm.customer and cc.commoditycode=rmr.pri_comm_cd and c.commoditycode=cc.commoditycode and rmr.RM_RAILCAR_KEY_ID = rm.RM_RAILCAR_KEY_ID and sfc.customer=cacdf.customer and scd.shp_car_detail_id = ss.shp_car_detail_idand sss.shp_shopping_status_id = ss.shp_shopping_status_id and sst.shp_shopping_types_id=ss.shp_shopping_types_id and sst.shp_shopping_types_id=''4'' AND rm.rptg_mark=cacdf.rptgmark and rm.car_number=cacdf.carnumber and sfc.customer='+@fmcustomer+' and csts.customer=sfpa.customer and sfpa.description='''+@plantassignment+''' and c.Description='''+@ccomm+''''--print @ssql1exec(@ssql1) I want to bind the Report by the columns that are returned by the query and display the report in the page(report viewer control).I need the complete procedure to build a Report.rdlc file dynamically. Can you please help? thanks Mythili

First question is what is the nature of those seven columns. Are they the same data type? Other than appearing or not in the report do they need to be handled any differently (i.e. some get subtotaled) ? If they do not I would probably create some dynamic capability in my stored procedure where it always brought back 7 generically named columns (say col1 through col 7) but that they alias different data depending on user selections. Then I woulduse report parameters to hide columns. So in this scenario I would not take the avenue of dynamically generating the RDLC. If I needed to dynamically generate the RDLC I would use the report viewer in local mode since that would probably be the simplest and most elegant solution.

thanks for your quick reponse. They are all of the same type varchar.I do not need to total them up .The fields are Fleet manager,Subfleetassigned,home cost center(I don't have to do anything with this but format it to currency.),Product service date(format to date),Group1, Group2,major control type. I have been trying to accomplish this using the Visibility report property. In that case if the user chooses col1,col3,col5 then my report will not rebind the rest of the columns meaning my report will be col1,Blank,col2,blank,col3. if you could tell me a way I can make this col1,col2,col3 that would be great.Samples of the code I tried is IIF(parameters!fieldsDisplay1.value=true,true,false). IIF(instr(Fields!numrows.value="a")>0,True,false) I used this in the visibility property of each of the columns I want to appear dynamically. Thanks Mythili

First I would create a procedure that looked something like Create Procedure Someprocedure @col1 varchar(50), @col2 varchar(50), @col3 varchar(50), @col4 varchar(50), @col5 varchar(50), @col6 varchar(50), @col7 varchar(50) as set nocount on select somestandardcolumns, col1=case when @col1='Fleet Manager' then table.fieldmanger when @col2=subfleetassignid' then table.subfleetassignid when @col3='homecenter' then table.homecenter etc,, else '' end col2=case when @col1='Fleet Manager' then table.fieldmanger when @col2=subfleetassignid' then table.subfleetassignid when @col3='homecenter' then table.homecenter etc,, else '' end etc.. Then I would design the report to support col1 through 7. The same @col procedure paramaters I would also use as report parameters and make those my columns headers. Then on the visibility property of the columns I would put =iif(len(Parameters!col1)=0,True,False). At least thats the basic idea.

sample code is available in the following url http://www.gotreportviewer.com/

Actually the report viewer control allows you to specify the RDLC either from a resource in your project or from a file in the file system. So you can just dynamically create the file in your code and trim the unneeded columns. It is a little more work, but I think the experience will be better overall. Hope this helps, -LukaszGot a bug or feature request? Tell me about it at http://connect.microsoft.com. This posting is provided "AS IS" with no warranties, and confers no rights.

You might take a look at www.rptgen.com. Thanks