[Solved] Several requests in report

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Several requests in report

Postby contremaitre » Wed Mar 18, 2015 9:33 am

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
contremaitre
 
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Several request in report

Postby eremmel » Thu Mar 19, 2015 11:14 am

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).
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Several request in report

Postby contremaitre » Thu Mar 19, 2015 11:32 am

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"
Openoffice 4.3 Kubuntu 14.10
contremaitre
 
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Several request in report

Postby eremmel » Thu Mar 19, 2015 1:41 pm

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"

Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Several request in report

Postby contremaitre » Thu Mar 19, 2015 2:12 pm

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
contremaitre
 
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Several request in report

Postby eremmel » Thu Mar 19, 2015 6:14 pm

It might that those columns have no alias, you might try:
, COALESCE("R"."DateDébut", '-') "DateDébut" , COALESCE("R"."DateFin", '-') "DateFin"
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Several request in report

Postby contremaitre » Thu Mar 19, 2015 7:03 pm

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
contremaitre
 
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Several request in report

Postby eremmel » Thu Mar 19, 2015 7:55 pm

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?
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Several request in report

Postby contremaitre » Thu Mar 19, 2015 11:09 pm

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
contremaitre
 
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Several request in report

Postby eremmel » Fri Mar 20, 2015 12:14 am

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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Several request in report

Postby contremaitre » Fri Mar 20, 2015 10:44 am

Ok found it. Thanks.
Openoffice 4.3 Kubuntu 14.10
contremaitre
 
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest