Page 1 of 1
[Solved] Several requests in report
Posted: Wed Mar 18, 2015 9:33 am
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
Re: Several request in report
Posted: Thu Mar 19, 2015 11:14 am
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).
Re: Several request in report
Posted: Thu Mar 19, 2015 11:32 am
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"
Re: Several request in report
Posted: Thu Mar 19, 2015 1:41 pm
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"
Re: Several request in report
Posted: Thu Mar 19, 2015 2:12 pm
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.
Re: Several request in report
Posted: Thu Mar 19, 2015 6:14 pm
by eremmel
It might that those columns have no alias, you might try:
, COALESCE("R"."DateDébut", '-') "DateDébut" , COALESCE("R"."DateFin", '-') "DateFin"
Re: Several request in report
Posted: Thu Mar 19, 2015 7:03 pm
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
Re: Several request in report
Posted: Thu Mar 19, 2015 7:55 pm
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?
Re: Several request in report
Posted: Thu Mar 19, 2015 11:09 pm
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
Re: Several request in report
Posted: Fri Mar 20, 2015 12:14 am
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.
Re: Several request in report
Posted: Fri Mar 20, 2015 10:44 am
by contremaitre
Ok found it. Thanks.