Selecting an Attribute Join Type

You can determine attribute join types in the following ways:

• Use a VLDB property setting to determine attribute join types for a given database instance, report, or report template. See details about the Preserve All Lookup Table Elements VLDB property in Preserve All Lookup Table Elements.
• Use the Report Data Options dialog box to individually select attributes on the template that need to display attribute elements. When you change from the default attribute join type setting (Preserve common elements of lookup and final pass result table), it is assumed that you want to keep ALL elements of the attributes in their lookup tables. However, sometimes you want such a setting to affect only some of the attributes on a report. For example, for a report containing Store, Month, and Sum(Sales), you may want to show all the store names, even though they have no sales, but not necessarily all the months in the LOOKUP_MONTH table. To do this, use the Report Data Options dialog box as described in the procedure below.

  The four attribute join types available in the Report Data Options dialog box are the same as those in the VLDB Properties dialog box. The system reads them from the same location.

To View and Change Attribute Join Types

1. Open a grid report.
2. Choose Data > Report Data Options.
3. Under Categories, expand Calculations, and select Attribute Join Type. The Calculations - Attribute Join Type appears on the right side of the interface. This subcategory lists all attributes on the report, along with each attribute's join type, as shown in the image on click here.

   If you have a long list of attributes, you can sort them by attribute name or by join type, by clicking the Attribute or Join Type column headers.

4. Depending on your goals for joining attribute data, select one of the following join types. Details and examples for each join type are provided in Understanding Attribute Join Types.
• Preserve common elements of lookup and final pass result table: (Default setting) This join type causes the SQL Engine to calculate for only the attribute elements that exist in both the lookup and fact tables. This join type lets you display all attribute elements that exist in the lookup tables in your data warehouse, regardless of whether there is a corresponding fact value in the data warehouse. You cannot change the join types of individual attributes with this setting. The attributes and their join types are displayed for information only.
• Preserve lookup table elements joined to final pass result table based on fact table keys: This join type causes the SQL Engine to calculate for only the elements that are joined to the fact table based on fact table keys.
• Preserve lookup table elements joined to final pass result table based on template attributes without filter: This join type causes the SQL Engine to calculate for all attribute elements and ignores all related filtering conditions.
• Preserve lookup table elements joined to final pass result table based on template attributes with filter: This join type causes the SQL Engine to calculate for all attribute elements and applies all related filtering conditions.
5. You can change the join type of a specific attribute, if you select any of the Preserve lookup table elements join types. In the Join Type settings table, click the Join Type for the attribute and select the join type from the drop-down list. The join type options are:
• Default: This option sets the attribute to use the join type set for that individual attribute using the Attribute Editor. If a join type was not set at the attribute level, the attribute uses the join type set at the project level.
• Inner: This option displays only the attribute elements common to all data warehouse tables from which data is being gathered for this attribute.
• Outer: This option displays all of the attribute elements from all data warehouse tables from which data is being gathered for this attribute.

  If the report contains an object prompt on the report grid, the report's attributes are not yet defined and therefore cannot be displayed in the Join Type settings table. To assign join settings to the attributes included in the object prompt, see Join Type Settings for an Object Prompt.

6. Click OK. The report is re-executed against your data warehouse, and the newly calculated results are displayed.

Join Type Settings for an Object Prompt

If a report contains an object prompt on the report grid, the report's attributes are not yet defined and therefore cannot be displayed in the Join Type settings table. You can assign join settings to the attributes included in the object prompt by answering the prompt in SQL View. This adds the attributes to the report, so that you can assign join type settings.

For example, the following report contains an object prompt, called Choose Attributes, on the rows:

When you access the Report Data Options dialog box to select join types for the attributes, no attributes are available for modification, since no attributes have been added to the report.

Running the report in SQL View and answering the prompt with all the available attributes adds them to the report. Now when you access the Report Data Options dialog box, all the attributes are displayed and available for modification. When you save the report as a prompted report, the user can select his own answers to the prompt and populate the report as he wants. The attribute join types that you assigned are saved and used.

To Assign Attribute Join Types to Attributes in an Object Prompt

1. Choose View > SQL View.

   If the Report Data Options dialog box is still displayed, click OK to return to the report, and then change the report view.

2. Choose all of the attributes that are available in the prompt.
3. Click Finish.
4. Choose View > Design View. Notice that all the attributes are included in the report.
5. You can use the Report Data Options dialog box to assign join types for every attribute, as described in To View and Change Attribute Join Types.
6. Save the report as prompted, rather than static.

   When the report is re-executed, the prompt is displayed so that the user can select his own prompt answers. Each attribute that is selected uses the specified join setting.