[Solved] Two column report

[clausisme]

How do I make a two column report. I what it to look like this

id info id info
1 abc 2 iog
3 sad 4 fdg
5 vad 6 asd
7 asd 8 tnk

I tried the sun report builder and the normal wizard, and I did manage to make it look like this:

id info id info
1 abc 5 vad
2 iog 6 asd
3 sad 7 asd
4 fdg 8 tnk

But this is not good for me.

[F3K Total]

Hi,
bind you report to a query like this:

Code: Select all

SELECT 
    "ODD"."ID" AS "ID1", 
    "ODD"."Name" AS "Name1", 
    "EVEN"."ID" AS "ID2", 
    "EVEN"."Name" AS "Name2" 
FROM 
    (SELECT 
         "ID", 
         "Name" 
    FROM "Contacts" 
    WHERE MOD( "ID", 2 ) = 0) AS "EVEN",
 
   (SELECT 
         "ID", 
         "Name" 
    FROM "Contacts" 
    WHERE MOD( "ID", 2 ) <> 0) AS "ODD" 
where "ODD"."ID" + 1 = "EVEN"."ID"

R

[clausisme]

Thanks for the quick reply but that solution doesn't work that well for me. It simply cuts the last record if ID is a odd number (like 7,9 etc) .
I also was hopping to be able to order the names myself by a grade_number something like this:

grade name grade name
1.12 Jake 2.3 Mike
6.1 Terry 7.1 Ted
8.2 Tim 9.9 Elly
10 Aaron

Sorry if I've misled you with my first example, I was thinking that I just missed something obvious in making a report >> sort by grade and put 2 columns in report but that doesn't seem to be that simple.

[F3K Total]

Hi,
ok, this could be done with a more complex Query, or e.g., if you use the MailMerge functions on a writer document.
Just insert ID and Name using the databrowser, wherefore the database has to be registered, followed by the "next record" command, a tabulator to the startposition of the second column and CR. Fill one Page, leave away the "next record" command at the very last entry on that one page.
Works:

R

[frofa]

A while back, I was able to get very neat and condensed 3-COLUMN 'catalogue-style' reports with many HEADINGS (each 'detail section' column had 3 database fields across) using the 'old' legacy report designer wizard (LRD). One good thing about the 'old' wizard was that it created proper (WRITER/WP) tabled layouts.

I believe the 'old' LRD wizard interface is still reachable if you disable the REPORT BUILDER module (I recall there are some posts on OO and AOO fora on how to do that). Once you have a working 'tabled' report template, you can re-enable the RB, but keep using the your multi-column report template (I have been able to do this). I also understand that the Report Builder does have a column control built-in but it is not 'implemented', so it doesn't do anything. It's a pity that multi-column reports are not easier to do because I think they would give a lot more flexibility in creating nice report layouts. Why not just give users access to BOTH report-generating tools though additional the report menus? Surely this can't be too hard to do if the code is still there?

[clausisme]

To F3K Total : I can't use the MailMerge as I don't think it has the complexity needed, the report I have uses pictures (from a folder linked to the base), shapes, page formating etc I guess it does look like a catalog.
There should be a tutorial:
How to make multi-column report where the items flow left->right.

To frofa :
In the old normal wizard I managed to do a
1 5
2 6
3 7
4 8
type columns but I don't want that, as I said before. Also I can't switch between them... anything made in the old wizard stays in that format even if I re-enable the sun report builder. I can't get the multi-column report template to open in the sun report builder.

Could you give me a file were you did that?

[frofa]

Hi clausisme:

Steps to create a report template using the 'legacy' Report Designer tool in AOO:

1. Disable the Report Builder using TOOL>EXTENSION MANAGER and then disable the Oracle Report Builder 1.2.1 (ORB) and close the EM window (you might have to restart AOO).

2. Click on the REPORTS option under the Database tools pane on the left, and then choose Use Wizard to Create Report which should then allow you to create your report using the LRD tool (the 'old' one).

3. Create your report with appropriate 'details' columns/fields, grouping and sorting columns and set the template style and page orientation, etc. I used default and portrait.

4. The choose the open report for editing option. You should then see your report layout in the edit window of the LRD.

5. Now here is the crucial bit, go to FORMAT>PAGE in the menus, and then click on the Columns tab in the Page Style dialog-box that comes up. Set your number of columns to 2 (or 3). You'll need to do this for first page, and also default page using the FORMAT>STYLES AND FORMATTING menu (using 4th button along at the top of the Styles and Formatting 'palette').

6. You should now see 2 columns in your Report edit layout.

