OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] Help with big invoice report!
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Kurisuellegarden
General User
General User


Joined: 25 Feb 2009
Posts: 15

PostPosted: Wed Mar 11, 2009 12:48 pm    Post subject: [Solved] Help with big invoice report! Reply with quote

How could I have both of these reports information together into a report?

For example, like take these two reports!





into something LIKE this?



Now I did this in paint but I was just showing a visual example. Razz

I've been using the Sun Report Builder but I have three tables to need to go into one invoice.

RIght now, it goes into THREE different ones so it's really not useful especially when they also need a grand total invoice which you'd either need to create by hand or another report. =\

I was hoping someone could help me?[/img]


Last edited by Kurisuellegarden on Fri Mar 20, 2009 11:07 am; edited 2 times in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Mar 11, 2009 1:23 pm    Post subject: Reply with quote

Create a query merging all the related info from all tables.
Create a report with invoice details in the header section and the articles details in the main section.
Code:

Select "I"."this", "I"."that", "A"."this", "A"."that",  "C"."this", "C"."that"
FROM "Articles As "A", "Invoices" AS "I", "Clients" AS "C", "Invoices_Articles" AS "I_A"
WHERE "I"."C_ID" = "C"."ID"
AND "I_A"."Article_ID" = "A"."ID"
AND "I_A"."Invoice_ID" = "I"."ID"

... assuming 4 tables for articles, clients and invoices with a 4rth table mapping multiple articles to multiple invoices.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
eremmel
General User
General User


Joined: 29 Dec 2008
Posts: 17
Location: NL, Barneveld

PostPosted: Wed Mar 11, 2009 1:41 pm    Post subject: Reply with quote

I guess you have 3 tables I guess a master (M) and shipped (S) and ordered (O) and you want to have per M all corresponding S and O on one form. I would go for the following SQL structure:
Code:
SELECT '0-Shipped' as "grouping" ,M.CustId, M.xx, ..., S.*
   FROM M INNER JOIN S ON ....
UNION ALL
SELECT '1-Ordered' as "grouping" ,M.CustId, M.xx, ..., O.*
   FROM M INNER JOIN O ON ....
ORDER BY 2,1

This will give you an extra grouping level on the introduced field "grouping".
Note that UNION ALL can only be used when each SELECT statement contains the same number of fields and that each n-th field should have the same type for all SELECT statements.
In your example the tables S and O do not contain the same amount of fields, so you might use CONCAT() or alike depending on your database or introduce dummy fields for missing columns.
Because UNION ALL is not parsed by BASE you have to run it in 'direct SQL mode', but I think SRB does not like this. So make it an stored query or a view and now you can use a Base SQL query to retrieve the data.

I hope this outline gives you enough food for thought to continue.
Back to top
View user's profile Send private message
Kurisuellegarden
General User
General User


Joined: 25 Feb 2009
Posts: 15

PostPosted: Thu Mar 12, 2009 9:13 am    Post subject: Reply with quote

Thanks for the replies! Very Happy I'm going to go give those a try and see what works and I"ll bring back my results! Wink
Back to top
View user's profile Send private message
Kurisuellegarden
General User
General User


Joined: 25 Feb 2009
Posts: 15

PostPosted: Thu Mar 12, 2009 9:52 am    Post subject: Reply with quote

Yeah, thanks you guys! I'm finally starting to get somewhere. =] I'm thinking about making a query that union alls of them into one big report for me. =]

The shipping and the items are actually not related. It's an account thing. People can come in and just ship things and it'll get billed to them later. They can also buy items inside our store and get billed later. But it should work just fine!

I've only been using openoffice base for about a month and I have a pretty tall order =\ But I must get it done! Very Happy Thanks for helping me! I learn something new everyday!

I'll come back to this topic if I have any more issues regarding this issue, if I don't within a few days, I'll mark it solved. Wink
Back to top
View user's profile Send private message
Kurisuellegarden
General User
General User


Joined: 25 Feb 2009
Posts: 15

PostPosted: Thu Mar 12, 2009 10:26 am    Post subject: Reply with quote

