I have recently started a new project for the Sanitation department in the municipality. The Local Recycling and transfer station where the sanitation truck go to dump trash or recycle has a report system that is a QlickView over their operational system. Their power user saw reports being generated by QlickView and offered we see them as well. We showed him earlier what we can do with Panorama Nova View and SSRS but he didn’t buy it. Having seen what QlickView did for the recycling center, I can see why. Though we didn’t have QlickView to offer him as a set of tools and would have to go to a tender to receive offers to buy it for the project, he was still adamant he wanted QlickView for the reports. Looking at one of the QlickView reports, I couldn’t help but think the styling looked quite familiar to me. So I started thinking – how can I imitate QlickView through Excel 2010?
Above you can see the report from the recycling center. When I looked at the horizontal and vertical slicers and the color scheme (obviously, shown behind the black bars in the picture :)) for the pivot table, I though to myself – that looks very similar to Excel 2010 BI capabilities! Now I knew Microsoft may have also used the idea of in – memory reports just as QlickView did, but now I understood I could use the Excel styling to make it look like a QlickView report as well..
The horizontal slicers in the reports are of: year, quarter, month, day of month.
The vertical slicers are of: City, contractor, driver, type of trash, type of vehicle, day of week, hour of entrance.
The pivot is hour of entrance and month.
I got from my power user data of about four months to play with and I made him this in Excel 2010:
The horizontal slicer is of day of week. The vertical slicer is of contractor (putting the relevant slicers is easy through the pivot table filed list).
The slicers are also connected out of the box and so when I choose a day of week I would only get the contractors working on that day.
The pivot is hour of entrance and month. I also applied the color scheme of conditional formatting in Excel.
The demo I did was based on a PowerPivot cube and so could not enable us a drill through, but I read that if I would base it on a table in my DB, then drill through would not be a problem. I would also like to add that drilling on a particular aggregate cell was not shown to us to be an option on the QlickView report we saw. instead the user was given an additional tab which showed the underlying data of the entire pivot table (meaning the operational data with the same filters as the aggregated data). So a drill through action from a specific cell was a functionality I could offer in Excel 2010 that was not given in QlickView.
I showed it to the customer and he was willing to drop his request we do it in QlickView. So I guess you can use Excel to imitate QlickView.