Oracle Sales Cloud: Reporting and Analysis (BIEE) Small details 2--use variables and filters to pass parameters (for example, show data from different departments based on hints)

In the last essay, we set up two tips for departments and sub-departments, and we associate departments with sub-departments. So, in this essay, we focus on the use of the built-in double hints for the transfer of parameters.

Before we do this, let's look at a specific requirement for reporting and analysis:

[1] Two related tips: departments and sub-departments.

1. The department includes Beijing sales department, Zhengzhou sales department (here is a small simulation example, the specific project implementation can be selected according to the corresponding field);

2. Sub-departments include Beijing Sales 1, Beijing Sales 2, Zhengzhou Sales 1 group, Zhengzhou Sales group 2;

3. when the value of the department is Beijing sales department, the Sub-department value List range is: Beijing sales 1, Beijing Sales 2; Zhengzhou Sales Department similarly.

(Satisfied, and: " department " corresponds to the variable "pv_a"; "Sub-department" corresponds to the variable "Pv_b", see the essay "Oracle Sales Cloud: report and Analysis (BIEE) Small details 1")

[2] New Opportunity Detail Report, specific display columns include: Opportunity ID, name, Sales stage, responsible person, success rate, amount, profit, margin, resource organization name (department).

[3] for prompting the request for the parameter.

1. When the value of the department is Beijing sales department, the report display resource organization name is the entire Beijing Sales department data (including Beijing Sales 1 and Beijing Sales 2 Department);

2. When the value of the sub-department is Beijing Sales 1, the report shows the resource organization name is Beijing sales of 1 data; when the value of the sub-department is Beijing Sales 2, the report shows the resource organization name is the data of the Beijing Sales department 2.

3. For the Zhengzhou sales department in the same situation.

Now, let's start with the analysis and dashboards that meet this requirement in BI:

(1) 1th step: Create "cux_ Opportunity Detail Report", select corresponding data display column, 1.

Note: The resource Organization Name column corresponds to the sub-department data, because the resource corresponds to each salesperson, and the organization is the corresponding sales department.

Figure 1: New Opportunity Detail Report

    (2) 2nd step: Add a filter to convert the filter to sql< Span style= "Font-size:small;" >

Figure 2: Adding a filter

(3) 3rd step: Modify the sql,3 of the filter.

Note: The value here is the SQL result, where the query method used is: union joins each select result

So here's the SQL (this gets the field as " Resource Organization "):

1. Results at the sectoral level are limited

"Employee". " Resource Primary Organization Name "in (
Select DISTINCT "Resource Primary Organization Name" from "Sales-crm Pipeline"
WHERE ' @{pv_a}{} ' is null
and "Employee". " Resource Primary Organization Name "in (' Beijing Sales 1 ', ' Beijing Sales 2 ', ' Zhengzhou Sales 1 group ', ' Zhengzhou Sales 2 group ')
Union
Select DISTINCT "Resource Primary Organization Name" from "Sales-crm Pipeline"
WHERE ' @{pv_a}{} ' = ' Beijing Sales Department '
and "Employee". " Resource Primary Organization Name "in (' Beijing Sales department 1 ', ' Beijing Sales 2 ')
Union
Select DISTINCT "Resource Primary Organization Name" from "Sales-crm Pipeline"
WHERE ' @{pv_a}{} ' = ' Zhengzhou Sales Department '
and "Employee". " Resource Primary Organization Name "in (' Zhengzhou Sales 1 group ', ' Zhengzhou Sales 2 group ')
)

2. The result of the sub-departmental level is limited, and the department uses "and" to connect

"Employee". " Resource Primary Organization Name "in (
Select DISTINCT "Resource Primary Organization Name" from "Sales-crm Pipeline"
WHERE ' @{pv_b}{} ' is null
and "Employee". " Resource Primary Organization Name "in (' Beijing Sales 1 ', ' Beijing Sales 2 ', ' Zhengzhou Sales 1 group ', ' Zhengzhou Sales 2 group ')
Union
Select DISTINCT "Resource Primary Organization Name" from "Sales-crm Pipeline"
WHERE ' @{pv_b}{} ' = ' Beijing Sales Department 1 '
and "Employee". " Resource Primary Organization Name "= ' Beijing Sales Department 1 '
Union
Select DISTINCT "Resource Primary Organization Name" from "Sales-crm Pipeline"
WHERE ' @{pv_b}{} ' = ' Beijing Sales Department 2 '
and "Employee". " Resource Primary Organization Name "= ' Beijing Sales Department 2 '
Union
Select DISTINCT "Resource Primary Organization Name" from "Sales-crm Pipeline"
WHERE ' @{pv_b}{} ' = ' Zhengzhou Sales 1 group '
and "Employee". " Resource Primary Organization Name "= ' Zhengzhou Sales 1 group '
Union
Select DISTINCT "Resource Primary Organization Name" from "Sales-crm Pipeline"
WHERE ' @{pv_b}{} ' = ' Zhengzhou Sales 2 group '
and "Employee". " Resource Primary Organization Name "= ' Zhengzhou Sales 2 group '
)

Figure 3: Modifying Filters

      (4) 4th step: Put the prompt and detail report on the same dashboard to deploy it

     

Summary: reporting and analysis (BI development) Small details 2--use variables and filters to communicate (for example, to show the data without the department), mainly introduces the use of the expression variables and filters for the parameters, This allows the report to show the corresponding data according to different prompts. It is also important to note that, with this approach, for the actual project, once the organizational structure of the enterprise has been adjusted, the filter and the prompt as well as the corresponding changes.

Thank you, you are welcome to correct and communicate at any time!

Oracle Sales Cloud: Reporting and Analysis (BIEE) Small details 2--use variables and filters to pass parameters (for example, show data from different departments based on hints)