ASP.NET - Representing unknown number of columns in RDL

Asked By Anandh Ramanujam on 11-Nov-11 07:13 AM
Hi,

I am working on rdl. My stored procedure is returning columns dynamically based on the parameters. That means, we cant predict the number of columns while designing rdl.

How we can achieve this...

Please help me in this regard.

Thanks.

Kirtan Patel replied to Anandh Ramanujam on 11-Nov-11 07:37 AM
This is how this can be done. In the RDLC file locate /Report/Body/ReportItems/Table node and do the following inside it:
 
define the header of a new column - add a new TableCell inside Header node
bind the column with data (from DataTable) - add a new TableCell inside Details node
define the width of the colum - add a new TableColumn inside TableColumns
Apart from that you need to modify the definition of the DataSet that will ship the data. It is defined as a Field node within /Report/DataSets.
 
The easiest way to add new nodes is to get its parent, copy its last child and update the copy with appropriate values, and add it as the new last child. This way you will probably have to update only a couple of strings instead of creating the new node which has another nodes as children etc.
 
Then you need to create a DataTable object, populate it with data and bind it with the ReportViewer as its data source.
 
Now Bind the DataFrom DataTable as Below
 
ReportDataSource rds = new ReportDataSource(DS_NAME, DATA_TABLE);
ReportViewer1.LocalReport.ReportPath = string.Empty;
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(rds);
 
byte[] rdlBytes = Encoding.UTF8.GetBytes(XML_DOC.OuterXml);
MemoryStream stream = new MemoryStream(rdlBytes);
ReportViewer1.LocalReport.LoadReportDefinition(stream);
 
Anil Kumar replied to Anandh Ramanujam on 11-Nov-11 07:40 AM
Hi Anandh,

See the steps below:

1). Name the columns with a result from your query, so you can either pass it in to the query or derive it there.
2). Build out the report as if it had the maximum number of columns, and hide them if they are empty.

Let say, I had to build a report that would report sales for a month up to a year, but the months weren't necessarily starting in January. I passed back the month name in one column, followed by the numbers for my report. On the .rdl, I built out 12 sets of columns, one for each possible month, and just used an expression to hide the column if it were empty. The result is the report appears to expand out to the number of columns needed.
Also you can create reports in excel, for this refer to the article:

http://msdn.microsoft.com/en-us/library/dd255234.aspx

Hope it helps you.

Do update us again

Thank you
Anil

Anandh Ramanujam replied to Anil Kumar on 11-Nov-11 07:58 AM
Your idea is good.

this is what my exact scenario, im passing the starting month and ending month. My dynamic columns are the month names only.

But the problem is im passing the start year and end year parameters also.

if i pass the year parameters as 2000 to 2011, then the month names of all the year has to be bind dynamically.

I think u understand my problem...
Anil Kumar replied to Anandh Ramanujam on 11-Nov-11 08:22 AM
Hi Anandh,

I'll return back with the solution, meanwhile have a look at the article to generate reports, I forgot to mention it in my earlier post.

http://www.codeproject.com/KB/reporting-services/DynamicReport.aspx

Thank you
Anil
Anil Kumar replied to Anil Kumar on 11-Nov-11 08:27 AM
Anandh, It would be a good idea to use the matrix structure as it will modify your sp to return dynamic data to the structure.
See what msdn have to say over it:
http://msdn.microsoft.com/en-us/library/ms157334.aspx

Also refer to a relevant blog from Sonali:
http://sonalimendis.blogspot.com/2011/07/dynamic-column-rdls.html

Hope it serves you better.

Do update us again.

Thank you
Anil