?
Solved

Crystal Function Problem

Posted on 2014-03-28
13
Medium Priority
?
369 Views
Last Modified: 2014-04-01
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
WhileReadingRecords;
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]

Open in new window


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)

Open in new window


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

Open in new window



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
Comment
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
13 Comments
 
LVL 101

Expert Comment

by:mlmcc
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

by:James0628
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

by:RickCooper
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
New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

 
LVL 101

Expert Comment

by:mlmcc
ID: 39966471
Where does the TotalM value come from?

mlmcc
0
 
LVL 1

Author Comment

by:RickCooper
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

by:mlmcc
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

by:James0628
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

by:RickCooper
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

by:James0628
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

by:RickCooper
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

by:
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

by:RickCooper
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

by:James0628
ID: 39969651
Glad to hear that it worked.

 James
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

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

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

Join & Ask a Question