- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- Date Range and Format
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Date Range and Format
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 09-12-2007 3:54 AM
Post Author: cht104
CA Forum: Formula
I'm new to Crystal Reports so please bear with me. I know this is easy and I'm just overlooking something obvious. I've got my report formatted and working except for the getting the date range or prompt to work and the format of the date itself. In my db, I"ve got a business_date field. I've got that showing line by line in the report as yyyymmdd, but would like to be formatted as "Sept 11, 2007" if possible. Also, for the date range, do I create a new formula or a new parameter field? How exactly is that written out? What other fields do I need to make this work?
Thanks
- SAP Managed Tags:
- SAP Crystal Reports
Accepted Solutions (0)
Answers (14)
Answers (14)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: SKodidine
CA Forum: Formula
I am sorry but I have no clue of how Micros works or what it is. You could try posting the question in some of the other forums and see if anyone has an answer.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: cht104
CA Forum: Formula
If I use this start_date / end_date command, Micros is happy but the link the to my business_date field isn't there. How can I change this to work like your command to change the business_date to a date?
"Period From : " + {@Start_Date}[6 to 10] + '/' + {@Start_Date}[1 to 4] + " To : " + {@End_Date}[6 to 10] + '/' + {@End_Date}[1 to 4];
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: SKodidine
CA Forum: Formula
When you say it is working in CR, is it bringing up the correct records within your parameter date range? If so, then I am not sure what is going on with Micros.
Try changing the last sentence to something like:
date(tonumber({svc_at_dtl.business_date}[1 to 4]), tonumber({svc_at_dtl.business_date}[5 to 6]), tonumber({svc_at_dtl.business_date}[7 to 8])) in minimum({?date_range}) to maximum({?date_range})
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: cht104
CA Forum: Formula
{svc_at_dtl.svan} in ["6036281297496447218", "6036281364474453041", "6036281475774461110", "6036282939574448316", "6036283115974448365", "6036283168796454200", "6036283395374462621", "6036283428674464130", "6036283753674445017", "6036283917774448418", "6036284153974449933", "6036284173996439566", "6036284257574445663", "6036284361474459493", "6036284371896470723", "6036284564874451909", "6036284758774459807", "6036284826974450872", "6036285282174468325", "6036285293874448033", "6036285529874453024", "6036285878174449855", "6036285999874448376", "6036286146296452491", "6036286186574458053", "6036286295596460760", "6036286335874446410", "6036286646674450703", "6036286674874445497", "6036286684874459826", "6036286879696436611", "6036286931974449860", "6036287191974445657", "6036287211774459063", "6036287254874446061", "6036287383874459108", "6036287395174475302", "6036287623696439776", "6036287911274449966", "6036287957774450009", "6036287972296473818", "6036289199196440994", "6036289336796478839", "6036289461796466359", "6036289792974471912", "6036289991196439695", "6275291111939468538", "6275291157455722999", "6275291342555724487", "6275291349939479559", "6275291437655724303", "6275291762455755275", "6275291867155724609", "6275291881655723946", "6275291886155724011", "6275292136155723957", "6275292195855724215", "6275292343855800229", "6275292363155724577", "6275292365155724637", "6275292431555731180", "6275292566755724381", "6275292632355723225", "6275292782455724172", "6275292851255723933", "6275292878655723921", "6275292953555723341", "6275293164155723919", "6275293169955723998", "6275293186455724649", "6275293318855723908", "6275293356655724478", "6275293373655724684", "6275293393739479064", "6275293553155724455", "6275293563655724004", "6275293586555778116", "6275293685955724201", "6275293757339475631", "6275293831955724141", "6275293834955730584", "6275293839562464447", "6275293884455723803", "6275293891639475878", "6275294142955724627", "6275294225939469203", "6275294244755724273", "6275294288755724162", "6275294326839466482", "6275294372355724667", "6275294446455724442", "6275294544655723239", "6275294595362458066", "6275294789855747474", "6275294965255727665", "6275295235155724432", "6275295284562503051", "6275295464555724584", "6275295543955724533", "6275295627755723584", "6275295787355723841", "6275295794955723386", "6275295812555723983", "6275295843555791857", "6275295862755722759", "6275295886755724259", "6275295914155723433", "6275295926255723304", "6275296134855724618", "6275296221455724401", "6275296317755723398", "6275296367355723312", "6275296369339471746", "6275296395339476301", "6275296663855724390", "6275296685255723246", "6275296714755724469", "6275296726455767720", "6275296746555724281", "6275296765355724086", "6275296772362502797", "6275296786862497240", "6275296997255724379", "6275297253355723475", "6275297266655724182", "6275297333955724555", "6275297497155723555", "6275297524455723179", "6275297573255724675", "6275297577455724220", "6275297741455812692", "6275297792755724410", "6275297869455724110", "6275297869455724110", "6275297899755723655", "6275297937155723570", "6275298123755730444", "6275298141555724499", "6275298269755791964", "6275298321455723972", "6275298361655724339", "6275298483355724658", "6275298658455722739", "6275298713155724108", "6275298747639512868", "6275298786555723464", "6275298854355724511", "6275298949755724320", "6275298993755724244", "6275299172655724547", "6275299224655724095", "6275299261439479425", "6275299271562505976", "6275299287155767711", "6275299417555724234", "6275299445555724363", "6275299463755727679", "6275299497755724052", "6275299561855724508", "6275299721255723338", "6275299743555724319", "6275299745855724293", "6275299755255769069", "6275299792555723668"]anddate(tonumber({svc_at_dtl.business_date}[1 to 4]), tonumber({svc_at_dtl.business_date}[5 to 6]), tonumber({svc_at_dtl.business_date}[7 to 8])) in {?date_range}
I dont' know if my problem is with the "and", running 2 different things in here or just the date prompt. Without the date prompt, I get my number information correctly
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: SKodidine
CA Forum: Formula
Cut and Paste your whole record selection here and also indicate on which statement you are getting the error.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: cht104
CA Forum: Formula
When I put the last command in there, the report prompts and works inside Crystal, but when I try to run it in my Micros software I get error in formula <Record Selection> ... A date range is required here
I do have other stuff in my record selection - I only printing certain #'s in the report. I put an "and" before my date command. Is that right?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: SKodidine
CA Forum: Formula
First, do you have a date range parameter set up? When you run the report, does it ask you to enter two dates? If no, then you start on that first. If yes, then in Crystal Reports, in the record selection formula workshop (click on Report | Selection Formulas | Record), type in:
business_date >= totext(minimum{?date_range},"yyyymmdd")
and
business_date <= totext(maximum{?date_range},"yyyymmdd")
OR this:
date(tonumber([1 to 4],tonumber([5 to 6]),tonumber([7 to 8])) in {?date_Range}
By placing one of the two statements above, in the record selection formula, CR will fetch only those records that have a business-date between the date range that you have entered in the parameter.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: cht104
CA Forum: Formula
I'm so confused. Do I convert these inside the parameter or in a seperate formula? I've got the parameter set up, but don't know if I even have it right. I've changed this so many different ways and times that I'm pretty sure that I've made it worse.
I'm converting the business_date to date in another formula to get the format in the transactions correct. Will adding this mess with that?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: SKodidine
CA Forum: Formula
1. I am assuming you have a parameter that accepts two dates to be used as the date range to select records.
2. You will have to convert both the beg and end dates to string to compare with your 'business_date' or convert 'business_date' to date and compare.
You can convert the parameter beg and end dates in your selection criteria type in:
business_date >= totext({?beg_date},"yyyymmdd")
and
business_date <= totext({?end_date},"yyyymmdd")
The other way would be to convert 'business_date' to date by using:
date(tonumber([1 to 4],tonumber([5 to 6]),tonumber([7 to 8])) in {?beg_date} to {?end_date}
Either way, I think the data access will be slow to say the least as it does type conversion prior to selection.
Try it and see if it works.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: cht104
CA Forum: Formula
I'm at crunch time and can't get anything to work. I can get several options to prompt or print a date range, but no matter what dates I key in, I get all transactions in the database. I believe my business_date field is a string.
How can I make my date options to look at my business_date field and only display or print what I want. I've got to have this report working by end of today.
Here's the 3 formulas that I had to start with: Date_Range - "Period From : " + {@Start_Date}[6 to 10] + '/' + {@Start_Date}[1 to 4] + " To : " + {@End_Date}[6 to 10] + '/' + {@End_Date}[1 to 4]; Start_Date - '01/01/1990' End_Date - '12/31/2099'
It allowed me to key beg and end dates, printed those dates on the report, but displayed everything in my db
When I added the 3 formula's you mentioned above, the report preview prompts for dates, but then again displays everything. When I pull the report up with my POS software, it displays everything and the date fields show "AND Between..." in both the min and max fields.
How exactly should the parameter be set up? What tells Crystal to only search and display what dates?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: cht104
CA Forum: Formula
I'm at crunch time and can't get anything to work. I can get several options to prompt or print a date range, but no matter what dates I key in, I get all transactions in the database. I believe my business_date field is a string.
How can I make my date options to look at my business_date field and only display or print what I want. I've got to have this report working by end of today.
Here's the 3 formulas that I had to start with: Date_Range - "Period From : " + {@Start_Date}[6 to 10] + '/' + {@Start_Date}[1 to 4] + " To : " + {@End_Date}[6 to 10] + '/' + {@End_Date}[1 to 4]; Start_Date - '01/01/1990' End_Date - '12/31/2099'
It allowed me to key beg and end dates, printed those dates on the report, but displayed everything in my db
When I added the 3 formula's you mentioned above, the report preview prompts for dates, but then again displays everything. When I pull the report up with my POS software, it displays everything and the date fields show "AND Between..." in both the min and max fields.
How exactly should the parameter be set up? What tells Crystal to only search and display what dates?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: SKodidine
CA Forum: Formula
Explain some more on what your parameter range is setup as and what/how you are trying to use it. Perhaps we can resolve this if you provide more information so that I can have better idea.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: cht104
CA Forum: Formula
I set up the business_date formula just as you have above. The date format works great, but I'm getting an error when I do the minimum and maximum formula's telling me the field cannot be summerized when trying to do the date range. Maybe I didn't set the range up correctly. Where do I set the selection criteria? It looks like it's working, but it is pulling transactions for all dates... like it isn't looking at my business_field at all.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: SKodidine
CA Forum: Formula
I am assuming that business_date field is not a date field but is numeric. If that is indeed the case then use this formula to display the date as desired.
totext(date(tonumber(totext([1 to 4])),tonumber(totext([5 to 6])),tonumber(totext([7 to 8]))),"MMM Dd, Yyyy"); As for using date range, in the selection criteria you would have a statement such as: in {?Date Range}
To display the dates in the date range parameter, you need to create two formulae such as:
minimum({?Date Range});
and another with maximum({?Date Range})
Place these two formulae on the report to display the two dates of the date range parameter which have been input by the user.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- I have a date column and I want to derive Week range in Technology Q&A
- Change default date format for a language in SAP BTP Workzone in Technology Q&A
- To get records for last 120days(4 Moths) in BODS in Technology Q&A
- Consuming SAP with SAP Build Apps - Mobile Apps for iOS and Android in Technology Blogs by SAP
- How to improve the ADT Class Runner with additional Features in Technology Blogs by Members
| User | Count |
|---|---|
| 87 | |
| 10 | |
| 10 | |
| 9 | |
| 7 | |
| 7 | |
| 6 | |
| 5 | |
| 4 | |
| 4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.