Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

Simple Report on a VIEW

Post Reply
EcoReality
Posts: 26
Joined: Wed Feb 15, 2017 8:50 am
Location: Salt Spring Island, British Columbia, Canada
Contact:

Simple Report on a VIEW

Unread post by EcoReality »

I'm having trouble simply displaying all records in a VIEW in a report.

From the "Reports" button, I enter the following:
Screenshot  2017-02-27 at 18.27.07.png
Screenshot 2017-02-27 at 18.27.07.png (27.02 KiB) Viewed 5949 times
The criteria form looks like this:
Screenshot  2017-02-27 at 18.31.44.png
Screenshot 2017-02-27 at 18.31.44.png (37.8 KiB) Viewed 5949 times
with the following Browse tab:
Screenshot  2017-02-27 at 18.30.30.png
Screenshot 2017-02-27 at 18.30.30.png (29.34 KiB) Viewed 5949 times
But when I Run Report, I get "No Objects:"
Screenshot  2017-02-27 at 18.33.08.png
Screenshot 2017-02-27 at 18.33.08.png (10.47 KiB) Viewed 5949 times
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;
It should look like this in the report:

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
What am I doing wrong?
:::: Jan Steinman EcoReality Co-op ::::
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: Simple Report on a VIEW

Unread post by admin »

Jan,

Breadcrumb Titles need to be SQL but you can insert hash ids..

http://wiki.nubuilder.net/nubuilderv3/i ... rumb_Title

Steven
Post Reply