C4C report accounts without sales orders

[former_member459106]

Hi Colleagues,
I'm trying to create a dashboard to check in a particular period, the rate of customers that has sales orders from the total of my customers.

For instance if I have 100 customers and last quarter I created sales orders for 40 of them, my dashboard should display 40%.

I'm in trouble to find a way to create a report to count number of customers that had sales.

Any tips?

Regards,

Renato

[bnienhaus]

Hi Renato,

This was a really interesting (and much more difficult than expected) question! However, I think I figured it out.

As a side note, all examples below are with Opportunities because I have better data in our test tenant, but the same principles should apply.

Requirements and Implications

So, let me review your requirements first:

• Since we must capture all accounts regardless of sales activity, your anchor Data Source must be account-related, for example Account Details.
• Then, we must perform a Left Outer Join (LOJ) to the sales object (in my example Opportunities)

These facts produce the following situation:

• Each Account will appear in the resulting Data Source at least one time - once if no Opportunities exist (null Opportunity ID) and multiple times if at least one Opportunity is created. We must keep this in mind because the join condition from Account --> Opportunity will increment the Key Figure (KF) Counter each time a join occurs. So, for an Account with 3 Opportunities, Counter = 4 (3 Opportunities + null value)
• Therefore we must eventually deal with the duplicated count of Accounts

KF Creation

• Number of Accounts: I created a Restricted KF on Counter with Fixed Value Selection of Account ID !(#). Because of the duplication issue, on the Properties tab > Local Calculation section, I calculate the result as "Counting All Detailed Values That Are Not Error..." This means that I cannot rely on the row data of the report (because it includes duplication) but the result across all Accounts will be correct as duplicates are only counted as a unique occurrence.
• Number of Opportunities: Another Restricted KF based on Counter. Fixed Value Selections of Opportunity ID !(#) and Close Date in the date range you wish. IMPORTANT: You should not use the report selection to select a date range as I believe this will create problems with the Restricted KFs. Better to include a Fixed Value Selection.
• Has Sales: Calculated KF with formula [Number of Opportunities] GT 0 with the same "Calculate Result As" value and explanation as the "Number of Accounts" KF
• Ratio: Calculated KF with formula SUMCT([Has Sales]) %A SUMCT([Number of Accounts]). Remember, we cannot rely on the "row by row" values as they include duplicates. This KF tells C4C to only take the rolled-up result values - which are counted instead of summed because of the local calculation behavior set on "Number of Accounts" and "Has Sales"

Last Consideration

• It's important to note that since we're performing local calculations on some fields and not others that viewing the data with characteristics included or at the "row by row level" will make certain KFs look "invalid/incorrect" - so such a report is designed very specifically to produce the calculation you requested, ignoring all other "interim" data.
• Therefore, your report view should have no row data and the only column data should be the "Ratio" KF
• If you wish to have detail behind the tile, you will need to construct a second report view that can more appropriately display the detailed data.

I will leave this test report on my test tenant for a few days in case you have follow-up questions. Good luck!

Best Regards,

Brian