7. You will probably need to do a lot of fiddling with the field boxes (font sizes and positions), margins and column separation settings, but you should be able to achieve the result you want pretty easily. You can also include page headers and footer using the Format>Page menu dialog window.

8. When your report is running as you want it to, you can reverse step 1, and re-enable the ORB.

9. You should still be able to open your 'legacy' report for editing even after you have re-enabled the ORB.

Let us know how you go.

WARNINGS: Save your report constantly (I recall crashes did occur when I developed my reports using the LRD). Also, always keep a few back copies of the report-in-progress (just use copy and paste in the main report listing pane on the left) while developing the report, so you can backtrack to an earlier version if something doesn't work - that procedure saved me a lot of time. It's also very important to have fresh backup copies of your database file to protect again catastrophic loss of data that might occur when developing your report templates.

[F3K Total]

It simply cuts the last record if ID is a odd number (like 7,9 etc)

this could be done with a more complex Query

e.g.:

Code: Select all

SELECT 
"ODD"."ID" AS "ID1", 
"ODD"."Name" AS "Name1", 
"EVEN"."ID" AS "ID2", 
"EVEN"."Name" AS "Name2" 
FROM 
( SELECT "ID", "Name" FROM "Contacts" WHERE MOD( "ID", 2 ) = 0 ) AS "EVEN", 
( SELECT "ID", "Name" FROM "Contacts" WHERE MOD( "ID", 2 ) <> 0 ) AS "ODD" 
WHERE ( "ODD"."ID" + 1 = "EVEN"."ID" )
UNION 
SELECT "ID", "Name",NULL,'' 
FROM 
(SELECT "ID", "Name" FROM "Contacts" WHERE MOD( "ID", 2 ) <> 0 ) 
where ID NOT IN 
(SELECT "ODD"."ID" 
FROM 
( SELECT "ID", "Name" 
FROM "Contacts" 
WHERE MOD( "ID", 2 ) = 0 ) AS "EVEN", 
( SELECT "ID", "Name" 
FROM "Contacts" 
WHERE MOD( "ID", 2 ) <> 0 ) AS "ODD" 
WHERE ( "ODD"."ID" + 1 = "EVEN"."ID" ))

And don't tell me, that's without a grade_number, you can easily add a extra grade_number column.
R

[clausisme]

Thanks for your time and effort I tried them both

1 Solution frofa : I just could not do the 7th step "the fiddling". It's really hit and miss for me, and frustrating. But if you did it I'm sure it can be done.

2 Solution F3K Total : The SQL solution works. I'm going to put here the version for 3 table-colums for other novice people like me.

Code: Select all

SELECT
"ODD"."ID" AS "ID1",
"ODD"."Grade" AS "Grade1",
"ODD"."Name" AS "Name1",
"EVEN"."ID" AS "ID2",
"EVEN"."Grade" AS "Grade2",
"EVEN"."Name" AS "Name2"
FROM
( SELECT "ID", "Grade", "Name" FROM "Sheet1" WHERE MOD( "ID", 2 ) = 0 ) AS "EVEN",
( SELECT "ID", "Grade", "Name" FROM "Sheet1" WHERE MOD( "ID", 2 ) <> 0 ) AS "ODD"
WHERE ( "ODD"."ID" + 1 = "EVEN"."ID" )
UNION
SELECT "ID", "Grade", "Name",NULL,'',''
FROM
(SELECT "ID", "Grade", "Name" FROM "Sheet1" WHERE MOD( "ID", 2 ) <> 0 )
WHERE ID NOT IN
(SELECT "ODD"."ID"
FROM
( SELECT "ID", "Grade", "Name"
FROM "Sheet1"
WHERE MOD( "ID", 2 ) = 0 ) AS "EVEN",
( SELECT "ID", "Grade", "Name"
FROM "Sheet1"
WHERE MOD( "ID", 2 ) <> 0 ) AS "ODD"
WHERE ( "ODD"."ID" + 1 = "EVEN"."ID" ))

As a novice at SQL I got stuck on this line

Code: Select all

SELECT "ID", "Grade", "Name",NULL,'',''

not seeing that I had to put the extra ''

I guess that if you have more a complex query you just throw the results in a temporary table and the use this query on that for the report.

3 Solution Print layout: I found that if I make the pages small I can reassemble them in a pdf with Writer -> File>Print>Page Layout> Pages per sheet> Custom (pick two columns>left to right). And there are many other ways to assemble them there.


The Print layout solution was a good temp solution but I'm going to using the SQL solution. I guess if you use a database you need to know SQL.

Thanks again