none
Newbie Subreport parameter problems RRS feed

  • Question

  • Hi, I've recently started using the "Report viewer" control in VB.NET VS2005. I'm working on a project which involves me creating a report displaying all module information (one at a time). The layout will be something like:

    --**-

    Module Code(unique)

         mod name      lecturer name     mod title      mod credit   etc

    --**-

    (Subreport - for that module)

       mod pre-requisites   mod co-requisites  etc

     

    (Subreport - for that module)

       mod pathway  etc

     

    I can generate the --**- first section easy enough, however I want the subreports to follow immediately after with the info for that module. This will then get repeated, etc, for each different module. I think I need to pass the module code onto the subreports(?). I cannot find any decent guides, etc on the internet for a solution.

    I hope I've explained enough, can anybody help please?

    Thanks

    Dan

    Wednesday, August 8, 2007 3:53 PM

Answers

  •  

    We did something similar to this where we:

     

    1) Embedded a list in the main report

     

    2) Added the summary fields about the module (row of data) in the list, formatted approprriately

     

    3) We created a subreport as a per-"module" report

     

    4) We wired the list to pass the PK or other per-module unique identifier to the subreport (select the reportviewer embedded in the list, right-click, select Properties, then click the Parameters tab)

     

    5) The subreport had it's own datasource that took the PK/etc - passed into the subreport as a parameter - and, using that as a parameter, built a parametric SQL query that spanned tables and retrieved the fuller set of per-module information. To configure the subreport to receive the parameters, open the subreport in the designer, select it, and in the main menu select Report -> Report Parameters

     

    6) Created and populated appropriate fields in the subreport from it's own datasource

     

    Note that the parameter name has to be exactly the same in both places referenced above. In the subreport's SQL query, use the exact same parameter name except it has to be preceded with an ampersand, i.e. @MySubReportParameter1.  Something like

    SELECT ColumnOfInterest1, ... WHERE TheColumnImSelectingOn=@MySubReportParameter1

     

    What happens at runtime is that the list operates basically as an iterator (for lack of a better term) in that it creates an instance of itself for each row (the current row) of data in it's dataset.

     

    When you wire up the parameters, the list will give the parameters the values out of the row it is currently operating on (just as it populates it's own fields with data from the current row) and instantiates the subreport via the embedded reportviewer, passing those values (from the current row) as parameters

     

    It's pretty slick but it's kind of klunky and takes some patience/experimentation/mucking-about to get all the plumbing right.

     

    Hope this helps...

     

    - Lance

    Saturday, August 11, 2007 1:14 AM
  • If you have sub-reports then you need to handle the SubreportProcessingEventHandler event.

     

    That is the event that is fired (handler that is called) when the main reportviewer control is processing the (each) subreport (in turn.)

     

    You set up the eventhandler in the form load.

     

    If ModuleID is the value that you need in order to create the subreports' parametric SQL statement, the process would go something like this:

     

    1) You set up the main report to set the value of a/the parameter to ModuleID. Make sure you set it to the field in the table the main report is processing so that it gets it's value from the current row

     

    2) In the sub report you set up the parameters to receive that value

     

    3) In the sub-reports' data set, you create a parametric SQL query that uses the parameter by name but with the @ prepended

     

    4) Create the event handler and have it reload the dataset when it is called, passing the parameter to the parametric SQL query indirectly through the table/adapter.

     

    Here's what the (sanitized) s.r.processing handler looks like for a proof-of-concept thing we did a while backSadin C#):


            public void SubreportProcessingEventHandler(object sender, SubreportProcessingEventArgs e)
            {
                string sDataSourceName = "MyDBDataset_SomeTableOfInterestInTheDataset";
                try
                {
                    e.DataSources.Clear();
                    this.MyDBDataset.SomeTableOfInterestInTheDataset.DataSet.EnforceConstraints = false;
                    int iCurrentID = System.Convert.ToInt32(e.Parameters[0].Values[0].ToString());
                    this.SomeTableOfInterestInTheDatasetTableAdapter.Fill(this.MyDBDataset.SomeTableOfInterestInTheDataset, iCurrentID);
                    ReportDataSource rds = new ReportDataSource(sDataSourceName, this.MyDBDataset.SomeTableOfInterestInTheDataset);
                    e.DataSources.Add(rds);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("There was an error attempting to collect/set the data for the report, the system error message was: " + ex.Message);
                }
            }

     

     

     

     

    Monday, August 13, 2007 7:09 PM
  • I've done it!!! Thanks for all your help. I've been doing this at home, so have used a different database / etc. My code is below:

     

    Protected Sub FormReport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

     

    AddHandler ReportViewer1.LocalReport.SubreportProcessing, AddressOf DemoSubreportProcessingEventHandler

     

    End Sub

     

     

    Protected Sub DemoSubreportProcessingEventHandler(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)

     

    Dim ta As DataSet2TableAdapters.DataTable1TableAdapter = New DataSet2TableAdapters.DataTable1TableAdapter

    Dim dt As DataSet2.DataTable1DataTable = New DataSet2.DataTable1DataTable

    dt.Constraints.Clear()

    ta.Fill(dt, CType(e.Parameters().Item(0).Values(0), Integer))

    e.DataSources.Add(New ReportDataSource("DataSet2_DataTable1", dt))

     

    End Sub

    Monday, August 13, 2007 11:41 PM