Code:
SELECT "Ship Date" AS "Date", "First, Last, City, State" AS "Info", "Shipped By" AS "By and Qty", "Shipping Type" AS "Type", "Insured" AS "Insured and Price", "Tracking" AS "Tracking and Notes", "Total" FROM "Northwest Arms ACCOUNT Information"

UNION ALL

SELECT "Time" AS "Date", "Notes" AS "Info", "Qty" AS "By and Qty", "at" AS "Type", "Price" AS "Insured and Price", "Item" AS "Tracking and Notes", "Total" FROM "Go Figure ITEMS"


It keeps on telling me that this is not a SELECT statement. Only queries allowed. =\

EDIT: Okay, I solved and created a new problem. I accidentally clicked a button but then it saved without questioning. Now I'm coming up with an error that says Table not found. I'm wondering if the format of my code is right? Maybe it's trying to use UNION ALL as a table?
Code:

SQL Status: S0002
Error code: -22

Table not found in statement [SELECT "Ship Date" AS "Date", "First, Last, City, State" AS "Info", "Shipped By" AS "By and Qty", "Shipping Type" AS "Type", "Insured" AS "Insured and Price", "Tracking" AS "Tracking and Notes", "Total" FROM "Northwest Arms ACCOUNT Information" UNION ALL SELECT "Time" AS "Date", "Notes" AS "Info", "Qty" AS "By and Qty", "at" AS "Type", "Price" AS "Insured and Price", "Item" AS "Tracking and Notes", "Total" FROM "Go Figure ITEMS"]
Back to top
View user's profile Send private message
eremmel
General User
General User


Joined: 29 Dec 2008
Posts: 17
Location: NL, Barneveld

PostPosted: Thu Mar 12, 2009 11:06 am    Post subject: Reply with quote

How would solve it is take the query out and put it in again part by part:
First 'Select' and check, Second 'Select' and check etc.

BTW you do not have to give column aliases to the fields of the not-first SELECT statements in a sequence of UNION or UNION ALL.
Back to top
View user's profile Send private message
Kurisuellegarden
General User
General User


Joined: 25 Feb 2009
Posts: 15

PostPosted: Fri Mar 13, 2009 10:34 am    Post subject: Reply with quote

Are you sure the UNION ALL command works on it??? =\

I'm having issues getting it to work.

Code:
SQL Status: HY000
Error code: 1000

Syntax error in SQL expression

________________________________________

SELECT "Items"."Client" FROM "Go Figure Items"

UNION ALL

SELECT "Services"."Client" FROM "Go Figure Services Info"


I'm doing those two from a query because I'm trying to bring three queries together in one query trying to use the UNION ALL command as suggested.

There is a lot of math involved so I have to stick to queries (a table view might work too but I don't know...) somehow.

It works with TABLES but not Querys. Everytime I attempt to refer to any query in a query, it tells me the column exists. I have a feeling that once i get that solved (or someone points me in the right direction) I should be able to complete this since I know the UNION command works. =]
Back to top
View user's profile Send private message
eremmel
General User
General User


Joined: 29 Dec 2008
Posts: 17
Location: NL, Barneveld

PostPosted: Fri Mar 13, 2009 3:58 pm    Post subject: Reply with quote

Did you check the 'Direct SQL' button in the SQL editor? Base's parser will fail on UNION ALL. Icon is 'SQL' with green tick-mark. Tool tip reads "Run SQL command directly".
Back to top
View user's profile Send private message
Kurisuellegarden
General User
General User


Joined: 25 Feb 2009
Posts: 15

PostPosted: Mon Mar 16, 2009 9:16 am    Post subject: Reply with quote

It would give me an function error if I didn't have that selected, if I did, it'd say that the column already existed...

I got it to work with tables...

so...I'm working on mashing all of my queries into one query but it's going to become a pretty huge query. =\ I was hoping to keep it separated.
Back to top
View user's profile Send private message
eremmel
General User
General User


Joined: 29 Dec 2008
Posts: 17
Location: NL, Barneveld

PostPosted: Mon Mar 16, 2009 11:57 am    Post subject: Reply with quote

