Solved

Crystal Reports - How to extract data from a data string

Posted on 2011-03-07
29
930 Views
Last Modified: 2012-08-13
I have a database column which is a varchar string.  In it, the user stores an alpha string which (somewhere in the string) contains a date (i.e. 03-07-11).  I need to be able to extract the date part of the string to a string variable and use this on a report.
 An example of two of the strings are '03-07-11 DAVISON COMPANY' and 'WORTH VILLAGE 03-03-11.'  In example one, I want to extract '03-07-11' and in example two, I need to extract '03-03-11.
I appreciate your help.
0
Comment
Question by:mbroad02
  • 16
  • 11
  • +1
29 Comments
 
LVL 1

Expert Comment

by:dbradley626
Comment Utility
If it is for SqlClient you can use the SqlConnectionStringBuilder class

 string spliter
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Can there be other -'s or numbers in the string?

Can there be strings that look like but aren't dates like

'WORTH 83-14-99 VILLAGE 03-03-11'

mlmcc
0
 
LVL 1

Expert Comment

by:Hath1ASC
Comment Utility
You would need to write a custom function in the Crystal Formula Editor, using your own custom logic and some crazy kind of combination of Crystal functions such as InStr, Mid, NumericText (to test the values between the hyphens returned from Mid), or Split and various Crystal Array functions.

