This article explains how to use a single or multiple User Defined Project Property (UDPP) in a report based on a single work order. The issue that makes this a subject for training is the fact that the UDPP (User Defined Project Properties) are populated to a single table in the factory database, one that contains all the UDPP for all the projects in a specific configuration. It is not a matter of merely defining the data source for the report component. 

This article focuses on three ways to configure a report to consume the UDPP. One method to configure a report involves subreports and parameters. Another method is with subreports and event code. The third method is with a reverse database relationship and three extra databands. The method that you use will depend on how you want the data to be displayed, and whether you want all the properties to be displayed or only some of them. 

This article shows you how you can configure a report connecting to a work order database to consume one or more UDPP in 3 different ways. The issue to overcome is that a work order can be taken from multiple projects, but we want to display only the properties associated with a single work order and a single project.

One of the ways to accomplish this is to set up a reverse parent/child relationship in the report and add the appropriate databands. See the attached report named “Workorder Summary – ProjectProperties” as an example of how to accomplish this.

1. The advantage of this method is that it is the simplest of the three methods.
   
2. The UDPP are displayed as a simple list from top to bottom. This may be a limitation with this method, depending on your specific requirements.
   

The following tutorials assume that you have a basic knowledge of the report designer. If you don’t, click here for the Reporting Basics Course outline.

They also use only the default project properties with values given to those existing properties. No new properties were added.

Tutorial 1:

In this first of three tutorials, we will create a Shipping Manifest Report that contains all of the User Defined Project Properties using the reverse parent/child relationship, as mentioned above. We will start with the default Microvellum report Product Shipping Manifest.

1. Open Toolbox and click: “Toolbox Setup > Design Reports > Design Manufacturing Report > New Report From Existing.”
   
2. Select Product Shipping Manifest and change the name to “Product Shipping Manifest – Project Properties” in the Report Name box of Report Setup. Click Microvellum Factory under Database Types and tick the checkbox for the ProjectProperties database table, and click OK.
   
3. Add a parent relation to the Projects table. Click the Dictionary tab in the lower right corner and expand “Data Sources > New Data Set > Projects.” Right-click Projects and left-click New Relation. Set the properties, as shown in Fig. 1 with Products as the parent, and Projects as the child. This is the reverse of how a database relation is normally setup but is necessary in this instance.
   


Fig. 1



4. Click the Page1 tab and add a Databand to the area of the report immediately below PageHeaderBand1. After placing it, double click it and set the Count Data property to a value of 1. Don’t set any other properties of that databand. Set the Name property of the databand to “DataProductsCount.”
   


Fig. 2



5. Add a second Databand immediately below that one and name it “DataProjects.” Set its Data Source to Projects, the Relation to Products, and the Master Component to “DataProductsCount.”
   
6. Add a third Databand immediately below that one and name it “DataProjectProperties.” Set it Data Source to ProjectProperties, the Master Component to “DataProjects, and the filter to ProjectProperties.PropertyName -> NotContaining (the string) “user-defined property” as shown below.
   


Fig. 3



7. Add a text component to the databand named DataProjects to display the project name. The expression should be: “{Microvellum.Globalization.Culturestrings.PROJECTNAME}: {Projects.Name}.”
   
8. Add two text components to the databand named DataProjectProperties to display the Project Property name and value. The expressions should be: “{ProjectProperties.PropertyName}” and “{ProjectProperties.PropertyValue}.”
   
9. Click the Preview tab and correct any errors. The rendered report should appear similar to Fig. 4.
   


Fig. 4

Tutorial 2:

The second in the series of three tutorials will show you how to create a Work Order Summary that contains selected User Defined Project Properties using subreports and parameters, and that displays those properties in specific locations. We will start with the default Microvellum report Work Order Summary.

1. Open Toolbox and click: “Toolbox Setup > Design Reports > Design Manufacturing Report > New Report From Existing.”
   
2. Select Work Order Summary and change the name to “Work Order Summary – Project Properties(parameter)” in the Report Name box of Report Setup. Click Microvellum Factory under Database Types and tick the checkbox for the ProjectProperties database table, and click OK.
   
3. Make sure you are working on Page1 of the report and click on the Group Header Band with the data source of Products.Projects.Name (GroupHeaderBand2), and if you’re working in Imperial, increase the height of the band to 1. This provides room to insert the subreports in the following steps.
   
4. Click: “Components > Sub Report” to add a subreport to the GroupHeaderBand you modified above. Set the height to 0.65.”
   


Fig. 5




