Sub Reports - Configuring and Setting up a Sub Report Walkthrough

Walkthrough of configuring and setting up a sub-report

The following instructions will guide you through the major steps required to recreate the Sample Sub Report in the hope that it will help you to understand the processes and concepts required for you to utilize Sub-Reports within your company. To speed this tutorial along, the controls, calculated fields, and parameters have been created and placed on the reports, but they are not yet configured.

Before going through this walkthrough, please run both the eV_Sample Sub Report_MainReport_FIN report and the WIP_eV_Sample Sub Report_MainReport_WIP report to compare the results of a report that has been configured with the one that has not. Once the walkthrough is completed, the reports should be exactly alike.

Attachments

• eV_Sample_SubReports_WIP_R23.zip

Download the attached file, extract the contents, and place the files in the following directory:

• C:\Temp\SubReports

  

  IMPORTANT: The files MUST be placed into this directory, as all configurations are pointing to this directory.

Adding a SubReport control

IMPORTANT: Due to how we have to pass model data from Revit to the designer, the EVOLVE implementation of SubReports differs from how DevExpress implements SubReports. While most of the DevExpress documentation regarding SubReports still holds true, the configuration is different.

NOTE: The SubReport controls have already been added to the report in the sample above, and the sections below will guide you on how to configure the controls in the provided sample report. This section tells you how to add a SubReport control to a report, but it is not necessary to perform as the controls already exist in the sample.

1. From the Report Designer, in the Toolbox, drag and drop the SubReport control onto the Design Surface.
2. From the Design Surface, drop the control into the desired band.
3. Optional - Once placed, the control may be resized, and the position can be adjusted.
4. Optional - To rename the control, locate the Name field on the Miscellaneous tab within the Properties panel.
5. Save the report. No other configuration is necessary at this time.

Defining the report configurations

A report's configuration contains information like Name, Description, Profile, Report File Path, Data Relationships, etc, and is set up in the Report Manager.

TIP: While the following steps outline exactly what is needed for each report, you may duplicate the main report's configuration to use on all sub-reports. Note that this may copy unnecessary tables and/or relationships that are only required for one or more sub-reports, but it will not hurt to have them, and you will have access to all the necessary data to run each report. If already defined, it may also copy the mappings, which will need to be deleted.

Configuring the main report

1. From the Report Manager window, perform the following on the row with the WIP_eV_Sample Sub Report_MainReport_WIP report:
   1. Click the Data Tables menu and select SubReportsDataTable.
   2. Click the Data Relationships ellipsis and specify the following from the Define Data Relationships window:
      1. From the Data Profile Alias Names panel, in the Alias cell, type PrimaryProfile.
      2. From the Data Tables Alias Names panel, in the Alias cell, type SubReportsDT.
      3. From the Data Relationships panel, from the Data Navigator, click Add new .
      4. From the newly created row, specify the following:

         Parent Source	Parent Field	Child Source	Child Field
         PrimaryProfile	Family and Type	SubReportDT	FamilyAndType

      5. From the Define Data Relationships window, click OK.
   3. From the Report Manager, click the Sub Reports ellipsis, and specify the following from the Mappings for Sub Reports window:

      Sub Report Name	Data Member	Sub Report File Name
      Counts_SubReport	SubReportsDT	C:\Temp\SubReports\WIP_eV_Sample Sub Report_SubReport_Counts_WIP.repx
      Totals_SubReport	SubReportsDT	C:\Temp\SubReports\WIP_eV_Sample Sub Report_SubReport_Totals_WIP.repx

   4. Once complete, click OK to close the Mappings for Sub Reports window.

Configuring the counts sub-report

1. From the Report Manager window, perform the following on the row with the WIP_eV_Sample SubReport_SubReport_Counts_WIP report:
   1. Click the Data Tables menu and select SubReportsDataTable.
   2. Click the Data Relationships ellipsis and specify the following from the Define Data Relationships window:
      1. From the Data Profile Alias Names panel, in the Alias cell, type PrimaryProfile.
      2. From the Data Tables Alias Names panel, in the Alias cell, type SubReportsDT.
      3. From the Define Data Relationships window, click OK.

