Graph output incorrect

[BradCustom]

I'm creating a report to show the daily net shipping dollars for a date range. Everything on the report calculates correctly but the chart output doesn't match the Net Shipping dollars formula.

Below is how the report is structured.

Main Report:
Group1: Invoice_Detail.Ship_Date
Group2: Customer.Customer

Fields: Customer_PO, Ship_Date, Quantity, Unit_Price, "Formula" TotalShipDollars {Invoice_Detail.Quantity}*{Invoice_Detail.Unit_Price}
This formula is summerized for each ship day.

Subreport: (this is to capture customer returns)
Group1: Packlist_Header.Packlist_Date
Group2: Packlist_Detail.Packlist_Date

Fields: Customer, Customer_PO, Packlist_Date, Quantity, Unit_Price, "Formula" TotalReturnPrice ({Packlist_Detail.Quantity})*({SO_Detail.Unit_Price})
This formula is summerized for each packlist day.

I then paas the summerized TotalReturnPrice formula to the Main Report with the following formula. whileprintingrecords;
shared currencyvar PassPrice:=Sum ({@Total Return Price}, {Packlist_Header.Packlist_Date}, "daily")

On the Main Report I have the following formula for the passed value "ReturnPass".
whileprintingrecords;
shared currencyvar PassPrice;
PassPrice

This value is used in the formula "NetShippingDollars"
Sum ({@TotalShipDollars}, {Invoice_Detail.Ship_Date}, "daily")-({@ReturnPass})

The two reports are linked by the date fiels.
The "NetShippingDollars" formula works fine. The problem I'm having is when I try to make a Side by Side Bar Chart. The values that are displayed on the chart are the same as the "TotalShipDollars" forumla. The chart is setup to use the "NetShippingDollars" as the value and to change on Ship Date.

I've tried everything I can think of to get the chart ouput correct but so far no luck.

Any help would be greatly appreciated.

 

[lbass]

You need to use a method called charting on print-time formulas, which involves creating two formulas in the main report, both of which should be placed in the group footer where you are displaying {@NetShippingDollars}:

//{@onchgof}:
whileprintingrecords;
shared stringvar onchgof := onchgof + totext({Packlist_Header.Packlist_Date},"yyyy/MM/dd") + "^";

//{@showval}:
whileprintingrecords;
shared currencyvar passprice;
shared stringvar showval := showval + totext(tonumber(Sum ({@TotalShipDollars}, {Invoice_Detail.Ship_Date}, "daily")-passprice)) + "^";

Insert a subreport that uses the Pack_Header table, and in the sub, insert a group on packlist_date. Link the sub to the main report using {@onchgof} as the main report linking field. In the sub, change the record selection formula to:

totext({Pack_Header.packlist_date},"yyyy/MM/dd") in split({?Pm-@onchgof},"^")

Suppress all sections except the subreport footer. Then create a formula, being careful NOT to place it on the report (if you place it on the report it will be unavailable in the chart expert):

//{@showval}:
whileprintingrecords;
shared stringvar showval;
tonumber(split(showval,"^")[groupnumber]);

Then insert a chart in the report footer that uses {Pack_Header.PackList_Date} as the on change of field->group options->on change of day, and {@showval} (check "do not summarize") as the summary field.

-LB

 

[BradCustom]

I have a question about your response because I think I'm misinterpreting what you've written. It's regarding the section:

Insert a subreport that uses the Pack_Header table, and in the sub, insert a group on packlist_date. Link the sub to the main report using {@onchgof} as the main report linking field. In the sub, change the record selection formula to:

After reading your suggestion I think you want me to add a second subreport?

Thanks for your help.

 

[lbass]

I mean for you to insert a new subreport where you will be creating the chart. This is the only way you will be able to use the shared variables (from the original sub and main report) in the chart.

-LB

 

[BradCustom]

I've tried the formulas you described but I can't get it to work and I'm thinking it's because of something I've omitted from my original post.

The Main Report uses the following tables.
Customer
Invoice_Detail
Invoice_Header
So_Detail (which is Sales Order Detail)

The Subreport uses the following tables.
Packlist_Detail
Packlist_Detail_1
Packlist_Header
Packlist_Header_1
SO_Detail
SO_Header

The subreport is linked to the main report by linking the Packlist_Header.Packlist_Date to Invoice_Detail.Ship_Date

I've tried changing your formula so that it uses the Invoice_Detail.Ship_Date but I haven't been able to get it to work.

Any ideas would be greatly appreciated.

 

[lbass]

You can use the Invoice_Detail.Ship_Date. I can't really help without knowing exactly what you did. Also you haven't said what you mean by not getting it to work. I did test this, so know it does work.

-LB

 

[BradCustom]

Sorry for the delay in responding I've been working on some other projects. When I say I can't get it working it's really the step below.

Suppress all sections except the subreport footer. Then create a formula, being careful NOT to place it on the report (if you place it on the report it will be unavailable in the chart expert):

//{@showval}:
whileprintingrecords;
shared stringvar showval;
tonumber(split(showval,"^")[groupnumber]);


