Sales Graphs - Use for GL Budget Data?

[VortecCPI]

SalesGraph.php

I realize this report uses data from salesanalysis table but could it not also have a choice to use data from chartdetails table if budget values exist?

Of course this would only be applicable if all dropdowns were set to default of "All".

Just a thought...

[VortecCPI]

Below is my first crack but we would need to alias/select ChartDetails.AccountCode in case the user changed it from the default of 1460...

And... I guess "AND" should really be "&&"...

My thought on this whole thing is if a user is unfamiliar with loading up SalesAnalysis table with data this approach would be easier to use...

And... After some thought would this graph not be useful for ALL GL Accounts with associated budget value data?

So... Copy this script, remove all sales-related choices, and produce the same sort of graph...

Perhaps a hyperlink from other GL-related scrips (Show Graph)?

PHP Code:

    if ($_POST['SalesArea']=='All' 
        AND $_POST['CategoryID']=='All' 
        AND $_POST['SalesmanCode']=='All' 
        AND $_POST['GraphOn']=='All' 
        AND $_POST['GraphValue']=='Net') {

        $SQL = "SELECT salesanalysis.periodno, 
                periods.lastdate_in_period, 
                chartdetails.budget AS budget, 
                SUM(CASE WHEN budgetoractual=1 THEN amt - disc ELSE 0 END) AS sales
        FROM salesanalysis 
        INNER JOIN periods ON salesanalysis.periodno=periods.periodno 
        INNER JOIN chartdetails ON salesanalysis.periodno=chartdetails.period " . $WhereClause . "
        AND chartdetails.accountcode = '1460' 
        GROUP BY salesanalysis.periodno, 
            periods.lastdate_in_period 
        ORDER BY salesanalysis.periodno";
    } 


[phil]

I think the difficulty here would be in matching the salesanalysis sales to GL accounts - as the GL account a sale is posted to depends on the salesarea, salestype and stock category of the item sold as per includes/GetSalesTransGLCodes.inc - it could be done I suspect but might be slow. Also, if the posting schema of sales changed then there will be a mismatch between salesanalysis and GL postings

[VortecCPI]

I agree there exists the potential for many issues, hence a simple graphing utility script for any GL Account instead.

I am working on it now...

---

Right now I am thinking something like this:

   

---

I have to pack it up for now but here is where I am at... On vacation with family...

[VortecCPI]

A bit more work on this...

[TimSchofield]

A bit more work on this...

Hi Paul,

A couple of observations, I hope you don't mind my interfering

1 I have removed some obsolete code, and tidied up some variable names etc, and re-formatted the code as per the guidelines, and attach the result
2 You have hard coded some account group names (Liabilities and Revenue) but we cannot assume that groups with these names exist, they are in English for a start, but also many (the majority maybe?) countries do not allow free form charts of account as the UK ans USA do, and have them defined by the government (see the PCG that the French use as an example - http://www.plancomptable.com/titre-IV/li...tes_sd.htm)
I don't have a solution to this yet, just the problem (maybe after some sleep I will!!)

Enjoy your vacation and don't tell your family I distracted you
Tim


  GLAccountGraph.php (Size: 9.16 KB / Downloads: 2)

[VortecCPI]

Tim,

Thank you so much for helping me out on this. I was really just sort of hacking away hoping for some insight from others like you.

In my opinion this little graphing feature will be quite helpful and useful to our needs so I really appreciate your input.

I did not know what to do with Liabilities and Revenue groups either so I just hard-coded it for now.

i hope you can come up with something to make it adjust accordingly...

Paul

[TimSchofield]

Paul,

Thinking about this, any sales postings (including discount) created by the system would have an entry in the salesglpostings table for the account. This wouldn't guarantee catching all revenue but would certainly catch most.

As for liabilities we already know the purchase ledger control account number, and the GRN clearing account number, and that is the most likely to be graphed. You would perhaps hope that anybody wishing to graph any of the other liabilities accounts would understand why they were negative, but I will think some more.

Tim

[VortecCPI]

Tim,

I agree that a true accountant should/would realize the number being negative is appropriate but when it comes to budgets and tracking we almost always see positive numbers in documentation and presentations.

Perhaps we are over thinking this and all we need is a "flip axis" or "reverse axis" checkbox that allows the user to change the graph at will?

In this way we have flexibility to cover all bases.

Paul

---

It would also be nice to have a "Select a Different Period" button like we have on other GL-related reports.
   

[TimSchofield]

Hi Paul, I have (eventually) got back to this. I have added an "Invert Graph" checkbox, and also a "Select Different Criteria" link on the graph, as per your suggestions. Let me know what you think,

Tim

Attachment has been removed as their is a later version posted later in this thread