All replies

  •  

    We did something similar to this where we:

     

    1) Embedded a list in the main report

     

    2) Added the summary fields about the module (row of data) in the list, formatted approprriately

     

    3) We created a subreport as a per-"module" report

     

    4) We wired the list to pass the PK or other per-module unique identifier to the subreport (select the reportviewer embedded in the list, right-click, select Properties, then click the Parameters tab)

     

    5) The subreport had it's own datasource that took the PK/etc - passed into the subreport as a parameter - and, using that as a parameter, built a parametric SQL query that spanned tables and retrieved the fuller set of per-module information. To configure the subreport to receive the parameters, open the subreport in the designer, select it, and in the main menu select Report -> Report Parameters

     

    6) Created and populated appropriate fields in the subreport from it's own datasource

     

    Note that the parameter name has to be exactly the same in both places referenced above. In the subreport's SQL query, use the exact same parameter name except it has to be preceded with an ampersand, i.e. @MySubReportParameter1.  Something like

    SELECT ColumnOfInterest1, ... WHERE TheColumnImSelectingOn=@MySubReportParameter1

     

    What happens at runtime is that the list operates basically as an iterator (for lack of a better term) in that it creates an instance of itself for each row (the current row) of data in it's dataset.

     

    When you wire up the parameters, the list will give the parameters the values out of the row it is currently operating on (just as it populates it's own fields with data from the current row) and instantiates the subreport via the embedded reportviewer, passing those values (from the current row) as parameters

     

    It's pretty slick but it's kind of klunky and takes some patience/experimentation/mucking-about to get all the plumbing right.

     

    Hope this helps...

     

    - Lance

    Saturday, August 11, 2007 1:14 AM
  • I guess I didn't a couple points clear...

     

    a) Embed a reportviewer control in the list itself in order to host the subreport. The list and (sub)reportviewer are aparently "smart" enough to know what the obviously-intended behavior is

     

    b) Add the subreports' datatable to the projects' Dataset(s). You can do that in the designer by right-clicking and selecting Add DataTableAdapter (or whatever the selection is), renaming it appropriately, then hacking/slashing it by right-clicking it and selecting Configure (or whatever it is)

    Saturday, August 11, 2007 1:19 AM
  • Hi Dan,

     

    I think you've already got a pretty good description of subreports and how they work in your layout scenario on this thread...

     

    But I'm curious: what is the problem with using Grouping here, instead of subreports, in the situation you describe?  Can't you do a SELECT which joins/flattens your data?

     

    >L<

     

    Saturday, August 11, 2007 2:43 PM
  • No, I'm afraid the SQL is too complex and would go onto around 3 pages on A4.

     

    I've tried "El Delo's" suggestion, but I think I need to handle the subreport event? I have read that I need to create the handler in a form load. Then I must set the datasource / dataset, and refresh the report.

     

    This is where I am stuck. I have created 2 datasets, one for the main module details, another for more complex logic for subreport. I need to somehow pass the ModuleID as a parameter to the subreport (don't think its easy as putting it in report parameters(?)).

     

    I just need the VB code to put in the handler / form load to display and set the subreport. I'm so close!
    Monday, August 13, 2007 6:01 PM
  • If you have sub-reports then you need to handle the SubreportProcessingEventHandler event.

     

    That is the event that is fired (handler that is called) when the main reportviewer control is processing the (each) subreport (in turn.)

     

    You set up the eventhandler in the form load.

     

    If ModuleID is the value that you need in order to create the subreports' parametric SQL statement, the process would go something like this:

     

    1) You set up the main report to set the value of a/the parameter to ModuleID. Make sure you set it to the field in the table the main report is processing so that it gets it's value from the current row

     

    2) In the sub report you set up the parameters to receive that value

     

    3) In the sub-reports' data set, you create a parametric SQL query that uses the parameter by name but with the @ prepended

     

    4) Create the event handler and have it reload the dataset when it is called, passing the parameter to the parametric SQL query indirectly through the table/adapter.

     

    Here's what the (sanitized) s.r.processing handler looks like for a proof-of-concept thing we did a while backSadin C#):


            public void SubreportProcessingEventHandler(object sender, SubreportProcessingEventArgs e)
            {
                string sDataSourceName = "MyDBDataset_SomeTableOfInterestInTheDataset";
                try
                {
                    e.DataSources.Clear();
                    this.MyDBDataset.SomeTableOfInterestInTheDataset.DataSet.EnforceConstraints = false;
                    int iCurrentID = System.Convert.ToInt32(e.Parameters[0].Values[0].ToString());
                    this.SomeTableOfInterestInTheDatasetTableAdapter.Fill(this.MyDBDataset.SomeTableOfInterestInTheDataset, iCurrentID);
                    ReportDataSource rds = new ReportDataSource(sDataSourceName, this.MyDBDataset.SomeTableOfInterestInTheDataset);
                    e.DataSources.Add(rds);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("There was an error attempting to collect/set the data for the report, the system error message was: " + ex.Message);
                }
            }

     

     

     

     

    Monday, August 13, 2007 7:09 PM
  • I've done it!!! Thanks for all your help. I've been doing this at home, so have used a different database / etc. My code is below:

     

    Protected Sub FormReport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

     

    AddHandler ReportViewer1.LocalReport.SubreportProcessing, AddressOf DemoSubreportProcessingEventHandler

     

    End Sub

     

     

    Protected Sub DemoSubreportProcessingEventHandler(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)

     

    Dim ta As DataSet2TableAdapters.DataTable1TableAdapter = New DataSet2TableAdapters.DataTable1TableAdapter

    Dim dt As DataSet2.DataTable1DataTable = New DataSet2.DataTable1DataTable

    dt.Constraints.Clear()

    ta.Fill(dt, CType(e.Parameters().Item(0).Values(0), Integer))

    e.DataSources.Add(New ReportDataSource("DataSet2_DataTable1", dt))

     

    End Sub

    Monday, August 13, 2007 11:41 PM
  • Excellent! It's pretty slick once you get the plumbing right, no?

    Tuesday, August 14, 2007 5:41 PM
  • All,

     

    Newbie as well and am trying to do the same thing as Dan. I understand most of the code, but what I don't understand is what you're passing for the SubreportProcessingEventArgs when the DemoSubreportProcessingEventHandler is called. What event args are you passing and how are they declared?

     

    Thanks in advance!

     

    Greg

     

    Tuesday, August 14, 2007 6:37 PM
  • Perhaps I'm not understanding your question, but taken at face value...

     

    You don't pass args to the subreportprocessing event handler. It is called from the parent control/form/reportviewer when/if the parent control/form/reportviewer is building out (an instance of, out of zero to howevermany instances of) the subreport.

     

    The args that are passed to it are built out by the runtime muck and include an instance of the dataset you've specified for the subreport and any report parameters you've likewise defined. Those are stuffed into a

    SubreportProcessingEventArgs object.

     

    All of that happens outside/beyond your codes' scope and control, except to the extent that there are events (callbacks) and so on

     

    What you do in the SRProcessing event handler is access the dataset in the SubreportProcessingEventArgs object and, if you've defined any report parameters and they're wired up properly, they will be in that object as well.

     

    You use that info/data and whatever else you want/need to flesh out the dataset, create a ReportDataSource object, add that to the event args' datasources collection, and then let the event handler return.

     

    Does that help?

    Tuesday, August 14, 2007 8:08 PM
  • It does. Thanks so much for the clarificaiton!

    Wednesday, August 15, 2007 2:56 PM