[Solved] Several requests in report

[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

[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).

[contremaitre]

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

Code: Select all   Expand viewCollapse view

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   Expand viewCollapse view

SELECT  "tRapport"."DateDébut" , "tRapport"."DateFin" FROM "tRapport"


[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   Expand viewCollapse view

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"



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

[eremmel]

It might that those columns have no alias, you might try:
, COALESCE("R"."DateDébut", '-') "DateDébut" , COALESCE("R"."DateFin", '-') "DateFin"

[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

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

[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

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

[contremaitre]

Ok found it. Thanks.