The showval field isn't available and it's not placed in the subreport. I've done all of the other steps exactly as you said but for some reason the showval isn't available.

I'm going to continue working on the report now that I have some time to devote to it. Perhaps I've missed something.

Thanks for your help!

 

[lbass]

Try removing the chart subreport and starting from there again. Make sure that you add the {@onchgof} field as the link from the main report in the subreport linking before creating the {@showval} formula in the sub--so that it doesn't inadvertently get selected as the sub linking field. Check the subreport selection formula first and make sure it only says what it's supposed to. Then create the {@showval} formula in the field explorer and do NOT place it anywhere on the subreport--it should not have a checkmark next to it, and then insert the chart. It should appear in the field list. After you add it to the right and run the report, it will disappear from the field list, so you must do things in the correct order to prevent this.

-LB

 

[BradCustom]

I've done exactly what you suggested but the showval field is still not available when I create the chart in the Main Report's footer.

I'm wondering if the reason it's not working is because of the field difference from the Main Report to the SubReport. In the Main Report I'm using Invoice_Header.Ship_Date and in the Subreport I'm using Packlist_Header.Packlist_date.

Below are copies of your formulas in my report.

Mainreport

//{@showval}:
whileprintingrecords;
shared currencyvar passprice;
shared stringvar showval := showval + totext(tonumber(Sum ({@TotalShipDollars}, {Invoice_Detail.Ship_Date}, "daily")-passprice)) + "^";

//{@onchgof}:
whileprintingrecords;
shared stringvar onchgof := onchgof + totext({Invoice_Detail.Ship_Date},"yyyy/MM/dd") + "^";

Subreport

totext({Packlist_Header.packlist_date},"yyyy/MM/dd") in split({?Pm-@onchgof},"^")

/{@showval}:
whileprintingrecords;
shared stringvar showval;
tonumber(split(showval,"^")[groupnumber]);

The two formulas in the main report return the correct value with ^ at the end.

When I add the Subreport I've linked it to {@onchgof} and I did not put a check mark in the box for "Select data in subreport based on field". I placed the chart in the report footer B which is below all of the report summary totals.

Thanks for your help!

 

[lbass]

Why would there be any report summary totals in the chart subreport? Only the chart belongs there, but with a group inserted on the date field. As I said before, go ahead and change the table to the invoice_detail table for the chart subreport, and use that date field.

In the chart sub, did you change the record selection formula? After creating the formula {@showval} in the sub, do you observe that it has no checkmark next to it in the field explorer? Then it should be available in the chart field list--at least UNTIL you add it as your summary field.

-LB

 

[Miklah]

Hi,

lbass, I noticed that your method differ a little from what the document "Charting on Print-time formulas" says. However, I tried both methods but couldn't get a chart printed.

First what I want to do:
-Using Crystal Reports XI
-I'm using a view that I made by Business View Manager
-I have a main report that is grouped by a a field named AREA.
-The details section shows items. These items also has a field TIMESTAMP
-Then I have a subreport that calculates a value and places it in shared numberVar 'count' --> this shared numbervar is the used in the main report in a formula field that is named 'ratio'. --> So an item in the main report now has a timestamp and a ratio.
--> I want to make a chart of type 'stacked bar chart' where on change of is 'TIMESTAMP' and 'AREA' and in the show values there is 'sum of @ratio'. In addition the TIMESTAMP field is printed for every month.

I have (to begin with) tried to get the chart to work with only AREA in the on change of.
I made exactly as the document 'Charting on Print-Time Formulas' says, but I get the error "An array's dimension must be an integer between 1 and 1000". I tried to google this error, but I didn't find anything why this happens in my case.

Then I also tried with your instructions lbass, but again with no success; I get the "a subscript must be between 1 and the size of the array" error.
Above I mentioned that there were differencies to the document.
-->
1) First you said, that the formula fields @onchgof and @showval should be placed in the group footer, but the document says in the details
2)You mentioned that the chart subreport should be linked only with @onchgof, but the document instructs also to link with the showvalue field
3)The show value formuila in the subreport: the document says ToNumber(Split({?Pm-@ShowValue}, "^")[RecordNumber]), but you had the last part instead: [GroupNumber]

Can you tell what am I missing or doing wrong here ?

Regards,

Mikael

 

[lbass]

If you are trying to sum the ratio on change of the area group, then the showval formula belongs in the area group footer, as does the on change of formula. You don't need to link on the show value formula, because you can reference the shared variable formula directly in a subreport formula.

You would run into problems if you have more than 1000 values you are accumulating, since that is the maximum for an array.

It is hard to troubleshoot something as complex as this, so if you need more help, I'd need to see the content of your formulas, including your record selection formulas. I have never tried charting on two on change of fields or creating a stacked bar chart with this method.

-LB

 

[Miklah]

Thanks you for the answer lbass... I got it to work.

I had actually an error in one of my formulas.

Regarding that stacked bar chart, I have now only one 'onChangeOf' field (AREA) and that one showvalue field in the main report

Then in the subreport I simply created the stacked bar chart with TIMESTAMP and AREA in the on change of section and showvalue in the show values section and it works fine.

-Mikael