Solved

Crystal report formula

Posted on 2013-12-02
18
1,006 Views
Last Modified: 2013-12-04
I have a report where I'm trying to use a formula to look in the details section for a variable and pass that to the header as a declaration numbervar.

Then in the footer I want to print a message based on what the declaration is in the header

I'm attaching the report here.

Vendor Flag Checker (in the detail section)
Vendor Flag Starter (header)
Vendor Flag Display (message in footer)
test formula (test formula to test Vendor Flag Starter)

I can't get it to work for the Vendor Flag Checker (header) to declare the value to test formula (footer)

Anyone who can help me?
EIS-Order-Confirmation-EE.zip
0
Comment
Question by:Gerhardpet
  • 8
  • 6
  • 4
18 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39691704
You can upload rpt files directly

You need to add WhilePrintingRecords to the formulas.

Vendor Flag Starter
WhilePrintingRecords;
global numbervar perferedflag;
perferedflag := 2;

Open in new window


Vendor Flag Checker
WhilePrintingRecords;
global numbervar perferedflag;

if {INVENTORY.PREFERRED_VENDOR} = "9MMAG" then 
    perferedflag := 1

Open in new window


Vendor Flag Display
WhilePrintingRecords;
global numbervar perferedflag;

if  perferedflag = 1 then 
     "Please note: unless otherwise stated below the order line, that the planning, controlling and execution of all the processes at maxon motor are in accordance with the ISO9001 standards"

Open in new window



mlmcc
EIS-Order-Confirmation-EE.rpt
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39692722
Still not working.

Did the formulas as per your suggestions and it does not print the message.

Also testing with this formula I get 0 as a value

whilereadingrecords;
numbervar perferedlflag;
perferedlflag 

Open in new window


Appreciate your help Michael!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39693059
You need to name all the variables the same.

YOu have both perferedlflag  and perferedflag
 
mlmcc
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39693131
Yes notice that in the test formula I had the spelling wrong. Now I have double checked and all spellings are correct but still no luck.

Not working. As far as I know the test formula should return a value of 2 and it is 0
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39693208
Did you try the report I uploaded?

mlmcc
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39693239
Yes just now and get the error below. Before I was just testing your formulas in the original report

Also my reports have to be in Crystal 10. If they have been updated in a newer version they won't work.

error 732
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39693827
My report was saved in CR XI.  It shouldn't matter but it is possible.

Upload your latest version.

mlmcc
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39694342
Here is the latest version

This is for an old Sage ERP using run time crystal 10 and if reports are edited with a newer version I get errors
EIS-Order-Confirmation---Plain-P.rpt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39694393
Don't suppress the formulas.  They tend not to execute.
If you don't want to display anything use this trick

WhilePrintingRecords;
global numbervar perferedflag;

if {INVENTORY.PREFERRED_VENDOR} = "9MMAG" then 
    perferedflag := 1;

""

Open in new window


THe formula now calculates what you want but it displays a null/empty string.

Change your test formula to use the WhilePrintingRecord.  If you evaluate it in the Reading Records pass the other formulas haven't been evaluated yet.

mlmcc
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Author Comment

by:Gerhardpet
ID: 39695886
The formulas are working now. But still one small problem remaining. I want to print the display formula in the Page Footer. It prints on the Page Footer on the first page but not the second page.

I have a Page Footer where it prints other content. It prints on all pages but my display formula will only print on the first page. The display formula is in the same Page Footer.

Any ideas why?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39696084
No idea

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 39696110
You are talking about the {@Vendor Flag Display} formula, correct?

 You set the variable in the detail section, so the result that you get from that formula in the page footer will depend on the values in INVENTORY.PREFERRED_VENDOR in the detail records on each page.  If that's not what you're seeing, I don't know why.

 FWIW, I did notice that {@test furmula} (which is also in the page footer) is using WhileReadingRecords, while the other formulas that use that variable are using WhilePrintingRecords, so the results that you get from {@test furmula} may not match what's in the other formulas.

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 39696175
Actually, now that I look at it again, since you only set the variable if PREFERRED_VENDOR has a specific value, the result that you get from {@Vendor Flag Display} in the page footer will depend on what was in PREFERRED_VENDOR in all of the records up to that point, not just the records on each page.

 James
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39696207
James,
I think you are correct. When I use this test formula it declare a 1 on the first page and zero on the second page.

WhilePrintingRecords;
global numbervar perferedflag;
perferedflag

Open in new window


On the order I only need to have 1 SKU where PREFERRED_VENDOR = "VENDOR" and all other SKU's could be "VENDORXX". When the variable finds one record = "VENDOR" then it should display the messages on all pages.

Does that make sense? Do you have any idea how I could make this work?
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 39696376
In your report, you have PREFERRED_VENDOR = "9MMAG", not PREFERRED_VENDOR = "VENDOR".

 Ignoring that for the moment, can the records for a single vendor take more than 1 page?  And you may have only 1 record where PREFERRED_VENDOR has the target value, which could be on any of those pages?

 If so, one way to handle that would be to create a formula like this:

if {INVENTORY.PREFERRED_VENDOR} = "9MMAG" then
    1
else
    0


 Then your display formula would be something like:

if Sum ({@formula created above}) > 0 then
     "Please note: unless otherwise stated below <rest of your message>"


 That assumes that there is only one vendor on the report, so you're taking the summary of the first formula for the entire report.  If there could be multiple vendors on a report and you want a separate flag for each vendor, you'd need to group the report by the vendor and change the Sum to be for the group, instead of the entire report.  In that case, you'd also have to have each vendor start on a new page.

 James
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39696448
There can be multiple vendors on the report and I only want PREFERRED_VENDOR = "9MMAG"

Will the Sum formula work then?
0
 
LVL 1

Author Closing Comment

by:Gerhardpet
ID: 39697281
I can't believe how much time I spent on the report.

James, your idea works just like I wanted and was easy to do. Thanks for your help.
0
 
LVL 34

Expert Comment

by:James0628
ID: 39697306
You're welcome.  Glad I could help.

 James
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 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

19 Experts available now in Live!

Get 1:1 Help Now