Solved

Calendar Object for Reporting

  • 22 December 2022
  • 3 replies
  • 139 views
AWelty24
Rank
Userlevel 3
Badge

This is a question related to reporting for date-based activities.

In an ideal situation I’d be able to create the below Pivot Table showing a column for each day in the upcoming work week. If there is an activity scheduled against that day, I’d be able to display it in my pivot table. If there is no activity on that day, the day column would just act as a “placeholder.”

This would be to help users interact with the data and having a Calendar feel.

I see a possibly related question here that looks unresolved.

Just curious if, within the Generic Inquiry used to generate the pivot table, if I cross join to:

  • CS Calendar?
  • Field service calendar?
  • Payroll calendar?

I’d only want to do this in order to generate the upcoming calendar dates. 

Or maybe there’s an add-in or another way to solve this.

Would be great to hear any ideas.

Arline

Ideal layout concept is sketched out here:

 

icon

Best answer by aaghaei 30 December 2022, 21:02

View original

3 replies

lauraj46
Rank
Userlevel 7
Badge +7

Hi @AWelty24 ,

I think you might be able to get what you need by starting with the DateInfo table and do a Left join to your details records.  The DateInfo table has one record for every date from 1/1/1970 to 6/6/2079.

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
AWelty24
Rank
Userlevel 3
Badge

@lauraj46 Thank you! Didn’t know about that table. I added it. It does seem very particular about the joins. I added it as the primary table and left joined to my work order table, which was good. Weirdly, as soon as I started doing conditions & inner joins from the work order table to some sub-tables (like inventory), I no longer got the Date Info table to show ALL dates as I’m intending. But this is very helpful and thank you for sharing that suggestion! 

Arline

aaghaei
Rank
Userlevel 7
Badge +8

@AWelty24 

What you need to consider is the Date field of the DateInfo table has stored DATE ONLY (despite its type which is DateTime) so if you are joining with a DateTime field of another table you will lose some data. So before join you will need to extract the date part of the fields you are joining then apply join/filter.

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved