help needed with sub-report

[santosh1]

I have a sub-report on the group header that displays zero quantity items.

eg. Sales Order Item Qty
---------- ------ -----
1 T-Shirt 0
5 Poster 0
18 Pen 0

The main report displays non-zero qty items.

eg. Sales Order Item Qty
---------- ------ -----
1 Folder 20
5 Bag 100
18 Banner 50
19 Keychain 30

When I run the report, I want the report to display by sales order and display zero qty item for each
sales order.

eg. Report 1

Sales Order Item Qty
---------- ------ -----
1 T-Shirt 0
1 Folder 20

eg. Report 2

Sales Order Item Qty
----------- ------ ------
5 Poster 0
5 Bag 100

eg. Report 3

Sales Order Item Qty
---------- ------ -----
18 Pen 0
18 Banner 50

eg. Report 4

Sales Order Item Qty
---------- ------ -----
19 Keychain 30


Currently, the report displays all zero qty items in Report 1. It doesn't display zero qty item by sales
order. How can I fix this situation? Thanks much.

eg. Report 1

Sales Order Item Qty
---------- ------ -----
1 T-Shirt 0
5 Poster 0
18 Pen 0

1 Folder 20

eg. Report 2

Sales Order Item Qty
----------- ------ ------
5 Bag 100

eg. Report 3

Sales Order Item Qty
---------- ------ -----
18 Banner 50

eg. Report 4

Sales Order Item Qty
---------- ------ -----
19 Keychain 30

 

[Madawc]

You could do it by putting a version of the subreport in the detail line. It's a slow method but does the job. Note that in Crystal 8.5, you can pass a value like 'Order' using Subreport Links under Edit. Note that Crystal automatically puts a selection command in the subreport's selection formula, which would need to be removed for a different sort of report where the selection was not wanted.

You could also pass back a value using Shared Variables, though for zeros this is not likely to be necessary.

Madawc Williams
East Anglia, Great Britain

 

[santosh1]

Madawc,

Thanks for your suggestions.

I tried implementing your suggestion by putting
the subreport in detail section and linked the sub-report by Sales Order to the
main report. It works perfectly and displays the zero qty item by sales order as I
wanted. However, there's one glitch still. The zero-qty item for each sales order
appears repeatedly in the main report for each sales order item in the main report.


eg. sub-report has zero-qty item as below:


Sales Order Item Qty
---------- ------ -----
1 T-Shirt 0
1 Pant 0

and the main report has non-zero qty items as below:

eg. Sales Order Item Qty
---------- ------ -----
1 Folder 20
1 Bag 100


Currently, my report displays as follow.

Sales Order Item Qty
---------- ------ -----
1 T-Shirt 0
1 Pant 0
1 Folder 20

1 T-Shirt 0
1 Pant 0
1 Bag 100

Instead I want it to display as:


Sales Order Item Qty
---------- ------ -----
1 T-Shirt 0
1 Pant 0

1 Folder 20
1 Bag 100

How can I fix this problem? Thanks again.

 

[Madawc]

Sorry, I should have thought of that. Right-click on the detail and select 'create section below'. Move the subreport to the second detail section, and suppress it if it is not the first for that order: i.e. put
{your.order}=previous({your.order})
in the suppression formula for section formatting.

Alternatively, group detail lines by order (if you're not doing so already) and put the subreport in the group header.

Madawc Williams
East Anglia, Great Britain

 

[santosh1]

Madawc Williams,

It worked great. Thanks much. I have another simple
question.

On page header, I have a formula field @SalesTaxExempt.

If OnFirstRecord then //First record
If {tpoPrintPOLineWrk.ItemID} = "800" OR Next({tpoPrintPOLineWrk.ItemID}) = "800" Then
"NO"
Else
"YES"

Else If OnLastRecord Then //Last record
If previous ({tpoPrintPOLineWrk.ItemID}) = "800" OR {tpoPrintPOLineWrk.ItemID}="800" Then
"NO"
Else
"YES"

Else If Previous({tpoPrintPOLineWrk.ItemID})="800" OR next({tpoPrintPOLineWrk.ItemID}) = "800" Then
"NO"
Else
"YES"

Depending upon the detail line item, I want to this field to display 'YES'(if itemID <>800)
or 'No' (if itemID = 800).

eg.
sales order: 1
Tax Exempt: YES

ItemID

1
2
3

sales order: 2
Tax Exempt: NO

ItemID

800
4
5

The above formula displays &quot;YES/NO&quot; correctly however, when there are multiple pages report for the same
sales order when there's lots of items, the formula works on individual page basis. i.e. If report page1
doesn't have item '800', then it displays YES, and if page 2 has item '800', the page 2 report header
displays 'NO'. How would I be able to fix this situation so that it will check the whole report (page 1 -
all) before deciding to print YES/NO on the page header exactly the same in all report pages. Thanks.

eg.

sales order: 3
Tax Exempt: NO

page 1

ItemID

9
4
2

page 2

ItemID

6
8
4

page 3

5
6
800

 

[Madawc]

That's a new topic, it should really be a new post.

Madawc Williams
East Anglia, Great Britain