Three column report

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
philip888
Posts: 8
Joined: Wed Sep 14, 2016 1:51 pm

Three column report

Post by philip888 »

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
User avatar
charlie.it
Volunteer
Posts: 417
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: three column report

Post by charlie.it »

Ciao, do you use Report Builder extension?
There is this choice in Report Builder:
Attachments
Schermata 09-2457646 alle 18.50.48.png
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
philip888
Posts: 8
Joined: Wed Sep 14, 2016 1:51 pm

Re: three column report

Post by philip888 »

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
User avatar
charlie.it
Volunteer
Posts: 417
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: three column report

Post by charlie.it »

You're right, it does not work with only one field.
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Three column report

Post by Nocton »

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 347 times
OpenOffice 4.1.12 on Windows 10
philip888
Posts: 8
Joined: Wed Sep 14, 2016 1:51 pm

Re: Three column report

Post by philip888 »

thanks! will try and see what happened

Once again. Thank you very much
OpenOffice 4.x on Ubuntu 14.04
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Three column report

Post by chrisb »

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

--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

--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

--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.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
philip888
Posts: 8
Joined: Wed Sep 14, 2016 1:51 pm

Re: Three column report

Post by philip888 »

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
Post Reply