Configuring the totals sub-report

1. From the Report Manager window, perform the following on the row with the WIP_eV_Sample Sub Report_SubReport_Totals_WIP report:
   1. Click the Data Tables menu and select SubReportsDataTable.
   2. Click the Data Relationships ellipsis and specify the following from the Define Data Relationships window:
      1. From the Data Profile Alias Names panel, in the Alias cell, type PrimaryProfile.
      2. From the Data Tables Alias Names panel, in the Alias cell, type SubReportsDT.
      3. From the Define Data Relationships window, click OK.

Initial setup of the reports

While the calculated fields and parameters have already been created and the controls have been placed on the reports, this section outlines the minimum steps required before the sub-report's binding can be configured. The main report must contain the intended binding field(s) and/or parameter(s), and the sub-report(s) must contain the intended binding parameter(s).

Initial setup of the main report

1. From the Report Manager window, click the WIP_eV_Sample Sub Report_MainReport_WIP report's selection box and click Open Report Designer .
2. From the Field List panel, in PrimaryProfile, select the cal_Family_Count field, right-click, and click Edit Expression.
3. From the Expression Editor, enter the following expression and click OK.
   [][[Family and Type] == [^.Family and Type]].Count()

   

   NOTE: This expression counts the number of records in the dataset with the same "Family and Type" value.

   

4. Close the report, saving the change.

Initial setup of the counts sub-report

1. From the Report Manager window, click the WIP_eV_Sample Sub Report_Counts_WIP report's selection box and click Open Report Designer ​.

Swapping the Data Member

IMPORTANT: From step C in the Configuring the Main Report step above, this report was bound to the Data Table (SubReportsDT). However, this report's Primary Profile is set to the Project Information Data Profile (the Alias is PrimaryProfile). To work with the report, the Data Member must be set to SubReportsDT. Doing so will not change the binding, nor will the change to the Data Member be saved; swapping the Data Member allows access to the proper fields and parameters during an edit session.

1. From the Design Surface, click the Smart Tag , located in the upper left of the Design Surface.
2. From the Reports Tasks pop-out, click the Data Member menu and select SubReportsDT.

Create a parameter to bind to the data table

1. From the Fields List panel, expand Parameters, right-click on the par_FamilyAndType parameter, and click Edit Parameters.
2. From the Report Parameter Editor, ensure the par_FamilyAndTypeparameter is selected, then make the following changes:
   1. Visible - No

      

      TIP: While in an edit session, to test this sub-report, set the Visible option to Yes. This will allow you to change parameter values when previewing the report so that you can ensure data is being displayed. Currently, the report will not display information until the steps in the Setting up the counts sub-report section have been completed. Additionally, leaving the Visible option enabled will display the parameters on the main report, but since they are bound, changes made are not reflected in the main report.

      

   2. Value Member - FamilyAndType
   3. Display Member - FamilyAndType
3. Click OK to close the Report Parameters Editor window.

Create a parameter to bind to the main report's calculated field used to sum

1. From the Fields List panel, expand SubReportDT, right-click on the par_Family_Count parameter, and click Edit Parameters.
2. From the Report Parameter Editor, ensure the par_FamilyAndTypeparameter is selected, then make the following changes:
   1. Visible - No

      

      TIP: While in an edit session, to test this sub-report, set the Visible option to Yes. This will allow you to change parameter values when previewing the report so that you can ensure data is being displayed. Currently, the report will not display information until the steps in the Setting up the counts sub-report section have been completed. Additionally, leaving the Visible option enabled will display the parameters on the main report, but since they are bound, changes made are not reflected in the main report.

      

   2. Type - Number (32 bit integer)
   3. Default Value - 1
3. Click OK to close the Report Parameters Editor window.
4. Close the report, saving all changes.

Setting up the main report

Binding parameters to the counts sub-report

