none
Dynamic column generation for dataset to link into report viewer RRS feed

  • Question

  • Hi I am very new to report viewer. I have succesfully deployed one report which is quite simple with fixed column. First I ran the query then I insert into the dataset which I have previously design and in the report I have linked it well with all the columns.

      try
                {
                    string sql = "SELECT purchaseID,amount,debitSum,creditSum FROM tblPurchase";                

                    cmd = new MySqlCommand(sql, conn);

                    //conn.Open();

                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(set1,"Datatable1");
                    }


                    _reportViewer.Reset();
                    _reportViewer.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local;
                    _reportViewer.LocalReport.ReportPath = "Report1.rdlc";
                    Microsoft.Reporting.WinForms.ReportDataSource reportDataSource = new Microsoft.Reporting.WinForms.ReportDataSource();
                    // Must match the DataSet in the RDLC
                    reportDataSource.Name = "DataSet1";
                    reportDataSource.Value = set1.Tables[0];
                    _reportViewer.LocalReport.DataSources.Add(reportDataSource);
                    _reportViewer.RefreshReport();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(string.Format("An error occurred {0}", ex.Message));
                }
                finally
                {
                    if (conn != null) conn.Close();
                }

    My problem now next is below I have another query.

     string sql1 = "SELECT pCode FROM tblPurchase group by supCodes. Based on the codes these will be the columns (each pCode is one column)  with some other additional headings. The issue is that the columns list will by dynamic.  Next for each code I need to extra relevant data from the sample table using this query SELECT pSum, pCodes FROM tblPurchase where pCodes=?" where the pCode value here is the output from string sql1 above. I am stucked here cause I cant create a fixed dataset as I have done in my above report. What is the best mechanism to solve this? Thank you.

    • Moved by Kristin Xie Monday, December 7, 2015 9:59 AM move to appropriate forum
    Sunday, December 6, 2015 9:09 AM

All replies

  • i think it's not easy to like that.

    the report definition file .rdlc is the template for report generating at runtime. the report representation schema is defined at design time. if you want to extend report definition (e.g add new column at table) ,you must modify the report definition schema at runtime.it seems there does not has .net wrapper class for it.

     so that, you must modify the .rdlc xml file at runtime.



    DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.

    Sunday, December 6, 2015 11:04 AM
  • Hi Mathew,

                     Sorry I am kind of new to all this. Where is the .rdlc xml file? How to search that file and edit ? I am trying to google on this I dont find any good solution either.

    Sunday, December 6, 2015 4:34 PM
  • rdl stands for report definition language.

    It's used to define what a report does - when you design a report you end up with an .rdl or .rdlc file.

    rdl is xml so if you rename the extension on your report to xml and opened it in a browser or xml editor or some such then it'd be a bunch of xml.

    It's not compiled.

    As Matthew mentions, you can therefore build a report dynamically at run time by building xml.

    If you understand rdl, that is.

    There is a walk through you might want to take a look at:

    https://technet.microsoft.com/en-us/library/ms170667%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396 

    That gets you the report.

    You need some data.

    In order to get the data for that, you can use ado. Build an sql string with select... your list of fields... from... and use a datadapter to fill a datatable.

    You then present that datatable to the report in the reportviewer.

    I posted code for using a fixed string in your thread:

    https://social.msdn.microsoft.com/Forums/en-US/dc6ad484-0998-413d-ac3b-b1d6fde12e1d/c-wpf-add-reportviewer?forum=wpf

    You just need to adapt the way that a bit and build your string instead of using a fixed one:

    using (SqlDataAdapter da = new SqlDataAdapter(
    		    stringVariableYouBuild, conn))
    I suggest you take a look at that walk through and see what you make of it.


    Hope that helps.

    Technet articles: WPF: Layout Lab; All my Technet Articles

    Sunday, December 6, 2015 5:36 PM
  • take a look at the article in the Andy's post first and decide to how do you exactly want to do Next.

    BTW, does there have lots of reports as you mentioned have the same issue? if just a few, i consider create new report definition.


    DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.

    Monday, December 7, 2015 1:38 AM
  • Hi,

    This forum is discuss and ask questions about the C# programming language.  Based on your description, your case more related to report viewer, I will move your case to Other Forums     > Visual Studio Report Controls forum for better support.

    Regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Kristin Xie Monday, December 7, 2015 9:59 AM
    Monday, December 7, 2015 9:58 AM
  • Dear Matthew and Andy,

                                        I did a further googling and found about matrix. I am trying out that solution with some tweaks on my queries. I am experimenting that. Else I will move to your article. I would just like to update my progress. Will keep posted.

    Monday, December 7, 2015 3:14 PM