Skip to main content
Solved

Calendar Object for Reporting

  • 22 December 2022
  • 3 replies
  • 161 views

AWelty24
Jr Varsity II
Forum|alt.badge.img

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:

 

Best answer by aaghaei

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

 

View original

lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • December 22, 2022

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.


AWelty24
Jr Varsity II
Forum|alt.badge.img
  • Jr Varsity II
  • December 30, 2022

@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
Captain II
Forum|alt.badge.img+10
  • Captain II
  • December 30, 2022

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

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings