(SOLVED) Monthly report with percentages

[mbeley]

Hi,

I have a candidates list with a gender element.
I need to generate a monthly report of candidates registration per gender.

Issue #1 Count and Percentages
I have a split count on the gender element, and the corresponding chart.
But I can't find a way for the count and chart to include percentages.
I did vote for the suggestion below:
https://fabrik.uservoice.com/forums...ement-calculation-add-count-percentages-radio
But with only 2 votes since the end of 2012 I don't think it will be part of Fabrik's development priorities!
Is there any way to get the split count data into a new table from where I could calculate percentages?
This thread shows an alternative solution:
http://fabrikar.com/forums/index.php?threads/simple-chart-request.22518/#post-121553
At this point I'm able to generate the SQL queries separately in PHPMyAdmin, but I have no clue how to group them in a database table.

Issue #2 Monthly Report
I'm not sure how to proceed to generate a monthly report.
I tried to make a new list based on the existing candidates table with a pre-filter.
But Fabrik does not let me make a new list based on an existing one.

Please help!
Thanks,
Mathieu

 

[cheesegrits]

Typically for this kind of reporting it's best to build MySQL views, and create new Fabrik lists on those, as described in that thread you referenced.

So for the view, it'd be something like this:

CREATE VIEW gender_month
SELECT
   gender,
   COUNT(*) AS gender_total,
   COUNT(*) / (SELECT COUNT(*) FROM yourtable) * 100 AS gender_percent,
   DATE_FORMAT(date_time, '%m') AS gender_month
FROM yourtable
GROUP BY
   gender,
   DATE_FORMAT(created,'%Y%m')

I haven't tested that, but it should be close. Obviously you'll have to change the field and table names to match your table.

So play around with that query in phpMyAdmin (just the bit from SELECT onwards), and when it's working right, stick the "CREATE VIEW ..." in front of it and run it again to create the actual view.

-- hugh

 

[mbeley]

Thanks Hugh,

I am playing around with PHPMyAdmin, having a lot of fun! I'm an absolute beginner in SQL...
I have a bunch of different reports I'm trying to generate, and most of them involve quite complex table joins.
In order to not get too confused with huge queries, I'm making them in 3 steps: 1)joining data, 2)filtering by date, and 3)calculate counts and percentages.

You wrote that Fabrik tables based on views are "read-only".
If I'm not mistaken, SQL Views are dynamic, which would mean that the "read-only" limitation comes from Fabrik.
Do you confirm?
In which case I think I will decide not to use Fabrik to generate the reports for this application.
I'm looking at a Joomla SQL reporting extension (should I name it here?)

While playing around with PHPMyAdmin I had to tweak your code for percentage calculation.
After some amount of online research I came up with this, which may help other people on this forum:
SELECT
gender,
COUNT(gender) AS count,
CONCAT(FORMAT(((COUNT(gender) * 100) / newtable.icount),2),'%') AS percentage
FROM mytable, (SELECT COUNT(gender) AS icount FROM mytable) newtable
GROUP BY gender

This code returns a table that looks like this:
Man 2 66.67%
Woman 1 33.33%

 

[cheesegrits]

Well, the rules governing whether MySQL can update a vew are complex, and there's no way of telling from the application layer whether a given view is updateable or not.

So we say "Fabrik lists based on views are read-only", and leave it at that. You can try editing them via the resulting form, and we will issue an UPDATE query for it. But whether it works or not is entirely down to what your view does.

In this case, I doubt very much it would work. If you think about it ... your view is doing percentage and sum queries, based on the current state of another table. How would mySQL know what to do with a query which tried to update (say) the percentage value on a row in that view, which is based on the sums from some arbitrary selection of rows from another table?

Typically, views can only be updated if ALL they do is simple selects from one or more tables, such that MySQL can directly correlate each field in each row in the view, with it's corresponding field and row in the original table(s).

So what part of the "read only" limitation is problematic? As explained above, and certainly for your percentage reports, there is no component / module which would allow you to directly update your view. It's not a Fabrik Thing, it's a MySQL Thing.

And of course, you can continue to use the lists which sit on the tables you are using in your views as normal. And any changes you make to those, like adding or editing rows, will instantly be reflected in the Fabrik display of those views. So if you add a new row to your original table, then redisplay a chart based on your view, you'll see the new sums / percentages.

-- hugh

 

[cheesegrits]

(I suspect that you may be misinterpreting my use of the term "read only", thinking that I mean Fabrik won't see changes to the view data, which as explained above is not the case. I just mean you can't directly edit the data shown in the list which displays your view)

 

[mbeley]

Thank you so much for your reply Hugh.
I was misinterpreting your use of "read-only".
That's why I asked if it was a SQL thing or a Fabrik thing, and you answered very clearly.
Now I know that Fabrik will indeed see the changes in the views resulting from adding or editing a row, I will go ahead and build my reports in Fabrik.
Which makes me feel much better, because I don't want to use another extension.
I really like working with Fabrik, and support is awesome!
Thanks again,
Mathieu