1. From the Report Manager window, click the WIP_eV_Sample Sub Report_MainReport_WIP report's selection box and click Open Report Designer .
2. From the Design Surface, in the SampleSubReport_Main_FamilyAndType_SubBand band, select the Counts_SubReport control and click the Tasks cog .
3. From the Sub-Reports Tasks pop-out window, click Edit Parameter Bindings.
4. From the Parameter Binding Collection Editor, perform the following:
   1. Click Add.
      1. From the Misc panel, click the Binding menu, expand PrimaryProfile, and select Family and Type.
      2. From the Misc panel, click the Parameter Name menu and select par_FamilyAndType.
   2. Click Add.
      1. From the Misc panel, click the Binding menu, expand PrimaryProfile, and select cal_Family_Count.
      2. From the Misc panel, click the Parameter Name menu and select par_FamilyCount.
   3. Click OK to close the Parameter Binding Collection Editor window.
5. Close the report, saving all changes.

Setting up the counts sub-report

This Sub-Report pulls information from the data table by comparing the value of the elements Family and Type parameter with the value in the FamilyAndType column and returns all matching rows. Since the initial setup of the report has been completed, the following steps will walk you through configuring the calculated fields.

1. From the Report Manager window, click the WIP_eV_Sample Sub Report_Counts_WIP report's selection box and click Open Report Designer ​.

Swapping the Data Member

IMPORTANT: From step C in the Configuring the Main Report step above, we bound this report to the Data Table (SubReportsDT). However, this report's Primary Profile is set to the Project Information Data Profile (the Alias is PrimaryProfile). To work with the report, the Data Member must be set to SubReportsDT. Doing so will not change the binding, nor will the change to the Data Member be saved; swapping the Data Member allows access to the proper fields and parameters during an edit session.

1. From the Design Surface, click the Smart Tag , located in the upper left of the Design Surface.
2. From the Reports Tasks pop-out, click the Data Member menu and select SubReportsDT.

Setting a report filter

1. From the Design Surface, click the Smart Tag , located in the upper left of the Design Surface.
2. From the Reports Tasks pop-out, click the Filter String ellipsis.
3. From the FilterString Editor window, enter the following expression and click OK.
   [FamilyAndType] = ?par_FamilyAndType

Summing lengths

