Reporting and Analytics for Sales

Get Involved. Join the Conversation.

Topic

    Nawfal El Hannoun
    Automatical filtering a BI Report to the current Account...Answered
    Topic posted March 18, 2019 by Nawfal El HannounRed Ribbon: 250+ Points, tagged Account, Opportunity, Pipeline, Sales, Sales Manager, Sales Representative, Shared Reports 
    248 Views, 9 Comments
    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

    Best Comment

    Rupak Chowdhury

    You can set the Parameter for a Mashup region / subtab of type parameter as well but that method is different than how you do it for the Analytics subtab.

    For Mashup you can see examples here - https://docs.oracle.com/en/cloud/saas/sales/18c/oacex/extending-simplified-pages.html#OACEX2858812

    Following is another example where you can create a Mashup of type Groovy and pass contextual filters as parameters to analytics report.

    Assume you have a report which has "Account Id" & "BU Id" as "Is Prompted" filter.
    You want to embed the report to a Mashup region of type Groovy under Account object and pass the in context Account Id and logged in user's BU Id.

    //===Groovy sample===//

    def vAccountId = getAttribute('PartyId')
    def vUserPrBuId = oracle.apps.cdm.foundation.publicModel.common.util.HzSessionUtil.getUserPrimaryBU()

    def vUrl = 'https://YOUR_POD_URL/analytics/saw.dll?Go&Path=<OTBI_ANALYSIS_FULL_PATH_ENCODED>&Action=Navigate&col1=%22Dim1%22.%22Account%20Id%22&val1='+vAccountId+'&op1=eq&col2=%22Dim2%22.%22BU%20Id%22&val2='+vUserPrBuId+'&op2=eq'

    return vUrl

    //==**Please note that this is a simple example just to show the concept==

    Comment

     

    • Reghu

      Dear Nawfal,

      You will have to create a prompt in your analysis may be account id or something  and pass a value to that parameter from your Account page to the subtab, with a session scope variable.

      #{sessionScope.zcmAnalyticsCustomerId}

      • Nawfal El Hannoun

        Hi Reghu, 

        thanks for your answer. I've read the same somewhere else and tried to do it as you said, but failed with an Error 400 :Bad Request.

        Do you have any step by step Tuto for this? I am really lost without help! :-)

        Thanks a lot!

        Nawfal

        • Rupak Chowdhury

          This may be helpful

          https://docs.oracle.com/en/cloud/saas/engagement/19a/facaa/analytics-on-user-interfaces.html#FACAA2354846

          Regards-
          Rupak

          • Nawfal El Hannoun

            Hi Rupak, thanks for the Documentation.

            I used it also to follow this solution, but i might be really dumb like a nut in this case. I still can't get the report filtered on the record Id of the current account.

            Here is what i did:

            1) created a report in BI, basically a table, where i am listing the Account record Id, account Name, Parnership level, Industry, Sales Region.

            2)I clicked on the Record-ID column ==>Filter==>[Column:Record-ID /Operator: Is equal to /Value=(left empty)/More Options:Sessions Variable =#{sessionScope.zcmAnalyticsCustomerId} ==> then confirmed with OK and saved the report. Navigated to the tab more and opened the link above the SQL statements which opened a new browser tab showing the report results. I copied the link from the browser navigation bar.

            [ I guess here is my mistaken point: wher to put #{sessionScope.zcmAnalyticsCustomerId} ]

            3) In Application Composer: i created a Mashup content:wher i pasted the URL copied as URL-defintion, set to active, and added a parameter i called CustomerId, then saved it.

            4) In Application Composer, i navigated to Standard Objects==>Account, Pages, and opened the Detail Layout, added a sub view called "my report", selected the type "Mashup content", selected the right report as mashup link, and matched the prompt criteria with the right field within Account=Account ID from dropdown list. Saved and done.

            Now when i open Accounts and navigate to my new sub tab "my report", the report is displayed but still showing the table with all accounts and not filtered on the currently opened account.

            Please help! i am really stuck with it and feeld beeing rotating very close to the right solution without a positive result.

            Thanks a million in advance!

            Nawfal

             

            • Rupak Chowdhury

              As described in the following link 

              https://docs.oracle.com/en/cloud/saas/engagement/19a/facaa/analytics-on-user-interfaces.html#FACAA2354846

              You need to enable Analytics sub tab in App Composer and then mention the parameter #{sessionScope.zcmAnalyticsCustomerId} as shown in the screenshot in the document link.

              in OTBI - the analysis should be saved with the column as "Is Prompted" filter, once you embed the analysis in the analytics tab the Is prompted filter column will be automatically available for you to set the parameter.

    • Reghu

      Thanks Rupak. I was about the send the same :)

    • Nawfal El Hannoun

      Thanks a lot Rupak and Reghu, 

      it finally worked!:-)

      The issue was, that we were doing it all right, but our implementation partner had set in the past a wrong field for the Customer-Row-ID in the system, this way every filter was just throwing an error...we managed to find the right field and now the filter is working like a charm!!!!:-)

      But i am still curious about how to do it without using an analytics sub tab, as in a normal sub tab, you can't add parameters as described in the tutorial.??

      Thank you so much...

      Best regards

      Nawfal

      • Rupak Chowdhury

        You can set the Parameter for a Mashup region / subtab of type parameter as well but that method is different than how you do it for the Analytics subtab.

        For Mashup you can see examples here - https://docs.oracle.com/en/cloud/saas/sales/18c/oacex/extending-simplified-pages.html#OACEX2858812

        Following is another example where you can create a Mashup of type Groovy and pass contextual filters as parameters to analytics report.

        Assume you have a report which has "Account Id" & "BU Id" as "Is Prompted" filter.
        You want to embed the report to a Mashup region of type Groovy under Account object and pass the in context Account Id and logged in user's BU Id.

        //===Groovy sample===//

        def vAccountId = getAttribute('PartyId')
        def vUserPrBuId = oracle.apps.cdm.foundation.publicModel.common.util.HzSessionUtil.getUserPrimaryBU()

        def vUrl = 'https://YOUR_POD_URL/analytics/saw.dll?Go&Path=<OTBI_ANALYSIS_FULL_PATH_ENCODED>&Action=Navigate&col1=%22Dim1%22.%22Account%20Id%22&val1='+vAccountId+'&op1=eq&col2=%22Dim2%22.%22BU%20Id%22&val2='+vUserPrBuId+'&op2=eq'

        return vUrl

        //==**Please note that this is a simple example just to show the concept==