I'm not 100% sure what you trying to achieve, but I did not read your previous remark carefully. I tried to query from a query (I did it never before). This seems to work in Base, but not in direct SQL. Querying from a query is not uncommon in general SQL but than one specify the full query in the FROM clause, it is called a derived table. It might that Base takes care for for the substitution. But due to the UNION ALL you got to go native (direct-SQL). One solution direction might be to transform all the queries into views. You might try with one query and give the view the same name as the query (rename the query). When this does not break any of your other forms/queries this is the way to go. When you transformed two queries into views, then test you UNION ALL approach when that works you are getting some results.

Success.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Mar 16, 2009 2:03 pm    Post subject: Reply with quote

Nested queries work either within the scope of HSQLDB including tables and views or within the scope of Base which also includes the queries.
For a nested query in "direct mode" (parsed by HSQL) you need the HSQL-scope or a mix of inner direct mode with outer parsed mode.

Side note: You can achieve advanced parameter-queries with HSQL-views for the "difficult" part in combination with Base's parameter queries to prompt for the substitution.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Kurisuellegarden
General User
General User


Joined: 25 Feb 2009
Posts: 15

PostPosted: Tue Mar 17, 2009 10:34 am    Post subject: Reply with quote

Well, I did get it working but I rewrote all of my queries into one big query. =] Thanks for telling me about union all! It's been a very useful command! Very Happy

Here is my example code for future people to read. Razz

Code:
SELECT

"Services"."TIME" AS "Date",

"Services"."Client",

"Services"."Notes" AS "Info",

"Services"."Rate" AS "By",

"Services"."Dummy 1" AS "Type",

"Services"."Dummy 2" AS "Insured",

"Services"."Dummy 3" AS "Tracking",

"Services"."B&W Copies" * "B&W"."Price" + "Services"."Color Copies" * "Color"."Price" + "Astrobright Upgrades"."Price"

* "Services"."AB Upgrades" + "Cardstock Upgrades"."Price" * "Services"."CS Upgrades" + "Laminates Full Page"."Price"

* "Services"."Laminates" + "Stamps"."Prices" * "Services"."Stamps" + "Cutting"."Prices" * "Services"."Cutting" + "Services"."Faxes"

* "Faxes USA"."Price" + "Services"."Folding" * "Folding"."Price" + "Folding Fee"."Price" AS "Total",

"Clients".*

 FROM "Clients", "Astrobright Upgrades" AS "Astrobright Upgrades", "B&W" AS "B&W", "Cardstock Upgrades" AS "Cardstock Upgrades", "Clients" AS "Clients", "Color" AS "Color", "Cutting" AS "Cutting", "Faxes USA" AS "Faxes USA", "Folding" AS "Folding", "Folding Fee" AS "Folding Fee", "Laminates Full Page" AS "Laminates Full Page", "Services" AS "Services", "Stamps" AS "Stamps"

WHERE

/* CHANGE THE EQUAL VALUE TO THE CLIENT YOU WANT THE REPORT FOR */

"Services"."Client" = 'Go Figure' AND "Clients"."Client" = 'Go Figure'

AND "B&W"."Min" <= "Services"."B&W Copies" AND "B&W"."Max" >= "Services"."B&W Copies"

AND "Color"."Min" <= "Services"."Color Copies" AND "Color"."Max" >= "Services"."Color Copies" AND "Cardstock Upgrades"."MIN" <= "Services"."CS Upgrades"

AND "Cardstock Upgrades"."MAX" >= "Services"."CS Upgrades" AND "Astrobright Upgrades"."MIN" <= "Services"."AB Upgrades" AND "Astrobright Upgrades"."MAX" >= "Services"."AB Upgrades"

AND "Laminates Full Page"."MIN" <= "Services"."Laminates" AND "Laminates Full Page"."MAX" >= "Services"."Laminates" AND "Stamps"."MIN" <= "Services"."Stamps"

AND "Stamps"."MAX" >= "Services"."Stamps" AND "Cutting"."MIN" <= "Services"."Cutting" AND "Cutting"."MAX" >= "Services"."Cutting" AND "Faxes USA"."MIN" <= "Services"."Faxes"

AND "Faxes USA"."MAX" >= "Services"."Faxes" AND "Folding"."MIN" <= "Services"."Folding" AND "Folding"."MAX" >= "Services"."Folding" AND "Folding Fee"."MIN" <= "Services"."Folding" AND "Folding Fee"."MAX" >= "Services"."Folding"

