AUGForums.com

An Acumatica User Group

  • Forums
  • Podcast
  • Blog
  • Live
  • Login
  • Start Here
  • Rolodex
  • Courses
  • Consulting
  • Register
Acumatica Forums

By using this website, you agree to our Terms of Use (click here)

  • Overview
  • Member List
  • All-Stars
  • Stats
  • Recent Posts
  • Help
Forums
AUG Forums
Acumatica Report De...
Format of the finan...
 
Notifications
Clear all

Format of the financial period from the textbox.  

    Last Post
RSS

fnshem
Posts: 12
 fnshem
August 7, 2017 2:58 am
(@fnshemerirwemkenga-com)
Member
Joined: 4 years ago

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')

  1. 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. 
  2. 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.

10 Replies
Tim Rodman
Posts: 2519
 Tim Rodman     ★★ All-Star ★★
August 9, 2017 3:49 am
(@timrodman)
Over 200 Posts
Joined: 5 years ago

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.

Reply
fnshem
 fnshem
(@fnshemerirwemkenga-com)
Joined: 4 years ago

Member
Posts: 12
August 9, 2017 4:33 am
Reply toTim RodmanTim Rodman

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.

report format
Reply
Tim Rodman
Posts: 2519
 Tim Rodman     ★★ All-Star ★★
August 9, 2017 9:52 pm
(@timrodman)
Over 200 Posts
Joined: 5 years ago

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.

Reply
fnshem
 fnshem
(@fnshemerirwemkenga-com)
Joined: 4 years ago

Member
Posts: 12
August 10, 2017 4:35 am
Reply toTim RodmanTim Rodman

Thank you Tim. I worked with the formatting., however, there are still some issues.

  1. 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.

Reply
NickSM
 NickSM
(@nicksm)
Joined: 9 months ago

21-50 Posts
Posts: 29
November 1, 2020 11:59 pm
Reply toTim RodmanTim Rodman

@timrodman

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))

 
An error has occurred while the Format(Const({MMM}), CInt(BinaryOp + (Left(Identifier(@StartPeriodID), Const(2)), Const(3)))) function was being executed:
'Input string was not in a correct format.'

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.

Reply
Tim Rodman
 Tim Rodman
    ★★ All-Star ★★
(@timrodman)
Joined: 5 years ago

Over 200 Posts
Posts: 2519
January 24, 2021 2:17 am
Reply toNickSMNickSM
Tim Rodman

@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?

Reply
Tim Rodman
Posts: 2519
 Tim Rodman     ★★ All-Star ★★
August 10, 2017 11:49 pm
(@timrodman)
Over 200 Posts
Joined: 5 years ago

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:

  1. If the month number is less than or equal to 6, then just add 6 to the month number
  2. Otherwise, subtract 6 from the month number

It would be a messier formula, but I think it would get the job done.

Reply
Siva Katakam
 Siva Katakam
(@sivakatakam)
Joined: 11 months ago

6-20 Posts
Posts: 13
January 30, 2019 2:20 am
Reply toTim RodmanTim Rodman

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)

 

                                                                                                                     

Reply
Tim Rodman
Posts: 2519
 Tim Rodman     ★★ All-Star ★★
February 16, 2019 9:16 pm
(@timrodman)
Over 200 Posts
Joined: 5 years ago

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?

Reply
Siva Katakam
Posts: 13
 Siva Katakam
March 15, 2019 12:56 am
(@sivakatakam)
6-20 Posts
Joined: 11 months ago

HI Rodman,

I tried with your other comments related to this and  Its working fine .

Thanks.

 

 

Reply
Tim Rodman liked
  All forum topics
  Previous Topic
Next Topic  
  Forum Statistics
11 Forums
1,707 Topics
7,873 Posts
8 Online
1,159 Members

Latest Post: GI with customer and customer location Our newest member: thanh thuy Recent Posts Unread Posts

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Online Members

Acumatica Forums

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2021 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.

‹›×

    ‹›×