Alternatively, if you are using an advanced database, you may be able to use regular expressions or other methods to extract these values into the view that Crystal uses as your datasource.  (We use Oracle Enterprise, and I would probably solve your issue by coding a PL/SQL function that uses some REGEXP_SUBSTR call combined with some error-handling custom logic, returning NULL if unable to extract a single valid date or datestring.
0
 

Author Comment

by:mbroad02
Comment Utility
I am restricted to using a formula in Crystal as my means for extracting the data.  No, there are not otherr numbers in the filed which may resemble a date.  The only contents are an alpha name and the date.   How about we start with idea for a formula which can extract a date at the beginning and end of a string and go from there??

thanks
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
My questions were to clarify the issue.  So lon gas the - is only in the date and all dates have the format as NN-NN-NN
Try this formula

Local StringVar strField := {YourField};
Local NumberVar nbrHyphenLoc;

nbrHyphenLoc := InStr(strField,'-');
If nbrHyphenLoc > 0 then
   Mid(strField,nbrHyphenLoc-2,8)
else
  ""

mlmcc
0
 

Author Comment

by:mbroad02
Comment Utility
This worked GREAT!!  I have one other related question.   After I extract the date , I am finding that users have entered the date in three possible formats: MM-DD-YYYY , MM-DD-YY or YY-MM-DD.   I have handled the first two formats just fine, but HOW do I change YY-MM-DD to MM-DD-YY?
Your help is GREATLY appreciated.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
How can you tell the format it is in?

11-03-09  -  9 mar 2011,  3 sep 2011, 11 mar 2009, 3 nov 2009

mlmcc
0
 

Author Comment

by:mbroad02
Comment Utility
The format is 11-03-09
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
But how do I know what that means?  It is a valid date and can be interpreted in any of the 4 ways I mentioned.  Actually there are 2 more but that would mean the middle value would be the year which in general doesn't make any sense.

WHat I am getting at is if you have a date that someone entered wrong how can you tell it is wrong?  I cant tell if 11-03-09 is year 2009 or 2011

If all dates are supposed to be in 2011 then the 11 field is the year and it could be handled that way.

mlmcc
0
 

Author Comment

by:mbroad02
Comment Utility
The date format is YY-MM-DD.    I need to be able to change YY-MM-DD to MM-DD-YY.  The first two are ALWAY the year in this case.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You indicated above that users are entering data in 3 formats.  Assuming you can somehow differentiate between the formats.  If it is in YY-MM-DD the following will change it

Local StringVar Array DateParts;
DateParts := Split({YourDateFormula},'-');
DateParts [2] & '-' & DateParts[3] & '-' & DateParts[1]

mlmcc
0
 

Author Comment

by:mbroad02
Comment Utility
I will give it a try.
0
 

Author Comment

by:mbroad02
Comment Utility
What is YourDateFormula ??  is this the field containing the backwards date (i.e. 11-01-13)??  Please advise.

thanks
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
YourFormula is just the datefield you are trying to change.  Yes, the field with the backwards date.

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.

 

Author Comment

by:mbroad02
Comment Utility
Ok, we are getting really close to a conclusion here.  
Here is the formula I created from your answer (displayField2 is my backwards date):

Global StringVar displayField2;
Global StringVar displayField3;


Local StringVar Array DateParts;
DateParts := Split(displayField2,'-');
displayField3 := DateParts[2] & '-' & DateParts[3] & '-' & DateParts[1]


Small issue is that I am getting the following error in the formula:
"A subscript must be between 1 and the size of the array" and is it referencing the first DateParts in the above formula.Any suggestions?
thanks
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Do you have NULL fields?

Could some of the data not be in the correct format?


Global StringVar displayField2;
Global StringVar displayField3;
Local StringVar Array DateParts;
DateParts := Split(displayField2,'-');
If UBound(DateParts) <> 3 then
   "Bad Data"
Else
    displayField3 := DateParts[2] & '-' & DateParts[3] & '-' & DateParts[1]

mlmcc
0
 

Author Comment

by:mbroad02
Comment Utility
I see why I had nulls in the field and have corrected it.  Now I have another issue.  The result field now has one of two values:  either a date format of MM-DD-YYYY or a date format of YY-MM-DD.  How can I analyze this field to determine WHICH format I am looking at (so I know if I have to reformat it per your array formaula)??
In other words, is there a formula I could use which would look at this variable field and determine if the field is MM-DD-YYYY or YY-MM-DD?  
The most telltale difference that I can see is that from the 7th position on, the field would eith contain 2 characters (DD) or 4 characters (YYYY).  Any suggestions?

thanks
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
Comment Utility
If they have different formats then you could fix it in a formula

Global StringVar displayField2;
Global StringVar displayField3;
Local StringVar Array DateParts;
DateParts := Split(displayField2,'-');
If UBound(DateParts) = 3 then
     If Val(DateParts[3] > 999) then
          displayField3 := DateParts[1] & '-' & DateParts[2] & '-' & Right(DateParts[3],2)
     Else
         displayField3 := DateParts[2] & '-' & DateParts[3] & '-' & DateParts[1]
Else
    displayField3  := "";

That should give you "" for bad data and NULL and MM-DD-YY for good dates

mlmcc
0
 

Author Comment

by:mbroad02
Comment Utility
Awesome!  thank you.  I will try it and get back to you tomorrow.
0
 

Author Comment

by:mbroad02
Comment Utility
I think that this may solve my issue.  Just one small thing:  
when I add your code to my formula, I get the error "a string is required here" in the line  If Val(DateParts[3] > 999) then


Suggestion?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Where do you set displayField2?  You are splitting it so it needs to be the date field.

mlmcc
0
 

Author Comment

by:mbroad02
Comment Utility
i changed the first formula (which populates the date field) to use a field called displayField instead of displayField2.
The formula which takes the date field out of that to get in proper print format contains:
Global StringVar displayField;
Global StringVar displayField2;
Local StringVar Array DateParts;
DateParts := Split(displayField,'-');
If UBound(DateParts) = 3 then
     If Val(DateParts[3] > 999) then
          displayField2 := DateParts[1] & '-' & DateParts[2] & '-' & Right(DateParts[3],2)
     Else
         displayField2 := DateParts[2] & '-' & DateParts[3] & '-' & DateParts[1]
Else
    displayField2  := "";
displayField2;
0
 

Author Comment

by:mbroad02
Comment Utility
This is the first formula, which populates displayField.  It pulls a user-enetered date out of a text comment field.
Global StringVar strField := {service.dscr};
Global NumberVar nbrHyphenLoc := 0;
Global StringVar resultField;
Global StringVar displayField;
Global StringVar Array DateParts;
Global NumberVar nbrHyphenLoc := 0;

nbrHyphenLoc := InStr(strField,'-');
If nbrHyphenLoc > 0 then
   resultField := Mid(strField,nbrHyphenLoc-2,10);


nbrHyphenLoc := InStr(resultField,'C');
If nbrHyphenLoc > 0 then
    displayField := left(resultField,8)
else
    displayField := resultField;
displayField;
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Change this -  ) is in the wrong place

 If Val(DateParts[3] > 999) then

to
 If Val(DateParts[3]) > 999 then

mlmcc
0
 

Author Comment

by:mbroad02
Comment Utility
OK, almost there.   Check out the attachment.  I have it coded like above (I beleive) but I get NO result.
Date-formula-sample-data.doc
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Where on the report are the formulas?

Try this
Add WhilePrintingRecords; as the first line of formula 1

Add  EvaluateAfter({@Formula1}); as the first line of formula2

mlmcc
Database4.mdb
Database4Report.rpt
0
 

Author Comment

by:mbroad02
Comment Utility
Both formulas are on the group footer where all of the print line detail is located.  Perhaps I need to consolidate formula 2 into the end of formula 1.
Thanks
0
 

Author Comment

by:mbroad02
Comment Utility
It's a thing of beauty!!   I moved Formula 2 to the end of Formula 1 and....IT WORKED!!  Thank you so very much for all of your help!
0
 

Author Closing Comment

by:mbroad02
Comment Utility
Excellent work!  Very patient and detailed in your help!!  thanks again!
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. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
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…

743 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

11 Experts available now in Live!

Get 1:1 Help Now