Skip to main content
Solved

Calendar Object for Reporting

  • 22 December 2022
  • 3 replies
  • 147 views

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:

 

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.

@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

@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


Terms & ConditionsCookie settings