Oracle Sales Cloud: Reporting and Analysis (BIEE) Small details create double hints and establish associations (for example, departmental and sub-department tips)

Oracle Sales Cloud is an Oracle cloud-based customer opportunity management system that provides rich capabilities to help increase sales efficiency, better understand customers, discover and track opportunities, and pave the way for final sales deals (deal). Of course, our focus today is not to introduce Oracle Sales Cloud, but rather a small detail feature in its report development-hints. In fact, Oracle Sales Cloud's reporting and Analysis module development uses packaged BI tools, and the current (September 2016) version is: Oracle Business Intelligence 11.1.1.7.0.

Figure 1:oracle Sales Cloud navigation bar Figure 2:oracle Sales Cloud report and Analysis

We know that in BI development, you can deploy the hint to the dashboard, and according to the prompt to pass the parameters, so as to select different prompts, the report shows the corresponding data. Now, there is a need:

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

[2] 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);

Sub-departments including Beijing Sales 1, Beijing Sales 2, Zhengzhou Sales 1 group, Zhengzhou sales 2 groups;

[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.

[4] Opportunity Detail report data according to the corresponding prompts to display the corresponding department or sub-department data (follow-up we will say according to the double hint of this small detail, the main first to meet the demand for prompt)

Now, let's start in bi to meet this change demand tip:

(1) 1th step: Create "cux_ Department and Sub-department prompt" under the theme Area Dashboard hint folder, new column hint "department", 3.

Note: You need to set up a representation variable in order to qualify different conditions for the " child departments " column with SQL values.

Figure 3: Department column tips

(2) 2nd step: Create a new empty column hint, 4. Role: Do not display in the front-end interface, set the limit value is based on " Department ", in the " Department " column of the value update, with the update.

NOTE: The column hint is empty-the compilation formula has two single quotation marks; the label is empty; the user input is a check box, and the width is 0 pixels.

Figure 4: null hint

(3) 3rd step: New column hint "child department", 5.

Note: The value here is the SQL result, where the query method used is: multiple conditions in the WHERE clause

Select * FROM table Where (condition 1 and result 1) or (condition 2 and result 2) or (condition 3 and result 3)

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

                Select "Employee". " Resource Primary Organization Name "
                      from "Sales-crm Pipeline"  
                   WHERE (' @{pv_a} ' = ' Beijing Sales department ' and ' Employee "." Resource Primary Organization Name "in (' Beijing Sales 1 ', ' Beijing Sales 2 '))    
          & nbsp               OR (' @{pv_a} ' = ' Zhengzhou Sales department ' and ' Employee '. Resource Primary Organization Name "in (' Zhengzhou Sales 1 group ', ' Zhengzhou Sales 2 Group ')"
                   ORDER by "Employee". " Resource Primary Organization Name "

Figure 5: Child Department column Tips

In this way, we can deploy the hints we have made to the dashboard test, 6 and Figure 7.

     

Summary: report and Analysis (BI development) Small details create a double hint and have a parent-child association (for example, department and sub-department tips), mainly describe the use of the expression variables to build departments and sub-departments two tips, and to give parent-child association. In addition, we can see that the biggest drawback of this approach is: for the actual project, once the organizational structure of the enterprise has been adjusted, then the hint will be modified, because once the department has changed, whether it is the "Department" column of the specific value or "sub-department" The SQL results for the column are affected.

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

Oracle Sales Cloud: Reporting and Analysis (BIEE) Small details create a double hint and establish an association (for example, department and sub-department tips)