locked-how to generate daily,monthly,yearly sales report?

[rongame]

hi guys
any tutorials or how to's on generating daily monthly and yearly reports?
an example is to generate a sales report per month, per annum and so on...
thanks

 

[rongame]

hi

any news on this? just basic reporting on data gathered...

 

[cheesegrits]

Moving to pro support forum, I'll try and get an answer for you today.

-- hugh

 

[cheesegrits]

Typically, things like daily / monthly / annual reports need to be created with MySQL views, which requires building a standard MySQL SELECT query which does the grouping and summing, and introducing that view to Fabrik as a List.

Can you describe your main data setup? For instance, is it a single table, where each row has something like a SKU, price and number of units sold?

Do you have any experience with setting up MySQL views?

-- hugh

 

[rongame]

hi hugh

at the moment its only one table. and what i have is the date, buyer name, amount of purchase, product classification,location and commission. all of this in one table.

example how many sales for the month of june. average, sum, mean, median and mode.
how many bought a tv or refrigerator or aircon. this is for the classification.

i have no experience with sql view setup

thanks

 

[rob]

If you have access to Phpmyadmin they are fairly easy to set up. This mySQL page is usful for all agregate methods http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
So say want the average and sum per month, your query is something like:

SELECT DATE_FORMAT(date_time, '%Y-%m'), AVG(amount) AS `average` SUM(amount) AS `sum` FROM sales GROUP
BY DATE_FORMAT(date_time, '%Y-%m')

Once you have replaced the field and table names with your own table and fields, then you can run that in phpmyadmin, and there is then a create view button which you can use to create the view.
Its the available in Fabrik to create a list from.

If you wanted to group by year it would be :

SELECT DATE_FORMAT(date_time, '%Y'), AVG(amount) AS `average` SUM(amount) AS `sum` FROM sales GROUP
BY DATE_FORMAT(date_time, '%Y')

 

[rongame]

hi rob
i have access in the phpmyadmin. really have no idea what to do.
do you have any step by step tutorial?
also which ones are full element name and simply the element name?
thanks

 

[cheesegrits]

"Full" element names are only relevant inside Fabrik itself, where we create a unique "full element name" by using the table and field name joined by three underscores, like tablename___fieldname. The 'fieldname' is the short element name you give the element, which we use as the field name in the underlying MySQL table.

So in any actual MySQL query, you only ever use the short element name, which is actually the field name for that element in your table.

Have you tried executing any of those queries Rob gave you as examples?

-- hugh

 

[rob]

No sorry no step by step tutorial, but what I explained is really step by step as far as the query goes, its kind of out of scope to do a broader tutorial on using phpmyadmin, there should be enough resources on the net to answer any question you had about it.

As Hugh said, did you get the first query to run in phpMyAdmin?

 

[rongame]

hi guys.

its working now. i already created a new list in fabrik to display the results for sales but what if i want to count the no of aircons sold? or no of ref sold?

next is how to use visualizations to display graphs and charts.

thanks so much again

 

[rob]

Hi You'd use the COUNT() method .... so monthly units sold:

SELECT DATE_FORMAT(date_time, '%Y-%m'), COUNT(amount) AS `items_sold` FROM sales GROUP
BY DATE_FORMAT(date_time, '%Y-%m')

 

[skyrun]

I have this requirement and create and use reports and charts extensively in my fabrik app.

phpMyAdmin and SQL queries as suggested are a great start and meet requirements of most one-off or low volume admin-created reports.

Since your requirements seem simple, you could also show your one table/list through a list and add filters and SUMs and COUNTs to the elements and then Fabrik will show you the results in a list/table much like a report but without any coding.

But i needed something users could use to generate their own reports and charts/graphs and customize them and print and/or email them and needed to create very complex reports (like owner statements and cleaning work orders) based on data from many tables.

After trying many ways. Here is the overall approach i use (warning this approach assumes you can do php coding...):

