Page 1 of 1

Three column report

Posted: Wed Sep 14, 2016 6:17 pm
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.

Re: three column report

Posted: Wed Sep 14, 2016 6:53 pm
by charlie.it
Ciao, do you use Report Builder extension?
There is this choice in Report Builder:

Re: three column report

Posted: Wed Sep 14, 2016 7:10 pm
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 )

Re: three column report

Posted: Wed Sep 14, 2016 7:18 pm
by charlie.it
You're right, it does not work with only one field.

Re: Three column report

Posted: Sat Sep 17, 2016 12:26 pm
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

Re: Three column report

Posted: Sat Sep 17, 2016 4:02 pm
by philip888
thanks! will try and see what happened

Once again. Thank you very much

Re: Three column report

Posted: Wed Sep 28, 2016 12:38 am
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.

Re: Three column report

Posted: Fri Oct 07, 2016 1:24 pm
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.