Use Apply XML to modify custom SQL reports

When you create a custom SQL report, you manually apply all the formatting. The Apply XML feature allows you to modify custom SQL reports in Interactive Custom Report Writer (ICRW) and retain all the formatting.

ICRW automatically generates XML when you create a report. The XML provides everything you need to modify a report and retain formatting, such as column order and formatting, prompts, and comments.

Apply XML is available on the ICRW Custom tab. The examples in this topic use a simple Perpetual Calendar report to illustrate how to use Apply XML to do the following:

Subscription Interactive Custom Report Writer

Platform Services

Regional availability

All regions
User type Interactive Custom Report Writer: Business, Project Manager
Permissions

Interactive Custom Report Writer: Run, List, View, Add, Edit, Delete

Platform Services: Run, List, View, Add, Edit, Delete

About XML structure

The XML for an ICRW report built with custom SQL is organized as follows:

  • Top section: defines the report columns.
  • Middle section: contains the custom SQL.
  • Bottom section: defines the views.

Go to the Custom tab and expand the Apply XML pane to view the XML structure for the report.

Column definitions

The top portion of the XML contains the definitions for the columns. Each column definition starts with saw:column and ends with /saw:column, as shown in the following example. ICRW assigns a random hexadecimal column ID for each column. This is important when you add a new column.

An example of an XML column definition in the Apply XML pane on the Custom tab.

Custom SQL

Near the middle of the XML is the custom SQL that starts with a (select statement and closes with a final parentheses for the end) statement.

Example showing the XML custom SQL section in the Apply XML pane.

Views

The views follow the end of the SQL section. Each view begins with saw: view and ends with /saw:view.

Example of XML views in the Apply XML pane.

Each column reference in a view has a hexadecimal ID that was assigned by ICRW. Prompts that have been added to the report are included at the bottom of the views section.

Add a column

This section shows you how to a add a column to a custom SQL report with Apply XML.

If your report has a union of two or more reporting areas, you need to add the column to each reporting area.

  1. Go to the Custom tab > Apply XML pane.

  2. Go to the SQLsection and add a column formula.

    In this example, a Two_Amt column was added after to a Zero_Amt column.

    An example of adding a column to the SQL section of the XML in the Apply XML pane.

  3. Go to the top section of the XML and copy a column definition with the desired formatting.

    In the following example, the Zero_Amt column was copied and then modified to create the Two_Amt column.

  4. Change the hexidecimal name of the new column. Hex characters are not required. You can use a columID that is a human-readable value. The following example shows the original hexidecimal column name before it is changed.

    Example of adding a column definition and changing the hexidecimal ID so it is unique.

    The following example shows the column name after it is changed to columnID="c_Two_Amt".
    NOTE: To change all the XML column names to human-readable values, see Making XML column names human-readable.

    Changing the hexidecimal column ID in the previous image to a human-readable value.

  5. Select Apply XML and go to the Refine tab to view the results.

  6. Right-click the column tile and choose Include > column_name from the dropdown menu. This adds the column in the views.

    Select the column tile and choose Include column from the dropdown menu for the newly added column.

  7. Select Apply XML and go to the Refine tab to view the results.

    Example of the results in the Refine tab after adding a column with Apply XML.

Making XML column names human-readable

Hex characters are not required for XML column names. To make an XML file easier to work with, you can change all the columIDs to human-understandable values. For existing ICRW reports that use the hex string values for column IDs, it is a simple procedure.

  1. Copy the ICRW XML file into an ASCII text editor, such as Notepad.

  2. Change all occurrences of the column hex names to human-readable values, such as c_Job_ID.
    It is recommended that you prefix the column ID with c_ to clearly differentiate between the Expression and the columnID. The columnID can be as long or short as needed. Use the basic 63 characters (26 upper-case letters, 26 lower-case letters, 10 numbers, and the underscore).

  3. After all the column IDs are changed to human-readable values, copy the XML back into the Apply XML pane.

  4. Select Apply XML and Save the report.

Now, instead of seeing the following in the Views section of the XML:

Copy 
saw:view xsi:type="saw:tableView" name="tableView!1" 
 <saw:edges> 
 <saw:edge axis="page" showColumnHeader="true"/> 
 <saw:edge axis="section"/> 
 <saw:edge axis="row" showColumnHeader="true"> 
 <saw:edgeLayers> 
 <saw:edgeLayer type="column" columnID="cb58d295f002a047e1"/> 
 <saw:edgeLayer type="column" columnID="ca578b438c912ef498"/> 
 <saw:edgeLayer type="column" columnID="c398e311d309b857ff"/> 

You now see the following, which is much easier to interpret and work with:

Copy 
saw:view xsi:type="saw:tableView" name="tableView!1" 
 <saw:edges> 
 <saw:edge axis="page" showColumnHeader="true"/> 
 <saw:edge axis="section"/> 
 <saw:edge axis="row" showColumnHeader="true"> 
 <saw:edgeLayers> 
 <saw:edgeLayer type="column" columnID="c_Job_ID"/> 
 <saw:edgeLayer type="column" columnID="c_Job_Name"/> 
 <saw:edgeLayer type="column" columnID="c_Original_Contract_Price"/> 

Modify text

You can easily fix typos and modify text the with Apply XML. The following task shows you how to quickly fix the misspelling in this example.

Example of a misspelling in a custom SQL report that can be fixed with Apply XML.

  1. Go to the Custom tab > Apply XML pane.

  2. Go to the SQL section of the XML and find the text you want to modify and make the necessary change.

    Example of a misspelling in the SQL of a report that can be fixed with Apply XML.

  3. Select Apply XML and go to the Refine tab to view the results.

    Example of corrected misspelling that was fixed with Apply XML.

Rename a column

The following example shows you how to rename a column in a custom SQL report with Apply XML. In this example, the column name is changed from Month to LongMonth

  1. Go to the Custom tab > Apply XML pane.

  2. Find the column definition in the SQL section and modify it. In this example, the column name is changed from Month to LongMonth.

    Example of changing the column name in the SQL section of the XML.

  3. Find the column definition in the top section and make the same change to the column name.

    Example of changing a column name in the column definition section of the XML.

  4. Select Apply XML and go to the Refine tab to view the results.

    Results after changing a column name with Apply XML.

Reorder views

You can easily change the order of views using Apply XML. In this example, the Pivot table will be moved before the Table.

List of views in the views pane.

  1. Go to the Custom tab > Apply XML pane.

  2. In the views section at the bottom of the XML, find the view you want to move. The view starts with saw:view and ends with /saw:view

    Example of an XML pivot table view that will be copied and moved.

  3. Copy and paste the view in the new location.

    Example of an XML view after being copied to a new location.

  4. Select Apply XML and go to the Refine tab to view the results.

    Example of the results of the reordered views in the Views pane.

Delete a column

You can delete columns in the Apply XML pane in the following way.

1. Go to the Custom tab > Apply XML pane.

2. In the SQL section and delete the column formula.

3. In the top section, find the column, and delete it to remove it from the Views.

4. Delete the column from the bottom reporting areas section.

5. Select Apply XML and go to the Refine tab to view the results.