It’s now close to 4 years since Remedy Smart Reporting was first introduced as part of Remedy 9 to deliver a modern web and mobile user experience for ITSM in-app reporting. One of the big design choice we made for Remedy Smart Reporting was adhering to Remedy permission model when extracting data. This design also aligned with Smart Reporting as an in-app reporting tool and helped Customers avoid setting up parallel permission model for data extraction in Smart Reporting (eventually helping customers with faster RoI). AR JDBC was technology enabler to ensure what users see on Remedy screen is what users see in Smart Reporting reports, dashboards and so on.
Smart Reporting wins hands down when it comes to self-service reporting, intuitive data model, slick UI and great visualization. But as it happens often in our lives, some things are too good to be true. While underlying AR JDBC had distinct advantages when compared to data warehousing or ETL, this layer is proprietary to Remedy. Which meant, each capability in this AR JDBC is purpose built code by BMC engineers. On other hand, as users maturity rose, users started looking for complex querying capabilities in AR JDBC; something equivalent to database’s native query capabilities. However, combining database’s query capabilities with AR JDBC was not possible in our view.
Alternatively, we kept enhancing AR JDBC functions incrementally in Smart Reporting releases. And soon realized that R&D resources for bridging this gap between AR JDBC functions and database’s native functions were a bottleneck. We started thinking of some new approach for expanding support for functions in Smart Reporting. While ultimate aim was to enable users create advanced reports with use of complex queries, we also wanted to leverage underlying database’s query capability for this (and without compromising Remedy permission model).
What excites us now with Smart Reporting 19.02 version
I am happy to share that in 19.02 Smart Reporting release, you have now access to many more database’s native functions.
This is done with a tweak in querying in AR JDBC. In summary, you first define in AR form as which all database function you plan to use (we have done this for you). And then in Smart Reporting go ahead and use these functions with a query predecessor string ‘DBFN’. When AR JDBC sees DBFN string followed by defined database function, it will send this query straight to underlying database and retrieve results. A short and simple use case demo for this can be seen below where ‘datepart’ function for Microsoft SQL server is used for extracting ‘Year’ and ‘Week of the year’ from a date field.
More specifics of this new capability is available in Bhushan Desai's blog post that includes list of database functions supported for Freehand SQL as well sample AR JDBC compliant SQL https://communities.bmc.com/groups/get-started-with-smart-reporting/blog/2019/02/25/database-functions-support-in-smart-reporting-with-enhanced-ar-jdbc
We are looking forward to you trying this new capability in 19.02 release. As always, we are welcoming your questions, comments (and appreciation ).
I can’t conclude this post without acknowledging Get Started with Smart Reporting that keeps us on toes with different use cases. Also, I want to acknowledge many of the technical experts at BMC who responded to these challenges and realized the ‘unthinkable’. Thanks to all of you!
PS: Smart Reporting 19.02 release delivers a lot more new capabilities than just the new powerful query capability in AR JDBC. Check this blog post from Suresh Kumar Palivela where he has mentioned other exciting new capabilities in Smart Reporting in 19.02 release
Smart Reporting 19.02 Release Notes - https://docs.bmc.com/docs/itsm1902/19-02-enhancements-841096841.html#id-19.02enhancements-BMCRemedySmartReportingenhancements
Remedy Suite 19.02 List of Enhancements - https://docs.bmc.com/docs/itsm1902/19-02-enhancements-841096841.html