SubReports
- Amanda Thomas
Introduction
In reporting, users expect as much of their data in once place as they can afford, sometimes this means showing several views to the same data, or results from different sets of data. The best way to encapsulate and reuse these views in any reporting system is via SubReports. We've always utilized the developer express suite of reporting tools, which has always had it's own solution for SubReporting. We made this tool work with our reporting infrastructure.
To create and add SubReports to existing reports, you will need:
Two reports. One that you would wish to become the master report, another that you will use as a SubReport.
Creating a SubReport
Any report can be turned into a SubReport- including the ones that already exist! To create one, we need only to declare a new report in the same report category as the report(s) that we would like to add it to.
Depending on the type of report that we are creating, there are several configurations that we will need to set to correctly load, calculate and filter data for the SubReport:
If we wish to filter the results of our SubReport within the context of the band that we add it to and to see all transactions in a period sub, where the master record shows the total for the period, we will need to specify SubReport joins.
To do this, we will right click a report (sub-report) and select the menu option "SubReport Joins". A dialog appears that allows us to select filters that will relate our master report values to the selected SubReport. When that band is written to the report, the value for the current row will be passed to the SubReport, and the sub will be filtered accordingly.
Joins are declared by report type, and not by a specific report, so you can reuse the same SubReport over for reports of the same type. You can declare several joins for a report type, but you will not be able to create sets for the same type- you will have to copy the SubReport if you need a different join for the same report type. However, you can create joins for several different types against the same report- the ones that do not pertain to the master report we assign the sub to will not be included in the filters.
Report links and parameter sets cannot be assigned joins, and can therefore not be configured as SubReports.
Copying a report that has joins will also copy the joins to the new report.
There are some new options in the report editor as well, if you select the main "AIMReport" object and view it's properties, you will see a new category titled "Sub-reports". This category has a few new parameters that we can set during report design to determine how this report will load or filter:
IsIndependentSubreport is a true/false field that will determine what date is loaded and calculated for the SubReport data set. when a data set is executed from it's master, the master wizard parameters will override the sub parameters by default. If this value is set to true, this will prevent that from happening. Instead, the SubReport will use it's own wizard parameters and filter string to load the report data.
This will come in handy when you want to view reports side by side that have little or no relation to one another.
When the SubReport is run, the software will use the joins that we have established to generate a filter string. This filters the results that we will see in the SubReport for the current band. When this is generated, it will use the expressions we set to comare the values ( =, >=, <, etc. ). We can override this by typing our own into the FilterString property, and setting IsIndependentSubreport to "True".
Note: Because we do not have the parameter context for the master report at this time, you will not be able to generate override filter strings using the query builder control that appears when you drill-into the filter string field.
Adding a SubReport to An Existing Report
The SubReport control can be found in the lower right of the screen, in the controls menu.
To add a SubReport to any report drag this control onto the screen in the band that we would like to see it in. Keep in mind that where we drop the SubReport will detremine how many times it is repeated. Dropping a report on the detail line of a period report, for example, will print that report for each row of the period. If we use a period column in our report join the SubReports will filter accordingly. Dropping it on the report header or footer will print it once for the whole report, etc.
We'll go over this in a bit more detail in the examples below.
The SubReport has a property called "Report Source URL", this is the most important field to remember. This field is represented as a string that will load with a list of all reports in the same category as this one. Selecting a report will use it's joins to filter the results on whatever band it is placed on. If no joins can be found for the master report's type then the report will run without filters, on each band.
If the report we wish to add does not appear in the list, check that a.) It is in the same category b.) It is not a link, category or parameter set and c.) it has not been deleted.
Reports can be moved from one category to another, if this is the case,and a report source URL has been set, then the url will remain unchanged, and continue to appear in the list, until another one is assigned.
Report Source | This field is not used. If we were setting our data source like a traditional report writing tool, then the SubReport data set would appear here. |
Can Shrink | If this is set to "true", then the SubReport will attempt to shirink itself each time there is more space in the sub than there is rows. |
Clicking 'Save" or closing the form and selecting "Yes" on the save prompt will save all of our changes to the report and SubReport properties.
Here are DevExpress links to their documetation on SubReports:
https://documentation.devexpress.com/XtraReports/DevExpress.XtraReports.UI.XRSubreport.class
SubReport Examples
Simple SubReport
It's very simple to create and add a SubReport. For our first one, we'll attempt to do one without any context. By that, I mean we will just get another report to show on a master report, without a need to filter any of the data.
To start, we create a new report for mortality transactions. We name this report "SubReport: Mortality", and bind it to the Mortality table relation for Trans_Rpt (pictured). At this point, there is no need to set up the report parameters- they will be assigned when we run the master.
Next, We'll ceate a new report. For this one, we can create it from the house data set.
Once I create It, I add some fields to it, and drop a subreport control on it. I point the subreport to the url for "SubReport: Mortality", the report we just created.
I save this report, and edit it's parameters to run for active flock 10015, and that flock only.
Now, I set my dates and run the report. When I open it, I can see my report, as well as the SubReport underneath it. I dropped a mortality total for the period on there, and I can add the two values up to make sure they balance.
I did not have to assign the flock on the SubReport, because the master's flock list and wizard selections will be passed to the SubReport every time- unless the user set the "Is Independent SubReport" option to "true", in which case this step would be skipped, and the SubReport will run using its own wizard settings.
This first exercise was to show us how simple it is to create and add a SubReport. However, this example only worked out well for us because a.) we only ran the report for one flock, and b.) we set our report dates to one week, and the report to "Period" interval. For complex, filtered, repeated, SubReports we will have to make a few extra configurations to produce the desired results.
Filtered Sub on a Detail Band
We'll create a similar master and details, only this time, we'll add two flocks to the parameters on the master wizards. Our expectation would be that each flock's transactions would be grouped in the SubReport for that respective flock. However, when we run it, we notice that each flock in the report gets all transactions for each flock. We'll need to add a join and a filter to get the desired results.
To filter the SubReport, we will need to create a join. to do this, we will need to right click the SubReport and set it up.
We open the join dialog and create one join. We want to use this sub on a report that is based on the "House Data Set", so we select that data set, the one table it contains and column "General_GroupID". We do the same for the transaction data set itself. When the SubReport needs to draw now, the group ID for the current column will be passed and the data will be filtered accordingly.
Now that I have my join, if I open the editor for the report, I can see a filter value. Adding joins does two things:
At runtime, a parameter will be created and the value from the main report's current row will be passed to the SubReport.
A Filter string is generated that will filter the results, based on the parameter(s).
Now, when we run the same report, we can see that the sub is properly filtering by group.
Filtered Sub on a Group Band
If we change the master report to Period Interval: Weekly, group the report by flock, then move the SubReport to a group footer, we can see that the same report will show up as intended.
Filtered by a Range
However, if we need to move the sub back to the detail line, because, say, we want to only see the records that pertain to that week. we do not get the results we would expect. We need to expand the filters, and override them to filter by range.
Before we proceed, it is important to note that we added some functionality in this version. Prior to 1.45, users were not able to bind directly to tables in a data set with a parent/child relationship. Users would have to bind to the main table, then display the child records in a detail report. In this version, we have provded the option to bind directly to the child tables, and the report will run. This will greatly simplify report design for sets like the transaction set!
To make a report with a filter by range, we need only to open our SubReport joins, then create joins with expressions that are within some bounds. The expressions range can be >, <, >=, <=.
For this transaction report, I want to create a report with weekly periods. For each period, I want to see a list of mortalities for that flock, by that period. To do this, we create a few extra filters with ranges, making sure that we filter the date field between the period start and end date.
We will add this SubReport to our master, whos period interval is set to weekly.
Finally, we will run our report. When we view it, we can see that the transactions on the sub are filtered by the period date, as well as by the flock!
Unrelated Data Sets, Filtered Results
By default, when a SubReport is added to a master, the master's parameters will be passed to the sub. In some cases, we will need to add a sub to a master that is only loosely related. We'll cover the setup of that here:
Let's say we would like to create a report comparing pullets to the layers that we transferred to. Since these are separate flocks, we can't pass the flock ids to the master- the SubReport would only contain the pullet flocks.
When the list of flocks in the sub will differ from the master, we will set the SubReport's "Is Independent SubReport" property to "True". we will then setup that wizard to return the types of flocks that we will want to see, independent from the master wizard.
We'll set the master wizard up to return pullets from division A, but the sub to return layers from Division B.
We may sometimes be able to rely on report joins for the two datasets, but this is rarely the case- especially for flocks. Flocks can be transferred to or from many flocks, so there is no 1-1 relationship to relate to.
Some datasets, Like House, have a column called "Place_Combine_Origin_Code". This column lists all of the flocks that were tranferred in to this one. We can use it to filter our layer flocks, using the pullet flock itself.
Since we can't use joins, we will need to manually create a parameter ourselves, on the SubReport (pictured). We will then have to set "Is Independent SubReport" to True. We'll want to pass the pullet flock code, so create a parameter and set it to type "string"
Because we can't add records to the SubReport Join we'll need to write the filter string ourselves.
In the Filter String property of the SubReport, we will enter a "Contains" command, comparing what's in the list of Place_Combine_Origin_Code with what we will pass into the parameter we have created above.
The next step is passing a value into the prameter of the SubReport. From the master, where we dropped the SubReport object, We will add a before print event.
When we script this event, we will set the parameter that we created from the SubReport to the the value of the current column in the master.
With these steps completed, we can run our report. We will see that the layer flocks that became the destination of pullet flocks appear in the sub!
ADD Script to find Parameter Filter.
Add Label and Before Print Script on Label
Private Sub label6_BeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)
Dim label as XRLabel = sender
sender.Text = Me.FilterString
End Sub