Subreport With Swap Relation Direct Table (Swap-Table)

Owned by Morten Lindequist Køhler
Feb 14, 2024
6 min read

This guide will show you how to include Perfion swap-tables in Reports without sing the Perfion Table Designer.

By default, Reports are built based solely on the data defined in the underlying Perfion API query, but you can enhance and enrich this data using Actions. Actions also allows you to fetch additional data tables to be used in the Report and even to base the Report on an entirely different dataset than the one specified in the API query of the Report. This also works with Sub-Reports. The Report Designer allows you to add a Sub-Report to a template, this Sub-Report will have its own template and can run on a different data table than the main Report.

This guide will cover:

  • How to use Actions to fetch additional data tables for the Report .

  • How to use a Sub-Report in a Report template.

  • How to run a Sub-Report on a different data source than the main Report.

Introduction

In this guide, we will use the example of a product sheet for a dining room set. The set contains a table and some chairs. Both the tables and the chairs are individual items in Perfion, meaning that they can be sold as stand-alone products, but also combined into a single set and sold as a combined product.

image-20240112-133929.png
The chair item
image-20240112-134054.png
The table item

On the items (JGSET01 and JGSET02) in the Sets category, we have created a swap-table to manage the relation between the Set item and the items it consists of.

It is the information in the swap-table that we want to display in the Report. Swap-table information is not part a Perfion API query result by default, so we need another way to fetch it to the Report. This is where Actions comes in handy. In the image below you can see the final Report we will create.

Add Action Data To Report

Actions can find and transform data for many different purposes, including creating input for the Report Designer. With Actions you can both modify the existing data before it is sent to the Report Designer and you can create additional data sources, the latter of which is what we’re interested in for this example.

We will start by creating an Action that finds the entire swap-table and formats it to be used in the Report.

The Action simply finds every item in the selectable feature SetTable, saves it in ProductSets data source and gives the columns of the data source some new names.

  • MainProduct - The name of the product set item (JGSET02 from the screenshot of the item editor above).

    • This feature is important for matching the swap-table items with the items for the Report.

  • Itemname - The names of the part in the set (JGCHAR01, JGTAB01 or 13689 om the screenshot of the item editor above).

  • Image - The image of the set part.

  • Pieces- -The quantity of the part in the set.

With the Action configured, we can now specify in the Super Query for the Report that we want to use the result of this Action in our Report.

As soon as you specify the Action ID in the Super Query of the Report, every data source created by the Action becomes available in the Report Designer.

 

Add Sub-Report That Uses The New Data Source

Add Sub-Report element

To create a Sub-Report, first you need to add a new Perfion Sub-Report element to the canvas. In the properties for that element, check the Use root report data source. This will make sure that the Sub-Report has access to exactly the same data sources as the main Report.

Now, before going forward and styling the Sub-Report, we need to recap what exactly is in the ProductSets data source:

The data source contains the swap-information for ALL products, not just the one we are designing the table on. When we pass information to the Sub-Report we need to filter this information to get only the rows that are relevant for the selected product. That is, when we execute the Report on product JGSET02, we only want the items where MainProduct equals JGSET02.

Therefore, we need to create a parameter in the Sub-Report element to pass the product name.

To do so, click the three dots on Paramter Binding and bind the _Name element of the Items data source to a paramter called ProductName

Styling the Sub-Report

This next step is to double-click the Sub-Report element. This will open a new template where we can style the Sub-Report. We need to make sure that the Sub-Report runs on the ProductSets data source AND that we filter out any rows that are not relevant for our product.

To ensure those conditions, we set up a new parameter in the Sub-Report with exactly the same name that we used for the parameter in the Sub-Report element earlier. This will make sure that the parameter passed to the Sub-Report is caught and stored in the parameter we create in the Sub-Report template.

 

Now, by clicking the square in the top-left corner we can change the Data Member to ProductSets.

Next, we add a Filter String to make sur we only use items where the MainProduct of the ProductSets data source equals the parameter ProductName that was passed to the Sub-Report from the main report.

Now, we are ready to design our Sub-Report. Remember to save the template, otherwise the changes will not show in the main Report.

When done with the layout of the Sub-Report, select the Sheet tab to return to the main Report to check the final result in the Preview window.