Query Works but not in Report

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
pizzipie
Posts: 54
Joined: Sat Sep 13, 2008 2:28 am

Query Works but not in Report

Post by pizzipie »

Hi,

I wrote a query which works fine, see below. Out of twelve records I had the query show the top six.

When I use this query for a report, all twelve records show up.

Code: Select all


SELECT "id", "playdate", "player", "score" FROM "bridge"."bdata" AS "bdata" WHERE "playdate" = '2015-12-07' ORDER BY "playdate", "score" DESC LIMIT 6

Thanks in advance for your help in fixing this problem.

R
Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Query Works but not in Report

Post by MTP »

I suspect it's a limitation of the Base parser that is used in creating the reports - I've always had to remove any ORDER BY clauses from a query to get a report to work, it wouldn't surprise me that LIMIT causes issues, too.

Maybe you could approach getting the top six scores in a different way:

Code: Select all

SELECT "id", "playdate", "player", "score", COUNT(*) AS "rank" 
   FROM "bridge"."bdata" AS "bdataA" 
   LEFT JOIN "bridge"."bdata" AS "bdataB" ON "bdataA"."score" <= "bdataB"."score"
WHERE "bdataA"."playdate" = '2015-12-07' AND "bdataB"."playdate" = '2015-12-07'
GROUP BY "id", "playdate", "player", "score"
HAVING COUNT(*) <= 6
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
pizzipie
Posts: 54
Joined: Sat Sep 13, 2008 2:28 am

Re: Query Works but not in Report

Post by pizzipie »

WOW!! That's weird!

I had to doctor your query because of 'ambiguous data' but it is not working just right.
query-results.png
Comments please.

Would Top 6 possibly work. I haven't tried that yet.

R
Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
longi
Posts: 110
Joined: Mon Jul 15, 2013 5:04 pm

Re: Query Works but not in Report

Post by longi »

Hi!
I'm supposing that you are using the wizard of Openoffice to generate a report (the old-fashioned system). This system has some problems, but they can be fixed.
In this case, if I'm not wrong, it could be easy: the report put the order from the first column that the query has, not the order of the query. So you have to put the query's column with the order in the first place. Normally, the report should show the results in the order you want.
It could be like this:

Code: Select all

SELECT "playdate", "score" ,"player", "id" FROM "bridge"."bdata" AS "bdata" WHERE "playdate" = '2015-12-07' ORDER BY "playdate", "score" DESC LIMIT 6 
As you can see, I only changed the order of the query columns

If it doesn't work, see this link: viewtopic.php?f=42&t=70257.
It is the case in which a query has several tables as origin of data, so, we change the origine of data from a query to an SQL order.

Sorry for my English. I'm doing things as I can, but I keep making lots of mistakes

If you want to see things to do with reports in Openoffice, without Oracle Report Builder, you can see this link: https://forum.openoffice.org/es/forum/v ... 70&t=11605. All is made in Spanish, but I think it could be understood.
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Query Works but not in Report

Post by MTP »

The poster's signature indicates LibreOffice, which comes with the Report Builder included, so I'm assuming that's what they are using.

I did not include an 'ORDER BY' clause in the query I wrote because my experience is that Report Builder has trouble with those. To order a query used in a report, open the "Sorting and Grouping" window (the icon with the paper with lines on it, or View→Sorting and Grouping) and select the value you want to sort by, setting the "sorting" property as "ascending" or "descending" as desired. Still in the Sorting and Grouping window, make sure to set the "Group Header" as "present" or "not present" as you want.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
pizzipie
Posts: 54
Joined: Sat Sep 13, 2008 2:28 am

Re: Query Works but not in Report

Post by pizzipie »

I'm supposing that you are using the wizard
Thanks longi, I don't use the wizard since I can't make it do what I want it to. LIMIT 6

Thanks MTP,

How do you get the Report Writer to limit the records to, say, 6. The query works with LIMIT 6 but the Report Writer sends ALL records.

I am about to create a new table in MySql in which only the top 6 'scores' are contained.
(apparently) the 'queries' design view doesn't know about views?)
Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query Works but not in Report

Post by Villeroy »

Download download/file.php?id=23688 and add the following query.

Code: Select all

SELECT "Categories"."N" AS "Category", "Persons"."N" AS "Name",  YEAR( "D" ) AS "Year", MONTH( "D" ) AS "Month", 
  SUM( "Data"."V" ) AS "Sum" 
FROM "Data", "Categories", "Persons" 
WHERE "Data"."CID" = "Categories"."ID" AND "Data"."PID" = "Persons"."ID" 
GROUP BY "Categories"."N", "Persons"."N", YEAR( "D" ), MONTH( "D" )
Right-click the query > Report Wizard...
Add all columns, group by Persons, order by Category, Year and Month.
Finish with any layout and notice that the report can't be filled with data for some reason.
Close the report, open the query in SQL mode again and check menu:Edit>"Run SQL directly" or check the corresponding toggle button on the toolbar. Save the query. Open the same report again and enjoy the nice report with data. Close the report, append LIMIT 6 to the query, save the query and the report will show the first 6 records.

