Best way to do that sort of thing is usually to create one or more MySQL views which create the "report" you need, then build a Fusion Chart viz on that. In Fabri, MySQL views can be treated as (read only) tables, for the purpose of creating Lists and visualizations.
The tricky part is typically crafting the query to create the view, although sounds like this one would be fairly simple.
You can't create MySQL views in Fabrik, you'll have to use phpMyAdmin, or whatever your mysql tool of choice is. You'll need to open up the SQL editor, where you can create and run queries by hand.
Assuming your form is using a table called 'selections', and contains (at least) these fields:
id, userid, track, winloss
... although it would obviously have others, like at least 'horse', and probably others, but those are the only ones we care about for this. If the values assigned to winloss are 'Win' and 'Loss', the view queries would look something like this ...
For the "wins / losses by user":
Code:
SELECT
id,
userid,
SUM(CASE WHEN winloss = 'Win' THEN 1 ELSE 0 END) as wins,
SUM(CASE WHEN winloss = 'Loss' THEN 1 ELSE 0 END) as losses
FROM selections
GROUP BY userid
For the "wins / losses by user, by track":
Code:
SELECT
id,
userid,
track,
SUM(CASE WHEN winloss = 'Win' THEN 1 ELSE 0 END) as wins,
SUM(CASE WHEN winloss = 'Loss' THEN 1 ELSE 0 END) as losses
FROM selections
GROUP BY userid, track
Obviously tweak that to suit your table and field names. Run them as simple queries in phpMyAdmin, and make sure they generate the results you would expect. Then, create two views from them, by just prepending "CREATE VIEW wins_by_user" to each one (or whatever you want to call them.
Then in Fabrik, you can create two lists, selecting the appropriate view as the table to use. make sure you select the 'id' field as the Primary Key (views don't actually have PK's, but Fabrik still needs to have something it treats as one, so it knows what to use as the unique rowid). Set the "access" on the lists so nobody can add, remove or edit rows.
Fabrik will then create your list / form / group / elements form the view. You'll need to edit the 'userid' element, and set it to type 'user', displaying the username. And edit the labels to look pretty, and any other tweaking you want just to make the display look nice.
Then you can create a fusionchart graph of whatever sort you want, using the two lists.
-- hugh