1. From the Field List panel, expand SubReportDT, right-click on cal_Length_Counts, and click Edit Expression.
2. From the Expression Editor window, enter the following expression:
   Iif([UM] = 'LF', ToLengthDisplay(?par_Family_Count * [Length]), null)

   

   How this expression works:
   The IF (IiF) statement evaluates each applicable row in the data table (those that match the element's Family and Type). If the UM value equals LF, then the Length value is multiplied by the ?par_Family_Count value; else, null (nothing) is returned. The (?par_Family_Count * [Length]) value is also being transformed via the ToLengthDisplay function so that the value displayed in the report adheres to the units set in your project, in this case, Feet and Inches. Also, remember that the ?par_Family_Count parameter is bound to the cal_Family_Count calculated field. So, when the report runs, this value is passed instead of the default value stored in the ?par_Family_Count parameter.

   

3. Click OK to close the Expression Editor window.

Summing quantities

1. From the Field List panel, expand SubReportDT, right-click on cal_Qty_Counts, and click Edit Expression.
2. From the Expression Editor window, enter the following expression:
   Iif([UM] = 'EA', ?par_Family_Count * [QtyToCount], null)

   

   How this expression works:
   The IF (IiF) statement evaluates each applicable row in the data table (those that match the element's Family and Type). If the UM value equals EA, then the QtyToCount value is multiplied by the ?par_Family_Count value; else, null (nothing) is returned. Keep in mind, the ?par_Family_Count parameter is bound to the cal_Family_Count calculated field. So, when the report runs, this value is passed instead of the default value stored in the ?par_Family_Count parameter.

   

3. Click OK to close the Expression Editor window.

Accumulating ancillaries

We need a way to accumulate the ancillaries in the data table, whether the UM is EA or LF, and then pass that value to other reports. To do so, we will utilize the GetGlobalNumber function.

1. From the Field List panel, expand SubReportDT, right-click on cal_Ancillaries_Counts, and click Edit Expression.
2. From the Expression Editor window, enter the following expression:
   Iif([UM]='LF',ToLengthDisplay(SetGlobalNumber([Ancillary],GetGlobalNumber([Ancillary])+(?par_Family_Count*[Length]))),Iif([UM]='EA',SetGlobalNumber([Ancillary],GetGlobalNumber([Ancillary])+(?par_Family_Count*[QtyToCount])),null))

   

   How this expression works:
   The IF (IiF) statement evaluates each applicable row in the data table (those that match the element's Family and Type). When utilizing the SetGlobalNumber function, a Name and a Value must be supplied. For the Name, the [Ancillary] value in the data table is used, and the Value, depending on their [UM] value, is either the sum expression for Length or Qty. Note that these are the same calculations used to sum length and quantities above. Additionally, any previously stored "GlobalNumber" matching the current item is added to this value; this is done via the GetGlobalNumber function. If the UM value is not LF or EA, null (nothing) is returned.

   

3. Click OK to close the Expression Editor window.
4. Close the report, saving all changes.

Setting up the totals sub-report

This Sub-Report totals and displays the accumulated ancillaries. Since the initial setup of the report has been completed, the following steps will walk you through configuring the calculated fields.

1. From the Report Manager window, click the WIP_eV_Sample Sub Report_Totals_WIP report's selection box and click Open Report Designer ​.

Summing lengths

1. From the Field List panel, expand SubReportDT, right-click on cal_Length_Counts, and click Edit Expression.
2. From the Expression Editor window, enter the following expression:
   Iif([UM] = 'LF',ToLengthDisplay(GetGlobalNumber([Ancillary])),null)

   

   How this expression works:
   The report is bound to the Data Table, and the Grouping in the report is by the UM field, then the Ancillary field. However, since this is a sub-report, you must remember that this is all done in the main report in the context of the element whose Family and Type value matches that of the FamilyAndType value in the Data Table. The IF (IiF) statement evaluates each applicable row in the data table (those that match the element's Family and Type). If the UM value equals LF, then the GetGlobalNumber function is called, returning the required [Ancillary] value(s), else null (nothing) is returned.

   

3. Click OK to close the Expression Editor window.

Summing quantities

1. From the Field List panel, expand SubReportDT, right-click on cal_Qty_Counts, and click Edit Expression.
2. From the Expression Editor window, enter the following expression:
   Iif([UM] = 'EA', GetGlobalNumber([Ancillary]), null)

   

   How this expression works:
   The report is bound to the Data Table, and the Grouping in the report is by the UM field, then the Ancillary field. However, since this is a sub-report, you must remember that this is all done in the main report in the context of the element whose Family and Type value matches that of the FamilyAndType value in the Data Table. The IF (IiF) statement evaluates each applicable row in the data table (those that match the element's Family and Type). If the UM value equals EA, then the GetGlobalNumber function is called, returning the required [Ancillary] value(s), else null (nothing) is returned.

   

3. Click OK to close the Expression Editor window.
4. Close the report, saving all changes.

Clearing global values

The SetGlobal functions persist across reports and, if not cleared, may accumulate, displaying incorrect values. In the Report Header of the main report, I have placed a label with an expression that's been commented out. After making all the changes above, if you run the report multiple times (click back and forth between the Design tab and the Preview tab), you will notice that the values in the "Totals for Ancillaries" section keep increasing each time. To correct this,

1. From the Report Manager window, click the WIP_eV_Sample Sub Report_MainReport_WIP report's selection box and click Open Report Designer .
2. From the Design Surface, select the label at the top of the report, directly to the left of the report's title, and click the Expression Editor button.
3. From the Expression Editor window, remove /* and */ characters above and below the expression, then click OK.

   

   NOTE: Once complete, the expression should read:
   ClearGlobalValues()

   

4. Close the report, saving the change.

The configuration and setup of the reports are now complete. From the Reports Manager window, in the grid, select the eV_Sample Sub Report_MainReport_FIN report and click Run Report .

Relevant Articles

• Reports - The Expression Editor