How to set query parameters before loading a Crosstab

By default, Cross-tab will be loaded with full data from the cube. Retrieval parameters are run-time parameters which are used to load cross-tab with filtered views.

Note: Retrieval parameter created for one object cannot be accessed from other objects.

The below Sales cross-tab shows Product Category wise Gross sales over different years.


image002.png


PRODUCT CATEGORY WISE GROSS SALES OVER DIFFERENT YEARS 

Step 1

Go to the Cross-tab Settings toolbar > Manage Retrieval Parameters

Step 2

In the Manage Retrieval Parameters dialog box, options to select the Dimensions and their default values are available.

In the Selected dimension(s) field, select the Year dimension from the drop-down list.

Step 3

In the Default value(s) field (this is optional), you can select any years from the drop-down list.


image004.png

MANAGE RETRIEVAL PARAMETER WINDOW 

Step 4

Click OK to create retrieval parameters.

After creating the Retrieval Parameters, select the Retrieval Parameters option from the Cross-tab toolbar to access them. You can give year values (say 2013 and 2014) from the drop-down list.


image006.png

RETRIEVAL PARAMETERS OPTION TO PROVIDE VALUES

The result will be queried like an 'OR' condition between these values of the dimension 'Year' (ie; Year = 2013 OR 2014). Hence, the Category sales of 2013 and 2014 will be displayed.


image008.png

    CATEGORY SALES OF 2013 & 2014



Anytime you can change the retrieval parameters values as needed based on which the data in the cross-tab will get reflected.

Multiple Parameters can be defined from the Manage Retrieval Parameters dialog.

For example, you can create two retrieval parameters – Year and Product Category. Once these retrieval parameters are created, you can retrieve this cross tab based on selected values from Product Category and Year – e.g. Sales of 'Bakery' and 'Fruit Juices' for '2013' and '2014'.


image010.png

MULTIPLE PARAMETERS FOR PRODUCT CATEGORY AND YEAR


image012.png

    AFTER APPLYING MULTIPLE FILTER

The data will be queried like an 'AND' condition of the dimensions Product Category and Year. So, this whole query would be (Category = 'Bakery' OR 'Fruit Juices') AND (Year = '2013' OR '2014'). Hence the sales of 'Bakery' and 'Fruit Juices' for the years '2013' and '2014' will be displayed.

 Note: This article is based on ElegantJ BI Version 4.1. This may or may not be relevant to the ElegantJ BI version you may be using.