Solved

Crystal Function Problem

Posted on 2014-03-28
13
353 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
  • 5
  • 5
  • 3
13 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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 34

Expert Comment

by:James0628
Comment Utility
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
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
Where does the TotalM value come from?

mlmcc
0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
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 100

Expert Comment

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

mlmcc
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:James0628
Comment Utility
> 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
Comment Utility
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 34

Expert Comment

by:James0628
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
James0628 earned 500 total points
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:James0628
Comment Utility
Glad to hear that it worked.

 James
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now