x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 379

# Crystal Function Problem

Hi
I have a problem where I need to work out percentages in a report.
I have the following formulas in the header:

Build
``````//Build
StringVar array Keys;
NumberVar Array Amts;
NumberVar Qty;

Qty :=0;
if {Documents/Document/DetailData/DetailLine/Material_requirements.DetMatStockCode} like "CH*" then
Qty := tonumber({Documents/Document/DetailData/DetailLine/Material_requirements.DetMatUnitQtyReq});

if {Documents/Document/DetailData/DetailLine/Material_requirements.DetMatStockCode} like "FI*"
Then
Qty := tonumber({Documents/Document/DetailData/DetailLine/Material_requirements.DetMatUnitQtyReq});

Local StringVar Key := "" & {Documents/Document/DetailData/DetailLine/Material_requirements.DetMatStockCode};
Local NumberVar Amt := Qty;

if not(Key in Keys) then
( redim preserve Keys [count(Keys)+1];
redim preserve Amts[count(Keys)+1];
Keys[count(Keys)]:= Key;
Amts[count(Keys)]:= Amt );
Amts [1]
``````

TotalW
``````WhilePrintingRecords;
Shared NumberVar TotalM;
Shared Numbervar TotalQty;

TotalQty:=0;
TotalQty:=tonumber({Documents/Document/DetailData/DetailLine/Material_requirements.DetMatUnitQtyReq})*100;
If TotalM >0 then
TotalQty:=TotalQty/TotalM;

round(TotalQty)
``````

Display
``````//Display
WhilePrintingRecords;
Shared Numbervar TotalM;
NumberVar Array Amts;
TotalM:= Sum (Amts)
``````

In the group Header I have the TotalW Formula.

This works fine when you run a single report but does not return the correct values when running multiple reports.

i think the problem is in the Build Formula as it reads all the records in before calculating the required amounts.

Is there any other way to achieve the result when printing multiple records.
Percentage.rpt
0
RickCooper
• 5
• 5
• 3
1 Solution

Commented:
WHat are the formulas trying to do?

I think what you want is for the build formula to be in the group header and to execute it in the printing pass (WhilePrintingRecords) rather than when reading the records.  It is my understanding that variables assigned during the reading pass are not guaranteed to retain their value later in the report.

mlmcc
0

Commented:
mlmcc may be right about using WhilePrintingRecords instead of WhileReadingRecords in @Build.  I seem to recall trying to do something similar once (gather data during the reading phase, to be used during the printing phase), and it didn't work.

But your first problem is that you only have @Build in the report header.  mlmcc suggested moving it, but didn't explain why.  The report header is only produced once, at the very beginning of the report, so that formula will only see the first record.  It will not be evaluated for any other records.

Since you're referencing fields in that formula, I would guess that @Build should be in a detail section.  But if those fields are "group level" fields (eg. DetMatUnitQtyReq should only be included once for each DetMatStockCode), then you'd put @Build in the appropriate group header (or footer), instead of a detail section.

Similarly, since @TotalW references a field (DetMatUnitQtyReq), I would normally expect the formula to be used at the detail level, instead of a group level (eg. in a group header), but maybe the DetMatUnitQtyReq value in a single record is actually the total for a group.

James
0

Author Commented:
Hi,

I am trying to work out the percentage of material required.
To do this I total this field (While Reading the Records)
{Documents/Document/DetailData/DetailLine/Material_requirements.DetMatUnitQtyReq}
to give me TotalM

I then Multiply reach row by 100 to get TotalQty
{Documents/Document/DetailData/DetailLine/Material_requirements.DetMatUnitQtyReq}
* 100

Finally Divide one by the other.

TotalQty:=TotalQty/TotalM;

There might be an easier way than the one I am using.

Thanks
0

Commented:
Where does the TotalM value come from?

mlmcc
0

Author Commented:
Hi
It's on the first paragraph of my post.
{Documents/Document/DetailData/DetailLine/Material_requirements.DetMatUnitQtyReq}

I total the above field and call this TotalM

Thanks
0

Commented:
Your formula doesn't have it.  Also TotalM is a shared variable so it can't be totaled in the WhileReadingRecords pass.

