By using this website, you agree to our Terms of Use (click here)
Hello everyone,
I added this parameter box to my report in Report designer to prompt the user for the date range in months.
Name: PeriodFrom
View name: =Report.GetFieldSchema('GLHistory.FinPeriodID')
Default Value: =Report.GetDefExt('RowBatchReport.TranPeriodID')
- I would like to format the @PeriodFrom in the textbox to display in this format 02-2018-August. Currently i can only display 02-2018. How can I format this date.
- I would also like the from period to default to the start of the current financial period. In this case 01-2018-July instead of 02.2018.August. How can I do that?
Thank you.
Hi fnshem,
Just to make sure, it seems like you're talking about the display of the financial period value BEFORE you actually run the report.
If that's the case, then I'm pretty sure that it will require some programming modifications because the financial period lookup is something coded into the Framework, not something coded into the Report. At least that's my understanding of it.
I meant the way the financial period is displayed in the page header for the report. Currently, I can display it as shown below, however I would like to include the month.
Ah, ok, good. Then I think I can help here.
Try this formula in the Value field in Report Designer:
=Format('{0:MM-yyyy MMMM}',CDate(Left(@PeriodID,2)+'/1/'+Right(@PeriodID,4)))
I know it's a little on the long side, but the reason I like this method is that you can copy/paste the format in other situations. All you have to do is change the "MM-yyyy MMMM" portion to fit the specific situation.
Let's say the period is August.
M would display 8
MM would display 08
MMM would display Aug
MMMM would display August
Let me know what you think.
Thank you Tim. I worked with the formatting., however, there are still some issues.
- Our financial year starts in July, therefore the ID for July is 01-2018. Therefore the format returns 01-2018 January which is not the expected date.
May be you could advise me if I took the wrong approach. I am currently trying to group the net sales on a monthly basis that is total sales for July, August ....etc.
Thank you.
How would I be able to add say 3 months onto your following code. My parameter is called @StartPeriodID.
=Format('{0:MM-yyyy MMMM}',CDate(Left(@StartPeriodID,2)+'/1/'+Right(@StartPeriodID,4)))
I've also gone round in circles trying to =Format('{0:MM-yyyy MMMM}',CDate(Left(@StartPeriodID,2)+'/1/'+Right(@StartPeriodID,4))+3). I can get the number to appear using Cint(Left(@StartPeriodID,2)+3), however the trouble arrive again when I try to format that into a month.
i.e Format('{MMM}',Cint(Left(@StartPeriodID,2)+3))
|
|||
Lastly, for some reason I can't use =Report.GetPeriodDescription(@StartPeriodID) or =Report.GetPeriod(@StartPeriodID) at all in Report Designer, which would have been the simple answer.
Please any advice would be useful.
@nicksm I think you're on the right track with CInt, but shouldn't the +3 be outside of the CInt function, not inside it?
Ah, I see. Then just stop using a fiscal calendar and use a regular calendar instead! Just kidding 🙂
Hmmm, there might be a way to have it read from your fiscal calendar period descriptions, but I'm not sure how to do it.
What about wrapping everything in an IIf statement (also known as an IF statement, but Acumatica adds an extra "i" for some reason), and do this logic:
- If the month number is less than or equal to 6, then just add 6 to the month number
- Otherwise, subtract 6 from the month number
It would be a messier formula, but I think it would get the job done.
HI Tim,
Could you please suggest me how to format this below one. I tried different ways as per your previous suggestion but i failed to get the expected output.
My financial period : 11-2018 MAY , 12-2018 - JUNE , 01-2019- july, 02-2018 - Aug ...........
In my financial statements coulmn set i want to print first column as
my start period say "01-2019"
JULY For this =Report.GetPeriodDescription(@StartPeriod) , JUNE (How to print this as per my period description),
01-2019 =Report.FormatPeriod(@StartPeriod) , 12-2018 =Report.FormatPeriod(@StartPeriod,-1)
Hi Siva,
So you want to run the report for 01-2019 and have, say, four columns print with the following headings?
JULY AUGUST SEPTEMBER OCTOBER
You say that =Report.GetPeriodDescription(@StartPeriod) is gettting the description correctly for JULY but not the other months?
HI Rodman,
I tried with your other comments related to this and Its working fine .
Thanks.
Online Members
