CODEWITHSUNDEEP
foxpro
Dvann
Dvann

Reputation: 9

Visual FoxPro Reporting 2

I am doing Tax Code summarization in visual foxpro report, and I found a way to group the Tax Codes, which is:

First, I query the tables for invoice info and set a dummy variable "det" = 1.

Then I do another query to group up the Tax Codes and set variable "det" = 2.

By adding dummy variable "det", I can differentiate which row is the invoice detail and which row is the Tax Code grouping info.

Finally I UNION them up which mean for every invoice number, I will now have extra rows for Tax Code group.

For example:

inv_no | line | item | taxcode | taxamt

00001 | 1 | AAA | SR | 6 <-invoice info group

00001 | 2 | BBB | TX | 7 <-invoice info group

00001 | 3 | CCC | SR | 6 <-invoice info group

00001 | 999 | ZZZ | SR | 12 <-Tax code group

00001 | 999 | ZZZ | TX | 7 <-Tax code group

Before union, I only have 3 rows for the invoice detail.

In the report layout, how do I display the Tax Code group separately from invoice info group (Detail)? I wish to display the Tax Code group info at the bottom after total amount was displayed.

For the invoice info group in Detail, I already did

IIF(det=1, *display*, "")

so in the report it only display the 3 items in invoice info group.

I tried to add the taxcode variable at group footer but it doesn't display all the tax codes and always display only 1 tax code.

Upvotes: 1

Views: 281

Answers (1)

Tamar E. Granor
Tamar E. Granor

Reputation: 3937

It sounds like you're doing things the way we used to before VFP supported multiple detail bands. If that's the case, use ORDER BY in your UNION to ensure that the det=1 lines come before the det=2 lines, and then group on det in the report.

I wrote about this here

Upvotes: 1

Related Questions