This is the first article of a series with the topic of ZeyReports, our own Excel addin to easily create virtually any report for ZeyOS (also third-party integration is simple) and refresh them with just one click at any time. The thought process and struggles during the development process as well as some of the implementation details will certainly prove useful for others in their strife for a better user experience and integration of reporting and business intelligence solutions.
Probably every user of a CRM/ERP system has already encountered the following scenario: You have a lot of data on your platform and you want to create a custom-tailored report to showcase certain aspects of them, such as a monthly revenue report. Usually in such a case, the customers would have two options, none of which are completely satisfying:
Tell the developing company to create a fancy custom report especially for them The result is a nice concise report which is pleasant to look at, but lacks versatility. This is certainly acceptable in special cases, but usually customers desire a catch-all solution. The happiness about such a report turns sour pretty fast when the user realizes that the expected horse turns out to be a one-trick pony. Each new requirement inevitably leads to a new implementation request, so the report again and again requires manual editing instead of automatically providing a versatile interface.
Use standard ways to obtain the raw data, e.g. through a file export Basically, this way is the complete opposite of the former approach. While being quite versatile, it lacks any of the positive features of a custom report (e.g. custom data, layout, special features) and offers little to make up for it. Even when such an export enables the user to gather all data from a specific table or view from your system, it will usually not be enough, since most of the insight is gained by actually linking and connecting the data and creating meaningful mashups for a certain topic in a specialized report. Using standard exports, the task to create links and mashups would remain with the user and any update would again require the users to adapt their data model or to develop their own Excel sheet with sophisticated links (VLOOKUP and the like). This is a path walked by many companies, but one that leads to much more work than necessary especially when the data model changes.
Both ways have major disadvantages, so we tried to find a way to get the requested data to the users in a flexible format and additionally offer means to create arbitrary reports with their desired design of tables, charts, billboards and diagrams.
Of course, we did not want to reinvent the wheel, so we searched for preexisting solutions which we could use right away or at least adapt to our needs.
Generic query interface There are some which try to offer a generic query interface to the data, so that you can basically assemble a sophisticated database query which will return the raw data in the specified format, usually at least XML. This is certainly an interesting solution when the data model is the same for everyone, but will be very complicated or even fail in cases where the data model differs between customers. Imagine as an example that one customer wants to integrate the contact's nickname into the respective table of the CRM, while many other customers do not want that field. Either you integrate it for everybody and let them (de)select it through the query interface or you create a generic table for additional data which then has to linked to the original entity in a complex fashion. Users tend to have very different ideas and requirements for their particular platforms, so a one-fits-all approach will spam the users with unwanted content and/or is likely doomed to fall short of user expectations. Conversely, requiring the users to craft very complex queries is presumptous and will lead to them not accepting the interface and in the end the whole product.
Special business intelligence application Other suppliers of business intelligence solutions offer client-side applications to tailor the exported raw data to the specific requirements for each report. These solutions usually offer fancy-looking charts, pleasurable layout and a neat design for the provided data. Such solutions are usually able to deal with a large variety of data, but occasionally you might still need to ask for a custom integration or special solution. This would only add to the sometimes high costs of such solutions. The biggest drawback is that each and every user of your platform would have to resort to your choice of business intelligence solution or to go on a quest for a suitable one and pave their own way. Both options are not really desirable for the usual customer who just wants a working solution out of the box at a relatively low price.
So there we were, with lots of solutions in the market, but nothing which exactly fits our goal - to offer a generic interface to our system (to query any data) which at the same time could be integrated into the standard environment of the usual customer (to show the data in any way). At this point, also drawing from insight gained from our market research, we decided to develop an Excel addin which would query the customer's platform (running a REST-based API) and enable the user to create their own reports in Excel with pivot tables and charts based on the specified and downloaded data.
More about how we approached this endeavour in the next part of the ongoing series about ZeyReports.
