Dynamic Parameters with a Nested DataSource

Assume a dynamic parameter is specified in the SQL query while adding the JDBC data source to fetch the records of a specific city from the Stores table. Here is how a composite data source is added to the repository which references the Stores data source and values are passed to the dynamic parameters.

  1. Make sure the Mondrian Database is available (e.g. through ODBC).

  2. Add a JDBC DataSource called Stores and choose the appropriate driver and URL for your database. If you are using Mondrian through JDBC/ODBC (as described in the section called “Using the JDBC/ODBC bridge driver”) then enter the URL as "jdbc:odbc:MondrianFoodMart".

  3. Click Next. Enter the following SQL query in the SQL tab window.

    SELECT DISTINCT
    store.store_id, store.store_country AS Country, 
    store.store_state AS State, store.store_city AS City, 
    store.meat_sqft, store.grocery_sqft, store.frozen_sqft, 
    store.store_sqft FROM store Where store_city like '${Enter City}';
  4. Click Next and choose Infer Schema. On the Dynamic parameter dialog, enter any city name and click Finish. The schema will be displayed. Click Finish to add the data source to the repository.

  5. Now add a Composite DataSource called Dynamic-Comp On clicking Finish, the Composite data source is added to the repository and opened.

  6. Drag and drop the Stores.ds file onto the Composite diagram and connect it directly to the Result.

  7. Open the Stores Properties from the shape on the diagram and click Next to see the DataSource Properties Screen. You will see the "Enter City" parameter. Enter the value as "Salem" (without the quotes) and click Finish.

  8. Select the Result, and choose View from the popup menu. The output is displayed as shown in the fig A1.9. This is similar to that of passing dynamic parameters where Enter City=Salem;

  9. Go back to the Stores DataSource Properties screen and change "Salem" to ${City1}.

  10. Select the Result, and choose View from the popup menu. This time, the "Dynamic Parameters" dialog appears.

  11. Enter "Salem" in the City1 text field and click Finish. The Result output is the same as when the value "Salem" was hardcoded.

  12. Repeat the process, replacing ${City1} with ${City1}${City2}.

  13. When you view the Result, the "Dynamic Parameters" dialog appears again, this time with two fields. Enter "Sal" in the City1 text box and "em" in the City2 text box. The text values from City1 and City2 are concatenated by the ${City1}${City2} substitution, so the records corresponding to "Salem" are fetched again.