| Author |
Topic |
|
JeniQ
Starting Member
29 Posts |
 Posted - 2009-06-08 : 09:17:29
|
| I am new to SQL and SSRS. I am attempting to update an existing report. The bulk of the changes are pretty straightforward. In order to achieve one of my goals, however, I need to change this Expression. Unfortunately, I'm not exactly sure what this Expression is doing. Can someone help translate this into plain English for me? I would really appreciate it!=IIF(Fields!NextFundingAmount.Value>0, Format( DateAdd( "d", -1, CDate( CStr(DatePart("m", DateAdd("m", 3, Fields!NextFundingEndDate.Value)))+ "/1/"+ CStr(DatePart("yyyy", DateAdd("m", 2, Fields!NextFundingEndDate.Value))) ) ) , "d"), "") |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-08 : 09:25:47
|
1. It adds 2 months to the date, pulls the calculated year.2. It adds 3 months to the date, pulls the calculated month.3. It subtracts 1 day from the date above, ie the last day of previous month of the above date.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
JeniQ
Starting Member
29 Posts |
Posted - 2009-06-08 : 09:37:21
|
| So, essentially it's finding 60 days from the original date? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-08 : 09:47:00
|
No. The last day in the month two months from now.If now is June 8, it finds Aug 31.Then it looks like it only return the day part due to the FORMAT (... , "d") thingy. It returns 30 for june (30 days in august).
E 12°55'05.63"N 56°04'39.26" |
|
|
|
JeniQ
Starting Member
29 Posts |
Posted - 2009-06-08 : 10:07:45
|
| Hmm. The starting date is always the last day of the month, so I think it is effectively 60 days from the starting date. |
|
|
|
JeniQ
Starting Member
29 Posts |
Posted - 2009-06-09 : 15:25:13
|
| Peso, I just realized that I read your message way too fast. You are saying that the code is using "NOW" to find the date, but I thought the code was using NextFundingEndDate to find the date. The NextFundingEndDate field is typically the last day of the month for our purposes. For example, when we run the report next month, the NextFundingEndDate field will be set to July 31, 2009. The output will be September 30th, is that correct?However, if I understand this right, if I picked any date in July, 2009, the output would be September 30th, right?Thanks, I really appreciate your mentoring. JeniQ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-09 : 16:32:19
|
Yes, that is correct.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
JeniQ
Starting Member
29 Posts |
Posted - 2009-06-09 : 16:37:00
|
| Hooray! |
|
|
|
JeniQ
Starting Member
29 Posts |
Posted - 2009-06-09 : 16:38:23
|
| Does anybody care? Can I show off what I did today?=IIF (Fields!NextFundingAmount.Value>0, (IIF(Fields!FinalInvoiceTerm.Value="60", Format( DateAdd( "d", -1, CDate( CStr(DatePart("m", DateAdd("m", 3, Fields!NextFundingEndDate.Value)))+ "/1/"+ CStr(DatePart("yyyy", DateAdd("m", 2, Fields!NextFundingEndDate.Value))) ) ) , "d"), (IIF(Fields!FinalInvoiceTerm.Value="90", Format( Dateadd("d", -1, CDate( CStr(DatePart("m", DateAdd("m",4, Fields!NextFundingEndDate.Value)))+ "/1/"+ CStr(DatePart("yyyy", DateAdd("m", 3, Fields!NextFundingEndDate.Value))) ) ) ,"d"), "Inv. Terms <> 60 or 90") ))), "") |
|
|
|
|
|
|
Translating DateAdd/DatePart Code