Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Crystal Function Problem

Posted on 2014-03-28
Medium Priority
373 Views
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
Question by:RickCooper
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 5
• 5
• 3

LVL 101

Expert Comment

ID: 39961425
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

LVL 35

Expert Comment

ID: 39963662
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

LVL 1

Author Comment

ID: 39966027
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

LVL 101

Expert Comment

ID: 39966471
Where does the TotalM value come from?

mlmcc
0

LVL 1

Author Comment

ID: 39966633
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

LVL 101

Expert Comment

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

mlmcc
0

LVL 35

Expert Comment

ID: 39966829
> 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

LVL 1

Author Comment

ID: 39968686
Hi James,

I think your right that I have been misunderstanding the 'WhilereadingRecords'
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

LVL 35

Expert Comment

ID: 39968899
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

LVL 1

Author Comment

ID: 39968937
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

LVL 35

Accepted Solution

James0628 earned 2000 total points
ID: 39969018
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

LVL 1

Author Closing Comment

ID: 39969051
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

LVL 35

Expert Comment

ID: 39969651
Glad to hear that it worked.

James
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirementsâ€¦
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater â€¦
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrasâ€¦
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produâ€¦
###### Suggested Courses
Course of the Month9 days, 10 hours left to enroll

#### 610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.