UNION

SELECT

"Items"."Time",

"Items"."Client",

"Items"."Item",

"Items"."at",

"Items"."Price",

"Items"."Type",

"Items"."Notes",

"Qty" * "Price" + "Extra" AS "Total" ,

"Clients".*

FROM "Items", "Clients"

/* CHANGE THE EQUAL VALUE TO THE CLIENT YOU WANT THE REPORT FOR */

WHERE "Items"."Client" = 'Go Figure' AND "Clients"."Client" = 'Go Figure'

UNION ALL

SELECT 

"Shipping Account Records"."Time",

"Shipping Account Records"."Client",

"Shipping Account Records"."First, Last, City, State",

"Shipping Account Records"."Shipped By",

"Shipping Account Records"."Shipping Type",

"Shipping Account Records"."Insured",

"Shipping Account Records"."Tracking",

"Shipping Account Records"."Total" ,

"Clients".*

FROM "Shipping Account Records", "Clients"

                                   /* Don't make me repeat myself... */

WHERE "Shipping Account Records"."Client" = 'Go Figure' AND "Clients"."Client" = 'Go Figure'


It got pretty long so, it was the reason why I was trying to keep it separate and simple. Also, I can't go into the OpenBases edit window which makes it easier for people who do not use openoffice too much...to define a criteria. o_o

Now I am onto a auto pdf and criteria form to make it easier for them. =\ Wish me luck.

EDIT: Well, I implement it into the report and everything, then I run it. BANG~! It's all blank. =\ Does someone know how to fix this? I looked online but I couldn't find a solution that'd work for me.


Last edited by Kurisuellegarden on Tue Mar 17, 2009 12:53 pm; edited 2 times in total
Back to top
View user's profile Send private message
eremmel
General User
General User


Joined: 29 Dec 2008
Posts: 17
Location: NL, Barneveld

PostPosted: Tue Mar 17, 2009 12:00 pm    Post subject: Reply with quote

I can see why you liked to reuse queries. The first SELECT of the query is realy a candidate for a view.
I tried to improve the educational purpose further by reordering the query text a little (I hope I did not make any mistakes
, so my remarks are to the point). First the rewrite:
Code:
SELECT
   "S"."TIME"       AS "Date",
   "S"."Client",
   "S"."Notes"      AS "Info",
   "S"."Rate"       AS "By",
   "S"."Dummy 1"    AS "Type",
   "S"."Dummy 2"    AS "Insured",
   "S"."Dummy 3"    AS "Tracking",
   "S"."B&W Copies"   * "B&W"."Price"
   + "S"."Color Copies" * "Clr"."Price"
   + "S"."AB Upgrades"  * "AU"."Price"
   + "S"."CS Upgrades"  * "CU"."Price"
   + "S"."Laminates"    * "LFP"."Price"
   + "S"."Stamps"       * "Stm"."Prices"
   + "S"."Cutting"      * "Ctt"."Prices"
   + "S"."Faxes"        * "Fxs"."Price"
   + "S"."Folding"      * "F"."Price"
   + "FF"."Price" AS "Total"
FROM
    "Services"               AS "S"
   , "Astrobright Upgrades" AS "AU"
   , "B&W"                  AS "BW"
   , "Cardstock Upgrades"   AS "CU"
--   , "Clients"              AS "Clients"
   , "Color"                AS "Clr"
   , "Cutting"              AS "Ctt"
   , "Faxes USA"            AS "Fxs"
   , "Folding"              AS "Fld"
   , "Folding Fee"          AS "FF"
   , "Laminates Full Page"  AS "LFP"
   , "Stamps"               AS "Stm"
WHERE
       "S"."B&W Copies"   BETWEEN "B&W"."Min"  AND "B&W"."Max"
   AND "S"."Color Copies" BETWEEN "Clr"."Min"  AND "Clr"."Max"
   AND "S"."CS Upgrades"  BETWEEN "CU"."MIN"   AND "CU"."MAX"
   AND "S"."AB Upgrades"  BETWEEN "AU"."MIN"   AND "AU"."MAX"
   AND "S"."Laminates"    BETWEEN "LFP"."MIN"  AND "LFP"."MAX"
   AND "S"."Stamps"       BETWEEN "Stm"."MIN"  AND "Stm"."MAX"
   AND "S"."Cutting"      BETWEEN "Ctt"."MIN"  AND "Ctt"."MAX"
   AND "S"."Faxes"        BETWEEN "Fxs"."MIN"  AND "Fxs"."MAX"
   AND "S"."Folding"      BETWEEN "Fld"."MIN"  AND "Fld"."MAX"
   AND "S"."Folding"      BETWEEN "FF"."MIN"   AND "FF"."MAX"

