[Solved] Several requests in report

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
contremaitre
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

[Solved] Several requests in report

Post by contremaitre »

Hi,

I created a report based on a request.
But I'd like to add some field from another request.
How is it possible ?

Thanks
Last edited by contremaitre on Fri Mar 20, 2015 10:44 am, edited 1 time in total.
Openoffice 4.3 Kubuntu 14.10
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Several request in report

Post by eremmel »

To get a better understand of your question:
1 request = SQL statement, query ?
2 Is the added field from other query related to data from base query?

Show your base query and other query and give possible relations.

As far as I understand reports with ORB: one has one query that feeds the report. I've been able to use charts in headers (grouping) and those were based on an other query that was linked to the data of that group. (This is broken in AOO 4.1.1, but works again in LO 4.2.8.2 (and likely newer).
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
contremaitre
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Several request in report

Post by contremaitre »

request = SQL statement
The report is based on a query called "rRapport" you helped me about on another thread :

Code: Select all

SELECT "Total", "A"."Marque", "A"."Largeur", "A"."Hauteur", "A"."Diametre", "A"."Charge", "A"."Vitesse" FROM "tPneus" "A" 
    INNER JOIN ( SELECT SUM( "tSorties"."QteSortie" ) "Total", "tSorties"."RefPneu" 
    FROM "tSorties", "tRapport", "tPneus" 
    WHERE 
        ( "tSorties"."DteSortie" > "tRapport"."DateDébut" OR "tRapport"."DateDébut" IS NULL ) AND ( "tRapport"."DateFin" IS NULL OR "tSorties"."DteSortie" < "tRapport"."DateFin" ) AND ( "tRapport"."DateFin" IS NULL OR "tSorties"."DteSortie" < "tRapport"."DateFin" ) AND ( "tPneus"."IdPneu" = "tSorties"."RefPneu" ) AND ( "tRapport"."Marque" LIKE '' OR "tPneus"."Marque" LIKE "tRapport"."Marque" ) AND ( "tRapport"."Largeur" LIKE '' OR "tPneus"."Largeur" LIKE "tRapport"."Largeur" ) AND ( "tRapport"."Hauteur" LIKE '' OR "tPneus"."Hauteur" LIKE "tRapport"."Hauteur" ) AND ( "tRapport"."Diametre" LIKE '' OR "tPneus"."Diametre" LIKE "tRapport"."Diametre" ) AND ( "tRapport"."Charge" LIKE '' OR "tPneus"."Charge" LIKE "tRapport"."Charge" ) AND ( "tRapport"."Vitesse" LIKE '' OR "tPneus"."Vitesse" LIKE "tRapport"."Vitesse" ) 
        GROUP BY "tSorties"."RefPneu" ) "B" 
    ON "A"."IdPneu" = "B"."RefPneu" ORDER BY "A"."Marque"
And I'd like to add two text fields with information from a table :

Code: Select all

SELECT  "tRapport"."DateDébut" , "tRapport"."DateFin" FROM "tRapport"
Openoffice 4.3 Kubuntu 14.10
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Several request in report

Post by eremmel »

You can apply to your main query same solution pattern. Add "tRapport" to the FROM clause as a CROSS JOIN because it will always be one record.
Here is a proposal, two notes:
1 the extra two fields are translated into a '-' in case of IS NULL due to COALESCE.
2 I changed the relation between "tSorties" and "tPneus" from implicit join in to an explicit. Result is the same, but better understandable and readable SQL.

Code: Select all

SELECT "Total", "A"."Marque", "A"."Largeur", "A"."Hauteur", "A"."Diametre", "A"."Charge", "A"."Vitesse" 
    , COALESCE("R"."DateDébut", '-') , COALESCE("R"."DateFin", '-')
FROM "tPneus" "A" 
    INNER JOIN ( SELECT SUM( "S"."QteSortie" ) "Total", "S"."RefPneu" 
            FROM "tSorties" "S" 
                INNER JOIN "tPneus" "P" ON "P"."IdPneu" = "S"."RefPneu"
                , "tRapport" "R"
            WHERE   ( "R"."DateDébut" IS NULL OR "S"."DteSortie" > "R"."DateDébut" ) 
                AND ( "R"."DateFin" IS NULL   OR "S"."DteSortie" < "R"."DateFin" ) 
                AND ( "R"."DateFin" IS NULL   OR "S"."DteSortie" < "R"."DateFin" ) 
                AND ( "R"."Marque"  LIKE ''   OR "P"."Marque"  LIKE "R"."Marque" ) 
                AND ( "R"."Largeur" LIKE ''   OR "P"."Largeur" LIKE "R"."Largeur" ) 
                AND ( "R"."Hauteur" LIKE ''   OR "P"."Hauteur" LIKE "R"."Hauteur" ) 
                AND ( "R"."Diametre"LIKE ''   OR "P"."Diametre"LIKE "R"."Diametre" ) 
                AND ( "R"."Charge"  LIKE ''   OR "P"."Charge"  LIKE "R"."Charge" ) 
                AND ( "R"."Vitesse" LIKE ''   OR "P"."Vitesse" LIKE "R"."Vitesse" ) 
            GROUP BY "S"."RefPneu" 
    ) "B" ON "A"."IdPneu" = "B"."RefPneu"
    , "tRapport" "R"
ORDER BY "A"."Marque"

It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
contremaitre
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Several request in report

Post by contremaitre »

I get a column not found error on R.DateDébut and R.DateFin.
If I remove the COALESCE line no error.
I don't understand why it does not find the column here but finds it in the WHERE clause.
Openoffice 4.3 Kubuntu 14.10
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Several request in report

Post by eremmel »

It might that those columns have no alias, you might try:
, COALESCE("R"."DateDébut", '-') "DateDébut" , COALESCE("R"."DateFin", '-') "DateFin"
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
contremaitre
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Several request in report

Post by contremaitre »

I don't understand, now I have a Wrong data type: java.lang.IllegalArgumentException in statement [SELECT .......... ]
With your first or second COALESCE you told me.
If I remove the COALESCE line it's working fine.
I don't know why the error changed
Openoffice 4.3 Kubuntu 14.10
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Several request in report

Post by eremmel »

Yes, I see the issue! Your field Debut and Fin have a date type, and I gave as optional argument a '-' that is a different type. This is not possible.
So we need to use a dummy date like '1970-01-01' or we need to convert the date value into a char type.
You might try something like: COALESCE( CAST("R"."DateDébut" AS VARCHAR(20) ), '-') or COALESCE( CONVERT("R"."DateDébut", VARCHAR(20) ), '-')

Excuse for the confusion, I did not check the syntax.
BTW which database are you using?
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
contremaitre
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Several request in report

Post by contremaitre »

Great now the COALESCE works, thanks ! It includes the date in every row but I can get this value in my report so it's fine.
I am using HSQLDB.

Now I'd like to add the grand total of the "Total" column.
I found "ROLL UP" instruction but it does not seem to work with HSQLDB.
What other way to do this ?

Thanks again
Openoffice 4.3 Kubuntu 14.10
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Several request in report

Post by eremmel »

Check documentation on Oracle Report Builder there is page out there which explains how to make functions (to add totals etc) in ORB, and use that in e.g.a footer. Use search on forum or google.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
contremaitre
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Several request in report

Post by contremaitre »

Ok found it. Thanks.
Openoffice 4.3 Kubuntu 14.10
Post Reply