Simple Parameterizing

Owned by Nicolás Palombo (Unlicensed)
Last updated: 20 Sept, 2008 by Former user (Deleted)Version comment
4 min read
Unknown macro: {scrollbar}

Reports frequently require a certain degree of parameterizing so that the user can filter the displayed information, thus restricting analysis to subsets of information.

 In this section we describe how to add to the report a parameter  which will enable the displaying of data for only one salesperson, without it affecting the structure of the report.  

Creating a new parameter. 

  1. In the Data Explorer panel, right-click on the Report Parameters element.
  2. Create a new parameter

    This action opens the parameter editor.
  3. Indicate the following data for the parameter:

    Name:

    The name of the parameter
    In our case Vendedor ( Salesperson)

    Data type:

    Parameter data type
    In our case String

    Display Type:

    Way to display data.
    In our case List Box

  4. Selecting the way in which data are displayed as List Box displays a set of additional options typical of this form of viewing.
    The drop-down list (list box) may contain static or dynamic options.
    The static case lets the designer of the report indicate in a static way the list of elements to be included on the list.
    The dynamic case lets the designer define a query to load the list with stored data, for instance, in an O3 cube.
  5. Indicate the type of dynamic list

    This type of parameter requires the existence of Data Set from which the values to load the list will be taken.
  6. Using the Create New... button, create a new Data Set as indicated below:

    Name

    Vendedores

    Data Source

    O3 Local MDX

    Data Set Type

    O3 Data Set

  7. Enter the following MDX query in the Data Set Specification 
    SELECT {[Vendedores].[Vendedor].members} ON COLUMNS
FROM [Vinos]

    For more details on the definition of a Data Set refer to the section Specifying the data to use

  8. Confirm changes to the Data Set editor 
  9. Complete the parameter data with the new information. 

    Data Set:

    Data Set to which the parameter is associated 
    In our case we will use the Data Set Vendedores

    Select value column:

    Data Set field that contains the key to the parameter.
    In our case we will use unique_name

    Select display text:

    Data Set field containing the parameter label to show the user. 
    In our case it will be label

  10. Finally, the editor should show the following.
  11. Press the OK button to conclude the creation of the Parameter, which will appear in the Data Explorer window under Report Parameters.  

At this stage we may run the report to see a window displayed, where the parameter value is requested  from a list of options obtained from the O3 Cube.

 



Using the Parameter in the query

Once the parameter is defined, it is necessary to use it in the query to filter it and return only the Salesperson selected by the user. 

In order to do so, we will modify the Data Set defined in the section  Specifying the data to use.

  1. From the Data Explorer, double-click the Data Set Ventas MDX.
  2. Modify the MDX query so that it contains the following.   
    SELECT
	{[Measures].[Litros Vendidos], [Measures].[Venta Neta]} ON COLUMNS
	,CROSSJOIN({[Vendedores].[Vendedor].?}, {[Vinos].[Vino].members}) ON ROWS
FROM [Vinos]
    Please note the query mark in the section corresponding to the rows. This mark indicates the place where the parameter value will be substituted when the query is run. 
  3. Select Parameters in the Data Set editor.
  4. Add a new parameter with the New... button
  5. Configure the parameter as follows

    Name:

    Parameter name
    In our case Vendedor

    Data Type:

    Parameter type
    In our case it requires no modification

    Direction:

    Parameter Direction (Input or Output)
    In our case we will use input

    Default Value:

    Value the parameter will have by default.
    In our case N/A

    Linked To Report Parameter:

    Parameter to which it is associated
    In our case Vendedor

  6. Finally, we just have to run the report to see how data are filtered to show only the sales for the selected Salesperson. 
Unknown macro: {scrollbar}