Cloud reports: How to create a Relation

Reports with custom fields need to have a Relation created in the report. Some Relations have been added to the built-in reports, but in case it's missing or a custom report doesn't have one, here is how to create it.

================================================================================

1. If you don't know it, find and mark down your schema. Usually this is the same as your tenant name. If you're not sure, here's how to find it:

Edit the report.
Click Dictionary at the lower left.
Expand out Variables, Do Not Touch
Double-click on schema.
Look at the Value field: Your schema is the text in that field, without any brackets.

In the screenshot, the schema is:
invcloudtest

================================================================================

2. Execute the data source so the custom fields are retrieved (this procedure is from the Cloud Custom Report FAQ):

At the left, double-click the custom field's data source (or right-click, Edit).
In the Query Text field, remove {schema} and replace it with the schema name you found above (without brackets).

In my example, the full value in Query Text is:
invcloudtest.SpItemViewsWithCustomFields

Find the "Retrieve Columns" button, and click the small icon just to the right of it, then check the box for "Allow Run StoredProc".
Click on the "Retrieve Columns" button.

Just below this, the columns list now shows your custom field(s). The example screenshot shows fields of "color" and "ham".

Click OK.

================================================================================

3. Create the relation:

Double-click the data band (or right-click, Design icon).
Click the Custom Fields data source one time (in this case, SpItemViewsWithCustomFields).
At right, click Data Relation.
At top, click New Relation.

Set the Parent Data Source to the source of the data band (in this case, SpInventoryLocation).
The Child Data Source should already be the Custom Field's data source (in this case, SpItemViewsWithCustomFields).

In the Parent Columns section on the right side, find asset_id (scroll down), then double-click it so it moves to the left side.
Do the same in the Child Columns section.

Click OK, then OK again.
At the left, the relation will be visible in the expanded Custom Fields source. In the screenshot below, the relation is called "Name".

The custom fields should now show at the left, so they are available to be added to the report layout.