subreport question

[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

 

[lbass]

I think you could do this by grouping on sales order in the main report and in the subreport, and linking the subreport to the main report on the sales order field. Then in the main report, insert a second group header section and in the section expert, use the direction key to toggle it so that the new section becomes group_header_a. Then place the subreport in group_header_a and use the group_header_b for the main report group header fields.

-LB

 

[santosh1]

LB,

Thanks for your suggestions.

I tried implementing your suggestion by putting sub-report in group_header_a and the main
report in group_header_b. Linked sub-report to the main report by Sales Order. It did not
work correctly. It displays just one Sales Order report even if there are multiples Sales Order.
So, I followed another method and it works correctly but still has one glitch. The zero-qty item for each sales order
appears repeatedly in the main report for each sales order item in the main report. Here's what I did.

I put 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.

 

[lbass]

Let me slightly change my earlier suggestion.

1-Group on sales order in the main report and in the subreport, and link the subreport to the main report on the sales order field.

2-In the main report, place the Sales Order, Item, and Qty fields in the detail section, and do the same in the subreport. In the subreport, suppress all sections except the detail section.

2-In the main report, place the subreport in the Sales Order group header. This will display the Sales Order group corresponding to the main report Sales Order number and the details for this group.

3-The details section of the main report will display the items with quantities > 0.

The report will look like your planned display. It works when I test it.

-LB

 

[santosh1]

LB,

It worked great. Thanks much for your help. 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

 

[lbass]

I am confused by your references to &quot;Report 1&quot;, &quot;Report 2&quot;, etc. Isn't this one report with different groups for each sales order? Also, why are you putting this formula in the page header instead of in a repeating group header? And is this in the main report? It would be easier to help if I understood your overall design better.

-LB

 

[santosh1]

LB,

The last one, I got it resolved. Thanks much for your help.