Key Performance Indicator Report - Counting hidden invoices?

Submitted by mckelvey on Fri, 12/07/2019 - 04:59

General developer's discussion

Greetings -

We're in the beginning stages of a practice valuation and have been making great use of the masterful Key Performance Indicators report. I am looking for a number, however, that from my reading of the MySQL query, is not filtering as I believe it should.

Invoice Count seems to be ignoring the 'hidden' flag. While this might have been a design decision, I'm thinking that it would be more beneficial to have Invoice Count reflect the number of 'non-hidden' invoices only, and Average Invoice = Total Billed / 'non-hidden' Invoice Count.

As I read it, the reported Invoice Count is going up as corrections are made, credits are issued, etc. Probably best to report what the final invoice count is after the dust has settled.

I do like the idea of reporting Total Credits, as with the few returns we actually get, it is a good indicator to us as to how many billing errors are being made and corrected. But without checking the hidden flag, I don't think we're getting a useful look into the number of invoices that in the final analysis, are actually being generated... a useful indication as to the state of the business for sure.

I have included the query snippet that I believe is relevant. My SQL skills are not really up to snuff and so how to modify the query to exclude those invoices with the 'hidden' flag is rather lost on me.

If anyone thinks that I'm making a valid point, could someone please look at the code and suggest a fix?

Thanks,

Sam

Kamloops, Canada

----------------------------------------------------------------

<![CDATA[select
    (1+datediff($P{DateTo}, $P{DateFrom})) as days,
    sum(if(((a.arch_short_name in('act.customerAccountChargesInvoice','act.customerAccountChargesCounter')) and (cu.arch_short_name='party.customerperson')),if(fa.credit,-(fa.total-fa.tax_amount),(fa.total-fa.tax_amount)),0)) as invoiceTot,
    sum(if(((a.arch_short_name in('act.customerAccountChargesInvoice','act.customerAccountChargesCounter')) and
   (cu.arch_short_name='party.customerperson')),1,0)) as invoiceCount,
    sum(if(a.arch_short_name = 'act.customerAccountChargesCredit',if(fa.credit,(fa.total-fa.tax_amount),-(fa.total-fa.tax_amount)),0)) as creditTot,
    sum(if(((a.arch_short_name = 'act.customerAccountChargesCounter') and (cu.arch_short_name='party.organisationOTC')),if(fa.credit,-(fa.total-fa.tax_amount),(fa.total-fa.tax_amount)),0)) as OtcTot
from acts a
join financial_acts fa on fa.financial_act_id = a.act_id
left join participations pl on pl.act_id = a.act_id and pl.arch_short_name = 'participation.location'
left join entities loc on loc.entity_id = pl.entity_id
left join participations pc on pc.act_id = a.act_id and pc.arch_short_name = 'participation.customer'
left join entities cu on cu.entity_id = pc.entity_id
where a.arch_short_name in('act.customerAccountPayment','act.customerAccountRefund',
                       'act.customerAccountPaymentDiscount','act.customerAccountRefundDiscount',
                        'act.customerAccountChargesInvoice','act.customerAccountChargesCredit','act.customerAccountChargesCounter',
                        'act.customerAccountBadDebt','act.customerAccountCreditAdjust','act.customerAccountDebitAdjust')
and (a.activity_start_time >= date_sub( $P{DateFrom}, INTERVAL ($P{Set}* $P{MonthsPrior}) MONTH)
      and a.activity_start_time <= date_add(date_sub( $P{DateTo}, INTERVAL ($P{Set}* $P{MonthsPrior}) MONTH), INTERVAL "23:59:59" HOUR_SECOND))
and a.status='POSTED'
and fa.total != 0
and ifnull(loc.name,"--NONE--") like concat(ifnull($P{Location},""),"%")]]>

Re: Key Performance Indicator Report - Counting hidden invoices?

Submitted by tanderson on Tue, 16/07/2019 - 10:16.

Before the where clause, try adding:

    left join act_details hide
        on hide.act_id = a.act_id
            and hide.name = 'hide'

After:

and fa.total != 0

Add:

and (hide.value is null or hide.value = 'false')

Re: Key Performance Indicator Report - Counting hidden invoices?

Submitted by mckelvey on Wed, 17/07/2019 - 00:14.