• I create a new list with a database table which creates a form. This form will take the user input for creating the form and can be displayed on a user menu.
• This by default will add rows to the database each time the report is run. If that's what you want. you're done. you can optionally not connect to a database (to not connect the form to a database because you don't care to save what people have typed) or can change the menu item to load the first record every time, which is like saving what the user types and displaying it next time they run the report (by showing the form). This last one is what i do by default... so the database attached to the list always has just one row which is what the user used last time.
• I use form submission plugin(s) to run a php script 'onBeforeLoad' (for pre-filling form items if applicable) or 'onAfterProcess' (to execute the report).
• Then the php that onAfterProcess calls uses the form input and generates the report. QED.

To generate the report:

• I use std php SQL to run the query(ies) and therefor am able to perform quite complex manipulation of the data, then i create HTML for the report output which i send to mPDF (http://mpdf1.com/manual/) which is a FANTASTIC pdf creation tool.
• Then the report is formatted and created and launched as .pdf in the browser. the user can look at it, zoom it, print it, email it, save it... whatever they like. then press 'back' to go back to the form to change it and run it again.

I also have a 'custom reports' form that just has a name (for the title) and a place for SQL. then when run with the following example script as a submission plugin onAfterProcess, it creates a nicely formatted rendering of the results of the SQL query.
I have these forms as rows in a fabrik list that users can run and i can add new custom reports to.
This example can also serve as a prototype for a non-custom method above where the php takes input parameters (like a date range or other info like if you want to count or sum the number of aircons sold in your example).

<?php
$db =& JFactory::getDBO();
 
// replace these 2 items with your info to be displayed on the header left and right
$headingleft = 'Your Company Name Here';
$logo = 'path/logoimagefile.jpg';
 
//set variables from input form.  replace yourform obviously and make varable names match element names
$title = $_REQUEST[yourform___title];
$query = $_REQUEST[yourform___sql_statement];
 
// setup mpdf
include("./MPDF54/mpdf.php");
 
$mpdf=new mPDF('c','Letter',0,'sans-serif',10,10,25,16,7,9);
//error_reporting(0);  // says could speed up... doesn't seem to
$mpdf->useSubstitutions=false;
$mpdf->simpleTables = true;
$html = '<html><body>';  // get the html string started...
 
$footer = '
<table width="100%" style="border-top: 1px solid #000000; vertical-align: bottom; font-size: 8pt; color: #000000; font-weight: bold; font-style: italic;">
<tr>
<td width="33%"><span style="font-weight: bold; font-style: italic;">Printed {DATE D, n/j/y g:ia}</span></td>
<td width="33%" valign="bottom" align="center"></td>
<td width="33%" valign="bottom" style="text-align: right;">{PAGENO}/{nbpg}</td>
</tr></table>
';
$mpdf->SetHTMLFooter($footer);
 
$header = '
        <table width="100%" style="vertical-align: bottom; font-size: 10pt; color: #000000;">
        <tr>
        <td width="25%" valign="top">'.$headingleft.'</td>
        <td width="50%" valign="top" align="center" style="font-weight:bold; font-size:14pt;">'.$title.'</span></td>
        <td width="25%" valigh="top" style="text-align: right;"><img height= "40" align="top" class="Right_Logo" src="http://'.$_SERVER['SERVER_NAME'].$logo.'"></td>
        </tr></table>
        ';
//
// execute query
//
$db->setQuery($query);
$result = $db->loadAssocList();
if (!$result) { // show ERROR
    $html .= "Error In Query:<BR>".$query."<br><br>Error is:<br>".$db->getErrorMsg()."</body></html>";
} else {  // no error... process sql results
    $thstyle = "text-align:left; border:1px solid white; background-color:black; color:white; padding:2px; font-size:7pt;";
    $th6style = "border:1px solid white; background-color:#dddddd; color:black; padding:2px; font-size:7pt;";
    $tdstyle = "border:1px solid black; padding:2px; font-size:7pt;";
 
    $first = TRUE;
    $html .= "<table style='width:100%; border-collapse:collapse; padding-top:20px;'>";
    foreach ($result as $lineofdata) {
        $headerhtml = ''; $datahtml = '';
        if ($first) $html .= '<thead>';
        $html .= '<tr>';
        foreach ($lineofdata as $heading => $data) {
            if ($first) { // first line, set table headers
                $headerhtml .= "<th style='".$thstyle."'>".$heading."</th>";
            };
            $datahtml .= "<td style='".$tdstyle."'>".$data."</th>";
        };
        if ($first) $html .= $headerhtml.'</tr></thead><tr>';
        $html .= $datahtml;
        $first = FALSE; 
        $html .= '</tr>';
    }
 
    $html .= "</table></body></html>";
};
$mpdf->SetHTMLHeader($header);
//echo $html; exit;  // comment this out to send to pdf... remove comment to send html to screen for debugging. 
$mpdf->WriteHTML($html);
//    echo 'run this query:<br>'.$query;
$mpdf->Output($title.'.pdf','I');
exit;    // causes pdf to be displayed vs. going back to form... user has to press 'back' to return to form and re-run report.
 
?>

Then for charting, i use 'plotalot' extension which costs all of $10 and is also FANTASTIC and can be imbeded anywhere in fabrik because it is a joomla plugin. It basically replaces mPDF as the final display mechanism in the example above but can do simple tables also, so you may be able to use just it vs. mPDF if your reporting needs are simple.

Sounds like php may be beyond your current skillset, and this approach may be beyond your current requirements. but here it is in case anyone else searching and finding this post can make sense of it.

 

[rongame]

hi rob
i used this code and it counts the entries in the amount element

SELECT DATE_FORMAT(date_time, '%Y-%m'), COUNT(amount) AS `items_sold` FROM sales GROUP
BY DATE_FORMAT(date_time, '%Y-%m')

i have a another element which is classification. its a drop down list with 4 to 5 items to choose from. example ref,tv,aircon etc... i want to have a summary of how many ref, tv and aircon per month and per year.

thanks again

 

[skyrun]

GROUP BY whatever you want to count vs. grouping by the date.

experiment and refer to http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html and other pages on dev.mysql.com/doc/refman/5.0 and i think you'll figure it out quickly.

 

[rongame]

thanks skyrun trying it now

 

[rongame]

hi

im using this code in phpmyadmin

SELECT DATE_FORMAT( date_time, '%Y-%m' ) AS `datetime`,
encoder AS `user_id`,
AVG( purchased_amount ) AS 'average',
SUM( purchased_amount ) AS `sum`,
COUNT( purchased_amount ) AS 'no of entries',
 
SUM(CASE WHEN product_clas = 1 then 1 Else 0 end) AS 'Aircon',
SUM(CASE WHEN product_clas = 2 then 1 Else 0 end) AS 'LCD TV'
 
FROM empenc_warranty
GROUP BY DATE_FORMAT( date_time, '%Y-%m' )

its working when i execute the code but when you create the view im getting errors

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'of entries,aircon,lcd tv) AS SELECT DATE_FORMAT( date_time, '%Y-%m' ) AS `date' at line 4

also i need to create a list that will view the current users average sum etc...
when creating the list i included a {$my->id} its not working. i checked the table and i think something should be added to code to display the users.

any help?

thanks

 

[skyrun]

looks like the error is in the syntax of your view creation SQL (which i don't think you gave here). the syntax in the error message seems way off though... should be able to just add 'create view viewname as ' on front of your select.

you realize that this is mysql and myphpadmin and outside of the scope of Fabrik.

i may not understand your 2nd question about the list and including the userid, but seems you should be able to add a dropdown filter on your userid element (encoder column?) where the user selects the userid they would like to view. of course you have to enable filters on the list first for any element filters to show up. or if you want it permanent, add a list prefilter. (list->data->prefilter->add prefilter).

 

[cheesegrits]

Oh, I should have followed up on this one - had a live chat with rongame, and we sorted this out.

Just for the record, the query we ended up with was:

DROP VIEW IF EXISTS hughtest;
CREATE VIEW hughtest AS
SELECT
id,
DATE_FORMAT( date_time, '%Y-%m' ) AS datetime,
encoder AS user_id,
AVG( purchased_amount ) AS average,
SUM( purchased_amount ) AS sum,
COUNT( purchased_amount ) AS no_of_entries,
 
SUM(CASE WHEN product_clas = 1 then 1 Else 0 end) AS aircon,
SUM(CASE WHEN product_clas = 2 then 1 Else 0 end) AS lcd_tv
 
FROM empenc_warranty
GROUP BY datetime, user_id

... which produces a per-user, per-month report.

-- hugh

 

[rongame]

hi skyrun
thanks so much for your assistance.

cheese,
thanks also, by the way how do i apply the vizualizations to create charts and everything?

thanks again