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.
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.
Question
budkroll
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
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.