Three column report

Getting your data onto paper - or the web - Discussing the reports features of Base

Three column report

Postby philip888 » Wed Sep 14, 2016 6:17 pm

I have a table (name: abc) have hundreds of record in single field (actual data is person name instead of numbers)
fieldA
=====
001
002
003
004
005
...
(Total:approx 680 records)

Report writer doesn't support auto multi-columns.... so I made a report with three columns
What I need to do is fit the single column data into 3 columns and print out....(each page will print 50 records)
That will be :

Field_X Field_Y Field_Z
001 051 101
002 052 102
...
049 099 149
050 100 150
151 201 251
152 202 252
153 203 253
...
200 250 300
301 351 401
....
...

I would like to know how can I do this in using mysql command?
Thanks in advance.
OpenOffice 4.x on Ubuntu 14.04
philip888
 
Posts: 8
Joined: Wed Sep 14, 2016 1:51 pm

Re: three column report

Postby charlie.it » Wed Sep 14, 2016 6:53 pm

Ciao, do you use Report Builder extension?
There is this choice in Report Builder:
Attachments
Schermata 09-2457646 alle 18.50.48.png
charlie AOO Italian Forum Admin https://forum.openoffice.org/it/forum/index.php
Apache Open Office 4.1.3 - Libre Office 4.3.7 - NeoOffice 3.4.1
on Mac OSX 10.7.5 Lion / macOS 10.12.1 Sierra (dual boot)
User avatar
charlie.it
Volunteer
 
Posts: 268
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: three column report

Postby philip888 » Wed Sep 14, 2016 7:10 pm

Hi, but it seems not I want...because I will try to go for 5 columns finally (BTW.... let me check that one more time )
OpenOffice 4.x on Ubuntu 14.04
philip888
 
Posts: 8
Joined: Wed Sep 14, 2016 1:51 pm

Re: three column report

Postby charlie.it » Wed Sep 14, 2016 7:18 pm

You're right, it does not work with only one field.
charlie AOO Italian Forum Admin https://forum.openoffice.org/it/forum/index.php
Apache Open Office 4.1.3 - Libre Office 4.3.7 - NeoOffice 3.4.1
on Mac OSX 10.7.5 Lion / macOS 10.12.1 Sierra (dual boot)
User avatar
charlie.it
Volunteer
 
Posts: 268
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: Three column report

Postby Nocton » Sat Sep 17, 2016 12:26 pm

Assuming a table with ID and Names fields, then first you need a query to find the total no. of Names:
SELECT COUNT( "Name" ) AS "Total" FROM "Names"

Then you need to make 3 queries dividing the data into three sets creating a new Row field.
SELECT "Names"."Name" AS "Name1", "Names"."ID" AS "Row" FROM "Names", "Query_Count" WHERE "Names"."ID" < ( "Total" / 3 )
SELECT "Names"."Name" AS "Name2", "Names"."ID" - ( "Total" / 3 ) AS "Row" FROM "Names", "Query_Count" WHERE "Names"."ID" < ( 2 * "Total" / 3 ) AND "Names"."ID" >= "Total" / 3
SELECT "Names"."Name" AS "Name3", "Names"."ID" AS "ID3", "ID" - ( 2 * "Total" / 3 ) AS "Row" FROM "Names", "Query_Count" WHERE "Names"."ID" <= "Total" AND "Names"."ID" >= ( 2 * "Total" / 3 )

Then you need to combine the three sets on the Row field:
SELECT "Query_Name1"."Row", "Query_Name1"."Name1", "Query_Name2"."Name2", "Query_Name3"."Name3" FROM "Query_Name2", "Query_Name1", "Query_Name3" WHERE "Query_Name2"."Row" = "Query_Name1"."Row" AND "Query_Name3"."Row" = "Query_Name2"."Row"

You can now make a report from this last query. If Base could do sub-reports, like it can sub-forms, then the process would be simpler.
You may have to add extra code to allow for the situation when Total is not exactly divisible by 3, but I think you can see the principle.

I've uploaded an example for you.

Regards

Nocton
Attachments
3ColDatabase.odb
(25.84 KiB) Downloaded 42 times
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 444
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Three column report

Postby philip888 » Sat Sep 17, 2016 4:02 pm

thanks! will try and see what happened

Once again. Thank you very much
OpenOffice 4.x on Ubuntu 14.04
philip888
 
Posts: 8
Joined: Wed Sep 14, 2016 1:51 pm