Hi Tim,

Thanks for that. I had been trying with the AND clause but missed the need for the LEFT JOIN completely.

I have run the modified report and like it very much. The change affects five numbers: Total Invoices, Invoice Count, Average Invoice, Total Credits and Total OTC. I believe that these better reflect the actual state of the business. I did like seeing the old Total Credits number however, as it somewhat reflected the number of errors being made in billing, and so will probably run both versions for different purposes.

I'm wondering, however, if a similar change should not be made to the following payments section. I assume that those numbers (payments, refunds, etc.) are also dependent on the hidden flag. I've tried just adding your changes to that query as well, but the report chokes.

I'm sorry I did not catch this when Tim G and I were originally working on the report. I couldn't help with the code, but he would send me each version, I would apply a smell test and check for internal consistency. I think he would have seen the wisdom of not including the hidden transactions in this financial report.

If you don't mind, could you take a look at the payment query and suggest a mod for that as well. I would be happy to post it to the user catalog if you don't think that the changes warrant an official update to the report. Alternatively, an official update could include an "Include hidden transactions" parameter checkbox... that would be most useful!

Thanks again for getting back on this. It's really helpful.

Sam

Re: Key Performance Indicator Report - Counting hidden invoices?

Submitted by tanderson on Tue, 30/07/2019 - 13:10.

The "left join act_details hide" should already take care of the payments, as the invoices and payments are all being returned by the same query.

Re: Key Performance Indicator Report - Counting hidden invoices?

Submitted by mckelvey on Wed, 31/07/2019 - 00:20.

Really? OK, I'll look again as I thought that Tim had broken the report into tables with a separate query for each. That's why I was looking for an equivalent clause to apply to the payments section. OK... I'll look again at the organization of that report.

Thanks,

Sam

Re: Key Performance Indicator Report - Counting hidden invoices?

Submitted by mckelvey on Wed, 31/07/2019 - 01:42.

The second query below the one that I've already applied the tweak to is the one that addresses the Payment/Refunds, etc. I played around with it again applying the same tweaks as you suggested above and finally got it working. I'm assuming that my earlier attempts failed because I had put the 'join' in a subquery instead of at the end. A snippet showing the end of the working one, with the additional lines in bold is as follows:

--------------

from acts a
join financial_acts fa on fa.financial_act_id = a.act_id
left join participations pl on pl.act_id = a.act_id and pl.arch_short_name = 'participation.location'
left join entities loc on loc.entity_id = pl.entity_id
left join participations pc on pc.act_id = a.act_id and pc.arch_short_name = 'participation.customer'
left join entities cu on cu.entity_id = pc.entity_id
left join act_details hide on hide.act_id = a.act_id and hide.name = 'hide'
where a.arch_short_name in('act.customerAccountPayment','act.customerAccountRefund',
                        'act.customerAccountChargesInvoice','act.customerAccountChargesCredit','act.customerAccountChargesCounter',
                        'act.customerAccountBadDebt','act.customerAccountCreditAdjust','act.customerAccountDebitAdjust')
and (a.activity_start_time >= date_sub( $P{DateFrom}, INTERVAL ($P{Set}* $P{MonthsPrior}) MONTH)
      and a.activity_start_time <= date_add(date_sub( $P{DateTo}, INTERVAL ($P{Set}* $P{MonthsPrior}) MONTH), INTERVAL "23:59:59" HOUR_SECOND))
and a.status='POSTED'
and fa.total != 0
and (hide.value is null or hide.value = 'false')
and ifnull(loc.name,"--NONE--") like concat(ifnull($P{Location},""),"%")

----------------------

It now shows 'non-hidden' value changes from real practice data, taken over 4 periods, in the following:

Total Invoices, Invoice Count, Average Invoice, Total Credits, Total OTC, Item Discount Rate, Total Payments, Total Refunds. We also get a minor change in Total Income, but I think that may be a late issue on our part that spanned over the end of the fiscal year. I wouldn't normally expect a change in that number.

I'll post the modified version to the User Catalog. I think it is useful for getting to the final numbers, but there is also a real value in seeing the 'hidden' values as well as they indicate the level of correcting being done.

Thanks for your help and guidance on this.

Sam