Re: Report with multiple stored procs
From: Bob Holmes (rholmes_at_REMOVEmmwec.org)
Date: 10/19/04
- Next message: Hallgeir: "Crystal Report v Access"
- Previous message: Jodi: "Viewing a report in Crystal 10 Enterprise Professional"
- In reply to: Telus: "Re: Report with multiple stored procs"
- Next in thread: Stephen: "Re: Report with multiple stored procs"
- Reply: Stephen: "Re: Report with multiple stored procs"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 19 Oct 2004 11:51:40 -0400
If you want to use a stored procedure as the source for your report, the
first thing you need to do is to create a query that will return the data
you need. (You can have it return only the data you need for this report,
but, if you have other reports that can make use out of the same stored
procedure, then you might want to have the query return fields that aren't
used in this report.)Once you have the stored procedure that returns all of
the necessary records, then use the procedure for your report. Insert a
group based on the OrderID, another by the TaxID and a third by PaymentID.
This should give you the basic structure of your report. If you have
questions about any of the details along the way, feel from to post them.
-- Bob Holmes MCNGP #31 "Telus" <satkins@skircr.com> wrote in message news:oU8dd.26427$z96.16716@clgrps12... > I should have told you before but all the information is from the same > table. Since I designed the DB I have full access to it. I'm just new to > Crystal. Here is a bit of the structure and data: > > TransactionID OrderID TransType TaxID PaymentID ProductID ProductNumber > Amount > 769 200270 PROD 208 1 > 59.91 > 770 200270 TAX 1 208 1 4.19 > 771 200270 TAX 2 208 1 5.39 > 772 200270 PROD 209 2 > 50.00 > 773 200270 TAX 1 209 2 3.50 > 774 200270 PAY 1 > 123.37 > > Where TaxID 1 = GST > TaxID 2 = PST > ProductID = "Product Description" > PaymentID = "Payment type description" > ProductNumber is a way to link taxes/refunds/voids to the original > product. > > Of course the ID fields map to other tables that hold description of what it > is. But basically I need to turn all of this information into a nice > looking invoice. Which shouldn't be this hard but for some reason I'm > having a devil of a time with it. > > Stephen > > "Bob Holmes" <rholmes@REMOVEmmwec.org> wrote in message > news:e$lThydtEHA.1308@tk2msftngp13.phx.gbl... > > Stephen, > > There are a number of options here. You could build your report based > > on the product and then insert subreports for the taxes and the payments. > > Link the supreports to the main report via the OrderID field. If you have > > access to the database, you could build a single query that will bring in > > all of the records at once. I would try something like this: > > Select... > > From Products INNER JOIN Taxes ON..... > > > > UNION ALL > > > > Select... > > From Products INNER JOIN Payments ON... > > > > In the select clause of the first join, I would have a field that defaults > > to "Taxes" and in the second join it would default to "Payments": > > Select Field1, Field2..., "Taxes" as Activity > > From... > > UNION ALL > > Select Field1, Field2..., "Payments" > > From... > > > > This should return all of the records from both the taxes table and the > > payments table for the orderID. You can use the "Activity" field to group > > the records or in formulas for summing the amounts in only those records, > > etc. > > If you need more specific help with one of these concepts, or another > > method, post your questions and someone will try to help. > > > > -- > > Bob Holmes MCNGP #31 > > "Telus" <satkins@skircr.com> wrote in message > > news:Mq7dd.26425$z96.18210@clgrps12... > >> Hello all. I've been tring for awhile now to generate a report that has > >> three stored procedures as its inputs. Basically I need to generate an > >> invoice. Here is what each procedure returns. > >> > >> 1) Products > >> OrderID, ProductNumber, Product Name, Amount > >> > >> 2) Taxes > >> OrderID, TaxName, sum(Amount) > >> > >> 3) Payments > >> OrderID, Payment Name, Amount > >> > >> There are two taxes returned for every order and there could be any > >> number > >> of products and payments made on an order. I've tried using groups and > >> multiple detail sections but both are not what I need. Basically I need > >> a > >> detail section that lists each product and its amount. After all the > >> products are listed I need to list the sum of the taxes group by the tax > >> name (so it shows both separatly). The a list of all the payments made. > >> Ideally I would then have a balance owing in the report or page footer. > >> > >> If someone has/knows of an example of this can you either forward me a > > copy > >> or a link to where it might be. > >> > >> Thanks > >> Stephen > >> > >> > >> > > > > > >
- Next message: Hallgeir: "Crystal Report v Access"
- Previous message: Jodi: "Viewing a report in Crystal 10 Enterprise Professional"
- In reply to: Telus: "Re: Report with multiple stored procs"
- Next in thread: Stephen: "Re: Report with multiple stored procs"
- Reply: Stephen: "Re: Report with multiple stored procs"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
