Checking if attributes are maintained

This chapter explains how to set up a report using the example of a manually triggered report that checks the products of a selected category if a selected attribute is maintained on the selected locale level. This is done using a SQL query. After running the report in the iPIM Portal, the file with the report result is downloaded, whereby the file contains the product number and the product name of the products that were captured by the report. Additionally, the products are added to a particular worklist.

INFO
Click here to find a list of the required rights.

Step 1: Generate a report and make the basic settings

In this step, a new report is generated and the basic settings are made.

INFO
Click here to find detailed information about the basic settings of reports.

In order to generate a new report and to make the basic settings, proceed as follows:

     
  1. Access the iPIM CC module and go to the “Reports” area of the the “System setup” section.
  2. Call up the “General” tab.
  1. Click on the Add button in the “Reports” card. Following this, a new dataset with empty input fields is generated. Just the input field “Identifier” contains an identifier that was assigned by the system.
  1. Possibly enter an individual report ID in the “Identifier” field.
  2. Move the toggle switch “Executable immediately” to the On position so that the report can be run manually in the “Reports” section of the iPIM Portal.
  1. Enter the multilingual report names in the respective input fields. You don’t need to fill in all the input fields.
  1. Enter the multilingual report descriptions in the respective input fields. You don’t need to fill in all the input fields.

The basic settings are now made and you can proceed to the next step.

Step 2: Specify the SQL query, the columns and the parameters

INFO
Click here to find detailed information about how to configure the SQL query, the columns of the report output and the retrieval parameters.

In this step, the SQL query statement, the columns of the report output and the retrieval parameters are specified:

  • Go to the “Configuration” tab.

Step 2.1: Specify the SQL query

In this step, the SQL query is specified. To do so, proceed as follows:

  • Enter the appropriate SQL query in the “Query statement” field. In this example it is:
    SELECT art.id, art.productNo, lft.text
    FROM Article art, ArticleAV aav, LocaleFreetext lft, NodeArticleRelation nar
    WHERE aav.article.id = art.id
    AND art.id = nar.article.id
    AND aav.attribute.id = 2
    AND lft.textId = aav.freetext
    AND lft.locale.id = ?3
    AND art.articleType.id IN (1, 10, 11)
    AND NOT EXISTS (SELECT aav2 FROM ArticleAV aav2 WHERE aav2.article.id = art.id AND aav2.attribute.id IN (?1) AND (aav2.attributeValue IS NOT NULL OR aav2.freetext IS NOT NULL OR aav2.booleanValue IS NOT NULL OR aav2.numberValue IS NOT NULL OR aav2.decimalValue IS NOT NULL OR aav2.dateValue IS NOT NULL OR aav2.plainValue IS NOT NULL))
    AND #LIST_PARAM#nar.node.id IN (?2)#LIST_PARAM#

Step 2.2: Specify the report output columns

Based on the SQL query entered in the previous step, in this step the columns of the report output are specified. In this case, these are the columns for the product number and the product name.

  1. Scroll to the “Columns” card.
  1. Define the column for the output of the product number. To do so, click on the CREATE button to call up the corresponding dialog box.
  2. From the menu, select the option “Product number”.
  3. Click on the SAVE button. Following this, the dialog box closes and a new entry appears in the table.
  1. Enter the multilingual column headers for the report output in the respective input fields. You don’t need to fill in all the input fields.
  1. Define the column for the output of the product name. To do so, click on the CREATE button to call up the corresponding dialog box.
  2. From the menu, select the option “Text”.
  3. Click on the SAVE button. Following this, the dialog box closes and a new entry appears in the table.
  1. Enter the multilingual column headers for the report output in the respective input fields. You don’t need to fill in all the input fields.
  1. Click on the SAVE button in the lower right corner to cache your recent settings.
Note
Before being able to save a newly created report, you need to define a name, a description, a SQL statement as well as the columns of the report output.

Step 3.2: Specify retrieval parameters

