advanced

Posts

Posts

  • Harshavardhan Konathala
    Incentive compensation: CRM SALES and IC dimensions linking...5.0
    Topic posted May 28, 2019 by Harshavardhan KonathalaBlue Ribbon: 750+ Points, tagged Activity, BI Publisher, Business Plan, Business Plan Objective, Channel Account Manager, Channel Sales Manager, Chart, Competitors, Customer, Dashboard, Data Visualization, Forecast, Infolets, Leads, MDF, Opportunity, Opportunity Management, Partner, Partner Announcements, Pipeline, Quota, Reporting and Analytics, Sales, Sales Campaign, Sales Executive, Sales Manager, Sales Representative, Shared Reports, Trends, Users in Sales Cloud Report Sharing Center public
    Title:
    Incentive compensation: CRM SALES and IC dimensions linking using common 'Resource Hierarchy'
    Summary:
    The below prototype helps in building common reporting solutions across fusion CRM and IC Pillars, current DWH model doesn't have direct reporting solution across the pillars.
    Content:

    Background & Business Case - 

    • Customers who are using both CRM SALES and IC, as of now they are unable to create the report with common dimensions and metrics from both the pillars.
    • Any customer whose sales representatives involved in SALES activities and to check their compensation details till now customers are importing the same sales representatives data into IC and creating the reports.
    • Any customer if wants to create the report using SALES and IC dimensions there is no proper linking between these pillars and end up creating a cross SA with completely improper data.
    • Hence to resolve this we have an idea on joining CRM sales resource hierarchy and IC participant hierarchy, functionally which is same but technically there is no implementation.
    • CRM resources or sales representatives are the same IC participants who are getting compensation in IC. Hence using this functional derivation we are joining the CRM resource hierarchy and IC participant hierarchy details to get the common report across both the pillars.
    • CRM sales data ends once the order details have been placed, from there IC starts with the order data and ends with payment information.
    • As of now, we don't have any direct reports which have complete sales details along with the Incentive compensation metrics.
    • With this joining, we can get a complete report with SALES dimensions and metrics followed by IC metrics.

    FA Implementation - 

    To implement this requirement we have to make a couple of table joins from FA side which was not implemented as of now.

    Customers which are importing sales data into IC and creating reports from them we have to provide a solution with FA database tables join like below:

     

    FA%20DB%20Joins.png?version=1&modificationDate=1549990664000&api=v2

    • JTF_RS_REP_MANAGERS_CF is used to fetch manager/hierarchy for resources stored in JTF_RS_RESOURCE_PROFILES in Sales. IC participants are stored in CN_SRP_PARTICIPANTS_ALL and the hierarchy is sourced from HCM table  PER_MANAGER_HRCHY_REPORTEES_DN
    • Sales resource hierarchy and participant hierarchy are not stored in a common table. There is no process to sync this data either. The only link is the hz_parties.party_id (TCA) present in both Sales and IC tables.
    • Ideally, a customer implementing both IC and Sales will import sales resources in IC as participants. So, yes, jtf_rs_resource_profiles.party_id can be looked up for matching party_id records in cn_srp_participants_all

    Data Mockups - 

    Need to consider the participant's data directly from the database, so that we can cross-compare the same when implemented in OTBI.

    SALES DATA with PARTYID as main columns followed by all the main dimensions columns BASE ID values, FOR IC data, we have participant ID for the equivalent PARTYID columns followed by all the base dimensions columns BASE ID values. Please refer to attached Mockups  

    Points to note - We have to make the join between PARTID from sales and PARTYID from IC.

    Mandatory Join, We have to join ORDER_ID from sales and TRANSACTION_ID from IC. Because functionally OREDR_ID = TRANSACTION_ID. I.E. SALES REPS who are making orders, only those are considered as TRANSACTIONS in IC. Hence we have to consider this joins for PROPER reporting data.

     

    SALES%20AND%20IC%20REPORT.PNG?version=1&modificationDate=1549991819000&api=v2

    REPORT WITH BOTH SALES AND IC ATTRIBUTES - 

     

    COMMON%20REPORT.PNG?version=1&modificationDate=1549991989000&api=v2

     

    Final Dashboard - Entire Prototype built on Oracle Data visualization Desktop Tool.

     

    Final%20Report.jpg?version=1&modificationDate=1556281441000&api=v2

     

    Note - 

    1) Credit AMOUNT calculated based on the SPLIT PCT for each transaction handled by the participant.

    2) SPLIT PCT was given if two participants involved in one single transaction.

    3) Earning AMOUNT will be 5% of the credit amount.

    4) Transactions with status "CREDITED" are only considered for credit amount calculations

    5) Similarly credits with status "Credited" are only considered for earning calculations.

     

    Please find the complete observations and current standings –

    • The entire idea of building this prototype is to provide a reporting solution which has both SALES and IC metrics.

    • The current model doesn’t support continuous reporting from sales to IC, hence to achieve this we want to integrate common dimensions shared across both the pillars i.e. joining both the pillars data using employee resource hierarchy from sales and participant hierarchy from IC. Similar to the partner dimension project.

    • By considering the above assumption we have started building the prototype by using SALES mockup (The one used for all the SALES POCS) and IC mockup(One we built completely new).

    • Sales mockup flow “Campaign-Lead-Opty-Order”, IC mockup flow “Transaction-Credit_earning-Payment”.

    • Joins established to build the prototype are SALES.PartyID = IC.PartyID and SALES.OrderID = IC.TransactionID.

     Pointes to Note:

    • Entire data model and dashboard was completed by considering above joins.

    • Current joining flow is “IC → Resource Hierarchy → SALES”, from the current FA model we were able to identify the joins to link IC facts to resource hierarchy dimensions(PFA FA tables screenshot).

    • Whereas we are encountering trouble finding the joins to resource hierarchy → sales facts. Because in the current model Order details are coming from ORDER management and SALES data ending with Opportunity data.

    • using Order_num from transactions table we can join IC data with Order management data but later from order management to SALES Oppty, we don't have proper joins to establish. 

    • There are no proper joins to get SALES Oppty data and SALES facts to build the solution at RPD level.

    Version:
    Oracle DV Version 12.2.5.0.0-20180829155113
    Image:
    Document:
    CRM SALES and IC linking using common 'Resource+Hierarchy'.doc (863KB)
    Document:
    IC Mockup.xlsx (33KB)
    Document:
    Sales Mockup.xlsx (293KB)
  • Yannik Pietschmann
    SQL statements for Datastores5.0
    Topic posted May 24, 2019 by Yannik Pietschmann, tagged BI Publisher, Opportunity, Reporting and Analytics in Engagement Cloud > Reporting and Analytics for Engagement Cloud public
    Title:
    SQL statements for Datastores
    Summary:
    What are the concrete SQL statements for an Datastore and what kind of joins are used for joining relevant database tables?
    Content:

    Hello,

    we are looking for the concrete SQL statements, which are used for getting the data of an datastore. Especially we need to know how multiple database tables are joined (inner/outer joins?).

    For example the datastore Opportunity (CrmAnalyticsAM.OpportunityAM.Opportunity) uses multiple tables like MOO_OPTY, MOO_REVN, HZ_PARTIES, ... This information can be found in the mapping spreadsheet R13.19A CX OTBI Subject Area Database Mapping.xlsx, which was posted by Ling Xiang (https://cloudcustomerconnect.oracle.com/posts/38c32f731c). What are the concrete SQL statements for getting the data from these tables for the datastore?

    Is there any documentation which provides this kind of information for datastores?

     

    Thanks,

    Yannik

  • Harshavardhan Konathala
    Incentive compensation - Recovery Amounts Dashboard5.0
    Topic posted May 7, 2019 by Harshavardhan KonathalaBlue Ribbon: 750+ Points, tagged Activity, BI Publisher, Business Plan, Chart, Customer, Dashboard, Data Visualization, Forecast, Infolets, Leads, Opportunity, Product, Reporting and Analytics, Resource, Revenue, Sales, Sales Campaign, Sales Executive, Sales Manager, Sales Representative, Shared Reports, Tip, Trends, Users, Year in Sales Cloud Report Sharing Center public
    Title:
    Incentive compensation - Recovery Amounts Dashboard
    Summary:
    19D release feature - Participant Recovery Amounts Dashboard & reports on Performance and Earnings Summary Subject Area
    Content:

    Business Use Case:

    Attainment Ranking & Summary

    Subject Areas used – IC Performance & Earning Summary

    Who can use this report (Roles) - Incentive Compensation Manager/ Analyst/ Sales Representative

    The below dashboard is a sample implementation that shows how the Performance and Earnings Summary Subject area can be used to report metrics related to payment recovery.

    This report is designed for the benefit of an Incentive Compensation Manager and/or an Incentive Compensation Analyst to help them understand the advance amount recovered and also the advance to be recovered based on ITD and PTD calculations so that the payments can be adjusted as required.  Further, the Subject Area has been enhanced to support reporting of comp plan assignment types (Direct/Role-based) and also whether the participant’s incentive has been customized at a comp plan level.

    All these enhancements are available from 19D onwards.

    Quarterly Recovery Amounts by Participant:

    A WAVE view provides info about all the recovery amounts by the participants against the target by Quarter.

    You can move to each quarter by the using slider to move between quarters.

    Period wise Recovery Amounts by Participant:

    A bar chart provides info about all the recovery amounts by the participants against the target by each period. You can move to each quarter by the using slider to move between quarters.

    How to deploy:

    1. Un-archive “Recovery Amounts Dashboard.catalog” files which have all the individual report, dashboard prompt, and the dashboard.
    2. Ensure that you un-archive it under /shared/custom folder to retain the references that the dashboard has to those individual files (else you may correct those again in the dashboard)

    Implementation notes:

    1. The data fetched is for the selected Year and Participant Name from the dashboard prompt.
    2. From R13 onwards, you are allowed to select multiple values to check the results .E.g. You can submit one year and all the period/participant vales to see how the responses compare on different parameters
    Version:
    Oracle Business Intelligence 11.1.1.9.0
    Image:
    Document:
    Recovery Amounts Dashboard.docx (110KB)
    Document:
    Recovery Amounts Dashboard.catalog (4KB)
  • Prakash Velamuri
    Issues with Kanban report55.0
    Topic posted May 6, 2019 by Prakash VelamuriGreen Ribbon: 100+ Points, tagged BI Publisher, Opportunity, Reporting and Analytics, Sales, Sales Representative, Shared Reports in Engagement Cloud > Reporting and Analytics for Engagement Cloud public
    Title:
    Issues with Kanban report
    Summary:
    For some of the Sales users the Kanban report is not displayed corrcetly
    Content:

    Hi,

    We have implemented the Sales rep Kanban report to show the Sales stages for various opportuntines. For some of the users its displayed fine where as for some of the users the top section is missing.

    Do we need any setup for this Kanban report to display correctly

    Thanks,

    Prakash

    Image:
  • Prakash Velamuri
    Issues with Kanban report
    Topic posted May 6, 2019 by Prakash VelamuriGreen Ribbon: 100+ Points, tagged BI Publisher, Opportunity, Reporting and Analytics, Sales, Sales Representative, Shared Reports in Engagement Cloud > Reporting and Analytics for Engagement Cloud public
    Title:
    Issues with Kanban report
    Summary:
    For some of the Sales users the Kanban report is not displayed corrcetly
    Content:

    Hi,

    We have implemented the Sales rep Kanban report to show the Sales stages for various opportuntines. For some of the users its displayed fine where as for some of the users the top section is missing.

    Do we need any setup for this Kanban report to display correctly

    Thanks,

    Prakash

    Image:
  • Nawfal El Hannoun
    Automatical filtering a BI Report to the current Account...Answered95.0
    Topic posted March 18, 2019 by Nawfal El HannounRed Ribbon: 250+ Points, tagged Account, Opportunity, Pipeline, Sales, Sales Manager, Sales Representative, Shared Reports in Engagement Cloud > Reporting and Analytics for Engagement Cloud public
    Title:
    Automatical filtering a BI Report to the current Account selected
    Summary:
    Automatical filtering a BI Report to the current Account selected
    Content:

    Hi Community, 

    i need your help please on a BI-Reporting subject within Sales Cloud R13-18C.

    Here is the scenario: i created a report based on Accounts and their opportunities, which are showing basically 2 axes (X= Opportunity Status which may be Lost, Won or Open), then Y= Expected revenue, which is the expected volume by Opportunity)

    The report will be embedded as Mqshup content link within a sub-Tab in the Accounts (means within an account, you have several sub-Tabs, like contacts, opportunities, relationships etc...and i added another sub-tab called analysis, which will open this BI report.

    All this works perfectly fine, but the rerport shows all volumes bases on the 3 statuses (open/lost/won) and  is not filtered on the current account. Can somebody please help on how to go about it? 

    I need the report to trim values and show only those related to opportunities of the current account, where i've the sub-tab opened. When i select another one, it should be showing values of the second one..etc.

    Manny thanks in advance and best regards

    Nawfal El Hannoun

     

    Version:
    Sales Cloud R13- 18C
  • Madhav Reddy
    Pipeline Team Dashboard5.0
    Topic posted February 6, 2019 by Madhav ReddyBlue Ribbon: 750+ Points, tagged Opportunity, Pipeline, Reporting and Analytics, Resource, Revenue, Sales, Sales Executive, Sales Manager, Sales Representative in Sales Cloud Report Sharing Center public
    Title:
    Pipeline Team Dashboard
    Summary:
    Monitor how my team is/was participating in closing deals,Status of opportunity, Channel contribution to closed deals by choosing year, quarter.
    Content:

    Business Use Case:

    Sales Manager:

    Sales Manager needs to view

    1. How my team is/was participating in closing deals.
    2. Status of opportunity. Channel contribution to closed deals by choosing year, quarter.
    3. How my people are/were distributed among the opportunity teams,  the roles they are/were playing and the deals they are/were working on.

     owned by him or by his subordinates for selected time period.

    Sales Representative:

    Sales Representative needs to view

    1. How my team is/was participating in closing deals.
    2. Status of opportunity. Channel contribution to closed deals by choosing year, quarter.
    3. How my people are/were distributed among the opportunity teams,  the roles they are/were playing and the deals they are/were working on.

     owned by him for selected time period.

    How to deploy:

    1. Un-archive “Pipeline Team Dashboard. Catalog” file which has all the individual report, dashboard prompt and the dashboard.
    2. Ensure that you un-archive it under /shared/custom folder to retain the references that the dashboard has to those individual files (else you may correct those again in the dashboard)
    3. Pipeline Team Dashboard comprises 4 sub reports :
      1. Pipeline Team Report
      2. Opportunity Revenue Vs Function Name
      3. Pipeline Team Report summary
      4. Pipeline Opportunity Revenue by Function Name

    Implementation notes:

    Shows the data for Pipeline Team Dashboard with details in tabular form and also the graphs that give Status of opportunity, Channel contribution to closed deals that are owned by the logged in user or the subordinates reporting into him.

    Note : Use Time Dimension filters as necessary

     

    Image:
    Document:
    Pipeline Team Dashboard.catalog (4KB)
  • Madhav Reddy
    Lead - Opportunity Conversion Report5.0
    Topic posted January 30, 2019 by Madhav ReddyBlue Ribbon: 750+ Points, tagged Leads, Opportunity, Sales, Sales Executive, Sales Manager, Sales Representative, Tip in Sales Cloud Report Sharing Center public
    Title:
    Lead - Opportunity Conversion Report
    Summary:
    Monitor how leads are being converted to the opportunities and whether Contributions of lead to opportunity are evenly allocated among the leads.
    Content:

    Business Use Case:

    Sales Manager :

    Sales Manager needs to view

    1. Monitor how leads are being converted to the opportunities.
    2. Contributions of lead to opportunity are evenly allocated among the leads.
    3. Channel contribution to closed deals.

     owned by him or by his subordinates for selected time period.

    Sales Representative :

    Sales Representative needs to view

    1. Monitor how leads are being converted to the opportunities.
    2. Contributions of lead to opportunity are evenly allocated among the leads.
    3. Channel contribution to closed deals.

     owned by him for selected time period.

    How to deploy:

    1. Un-archive “Lead - Opportunity Conversion Report. Catalog” file which has all the individual report, dashboard prompt and the dashboard.
    2. Ensure that you un-archive it under /shared/custom folder to retain the references that the dashboard has to those individual files (else you may correct those again in the dashboard)
    3. Lead - Opportunity Conversion Report comprises 3 sub reports :
      1. Detail Lead – Opportunity conversion report showing Allocation%, weighted amount Vs Opportunity revenue.
      2. Graph view of weighted amount across the channels.
      3. Summary report showing weighted amount with Allocation%.

    Implementation notes:

    1. Shows the data for Lead - Opportunity Conversion Report with details in tabular form and also the graphs that give my overview about the conversion, such as the channel contribution to closed deals that are owned by the logged in user or the subordinates reporting into him

      Note : Use Time Dimension filters as necessary

    Image:
    Document:
    Lead - Opportunity Conversion Report.catalog (3KB)
  • Stephanie Gott
    Formulas aren't working25.0
    Topic posted January 14, 2019 by Stephanie GottSilver Medal: 2,000+ Points, tagged Activity, Opportunity, Pipeline, Reporting and Analytics, Sales in Engagement Cloud > Reporting and Analytics for Engagement Cloud public
    Title:
    Formulas aren't working
    Summary:
    Trying to do math to create a custom metric and formulas aren't working.
    Content:

    Hello,

    I'm trying to calculate the percentage of future dated activities on an opportunity. First, I need to calculate that using this: CASE WHEN "Activity"."Due Date"> Current_Date then COUNT(DISTINCT "Sales - CRM Pipeline"."Pipeline Facts"."# of Opportunities") else 0 end. This formula works and returns results.

    Then, I'm doing this: 100*SUM(CASE WHEN "Activity"."Due Date"> Current_Date then COUNT(DISTINCT "Sales - CRM Pipeline"."Pipeline Facts"."# of Opportunities") else 0 end)/SUM("Sales - CRM Pipeline"."Pipeline Facts"."# of Open Opportunities"). This is not erroring out but it's also returning '0' as the results. 

    I've attached screen shots where I would expect to see a result of 9%. Any thoughts? I'm really hoping someone out here familiar with the OTBI for Sales Cloud has some information. 

    Thanks,

    Stephanie

     

    Version:
    11.1.1.9.0
    Document:
    Future Dated Activities.docx (237KB)
  • Stephanie Gott
    Showing null values as "Blank"25.0
    Topic posted November 2, 2018 by Stephanie GottSilver Medal: 2,000+ Points, tagged Opportunity, Reporting and Analytics in Engagement Cloud > Reporting and Analytics for Engagement Cloud public
    Title:
    Showing null values as "Blank"
    Summary:
    How do I show null values with the word "Blank" or "NA" or something similar?
    Content:

    Hello,

    In the Product Dimension for 'Product Name', we have some blanks due to poor process. We are trying to improve the process of always adding a product to an opportunity. To help us do that, we are using Analytics. I created a pivot table showing all of the opportunities we have open by Product Name. We have some that don't have a product so I want to show the word "Blank" or "NA". I'm using this case statement and it's not working. Any thoughts? 

    CASE WHEN "Product"."Product Name" is null THEN 'Blank' ELSE "Product"."Product Name" END

    Thanks,

    Stephanie

    Version:
    11.1.1.9.0

Change View

Sort

community forum