Re: Three column report

Postby chrisb » Wed Sep 28, 2016 12:38 am

hello philip888,

i've got to be honest & say that i don't get what this topic is all about (what's the purpose?).
however using SQL i have solved your issue.
i assume that you have a table called "tNames" which contains two fields "ID" integer & "Name" text.

the solution uses three queries.

1. we need a sequential value in order to calculate the values of 'page', 'column' & 'row'.
copy & paste the code below here >>>'Queries>Create Query in SQL View' & save as 'qRank'
Code: Select all   Expand viewCollapse view
--because the primary key field 'ID' may not be sequential we assign every table entry a unique sequential value between 0 & total records - 1
--we need this value in order to calculate page, column & row
select ID, (select count(*) from "tNames" where ID < N.ID) "Rank" from "tNames" N

2. next we calculate the grid position of each 'Name' ('page, 'column', 'row').
copy & paste the code below here >>>'Queries>Create Query in SQL View' & save as 'qName_Page_Column_Row'.
Code: Select all   Expand viewCollapse view
--calculates page, column & row using the field 'Rank'
select
N."Name",
R."Rank" / 150 "Page", --150 names per page
mod(R."Rank" / 50, 3) "Column",  --50 names per column & 3 columns per page
mod(R."Rank", 50) "Row"  --50 names per column per page

from "qRank" R

join "tNames" N
on N.ID = R.ID

3. the third & final query creates the necessary matrix.
copy & paste the code below here >>>'Queries>Create Query in SQL View' & save as 'qFinal_Output' or any name of your choice.
Code: Select all   Expand viewCollapse view
--select *  --show all columns & rows
select C1."Name" "Column1", C2."Name" "Column2", C3."Name" "Column3"  --names only(3 columns)

from
(
select "Name", "Page", "Row"
from "qName_Page_Column_Row"
where "Column" = 0
)
C1

left join
(
select "Name", "Page", "Row"
from "qName_Page_Column_Row"
where "Column" = 1
)
C2
on C2."Page" = C1."Page" and C2."Row" = C1."Row"

left join
(
select "Name", "Page", "Row"
from "qName_Page_Column_Row"
where "Column" = 2
)
C3
on C3."Page" = C1."Page" and C3."Row" = C1."Row"

order by C1."Page", C1."Row"
execute 'qFinal_Output' to ascertain all is OK.
the display is exactly as requested without line breaks (this is a query).

'Report Builder' is not the best choice of report engine for us here.
we want 50 lines per page. the only way to achieve this with 'Report Builder' is to adjust the height of the text boxes & even when you get this right your header may appear on the final & otherwise blank page.
'Calc' is by far the best option.
if you're not familiar with 'Base'/'Calc' then follow the steps below.
1. register your database here>>>Menu:Tools>Options>OpenOffice Base>Databases'. hit 'New' > hit 'Browse' > select your database & OK everything.
your database is now registered & can be accessed via 'Calc'.

2. open 'Calc' hit 'F4' & click the query 'qFinal_Output' in the left hand pane (the query will be displayed). drag the query icon from the left hand pane into cell 'A1'.
3. menu:View>Page Break Preview.
if necessary drag the slider in the bottom right hand corner to resize the output.
scroll down until you see a horizontal blue line. drag this line & position it between row 51 & 52 (this where the page will break).
repeat the process for each page(below row 101 then 151 etc.). remember 50 lines per page first row is a header.
menu:Format>Print Ranges>Define.
menu:Format>Print Ranges>Edit. in the large text box under 'Rows to Repeat' enter the number '1'. hit 'OK'.
menu:Format>Page. hit the 'Page' tab & set 'Table alignment' = 'Horizontal'. hit 'OK'
menu:View>Normal.
hit 'Page Preview' & admire.

i could of course have attached the database but that would be too easy. any problems then let me know.
open office 4.1.4 & LibreOffice 5.4.2.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.0 (Split) on Windows 10
chrisb
 
Posts: 148
Joined: Mon Jun 07, 2010 4:16 pm

Re: Three column report

Postby philip888 » Fri Oct 07, 2016 1:24 pm

thanks! ...very detail... appreciated that...
please give me some time digest the message (cos I'm new to mysql and calc ....sorry about that)

will let you know if I have any problem about that...

Once again, Thank you.
OpenOffice 4.x on Ubuntu 14.04
philip888
 
Posts: 8
Joined: Wed Sep 14, 2016 1:51 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 3 guests