In this step, the parameters that can be selected when running the report in the iPIM Portal are defined in order to determine which attribute is checked in which category and on which locale level to see whether values have been maintained for the products in the category. To do so, proceed as follows:

  1. Scroll to the “Parameters” card.
  1. Define the parameter for selecting an attribute when running the report. To do so, click on the CREATE button to call up the corresponding dialog box.
  2. From the menu, select the option “Attribute”.
  3. Click on the SAVE button. Following this, the dialog box closes and a corresponding entry appears in the table.
  1. Define the parameter for selecting a category of the primary taxonomy when running the report. To do so, click on the CREATE button to call up the corresponding dialog box.
  2. From the menu, select the option “iPIM Classification”.
  3. Click on the SAVE button. Following this, the dialog box closes and a corresponding entry appears in the table.
  1. Define the parameter for selecting a locale level when running the report. To do so, click on the CREATE button to call up the corresponding dialog box.
  2. From the menu, select the option “Language”.
  3. Click on the SAVE button. Following this, the dialog box closes and a corresponding entry appears in the table.
  1. In the appropriate input fields, enter the multilingual headers for the various selection menus in the dialog box for running the report in the iPIM Portal. You don’t have to fill in all the input fields.
  1. Click on the SAVE button in the lower right corner to cache your recent settings. If the process was successful, a corresponding message will appear.
    The data retrieval for the report is now configured and you can proceed to the next step.

Step 3: Configure the data output (optional)

INFO
Click here to find detailed information about how to configure the data output.

In this step, it is specified that the products that were captured by the execution of the report are added to the worklist “Worklist 5”. To do so, proceed as follows:

  1. Go to the “Output” tab and there go to the “Worklist card”.
  1. Select the folder that is to contain the worklist in question (in this case “Folder 5”).
    1. Click on the More button in the “Folder” field to call up the corresponding dialog box.
    2. Choose the folder from the folder directory. Possibly use the text search to filter the list of folders by a search term.
    3. Click on the folder to select it.
    4. Click on the ACCEPT button to finalise the selecting of the folder.
  1. Enter the name of the worklist in the “Worklist” field*.
  2. From the menu “Mode”, select the option “Hinzufügen” so that the products captured by the report execution are added to the selected worklist**.
  3. Click on the SAVE button in the lower right corner to save your settings. If the process was successful, a corresponding message will appear.
    The data output is now configured.
Note
  • *If you enter a non-existing worklist name in the input field “Worklist”, a new worklist with the entered name will be created.
  • **With this setting, the products captured by the report execution are added to the products that are already in the worklist.

Step 4: Run the report and download the report file

In this step, the report is run in the iPIM Portal in order to check the products in the primary taxonomy category “Toaster” if the attribute “Item description” has values stored on the english locale level.

INFO
Click here to find detailed information about how to run reports. And click here to find a list of the required rights.

Step 4.1: Run the report

In order to run the report, proceed as follows:

  1. Go to the “Reports” app of the iPIM Portal.
  1. Choose the report from the list of reports in the left-hand card. Possibly use the text search to filter the list of reports by a search term.
  2. Move the mouse cursor over the report. In the process, a menu bar appears.
  3. Click on the Execute button to call up the corresponding dialog box.
  1. From the “Attribute” menu, select the attribute which is checked wether it is maintained (in this case “Item description (ItemDesc)”).
  2. Select the category:
    1. Click in the “Category name” field to call up the category directory.
    2. Choose the category from the directory (in this case “Toaster”). Possibly use the text search filter the list of categories by a search term.
    3. Click on the category to select it.
  3. From the “Data locale” menu, select a data locale (in this case “English”).
  1. Click on the RUN button to trigger the execution of the report. If the process was successful, a corresponding message will appear. Following this, the report file can be downloaded.

Step 4.2: Download the report file

In order to download the report file, proceed as follows:

  1. Move the mouse cursor over the report once again. In the process, a menu appears.
  2. Click on the Action menu button to call up the action menu behind it.
  1. Click on the “Herunterladen Download” action to trigger the download. Once the download is complete, the report file can usually be found in your local download folder.
  1. Open the report file in a spreadsheet software.

Step 4.3: Call up the worklist

In this step, the worklist that contains the products captured by the report execution is called up.

INFO
Click here for detailed information about how to call up worklists. And click here to find a list of the required rights.

In order to call up the worklist, proceed as follows:

  1. Go to the “Worklists” app of the iPIM Portal.
  1. Choose the worklist from the worklist directory. Possibly use the text search to filter the list of worklists by a search term.
  2. Click on the worklist to select it. Following this, the products inluded in the worklist are shown in the table of the right-hand card.
    Alternatively you can call up the worklist in the “Excel” tab of the “Productdata update” app in order to add the missing attribute values there.