	Product(s):	Promis.e, Bentley Substation
	Version(s):	through 08.11.13.73
	Environment:	N\A
	Area:	Template Management
	Subarea:	Report Template Designer

Is it possible to have a bill of materials report like the BOM_Qty (With IDs) report, but with the device tags listed in a single line?

Background

This article answers the question above as a demonstration of some of the possibilities of BeforePrint scripting and report template functionality. It will describe the process of modifying the BOM_Qty (With IDs) template to achieve the goal of having the device IDs in a single line for each part number. A template modified as described in this article is available here.

Overview of this report template

The Detail band is used to concatenate the device tags and store them in a string variable via a script function. This band will not be used for displaying any data.
The concatenation string variable is declared outside any script function so that it can be accessed by any function.
The concatenation string variable is reset by a script bound to the GroupHeader.
Data is primarily displayed via the GroupFooter band instead of the GroupHeader and Detail bands.
A script function sends the value of the concatenation string variable to a cell in the GroupFooter table.

Steps to Accomplish

Create a new template

Use [[Project Manager]] to open a page in a project that contains several devices that have the same part number assigned to them.
Open the [[Report Template Designer]].
Select File > Open and open the BOM_Qty (With IDs) - Letter template.
Select File > Save As and give the new template a unique name such as BOM_Qty Inline IDs.

Declare and reset the variable that will store the concatenated device tags

In the Designer view, right-click the GroupHeader1 band and select Properties.
Expand the Scripts property in the Property Grid.
Click into the cell for the Before Print value.
Click the resulting down arrow button and select (New).

This will open the Scripts view and automatically add a new, empty function.
Add devicetags = ""; // reset devicetags to the new function between the curly brackets, like so:
```
private void GroupHeader1_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e) {
   devicetags = ""; // reset devicetags
}
```
This function will now reset the devicetags variable to have an empty value. The text following the double slashes is a comment and is disregarded by the software. The function will be called for every group header that is generated. In this report template, that occurs for each part number.
On the line before private void GroupHeader1_BeforePrint... add the following line:
string devicetags;
This declares the devicetags variable as a string, meaning it can contain letters, numbers, and other characters. Since it is declared outside the new GroupHeader1_BeforePrint and any other function, it persists and will be available to other functions.

Concatenate the device tags

In this template, the group header band is generated for each part number. For each group header, the detail band receives a row of data for each device to which the part number is assigned. To make the device tags appear in a single line, or row, they must be stored as they are received one-by-one and then displayed later, concatenated.

In the Designer view, right-click the Detail band and select Properties.
Expand the Scripts property in the Property Grid.
Click into the cell for the Before Print value.
Click the resulting down arrow button and select (New). This will open the Scripts view and automatically add a new, empty function.
Copy and paste from this article to make the new Detail_BeforePrint function look like the following:
```
private void Detail_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e) {
   if (GetCurrentColumnValue("DeviceTag") != DBNull.Value)
      if (devicetags == "")  // if devicetags is empty, set it equal to the DeviceTag value
        devicetags = (string)GetCurrentColumnValue("DeviceTag");
      else        // if devicetags is not empty, append the current DeviceTag value to it with a comma
         devicetags = devicetags + ", " + (string)GetCurrentColumnValue("DeviceTag");
}
```
Every time this function is run, it gets the device tag value and appends it to the value of the devicetags variable, along with a comma separator. Since this function is called by the Detail band, it will be run for each device to which the part number is assigned. It is possible to adapt the function to include the installation and location of the devices, but this would increase the amount of data in the cell which may make it less readable.
Select the Designer view.
With the Designer view active, select the Report Explorer tab and expand the Detail node.
In the Report Explorer tab, right-click xrTable4 in the Detail node and select Delete. Unlike the original template, no data will be displayed via the Detail band.

Display the part number and device tag data

Since the device tag data was gathered in the Detail band, it must be displayed in a subsequent band. So the table in the GroupHeader1 band must be moved to the GroupFooter1 band.

With the Designer view active, select the Report Explorer tab and expand the GroupHeader1 node.
In the Report Explorer, right-click xrTable1 in the GroupHeader1 node and select Cut.
In the Report Explorer, right-click the GroupFooter1 band and select Paste.
In the GroupFooter1 band, right-click the second cell (aligned with the Quantity cell in the PageHeader band) and select Insert > Column To Left.
In the PageHeader band, right-click the Quantity cell and select Insert > Column To Left.. Enter "Device ID" into the new cell.
Change the text in the Quantity cell to Qty.
Delete the Category column in the PageHeader band and delete the corresponding cell in the GroupFooter1 band. Resize columns in both bands so that the Device ID and Qty columns have adequate space. Columns can be resized by selecting them in Designer view, then dragging¹ one of the handles that appear. Be sure the columns in the PageHeader band align² with those in the GroupFooter1 band.
Use the Property Grid to set the font and size of the text and the foreground color of the new cells in the PageHeader and GroupFooter1 bands.
In the Designer view, find the cell in the GroupFooter1 band that is aligned with the Device ID column header (in the PageHeader band) and right-click it. Select Properties from the pop-up.
Expand the Scripts property in the Property Grid.
Click into the cell for the Before Print value.
Click the resulting down arrow button and select (New). This will open the Scripts view and automatically add a new, empty function.

Add (sender as XRLabel).Text = devicetags; to the new function between the curly brackets, like so:

private void tableCell2_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e) {
   (sender as XRLabel).Text = devicetags;
}

Preview and Save the template

Select the Preview view. The devices IDs for each part number should appear in a comma-separated list in a single row, as seen for item 2 in the screen shot below.
If the preview looks correct, select File > Save.
Close the Report Template Designer.

Run a report

Select [[Run Reports]].
Select the new template. It will be found in the Bill of Materials (Quantified) category.
Preview or output to file.

Quantified BOM report with device IDs on a single line