How Softplan enabled reporting capabilities in its judicial process management app with Power BI Embedded
Microsoft and Softplan, an independent software vendor (ISV), joined efforts to augment one of Softplan’s applications with reporting capabilities, enabled through the adoption of Microsoft Power BI Embedded. The application, called SAJ, is targeted at private companies and public organizations that operate in the Brazilian judicial system.
This report describes the partner scenario and how Power BI Embedded was used to provide the desired reporting features.
- Roberta de Jesus Martins – New Businesses Analyst, Softplan
- Lucas Humenhuk – Technical Evangelist, Microsoft
Softplan is a Brazilian ISV that began in 1990 by developing and selling systems for clinics, stores, and accounting offices. The company grew and expanded until it now has more than 1,500 employees organized in four business units: construction, law, city government, and public management.
Softplan has around 2,000 customers in Brazil and elsewhere. These customers include courts of law, attorneys, public infrastructure and transport organizations, universities, and contractors.
In general, judicial processes are complex. Many of them take months or years to be settled and consist of a series of hearings and procedures with many associated documents. Different areas of law (such as civil, criminal, and family) have different rules and regulations. Cases can be disputed by defendants, causing new hearings and sessions. In the case of Brazil, which has a long-standing culture of bureaucracy in public organizations, the matter is compounded even further.
Softplan developed a system called SAJ (Sistema de Automação Judicial, or Judicial Automation System) to attend to the needs of attorneys, courts of law, and public ministries, making it easier to handle processes and all their associated data. SAJ allows private companies and public organizations that operate within the judicial system to control the status and flow of judicial processes, tracking their lifecycle in an orderly fashion. It has important additional features, such as recording judicial hearings and enabling virtual trials.
SAJ’s main front end is a Windows desktop application written in Delphi. Users of this application are judges, notaries, scriveners, and administrative personnel. Despite excelling in handling judicial processes individually, the system did not provide a way for users to visualize information in an aggregated and consolidated manner.
Softplan initially addressed this need by creating an Excel spreadsheet that queries SAJ’s database, containing charts and graphics to provide consolidated reports. But this has been a reactive process, based on generating and sending spreadsheets on demand, which once ready go through a chain of people until they are delivered to the original requester. This delay clearly compromises decision making.
Softplan decided that embedding reports within the SAJ application would provide a much more integrated and consistent user experience. Information would be available to its users when needed, immediately. This business objective led the team to work on a joint proof of concept (POC) to integrate reports hosted on Power BI Embedded with SAJ.
Solution, steps, and delivery
The customer chosen as the scenario for the POC was TJAM (Tribunal de Justiça do Estado do Amazonas, or Amazonas State Justice Court). This is the first-instance court of law for the Brazilian state of Amazonas.
TJAM’s hierarchy is composed of two main levels: Foro (jurisdiction) and Vara (court). These were established at the start as two roles to be implemented in Power BI Embedded, so that users of the reports would only have access to data suitable to their rank or role.
Between 2014 and 2016, TJAM handled almost 3 million processes. The data associated with these projects is stored in an IBM DB2 database reaching 45 million records.
The main reports used by TJAM (nine in total) were defined as the scope for the POC. These reports are divided into three groups, listed below for reference. Screenshots of prototypes created on Power BI Desktop during the POC are included for illustration purposes (due to confidentiality, judge names are covered by red boxes).
The first group is composed of four reports that are all related to an index called IAD, which stands for Índice de Atendimento a Demanda or Demand Response Index. This index shows the relationship between Cases Received and Cases Filed (IAD = Cases Filed/Cases Received). All four reports show aggregated information. They are:
- IAD, the main report.
- IAD-Variation, showing the variation between IAD values in different points in time.
- Baixados (“Filed”), cases that have been filed and are ready for the next phase.
- Duração (“Duration”), showing the number of days spent at different phases.
Figure 1. IAD Report prototype
The second group is composed of a single report called Fases Processuais (“Procedural Phases”). This reports shows a different view of the number of days for each phase, with a focus on specific judges and some customized filters.
Figure 2. Procedural Phases Report prototype
The third group is composed of four reports that are more detailed views into each major procedural phase. The reports are:
- Pretensão (“Intention”), the first major phase in a process lifecycle.
- Resistência (“Resistance”), which happens when cases are disputed.
- Conciliação (“Conciliation”), when defendants and plaintiffs discuss and attempt to reach an agreement.
- Julgamento (“Trial”), which as the name implies consists of the actual sessions at court where cases are examined and rulings are made.
Figure 3. Trials Report prototype
Once the scenario was agreed on, the next step was defining the architecture that would enable integrating Power BI Embedded reports with SAJ. The environment already contained an Azure SQL database created for reporting purposes, so that was established as the data source for Power BI Embedded.
The reports can be integrated with the Delphi desktop application through a component called TWebBrowser, which allows hosting a webpage containing the Power BI Embedded reports. The diagram below (Figure 4) shows the solution architecture:
Figure 4. POC architecture
Creating the reports in Power BI Desktop
Most of the effort during the POC was focused on creating the nine reports detailed above. The following Power BI Embedded Visualizations were used:
- Clustered column chart
- Stacked bar chart
- Line and stacked column chart
- Area chart
- Donut chart
As mentioned before, two roles were defined: one at the Foro level and another at the Vara level. Slicer visualizations were added to allow filtering on these two levels (for users with full data access) and for other dimensions as well, such as Competence, Class, and Subject. As an example of the results obtained, the screenshot below (Figure 5) shows a newer version of the IAD Report, created by Softplan after the conclusion of the POC.
Figure 5. Improved IAD Report
Publishing and integrating the reports
Once reports were created, the next step was publishing them to a Power BI Embedded workspace in Azure. In general, this part of the POC was very straightforward, as the main steps were:
- Creating a Power BI Embedded workspace collection in Azure.
- Creating a workspace for TJAM’s reports.
- Publishing the reports created in Power BI Desktop to Azure.
- Integrating the report on the SAJ desktop application.
Step 1 was performed using the Azure portal. The team followed the steps shown at Get started with Microsoft Power BI Embedded.
Steps 2 and 3 were performed using the PowerBI-Cli command-line tool, available on this GitHub page. This tool has commands such as create-workspace and import that make it simple to publish the reports.
Step 4 was assigned to the core SAJ development team. Once the user experience is fully implemented, Softplan intends to roll out the new feature in waves, as it manages to incorporate the reporting needs of its most complex customers. The basic process to create a Delphi application with an embedded TWebBrowser component can be found in this article: Building a VCL Forms Web Browser Application.
The number of processes handled by courts in Brazil is huge, as evidenced by the number of cases managed by TJAM in three years (3 million for the 15th Brazilian state in terms of population). At this scale, it is almost inevitable that the lack of consolidated reports might lead to data discrepancy between systems. It’s hard for judges and courts to have accurate and timely information. Having integrated reports in SAJ will address this problem directly and efficiently.
Softplan was very satisfied with the results obtained through this engagement with Microsoft. Their priority was ensuring their team would be able to continue working on the reports by themselves, tuning and adjusting them according to the business needs of their users. They have been doing this successfully after the POC was concluded, and are intrigued by the possibility of creating custom visualizations. They are actively exploring this as a way to attend to some specific customer demands.
They found SQL Server Row-Level Security to be an extremely useful feature when combined with Power BI Embedded because it allowed them to greatly simplify authorization logic for the reports. Manually implementing and controlling the proper level of access would be quite complex for their scenario.
Current plans for Softplan are to finalize the user experience in the Delphi application and incorporate SAJ Analytics, based on Power BI Embedded, as a full-fledged product feature available to all its customers in the near future.
“Power BI is a powerful platform that allows courts of law to have full visibility into their operations and management. The knowledge transfer provided by Microsoft through Lucas Humenhuk allowed us to access the most sophisticated resources and features in the platform.
“As experts on our customers’ businesses and now fully enabled on Power BI, we can build intuitive and interactive solutions more easily, both in terms of format and content. With SAJ Analytics built on top of Power BI, our customers will significantly increase their level of knowledge about their institutions, contributing to more agile and assertive decision making.
“Being able to embed the reports in our desktop application means customers will access reports within the system they are already using, and this familiarity will improve user experience and enable more meaningful interaction with data.”
— Tiago Melo, New Businesses Executive, Softplan