Solved

Crystal v8.5 fromula

Posted on 2008-06-26
23
219 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
ID: 21879300
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
ID: 21882549
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
ID: 21882930
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
ID: 21883215
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
ID: 21883293
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
ID: 21883320
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
ID: 21883326
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
ID: 21884062
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
ID: 21884184
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
ID: 21884236
Now I am getting the error that "The result of selection formula must be a boolean"
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 42

Expert Comment

by:frodoman
ID: 21884345
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
ID: 21884595
My mistake, your formula works but the Select formula (for the group names) does not.
0
 

Author Comment

by:SAW56
ID: 21884618
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
ID: 21884694
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
ID: 21885033
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
ID: 21885447
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
ID: 21885548
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
ID: 21885678
It worked...thank you.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 21886469
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
ID: 31471133
Thank you....it worked.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

15 Experts available now in Live!

Get 1:1 Help Now