Posts

Posts

  • Yannik Pietschmann
    SQL statements for Datastores15.0
    Topic posted May 24, 2019 by Yannik Pietschmann, tagged BI Publisher, Opportunity, Reporting and Analytics in Sales > Reporting and Analytics for Sales 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

  • Madhav Reddy
    Top and Bottom 5 Reports5.0
    Topic posted November 1, 2017 by Madhav ReddyBlue Ribbon: 750+ Points, tagged Account, Assets, Channel Account Manager, Channel Sales Manager, Customer, Deals, Leads, MDF, Opportunity, Partner, Pipeline, Product, Revenue, Sales, Sales Executive, Sales Manager, Shared Reports, Territory in Sales Cloud Report Sharing Center public
    Title:
    Top and Bottom 5 Reports
    Summary:
    Top and Bottom 5 Reports for various sales metrics
    Content:

    Business Use Case:

    Sales Manager ->

    Sales Manager needs to view the Top and Bottom 5 data related to my Sales Representatives on various metrics, Products, Accounts,  Territories etc.

    Below are the reports included here:

    1. Top and Bottom 5 Accounts by Closed Revenue
    2. Top and Bottom 5 Accounts by Open Revenue
    3. Top and Bottom 5 Deal Registrations by Deal Size
    4. Top and Bottom 5 Lead Sources
    5. Top and Bottom 5 Open Opportunities by Revenue
    6. Top and Bottom 5 Partners by Closed Revenue
    7. Top and Bottom 5 Partners by Open Revenue
    8. Top and Bottom 5 Partners by Deals Submitted
    9. Top and Bottom 5 Partners by MDF Active Budget
    10. Top and Bottom 5 Partners by MDF Claims pending approval
    11. Top and Botom 5 Parnteres by MDF Requests pending approval
    12. Top and Bottom 5 Products by Asset Count
    13. Top and Bottom 5 Products by Closed Revenue
    14. Top and Bottom 5 Sales Reprensetative by Average Deal Size
    15. Top and Bottom 5 Sales Reprensetative by Closed Revenue
    16. Top and Bottom 5 Sales Reprensetative by Open Revenue
    17. Top and Bottom 5 Sales Reprensetative by Closed Opportunity Count
    18. Top and Bottom 5 Sales Reprensetative by Open Opportunity Count
    19. Top and Bottom 5 Sales Reprensetative by no. of Activities
    20. Top and Bottom 5 Sales Reprensetative by Sales Cycle
    21. Top and Bottom 5 Sales Reprensetative by Win Rate
    22. Top and Bototm 5 Territories by Closed Revenue
    23. Top and Bottom 5 Territories by Open Revenue

    How to deploy:

    1. Unarchive “Top and Bottom 5 Reports.catalog” file which has all the individual reports and the dashboard created using most of these reports.
    2. Ensure that you unarchive 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. If you plan to use few of the reports directly, please delete other reports/dashboards to avoid confusion.

    Implementation notes:

    1. The data fetched is for the Current Enterprise Quarter. Please change the time value per your needs.
    2. The Resource Org hierarchy based login filter is used which shows the data for the current logged in user and the subordinates .
    Image: