ExecuteSQL charting

[Teclo]

I am trying to create a bar chart (eventually a stacked bar chart, but I wanted to make sure that I got the different data for the x-axis correct first) in FileMaker 12 Pro using ExecuteSQL. This one shouldn't be so hard since I have all the data in the same table, but I can't figure out what I'm doing wrong.

 

I have people working in four different groups ("Group"), and a column for active people ("Active"=1, "Inactive"=0) and a gender column (Male/Female). When I use the formula below, I get a nice bar chart showing how many men are working in each group. However, when I change the gender from "Male" to "Female", the amount of women is correct (3 women in one group, 2 in one, 2 in one and 0 in the fourth group), but the columns/bars are displayed on the wrong group (so that the group that has no female employees in the table has 3 female employees in the chart).

 

 

ExecuteSQL(
"
SELECT COUNT(*)
FROM "Table"
WHERE "Active" = ?
AND "Gender" = ?
GROUP BY "Group"
ORDER BY "Group"
"
;"";¶; 1; "Male" )

 

I have checked my data to make sure that it is correct, and as you can see I have used the foolproof "brackets" (I'm writing in another language so some of the words have special characters). What is happening? The million dollar question is perhaps whether it is a mere coincidence that it is working when I write "Male"?

 

For clarification, this is the "y" axis:

 

ExecuteSQL(
"
SELECT DISTINCT ("Group")
FROM "Table"
WHERE Active = ?
GROUP BY "Group"
"
;"" ;¶; 1)

[Teclo]

ok I got it to work if I removed the "active" field. So how do I best go about to remove the inactive records from the chart?

[JerrySalem]

For the record delimiter, why are you including a paragraph symbol?

I don't know if this is the root of your problem, but it does look odd.

 

 

Seems like you can use the filemaker default;

 

ExecuteSQL(
"
SELECT COUNT(*)
FROM "Table"
WHERE "Active" = ?
AND "Gender" = ?
GROUP BY "Group"
ORDER BY "Group"
"
;"";""; 1; "Male" )  <<<--- change made here