Supress Section Based on Subreport Values

[AngelB]

Hi all.
I'm using crystal 8.5 and I have a simple stock report linked to a subreport by product code. The main report shows products waiting at a particular order stage, the subreport returns the location and quantity of available stock. All of the information is shown in the detail line. I would like to supress the detail line if the quantity in stock is zero. I have created a shared variable and tried to supress on the condition that it is zero, but instead of only supressing the lines where it equals zero, it is supressing all of the detail lines, leaving the report blank. What am I doing wrong? Is there a better way to achieve this result?

Thanks

Geraint

 

[DiggerDog]

Is your sub-report positioned before the detail lines you wish to suppress?

 

[AngelB]

Hi DiggerDog

Thanks for the reply. The subreport is in the detail section alongside the other details I want to supress. I suspect from your reply that the subreport will need to be evaluated first? I could put it in a group header but then it would be out of alignment and I'd really like to have it all in the one row if possible. If a subreport cannot achieve this, do you have any suggestions on what could?

Thank You

Geraint

 

[GJParker]

I think you will need to group the main report by product and place the subreport in the group header. Use shared variables to pass the location and quantity values to the main report and display these in the details section.

your suppression formula should then work ok.

It's just a case then of hiding the sub report in the group header, you can't suppress the subreport otherwise it won't run, instead renove the Can grow option from the subreport and in the subreport itself suppress unused sections.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[Madawc]

In Crystal 8.5, the subreport is evaluated after the rest of the section, and after the decions that the section can be printed. They apparantly fixed this in Crystal 9.

See [

http://www.tek-tips.com/viewthread.cfm?SQID=477236&SPID=782&newpid=782&page=4

] for a work-round.



Madawc Williams (East Anglia)

 

[AngelB]

Thank you all for your replies. I have tried placing the subreport in the group header as suggested and passing the quantity and location as shared variables. All of the quantities now come back as zero and the locations are all null! To completely explain I have posted the code I am using below. The views were created in Microsoft SQL Server 2000:

Main Report View:

SELECT SUM(ORDER_LINES.QUANTITY) as QUANTITY, PRODUCT_HEADER.PRODUCT_CODE,
PRODUCT_HEADER.PRODUCT_DESCRIPTION
FROM ORDER_LINES INNER JOIN
PRODUCT_HEADER ON ORDER_LINES.PROD_RECORD_NUM = PRODUCT_HEADER.PROD_RECORD_NUM
WHERE ORDER_LINES.ORDER_STAGE = 10)
GROUP BY PRODUCT_HEADER.PRODUCT_CODE, PRODUCT_HEADER.PRODUCT_DESCRIPTION

Subreport View:

SELECT PRODUCT_HEADER.PRODUCT_CODE,
STOCK_LEVELS.STOCK_QUANTITY,
STOCK_LOCATION.LOCATION_CODE
FROM PRODUCT_HEADER INNER JOIN
STOCK_LOCATION ON PRODUCT_HEADER.PRODUCT_CODE = STOCK_LOCATION.PRODUCT_CODE INNER JOIN
STOCK_LEVELS ON STOCK_LOCATION.UNIQUE_ID = STOCK_LEVELS.UNIQUE_ID
WHERE STOCK_LEVELS.STOCK_QUANTITY <> 0

When run on their own these views both return valid data. They even return valid data if I link them together in Query Analyzer. The only reason I don't do this is because if a product is available at more than one location, the whole line in the main report is duplicated. I cannot suppress duplicates just in case the next order in line has the same quantity.
The main report is linked to the subreport by product code. The main report contains all products on orders currently at order stage 10. These products may not currently be in stock and so I would expect a stock quantity of zero for some of them. I just can't seem to see why all the quantities are coming back as zero when the two reports are linked.

Thanks again for your help

Geraint

 

[lbass]

Are you saying that the shared variables are returning zero? Make sure that when you created the shared variables in the subreport that you placed them on the subreport canvas. Similarly, in the main report, you must reference the shared variables in the formula in which you use them. Also, in the main report, the shared variables must be used in a section below the one in which the subreport executes.

-LB

 

[AngelB]

Thanks for the reply lbass. I have placed the shared variables in the detail section of the subreport and suppressed the rest of the sections and shrunk them down. In the main report I have inserted a group by product code and placed the subreport in the group header which is then hidden. The shared variables are displayed in the detail section of the main report. The code for the shared variables is simple but I've included it below just in case:

Subreport formulas:
{@loc}
Shared stringvar location := {SUBREPORT_VIEW.LOCATION_CODE}

{@qty_in_stock}
Shared numbervar stock_qty := {MAIN_REPORT_VIEW.STOCK_QUANTITY}

Main report formulas:
{@locat}
Shared stringvar location

{@stock}
Shared numbervar stock_qty

Despite all this the shared variables are being displayed as zero/blank in the main report detail section. I've completely run out of ideas now, I think the user is going to have to put up with seeing the duplicates. Thanks for all your replies.

Geraint

 

[GJParker]

Geraint

I think all you need to do is add WhilePrintingRecords to each of your formulas so that CR knows when to evaluate them. i.e.

{@loc}
WhilePrintingRecords; //add this line to each formula
Shared stringvar location := {SUBREPORT_VIEW.LOCATION_CODE}










Gary Parker
MIS Data Analyst
Manchester, England

 

[AngelB]

Thanks for the reply, but I'm afraid it hasn't made a difference. I think I'll give the user the news.

Thanks anyway

Geraint

 

[GJParker]

Geraint I'd hate for you to give up when this is doable. if you want to e-mail me the report I'll take a quick look for you.

gary dot parker at k2group dot co dot uk

Gary Parker
MIS Data Analyst
Manchester, England

 

[AngelB]

Thanks you for the offer Gary, but unfortunately because I work for the government the information on the report would be deemed sensitive and therefore I can't e-mail it off site. It's not a huge deal if I can't get the report to work this way, because the user can see the data in the way I did it originally. They'l just have to live with the product being printed on every line.

Thanks for your help

Geraint

 

[lbass]

Geraint,

You cannot hide or suppress the section that contains the subreport or it will not execute. What you can do is suppress all of the sections within the subreport, remove the border, and resize it to make it as narrow as possible.

-LB