csv on the server side
On the server side all Pivot.js expects us to provide is a CSV file. In our project this is dynamically generated from the contents of a Mongo database. As Mongo is a NoSQL is does not store data in flat tables like a traditional database. Instead it stores data in a document like structure, so we have to flatten out the data into rows before converting it to CSV. For example if you had a Mongo record for a book the same document could contain details for each edition of the book. If we wanted to convert this record to CSV we would need to repeat the book details for the record for each edition to flatten out the data.
An added bonus of generating these CSV endpoints is that a user can use tools other than Pivot.js if they wish. They could download the CSV into their favourite spreadsheet programme for example.
flexible but easy for the end user
Once the raw CSV files are available it is time to wire these into Pivot.js. This involves telling Pivot.js about the columns in our CSV data and the summaries we want. Using display functions do such things as create links and format numbers nicely. We can also generate columns from the values of other columns and summary values thanks to pseudo columns. This was very flexible although we did find that we could not perform any calculations based on the differences between a current and last row as we found the rows were not being passed into the functions in order.
A concern we had initially was that this solution might be a bit slow given the large amount of data in our project. The Pivot.js demo site shows a CSV file of five thousand rows being manipulated and coping well. In our project we found that despite the dynamic calculations taking place Pivot.js performed very well with page generation taking no more than a few seconds at the most.
We’re pretty impressed with Pivot.js. It is a powerful tool, yet it is possibile to use it to make a system more useful to an end user without making it complex to use. If you are developing a solution that uses it then it is best to to take some time to learn about the library and experiment with it before diving in so that you can get the most out of it. Now that we have tried one project with the library we are thinking about how it could be useful to future projects too.