When the Sub Report box is displayed, click the tab Parameters, and then the Add button. In the Name box, enter the string “ProjectLinkIDParam” without the quotation marks. Then click the Fx formula button next to Expression and enter Products.LinkIDProject for this report. Alternatively, navigate to that field in the tree structure and double click it. Click OK after verifying that the parameter properties are identical to the sample below. If you need to reenter the subreport options box, double click on the subreport container on Page1.



The tab name of each subreport corresponds with the Sub Report Page name defined for each subreport component.



Fig. 6



5. Repeat this step and add a second subreport with the same parameter. Make sure that the extents of the subreports are inside the extents of the group header band, as shown below.
   


Fig. 7



6. Click the Preview tab and correct any errors. Save your report.
   
7. Click the tab for the first subreport and add a databand, 0.18” in height. Add a text component to that databand, and set it to the same height as the databand, with the Top property set to 0. Set the Data Source for that databand to be ProjectProperties and add a filter by clicking the Filters tab and selecting Expression in the Field Is dropdown list box. Click the Fx button and add the following expression there: 
   
   

   ProjectProperties.LinkIDProject = Me("ProjectLinkIDParam") AndAlso ProjectProperties.PropertyName.ToLower.Contains("scope of services")
   

   This filter, along with the parameter already defined for the subreport, will return only the Scope of Services project property.
   

8. Once this first databand is set up as needed, copy and paste it, so you have a series of three identical databands. Modify the filter expression of the second databand to be: 
   
   

   ProjectProperties.LinkIDProject = Me("ProjectLinkIDParam") AndAlso ProjectProperties.PropertyName.ToLower.Contains("exclusions”)
   

   Modify the filter expression of the third databand to be: 
   

   

   ProjectProperties.LinkIDProject = Me("ProjectLinkIDParam") AndAlso ProjectProperties.PropertyName.ToLower.Contains("addendum”)
   
9. Copy and paste the three databands from the first subreport into the second subreport. Do this by clicking on the first databand in the first subreport, and pressing Ctrl+C to copy. Then click the tab for the second subreport and press Ctrl+V to paste, and click in the grid area of that subreport. Repeat this series of steps for all three databands.
   


Fig. 8



10. Modify the filters expressions in the three databands you just copied. The filter expression for each databand should contain one of the expressions below:
    
    

    ProjectProperties.LinkIDProject = Me("ProjectLinkIDParam") AndAlso ProjectProperties.PropertyName.ToLower.Contains("date of drawings")
    

    ProjectProperties.LinkIDProject = Me("ProjectLinkIDParam") AndAlso ProjectProperties.PropertyName.ToLower.Contains("conditions”)
    

    ProjectProperties.LinkIDProject = Me("ProjectLinkIDParam") AndAlso ProjectProperties.PropertyName.ToLower.Contains("terms”)
    

11. Click the Preview tab and correct any errors. Save your report. The rendered report should appear similar to Fig. 9.
    


Fig. 9

Tutorial 3:

The third in the series of three tutorials will show you how to create a Work Order Summary that contains selected User Defined Project Properties using subreports and event code, and that displays those properties in specific locations. It also contains a combination of program-defined project properties and hard-locked values. We will start with the default Microvellum report Work Order Summary.

1. Open Toolbox and click: “Toolbox Setup > Design Reports > Design Manufacturing Report > New Report From Existing.”
   
2. Select Work Order Summary and change the name to “Work Order Summary – Project Properties(events)” in the Report Name box of Report Setup. Click Microvellum Factory under Database Types and tick the checkbox for the ProjectProperties database table, and click OK.
   
3. Make sure you are working on Page1 of the report and click on the Group Header Band with the data source of Products.Projects.Name (GroupHeaderBand2), and if you’re working in Imperial, increase the height of the band to 1.45. This provides room to insert the subreports and various other fields discussed in the following steps.
   
4. Add a report variable named “strProjectID.”
   
5. Add a ReportTitleBand component and add the following code to Before Print event: 
   
   

   strProjectID = Products.Projects.LinkID
   
6. Add text components and subreport components, as shown below.
   


Fig. 10



7. Add databands and text components in each subreport, as shown in the attached report sample. Add filters in each databand to limit the data returned to only what is needed.
   
   

   ProjectProperties.LinkIDProject = strProjectID AndAlso (Me.ToString(Me.ProjectProperties.PropertyName).ToLower.IndexOf(Me.ToString("date of drawings").ToLower) <> -1)
   

   ProjectProperties.LinkIDProject = strProjectID AndAlso (Me.ToString(Me.ProjectProperties.PropertyName).ToLower.IndexOf(Me.ToString("addendum").ToLower) <> -1)
   

8. Click the Preview tab and correct any errors. Save your report. The rendered report should appear similar to Fig. 11. The yellow highlighting represents UDPP, the green represents program-defined project properties, and the blue represents hard-locked values.
   


Fig. 11