Formula editor and sub reports 2

[PJOL]

The formula I want to insert on a main report needs to include a field from the sub report, i.e. the Quantity on the main report multiplied by the Sales Price from the sub report. Formula editor in Crystal 8.5 won't let me do it.

 

[lbass]

You need to use a shared variable. In the subreport, create a formula:

whileprintingrecords;
shared numbervar salesprice := <your sales price summary or field>

Place this somewhere on the subreport. Then in the main report, create a formula:

whileprintingrecords;
shared numbervar salesprice;
{table.qty} * salesprice

This formula MUST be in a section below the one in which the subreport is executing.

-LB

 

[PJOL]

Thank you. That worked a treat. Howandever, I have one more stumbling block: I would like to sum the results from the formula. The report is filtered on Product Group and Part Codes within a group. I now have the sub totals (sales price * quantity)per Part Code within a group but would like to sum those sub totals per Group ?

 

[lbass]

Without knowing your report structure, I'll take a guess. If you have a group #2 on part codes, and have the subreport in the part codes group header#2_a, and the shared variable formula in GH#2_b, you could then change the shared variable formula to:

whileprintingrecords;
shared numbervar salesprice;
numbervar cost := {table.qty} * salesprice;
numbervar sumcost := sumcost + cost;
cost //add this so that the calc is displayed instead of the running total

You should also have a reset formula in the GH#1:

whileprintingrecords;
numbervar sumcost := 0;

In the GF#1, place the following display formula:

whileprintingrecords;
numbervar sumcost;

If the salesprice value can ever be null, you would also need a reset formula for salesprice to be placed in the report header AND in GF#2_b (or insert another group header section above GH#2a and place the reset there:

whileprintingrecords;
shared numbervar salesprice := 0;

-LB

 

[PJOL]

Thanks you again: all working now.

The report has a selection criteria for a date range, i.e. 11/10/05 to 17/10/05 which the end user can change accordingly. Is there any way for that same date range to be displayed on the report without manually inserting a text object stating same ?

 

[lbass]

Create a formula:

"For the period: "+totext(minimum({?daterange}),"MM/dd/yyyy") + " to " +
totext(maximum({?daterange}),"MM/dd/yyyy")

-LB

 

[PJOL]

Thank you. The formula works when I substitute the {?daterange} with the appropriate field containing the required date, i.e. {SALES.REQUIRED_DATE}

What I notice though is that if the selection criteria has a start date of the 15th November AND there were no sales for products on that day, then the formula will only output the first day that there were sales on i.e. the formula will output say, 17th November. The 17th is not the start date of the selection criteria though.

I'd like to show the date selection criteria as entered by the user on the report (before refreshing the report)regardless of whether there were sales on the first and last day of the selection criteria.

 

[lbass]

Why aren't you using the parameter? The parameter itself should be placed in the formula. Or isn't it a daterange type of parameter?

-LB

 

[PJOL]

Thanks for the quick response. When I use the parameter in the formula, Crystal doesn't like it. It complains that it's not a valid field. The only way I could get it to work was to use the date field itself. Im using CR 8.5.

 

[lbass]

Of course you can use a parameter in a formula. Is this a parameter created within CR in the main report? Are you creating the formula to display the parameter in the main report by going to the field explorer->formula->new? What is the exact error message? What is the datatype of the parameter?

-LB

 

[PJOL]

I now have the parameter created in the main report and the formula to display the parameter on the main report also. The value type of the parameter is 'Date'.

I have made progress in that the formula has no errors and I get prompted to enter the date range when I refresh the report.The range is displayed ok. I still have the date range in the selection ciriteria (Report, Select Expert). While we can work with this, it would be more user friendly just to punch in the date range when prompted before refreshing the report, i.e. currently entering in the date range with Report, Select Expert and then for the ?daterange formula (when prompted). Is this workable ?

 

[lbass]

First, the formula I gave you is for a daterange parameter, not a date parameter. Try changing the parameter to a date range (datatype = date, allow range values = true) named {?daterange}. Then in the record selection formula, use:

{table.date} in {?daterange}

It sounds like you didn't add the parameter into the record selection formula initially.

-LB

 

[BobSuruncle]

On the Report Menu, Choose Edit Selection Formula, and on the Fly-out menu, select Record.
This is a much better way to edit your record selection, instead of using the Select Expert.
In your Selection Formula find the line where you're hardcoding a date range every time and replace it with your parameter.
For example, if it currently looks like this:
{SALES.REQUIRED_DATE} in Date(2005,11,01) to Date(2005,11,21)
then replace it with this:
{SALES.REQUIRED_DATE} = {?DateRange}


Bob Suruncle

 

[PJOL]

Great. That's sorted now and thanks for the help.

 

[PJOL]

We have a report called our Assembly Line Prep sheet and it's formatted ok except for one detail. At the GH1 level we have our Product Classes, then at the GH3a level we have the Products. Under this header are the Details. What we are trying to achieve is that when the Details for a particular Product reach the bottom of the page, then the Product(GH3a) will automatically start at the top of the next page thus not splitting the Details from one page to the next. This can be achieved to a certain extent with the Properties at the GH3a level in Crystal but it uses too much paper in that it puts the Products on their individual pages. Not environmentally friendly to say the least. Is there another way of achieving this goal ?

 

[BobSuruncle]

Have you turned on the "Repeat Group Header on Each Page" property for group 3?


Bob Suruncle

 

[PJOL]

Thank you. No, that option was not selected, but the 'Keep group together' did the trick. Thanks for pointing me in th right direction.

 

[PJOL]

We have a report with a total that's not an actual field, i.e. it's a sum calculated from sub-totals from other fields. I need to round it up or truncate it in our favour, i.e. 5.1 rounded up to 6 and so on. There is a formula for this in Crystal for fields, but I'm having difficulty applying it to a sum total. Any help appreciated.

 

[lbass]

Please start a new thread since this is an entirely new topic.

-LB