Jump to content
  • 0

Workaround To Save Only The Date Portion Of The Timestamp For Pivot Tables



Now that Caspio has been nice enough to add pivot tables to reports, I went to build an attendance report that I had been waiting to build.  My non-profit education group needed to check students in to an after-school program, then have a real-time view of who was checked in that day, and how many/which sessions each student had checked into over time.


Perfect application for a simple pivot table, right? Each student is checked in by clicking on their name on a drop-down list and clicking Save.  I wanted the date to be automatic to save check in time and preserve accuracy. 


Columns would be the date of the visit, rows would be the student name or ID for the pivot table.  But there was a catch.  If I used the timestamp field to record the date it was saved as mm/dd/yyyy hh:mm:ss.  Then if I used that value for my column in the pivot table, each second was given its own column.  I wanted each date to be a column.


After a couple of hours of looking for an answer online and in documentation I found none, but eventually figured it out, and want to share it with the community.  The workaround that I figured out was to create a second data field called override date in the table in date/time format.  I then fill that field in the data input form on the Configure Screens page of the Web Form Wizard using an external parameter and a label format that I found buried in one of the reports:  [@cbTimestamp*].  Click on the thumbnail below to view the attached jpeg for a screenshot.



So now this field only contains the mm/dd text data from the timestamp and when I use it as my column, any student who was clocked in that calendar day shows up in that column of the pivot table. 


Hope this helps someone.

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

I mistyped - I meant to say that "So now this field only contains the mm/dd/yyyy text data from the timestamp"  (not mm/dd)


You can format the pivot table to only display the mm/dd/yyyy date as mm/dd but the pivot table uses the mm/dd/yyyy information to group the data.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...