Create a Filter for a Session Delivery Manager Report: Example

The following example is used to create a filter with a device group, list of values, and device parameters for an Oracle Communications Session Delivery Manager report. The filter name is displayed as a drop-down list item in the report using the data model you added in the previous section.

Note:

The SQL queries must be entered exactly as they are shown in the following steps if you are going to use this example.

1. In the Data Model navigation pane, click List of Values.
2. In the List of Values pane, click the plus (+) icon above the table to create a new list of values in the table.
3. In the new table row under the Name column, enter the name devices.

   Note:

   Ensure that the Type drop-down list is set to SQL Query and the Data Source drop-down list is set to ocsrm for this step and subsequent steps.
4. In the SQL query box below the table, enter the following SQL query:

   select	 d_device.target_name as target_name
    from	ocsremdw.d_device_group d_device_group,
   	ocsremdw.d_device d_device 
    where   d_device.device_group_key=d_device_group.device_group_key and device_group_name in ( :device_group)
   

5. In the List of Values pane, click the plus (+) icon again to create another list of values in the table.
6. In the next table row under the Name column, enter the name realms.
7. In the SQL query box below the table, enter the following SQL query:

   select d_realm.realm_name as realm_name 
    from ocsremdw.d_realm d_realm
   

8. In the List of Values pane, click the plus (+) icon again to create another list of values in the table.
9. In the next table row under the Name column, enter the name device_groups.
10. In the SQL query box below the table, enter the following SQL query:

    select	d_device_group.device_group_name as device_group_name 
     from	ocsremdw.s_nnc_user s_nnc_user,ocsremdw.s_nnc_user_group s_nnc_user_group,
    	ocsremdw.d_device_group d_device_group 
     where   s_nnc_user_group.device_group_name=d_device_group.device_group_name
     and	 s_nnc_user_group.nnc_user_group_name=s_nnc_user.nnc_user_group_name and upper(nnc_user_name)=upper(:xdo_user_name)
    

    Once the list of values for devices, realms, and device groups is configured, the List of Values pane should appear as shown below:

    
11. In the Data Model navigation pane, click Parameters.
12. In the Parameters pane, click the plus (+) icon above the table to create a parameter in the table.
13. In the new table row under the Name column, enter the name device_group, select Menu from the Parameter Type drop-down list and complete the following fields:

    Name	Description
    Display Label field	Enter Device Group as the label for this parameter.
    List of Values drop-down list	Select device_groups.
    Number of Values to Display field	Enter 100.
    Options check box	Select from of the following check boxes: Multiple Selection Can select all Refresh other parameters on change

14. In the Parameters pane, click the plus (+) icon above the table to create a parameter in the table.
15. In the new table row under the Name column, enter the name start_date, select Date from the Data Type drop-down list, select Date from the Parameter Type drop-down list, select 2 from the Row Placement drop-down list and complete the following fields:

    Name	Description
    Display Label field	Enter Start Time as the label for this parameter.
    Date Format String field	Enter MM-dd-yyyy hh:mm.

16. In the Parameters pane, click the plus (+) icon above the table to create a parameter in the table.
17. In the new table row under the Name column, enter the name end_date, select Date from the Data Type drop-down list, select Date from the Parameter Type drop-down list, select 2 from the Row Placement drop-down list and complete the following fields:

    Name	Description
    Display Label field	Enter End Time as the label for this parameter.
    Date Format String field	Enter MM-dd-yyyy hh:mm.

    Once the list of parameter values is configured, the Parameters pane should appear as shown below:

    
18. In the Data Model navigation pane, select Data Sets > Hourly Session Realm Stats as shown in the Untitled Data Model window below:
    
19. In the pre-populated Edit Data Set - Hourly Session Realm Stats dialog box, enter the text shown in the red box in the figure below after the existing text in the SQL Query box:
    
    The line you entered joins the parameters that you created in the previous stems and filters the resulting query based on the devices, realms, start, and end dates selected in the report.
20. Click OK.
21. In the Untitled Data Model screen, click the save (disk) icon on the top right side of the window and save the data model. For example, you can name it "Hourly Session Realm Stats Data Model."
22. After you successfully save the data model, you can create a report called "Daily Session Realm Stats Report" using this newly created "Hourly Session Realm Stats Data Model."