From the "Reports" button, I enter the following:
The criteria form looks like this:
with the following Browse tab:
But when I Run Report, I get "No Objects:"
The VIEW has four fields, an aggregation :
Code: Select all
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `Per_Crop_Statistics`
AS SELECT
`c`.`ID` AS `ID`,
`c`.`Crop` AS `Crop`,format(sum(`qc`.`Metres2`),2) AS `Metres2`,format(sum(`qc`.`Kilograms`),2) AS `Kilograms`,format((sum(`qc`.`Kilograms`) / sum(`qc`.`Metres2`)),3) AS `Areal Productivity`
FROM ((`Questionares` `q` left join `Questionare_Crop` `qc` on((`qc`.`Questionare` = `q`.`ID`))) left join `Crops` `c` on((`qc`.`Crop` = `c`.`ID`))) where (`qc`.`Kilograms` > 0) group by `c`.`Crop` with rollup;
Code: Select all
1 apples 518,411.60 9,542.17 0.018
110 arugula 60.39 38.18 0.632
30 asparagus 58.06 31.82 0.548
26 basil 406.39 28.24 0.069
79 beans 404.69 236.36 0.584
64 beans, chick peas 8.36 0.91 0.109
67 beans, dry non-white 83.61 33.09 0.396
66 beans, dry white 0.00 10.00 NULL
73 beans, green 120.77 144.00 1.192
20 beans, string 10.99 58.26 5.300
13 beets 204.02 348.19 1.707
27 berries, goji 0.93 4.43 4.768
104 berries, Japanese silverberry 0.93 2.99 3.224
105 berries, Peruvian goldenberry 0.70 2.87 4.119
50 berries, raspberries 202.34 104.55 0.517
51 berries, strawberries 267.37 474.36 1.774
76 bok choi 0.00 96.00 NULL
89 broccoli 27.87 45.45 1.631
78 brussel sprouts 0.00 88.00 NULL
32 cabbage 202.34 1,000.00 4.942
74 cabbage, Napa 0.00 490.91 NULL
25 carrots 262.11 1,765.03 6.734
84 celery 0.00 144.00 NULL
21 celery, leaf 0.85 7.25 8.513
33 chard 0.00 64.00 NULL
46 cherries 2.51 3.08 1.227
34 corn 204.29 137.00 0.671
8 cucumbers 636.48 1,423.27 2.236
107 dill, weed 0.37 1.23 3.310
91 eggplant 0.37 2.50 6.727
81 fennel, bulb 0.28 3.01 10.800
63 flax seed 1.67 0.91 0.544
18 garlic 620.13 250.00 0.403
101 garlic, scapes 13.94 9.74 0.699
109 ground cherries 134.71 180.00 1.336
68 hazelnuts 9.29 0.45 0.049
102 jaltomato 0.85 4.70 5.513
19 kale 93.81 498.22 5.311
103 lavender florets 1.11 3.93 3.530
35 leeks 223.25 260.82 1.168
98 lettuce 809.37 595.45 0.736
95 melon 1.70 13.36 7.847
22 melon, canteloupe 139.35 159.09 1.142
3 mixed vegetables 0.00 45.45 NULL
5 mouse mellons 2.04 15.56 7.615
108 nettle 18.58 22.10 1.189
99 parsley 244.52 42.10 0.172
7 pears 911.56 562.50 0.617
38 peas 497.59 228.64 0.459
86 peppers 202.34 34.09 0.168
10 peppers, hot 23.50 55.97 2.381
29 peppers, savory 5.11 3.59 0.703
16 plums 607.03 239.09 0.394
9 potatoes 570.80 735.34 1.288
100 pumpkins 202.34 204.55 1.011
77 radish 244.15 466.36 1.910
52 rhubarb 202.34 27.27 0.135
2 rutabega/turnip 223.25 609.55 2.730
4 salad crops 404.69 534.24 1.320
62 soybeans 5.57 4.55 0.815
94 spinach 244.15 25.91 0.106
87 squash 328.47 1,163.64 3.543
39 squash, winter 0.00 454.55 NULL
12 tomatillos 0.84 38.35 45.872
6 tomatoes 1,105.08 3,154.60 2.855
96 wheat 8.36 7.27 0.870
15 zucchini 10.99 1,132.22 102.992
15 NULL 529,691.13 28,115.33 0.053