mlmcc
0

Commented:
> To do this I total this field (While Reading the Records)

I think you may be misunderstanding something.  Using WhileReadingRecords in a formula does _not_ mean "do this once for each record".  CR processes reports in passes.  One pass is when it's reading the records.  Another pass is when it's printing the records.  Using WhileReadingRecords tells it to evaluate that formula during the reading pass, but exactly when the formula will be evaluated, and how many times, is determined by where you place it in the report, and how you reference it.

Since you put Build in the report header, it will only be evaluated once, at the very beginning of the report.  So, Build will only see the values in the first record.  That's it.  It won't be evaluated for any other records.  If you want Build to see the values in each record, you need to move it from the report header to the detail section.  Make sure that it's only in the detail section.  If you put it in the detail section, and leave it in the report header too, it will be evaluated twice for the first record.

And, as already mentioned, you may need to use WhilePrintingRecords in Build, instead of WhileReadingRecords, so that all of your formulas are evaluated during the same pass through the report.

> I then Multiply reach row by 100 to get TotalQty

No, you don't.  You have TotalW in the report header and in a group header, so it will only be evaluated for the first record in the report, and then for the first record in each group.  If you actually want to "multiply _each_ row by 100", then TotalW needs to be in the detail section.

But TotalW doesn't really seem to make sense anyway.  All you do is multiply a field by 100 and put the result in TotalQty, and then divide TotalQty by TotalM, but TotalQty is not any kind of total.  It's just the value from that one record.

If you're trying to do what I think you're trying to do, then it's probably just not going to work.  If Build is supposed to be saving the values from every record, then it has to be evaluated in the detail section, as the records are read, and it won't have all of the values until the last record has been read.  If it could be evaluated during the reading pass and the values in the variables would be retained for the printing pass, then it might work (once you move Build to the detail section), but I don't think variable values are retained between passes.  But you can simply move the Build formula and see what you get.

James
0

Author Commented:
Hi James,

i will move the Build to the detail part of the record.
As you suggest in the that paragraph I need to have the values of the last record before I can calculate the percentages and then print them.

I will see if I can move the formulas around and get it to work.

Thanks
0

Commented:
Looking at your Build formula and the way that you're using the Keys array, it appears that you could have more than one record with the same DetMatStockCode, and you only want to include the quantity once for each DetMatStockCode.

If so, that does complicate things.  Without that condition, you could probably just use a regular CR summary.

You may have to use a subreport to calculate the total for you.  You could put a subreport in the report header and it would read the same data and save the total in a shared variable, which you could then use in the main report.  Subreports can be inefficient, but if you're only looking for a grand total, then you'd only need one subreport, so it shouldn't be too bad, unless you have a lot of data (basically, you'd be reading the same data twice, once in the main report and once in the subreport).

What is your datasource?  Your "table" name looks like a file path.  Depending on your datasource, you may be able to use a SQL Expression to calculate the total.  That's basically a sub-query that gets added to the main report query, to pull in additional information.  That would probably be more efficient than a subreport in most cases, although if you only have one subreport (to calculate a total), then it may not make much difference either way.

James
0

Author Commented:
Hi James,

I have been left this problem by an outgoing consultant. what I thought would be a simple fix is not as it seems.

The datasource is a file path which is generated by the ERP software.
I never thought of a subreport to get the total value.
i will give that a try as everything else I have tested only works if you print one report.

Thanks
0

Commented:
When you say "only works if you print one report", I assume that you really mean "record", not "report".  It would work with one record because the first record is available in the report header (assuming that your Build formula was located there), and your total is just the figure from that record.  But for any more records than that, you won't have the total until later.

For the subreport, you could start by importing your current report as a subreport, and put it in the report header.  If your report has parameters, you can link the parameters in the main report to the ones in the subreport.  Your Build formula would be in the detail section of the subreport (and only the detail section), and then you'd put your Display formula in the report footer of the subreport, to set your shared variable (TotalM).

James
0

Author Commented:
James solution to use a subreport solved my problem.

While it did not fix my original question without his help I would have been at this for days.
0

Commented:
Glad to hear that it worked.

James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.