Solved

Crystal v8.5 fromula

Posted on 2008-06-26
23
220 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

803 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