Note the the "Client" table in the FROM clause. This table is not related to other tables in the WHERE clause. This implies a cartesian product;
the result of the query (without the table "Client") is replicated according the amount of records in the "Client" table.
Such a query is a lot of typing, but the the use of table aliases make life more easy.
Success,
Back to top
View user's profile Send private message
Kurisuellegarden
General User
General User


Joined: 25 Feb 2009
Posts: 15

PostPosted: Tue Mar 17, 2009 12:22 pm    Post subject: Reply with quote

I actually slipped the "Clients" in on purpose because that's a special table with all of the address information which when I use a

Code:
WHERE "Clients"."Clients" = 'Northwest Arms'


It wouldn't multiply all of them by that because this invoice is only showing one place at a time so I use a find and replace all to replace all criteria to one company which eliminates that multiplying table effect.

I went ahead and looked into your view but how would I be able to combine the three tables again for the big invoice report?

I also only noticed two errors.

Quote:
SELECT
"S"."TIME" AS "Date",
"S"."Client",
"S"."Notes" AS "Info",
"S"."Rate" AS "By",
"S"."Dummy 1" AS "Type",
"S"."Dummy 2" AS "Insured",
"S"."Dummy 3" AS "Tracking",
"S"."B&W Copies" * "B&W"."Price"
+ "S"."Color Copies" * "Clr"."Price"
+ "S"."AB Upgrades" * "AU"."Price"
+ "S"."CS Upgrades" * "CU"."Price"
+ "S"."Laminates" * "LFP"."Price"
+ "S"."Stamps" * "Stm"."Prices"
+ "S"."Cutting" * "Ctt"."Prices"
+ "S"."Faxes" * "Fxs"."Price"
+ "S"."Folding" * "F"."Price"
+ "FF"."Price" AS "Total"
FROM
"Services" AS "S"
, "Astrobright Upgrades" AS "AU"
, "B&W" AS "BW"
, "Cardstock Upgrades" AS "CU"
-- , "Clients" AS "Clients"
, "Color" AS "Clr"
, "Cutting" AS "Ctt"
, "Faxes USA" AS "Fxs"
, "Folding" AS "Fld"
, "Folding Fee" AS "FF"
, "Laminates Full Page" AS "LFP"
, "Stamps" AS "Stm"
WHERE
"S"."B&W Copies" BETWEEN "B&W"."Min" AND "B&W"."Max"
AND "S"."Color Copies" BETWEEN "Clr"."Min" AND "Clr"."Max"
AND "S"."CS Upgrades" BETWEEN "CU"."MIN" AND "CU"."MAX"
AND "S"."AB Upgrades" BETWEEN "AU"."MIN" AND "AU"."MAX"
AND "S"."Laminates" BETWEEN "LFP"."MIN" AND "LFP"."MAX"
AND "S"."Stamps" BETWEEN "Stm"."MIN" AND "Stm"."MAX"
AND "S"."Cutting" BETWEEN "Ctt"."MIN" AND "Ctt"."MAX"
AND "S"."Faxes" BETWEEN "Fxs"."MIN" AND "Fxs"."MAX"
AND "S"."Folding" BETWEEN "Fld"."MIN" AND "Fld"."MAX"
AND "S"."Folding" BETWEEN "FF"."MIN" AND "FF"."MAX"


But other than that, it was perfect! Very Happy

My query I have works now but I run it in report and it gives me a blank report because I think the problem is, is that the report cannot run direct sql queries, it tries to run it in the normal mode, which WOULD normally give me an error. =\

That's the issue I've been running into now. The view you have is what I had before as a query. But then I had to take all of the code and jam it with three other tables in one query because the UNION ALL does not like other queries for me.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group