Solved

Crystal v8.5 fromula

Posted on 2008-06-26
23
218 Views
Last Modified: 2011-10-03
Can you tell me how to create a formula for a field that based on the following information will bring back the name of the group?  For example, all the product types belong to a group called ERP and I would like to create a field for a group name that would look at the product types and batch everything together under the group name ERP.
{PROBSUMMARYM1.CATEGORY} in ["security", "software"] and
{PROBSUMMARYM1.SUBCATEGORY} in ["sap applications - production", "scheduled software"] and
{PROBSUMMARYM1.STATUS} <> "closed" and
{PROBSUMMARYM1.PRODUCT_TYPE} in ["erp_finance", "erp_finance_ap", "erp_hr", "erp_payroll", "erp_supply_chain_inv_mgmt", "erp_supply_chain_purchasing"]
0
Comment
Question by:SAW56
  • 10
  • 8
  • 2
23 Comments
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
Can you assume that the group name is the first 3 characters of the product type as it is in your example or is that coincidence?  If you can't make that assumption then I guess you must have another table that contains a list of product types and what group they belong to?
0
 

Author Comment

by:SAW56
Comment Utility
In most cases the Product Types do not have the same first 3 characters.  For example, we have a product type with the name Basis that belongs to the group Delivery.
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
Okay, so how do you know that Basis belongs to the group Delivery?  Do you have a table that contains a cross-reference?  That would be by far the easiest way to solve this problem.  If you're intending to hard code this information it can be done but it will be a lot more work.
0
 

Author Comment

by:SAW56
Comment Utility
Unfortunately, the groups Delivery and ERP do not reside in the system.  I need some type of formula that I can create that will look at the product types and based on what they are will group them based on the group names (Delivery, ERP, EAM, CCS, etc.)
0
 

Author Comment

by:SAW56
Comment Utility
For example, the product types Basis, Bus_obj_ace belong to the Delivery group while the product types Erp_payroll, Erp_powerplant belong to the ERP group.  The product types are in the system but not the groups.
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
The formula to return group names is very simple, just long.  You just need to keep adding all of the possible results to this formula and it will return ERP, Delivery, etc.






select {PROBSUMMARYM1.PRODUCT_TYPE} 

case "erp_finance", "erp_finance_ap", "erp_hr", "erp_payroll", "erp_supply_chain_inv_mgmt", "erp_supply_chain_purchasing":

   "ERP"

case "Basis":

   "Delivery"

default:

   "Unknown";

Open in new window

0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
Followup - I think the above will work in 8.5

If it doesn't, then I can help you with if..then statements but they'll be longer...
0
 

Author Comment

by:SAW56
Comment Utility
Yes, could you please help.  I am getting an error message that "A number, currency amount, boolean, date, time, date-time, or string is expected here".
0
 
LVL 42

Accepted Solution

by:
frodoman earned 500 total points
Comment Utility
Let's start very simply then:

if {PROBSUMMARYM1.PRODUCT_TYPE} = "erp_finance" or
   {PROBSUMMARYM1.PRODUCT_TYPE} = "erp_finance_ap" or
   {PROBSUMMARYM1.PRODUCT_TYPE} = "erp_hr" then
      "ERP"
else
      "Other";


Make sure that formula works and then we can add to it.
0
 

Author Comment

by:SAW56
Comment Utility
Now I am getting the error that "The result of selection formula must be a boolean"
0
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

 
LVL 42

Expert Comment

by:frodoman
Comment Utility
Let's back up - where are you putting this formula?  It sounds like you are putting it into the selection criteria - this needs to be a new standalone formula (just create a new formula and name it anything you want).  After pasting in the formula drag the field onto your report.
0
 

Author Comment

by:SAW56
Comment Utility
My mistake, your formula works but the Select formula (for the group names) does not.
0
 

Author Comment

by:SAW56
Comment Utility
This is the information I need to incorporate into my formula (based on the formula provided earlier).  Can you somehow get it to work?
select {PROBSUMMARYM1.PRODUCT_TYPE}
case "erp_finance", "erp_finance_ap", "erp_hr", "erp_payroll", "erp_supply_chain_inv_mgmt", "erp_supply_chain_purchasing", "erp_powerplant":
   "ERP"
case "Basis", "bi", "isis-formatting", "isis-printing", "isis-online billing", "isis-messages", "isis-brochures", "isis-file transfer", "isis-workflow", "bus-obj-ace", "bus-obj-dqxi", "bus-obj-monthly update", "bus-obj-point of entry val", "bus-obj-us presort", "cronacle core software", "cronacle server agent",:
   "Delivery"
case "ccs_fica", "ccs_front_office", "ccs_back_office", "ccs_device_management",:
   "CCS"
case "eam_edeo", "eam_business_services", "eam_gen", "eam_managed_programs",:
   "EAM"
default:
   "Unknown";
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
I need to understand what you are trying to do with this information.  You should be able to create a new formula using the text you've just posted and then put this formula on your report to see ERP, Delivery, CCS, EAM, or Unknown for every record.  Once that's done you can use this formula to sort, group, etc.

I apologize, but I'm just not clear on what it is you need to do other than what I've just stated?
0
 

Author Comment

by:SAW56
Comment Utility
Everytime I go to create a new formula field using the formula provided I get the error that "A number, currency amount, boolean, date, time, date-time, or string is expected here" right after "cronacle server agent",:.  The cursor flashes between the comma and colon.  Does this information help?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Assuming that is apaste for the formula
Get rid of the commas before the : at the end of each case.

mlmcc
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
Agree - it's just a syntax error.  You want to *keep* the colon, just kill the comma that preceeds it.
0
 

Author Comment

by:SAW56
Comment Utility
It worked...thank you.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
SAW56 : This should have been split or full credit to frodoman.  

Frodoman answered the original question.  My comment only resolved a syntax error when you implemented frodoman's answer.

I'll reopen the question so you can accept the correct comment and expert as the answer

You should use http:Q_23519473.html#a21883320 or  http:Q_23519473.html#a21884184

mlmcc
0
 

Author Closing Comment

by:SAW56
Comment Utility
Thank you....it worked.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

772 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

10 Experts available now in Live!

Get 1:1 Help Now