P.S. In OpenOffice I enabled the "Oracle Report Extension" which is an integrated part of LibreOffice and built a similar report from the same query and yes, it fails. But LibreOffice can still open the old style report which had been created without ORB. However, LO can not generate any such report because ORB can't be disabled. They disabled a fall back option for a component that never really worked.
http://www.mediafire.com/download/vi8v9 ... rData5.odb (has become too big for the forum)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Query Works but not in Report

Post by MTP »

When Base makes a report in LibreOffice, or OpenOffice with the Report Builder extension, it runs the query through the Base parser, which can't handle some SQL. This is a limitation of Base and the price we pay for using free software written entirely by volunteers. I think the only way to get a report as you want is to find a way to limit the results to top 6 without using the SQL "LIMIT 6", e.g. the weird self-join I had posted before.

You wrote before that the query with the self-join was "not working just right" - I assumed from your picture that you meant it wasn't ordering the results, which is why I then posted directions on how to order the results from inside the report. Did you have a different issue with that syntax?
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Query Works but not in Report

Post by chrisb »

Hello pizzipie

i use OpenOffice on a Windows machine & do not have access to LibreOffice.

using the code below (your original query) saved with 'Run SQL command directly' selected, everything works as expected.

Code: Select all

select "playdate", "score", "player", "id" from "bdata" where "playdate" = '2015-12-07' order by "playdate", "score" desc limit 6
if the 'Sorting and Grouping' window in report builder contains one or more fields then 'Limit' will always fail (all records are displayed).
your report is a flat report & does not require sorting/grouping so hit ('Ctrl+G') & delete any entries.

if the issue persists then you could try running your query as an SQL command.
copy your query.
open report in edit mode.
click anywhere in the grey area.
if necessary hit (F4) to show the properties pane.
hit ('Data') tab.
'Content type' = 'SQL command'.
'Content' = paste your code here & hit ('ENTER')
'Analyse SQL command' = 'No' (when this option is set to 'No' you can hit the '....' gadget to the right of 'Content' & edit your data in sql view).
execute your report with fingers crossed.

Using the 'Rank' query posted by MTP may not be a viable solution because the value stored in the field 'score' may not be unique.
For example below we have 5 scores. 3 are unique & are ranked uniquely. 2 are identical & are ranked identically. if our query contained the code 'where rank <=4' then only 3 rows would be displayed when we wanted 4.
score = 100 rank = 5
score = 100 rank = 5
score = 200 rank = 3
score = 300 rank = 2
score = 400 rank = 1
 Edit: just downloaded Villeroy's database, added his query & used it as the source to create a report with Report Builder (literally took me 5 minutes).
before saving the query make sure to select menu:Edit>Run SQL command directly
the query contains 676 rows.
'limit 6' was appended to the query & only the first six rows were shown.
'order by "Category" limit 6' was appended to the query & only the first six rows of the sorted data were shown in ascending order by "Category".
in both cases the result was exactly as asked for.
if you try this & meet with failure then it's likely the problem lies with LibreOffice. 
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
pizzipie
Posts: 54
Joined: Sat Sep 13, 2008 2:28 am

Re: Query Works but not in Report

Post by pizzipie »

Thanks all,

Here is my adjusted Villeroy Code:

Code: Select all

SELECT "Categories"."N" "Category", "Persons"."N" "Name", YEAR( "D" ) "Year", MONTH( "D" ) "Month", SUM( "Data"."V" ) "Sum" FROM "Data", "Categories", "Persons" WHERE "Data"."CID" = "Categories"."ID" AND "Data"."PID" = "Persons"."ID" GROUP BY "Categories"."N", "Persons"."N", YEAR( "D" ), MONTH( "D" ) [b]ORDER BY "Category" LIMIT 6[/b]
Query results: As expected!
Selection_017.png

Report Results: Doesn't work!
Selection_018.pdf
(61.25 KiB) Downloaded 319 times
This attachment didn't work but, anyway, Report shows all data.

I have made progress with the original query for this post in that it works.

Thanks,

R
Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Query Works but not in Report

Post by MTP »

Could you upload your Base file itself and not just a screenshot? Or a sample file that displays the problem but has had all sensitive or private information removed or obfuscated? There may be some detail we're not thinking of that would jump out at us when looking at the actual file.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query Works but not in Report

Post by Villeroy »

See attached FilterData5.odb. It has one query about this topic #80890 and two reports.
I created both reports with AOO 4.1.2 with ORB and without ORB.
Then I tested both forms with the query in parsed mode and in direct SQL mode.
Then I loaded the database into LO 4.6 and tested both forms with the query in parsed mode and in direct SQL mode.

The form "topic_80890" is a report about my test results.
Attachments
FilterData5.1.odb
One query, two reports and a documenting form about topic_80890
(116.42 KiB) Downloaded 246 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply