Crystal Reports How to filter based on current period + 3 months

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: How to filter based on current period + 3 months

Author	Message
gen8888 Newbie Joined: 08 Oct 2012 Location: United States Online Status: Offline Posts: 5	Topic: How to filter based on current period + 3 months Posted: 08 Oct 2012 at 10:29am
	Hello all, I have a situation where I'm using Crystal Report 2008 which is connected to ECC6 Function Module. In the Function Module there is a field called: T_MRP_TOTAL_LINES.PER_SEGMT which lists Fiscal Months such as: 11/2012, 12/2012, 1/2013, 2/2013 etc. What I'm looking to do is display the data in the Function Module only for Current Fiscal Month and the next 3 months. Is it possible to setup a formula to filter based on the PER_SEGMT field to derive the current fiscal month and the next 3 months? The end result example what we'd like to bring into crystal report is below: 1/2013 2/2013 3/2013 4/2013 Field Supply 2 5 1 3 Field Demand 1 2 9 1 (Formula)Total 3 7 10 4 Thank you in advance! -Andy
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 08 Oct 2012 at 11:09am
	I assume that your T_MRP_TOTAL_LINES.PER_SEGMT field is a string type. You can either convert this to a date field and then use that in the select expert date(replace({T_MRP_TOTAL_LINES.PER_SEGMT},"/","/1/")) in dateserial(year(currentdate),month(currentdate),1) to dateserial(year(currentdate),month(currentdate)+ 3,1) or you can convert the date into a text and use that in the select expert totext(currentdate,'M/yyyy')={T_MRP_TOTAL_LINES.PER_SEGMT} or totext(dateadd('m',1,currentdate),'M/yyyy')={T_MRP_TOTAL_LINES.PER_SEGMT} or totext(dateadd('m',2,currentdate),'M/yyyy')= {T_MRP_TOTAL_LINES.PER_SEGMT} or totext(dateadd('m',3,currentdate),'M/yyyy')={T_MRP_TOTAL_LINES.PER_SEGMT} or totext(dateadd('m',4,currentdate),'M/yyyy')={T_MRP_TOTAL_LINES.PER_SEGMT} Edited by DBlank - 08 Oct 2012 at 11:10am
	IP Logged

gen8888 Newbie Joined: 08 Oct 2012 Location: United States Online Status: Offline Posts: 5	Posted: 08 Oct 2012 at 12:11pm
	Thank you for the quick reply Dblank. Yes T_MRP_TOTAL_LINES.PER_SEGMT is a string type. I may not need to convert to a date format, I was able to find 2 other fields called SORT_DATE and AVAIL_DATE which is the converted PER_SEGMT field in calendar date. So PER_SEGMT: 1/2012, 2/2012, 3,2012 is displayed for SORT_DATE as: 09/29/2012, 11/03/2012, 12/01/2012. How would the formula from above look like to ensure I capture the correct fiscal periods of 1/2012, 2/2012, 3,2012 as it is alittle tricky when different fiscal periods can be in the same month. Example fiscal period 2/2012 call fall between the range of calendar months 10/1/2012 to 11/1/2012. Thank you for your help! -Andy Edited by gen8888 - 08 Oct 2012 at 12:11pm
	IP Logged

gen8888 Newbie Joined: 08 Oct 2012 Location: United States Online Status: Offline Posts: 5	Posted: 10 Oct 2012 at 5:57am
	Hello, I can't seem to get the formula syntax correct in the Expert > Record. Can someone please double check this. Here is what I've tried: Field Name: BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.AVAIL_DATE Expert > Record > Formula: date(({BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.AVAIL_DATE},"/","/1/")) in dateserial(year(currentdate),month(currentdate),1) to dateserial(year(currentdate),month(currentdate)+ 3,1) Thank you, -Andy
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 10 Oct 2012 at 7:20am
	what is the value and data type in the BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.AVAIL_DATE field?
	IP Logged

gen8888 Newbie Joined: 08 Oct 2012 Location: United States Online Status: Offline Posts: 5	Posted: 12 Oct 2012 at 5:18am
	Hi Dblank, the data type for field is String. I was able to get the formula to work by doing the following: {BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.AVAIL_DATE} in dateserial(year(currentdate),month(currentdate),1) to dateserial(year(currentdate),month(currentdate)+4,1) Now that I have that in place I have a question regarding how to capture any records prior to current date and bucket/sum them up into what we call "Past Due"? So it would look something like this along with what was captured for Current Month + 3 months. Past Due = (any months prior to 1/2013) Past Due 1/2013 2/2013 3/2013 4/2013 Field Supply 15 2 5 1 3 Field Demand 10 1 2 9 1 Balance 25 3 7 10 4 Thank you! -Andy Edited by gen8888 - 12 Oct 2012 at 5:45am
	IP Logged

gen8888 Newbie Joined: 08 Oct 2012 Location: United States Online Status: Offline Posts: 5	Posted: 15 Oct 2012 at 4:18am
	Hello, I've tried to create a formula called "Past Due" but can't seem to get the results I need as displayed above. I can't seem to get a Total/Sum record instead just gives me independent values. I also have not included the logic to first check to pull records for those that are less than Current Date. Can someone please check if i'm on the right track. WhilePrintingRecords; Global NumberVar PASTDUE := 0; PASTDUE := PASTDUE +({BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.RECEIPTS}); Thanks,
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum