• Increase font size
  • Default font size
  • Decrease font size
Hits: 1013

Must Do:             But before you start reaping the benefits of creating awesome reports on anything you have stored in HoudiniEsq, you need to understand how the HoudiniEsq -> BIRT connection is made and used. The most important fact you need to understand and follow is that the HoudiniEsq database should never be accessed by anything but the HoudiniEsq program. That means that even if you want to develop a BIRT report to pull data from the HoudinEsq database, it must never pull data from the Live HoudiniEsq database – ALWAYS MAKE A COPY FIRST! (Then design and test your report using the copy of the data. When the report functions as desired, upload the report and do a final test from within HoudiniEsq.)
For those of you working with the SaaS version, you must have admin rights so that you can see the various types, subtypes and status codes, as well as be able to edit the report manager.

In order to create a BIRT report, the person creating the report has to understand (to some degree) all of the following:

  • How BIRT reports are designed
    • How BIRT data sources, data sets and report layouts are created and manipulated
    • How BIRT report parameters are used
  • How HoudiniEsq report parameters are defined
    • The relationship of one HoudiniEsq parameter to the next
    • The various types of HoudiniEsq parameters
  • How HoudiniEsq report parameters are linked to BIRT report parameters
  • How the various data tables in the Derby database that stores the HoudiniEsq data are related (this is key – if you do not understand how to relate the various tables in HoudiniEsq, you will not be able to pull the data from the tables into a BIRT report.)
  • How BIRT Reports are uploaded (added) to the HoudiniEsq Report Manager (great article on this in the HoudiniESQ KB: Upload a BIRT Report.)

Parameters

Not every BIRT Report has a parameter (like a date range or a type of matter), but when parameters are defined for a report, they should be considered as cumulative. That means that if you define three parameters: start date, end date and matter type, then all three parameters must be true in order for any records to be included in the report. Given the parameter set of: area of practice, responsible attorney and open/closed date parameters, then all there must be at least one record that matches all three parameters before it is included in the report.

In the example below, there must be at least one matter that meets the date range criteria (Start Date and End Date) as well as have a matching funding source:

HoudiniEsq report parameters

Parameters are always cumulative; they cannot be an OR situation; that is, parameters cannot be set up so that two of three parameters are met and the third may/may not meet the criteria.

Blank parameters are set up to include records not exclude records, i.e., this means that by leaving a parameter blank, you are not excluding records from the report.

As of the current version (1.9.9.3) when entering a text parameter the user must type in the value to be used as the criteria. (There is no option to define a drop-down list that contains the values found in the drop-down list of a custom field, for example.) This means that user must be very careful when entering parameter values.

Here are the various types of HoudiniEsq parameters:

Text

Matter

 

Contact

Staff

 

Date

Checkbox

 

Division

Type

 

When creating the parameters in the HoudiniEsq report setup, remember that the names of the parameters that were defined in the BIRT report design must match EXACTLY – this includes spelling and capitalization!

The HoudiniEsq report definition is on the left below – the BIRT report design on the right:

HoudiniEsq report parameter definition        BIRT report parameters definition

HINT:    If you do create a report in HoudiniEsq, and think you have created the report parameters properly, but then the report seems to be ignoring a specific parameter, go back and verify that you have spelled and capitalized that specific report parameter. My experience is that the report parameter is not spelled or capitalized correctly so the parameter is not passed to the report – so it is ignored!

Where is the Data?

The HoudiniEsq database is made up of many data tables. Each of these tables are linked to each other using key fields. It is impossible to create a BIRT report without defining a SQL query that pulls the fields needed for the report from a variety of these tables. Being able to decide how to “join” and knowing what type of “join” to use when pulling data from these different tables is a requirement in designing the BIRT report.

For example the basic matter data is stored in a data table named “Matter” – custom form basic data is stored in a table named “FORM,” etc… The SQL queries must join tables appropriately and take advantage of the ability of BIRT to create computed columns. All of the custom fields are stored in a separate table (Record_Column.)

(The screen shot below was taken from DbVisualizer:) 

DbVisualizer list of HoudiniEsq tables
   If you will be creating BIRT reports you will need a database tool that allows you to see the data and the structure of the HoudiniEsq database. The database behind the HoudiniEsq program is a Derby database, which uses SQL queries to pull data into the BIRT report designs. To develop and help design the SQL queries behind the BIRT report designs, it is a good idea to use a tool such as DBVisualizer, an open source tool (meaning that it is free to use.)

As you can see from the list of tables to the left, there are many different tables behind the HoudiniEsq interface. Each table stores different information, with the table name almost always a dead-giveaway of what data is stored inside.

All of the tables are linked to other tables by the use of “key” fields, which is where the ability to create SQL queries to join the data from multiple tables becomes a key skill in creating BIRT reports.


Another architecture design point that BIRT report designers must be aware of is that the data in checkbox fields are stored differently than what most report designers would expect. The actual check box values (Y for checked or N for not checked) are stored in the order of the checkboxes on the form, in addition to the label of the checkbox. For example, if a form has the following checkboxes:

custom multi-checkbox esq example

The data stored in the checkbox field in the data table would look like this:

N,N,Y,N|Option C

This also means that checkbox fields should only be modified by adding new values to the end of the list, not in any other position. Changing the order of the checkbox values will result in loss of data integrity and the ability to run historical reports.

Dates in HoudiniEsq Derby Database

Another data issue is that dates stored in a Derby database are not stored in the “expected” format: nn/nn/nnnn, i.e., “04/01/2015.” Instead, in order to be able to do “date math” where dates and times are added and subtracted from one another, dates (and their associated times) are stored as epoch time – also known as Unix time, which in HoudiniEsq is measured in milliseconds. For example, the number of milliseconds that has elapsed from the epoch date (January 1, 1970) until the beginning of the date 04/01/2015 is stored as: 1427903116000.

Obviously as the day progresses, the number will increase. This means that the end of day epoch/unix time will be:  1427947199000. This also means that in a BIRT report, the conversion between this format and the expected date format “04/01/2015” will have to be included in the field setup.

Here’s another screen shot, this time of the Key Date field (1) from a HoudiniEsq database (as seen from DbVisualizer):

Key date1 in DbVisualizer

Note:   Dates in HoudiniEsq use the starting date of January 1, 1970. Microsoft Excel uses the starting date of January 1, 1900. If you import raw data from HoudiniEsq into Excel - know the difference.

Tips on Adding a Report

  • If after, you’ve added a report the first time, you find that you need to re-add the report – do not try to simply re-upload the report design. Instead, edit the existing report (copy the report title to save yourself some time) and then delete the existing report. Re-add the report, paste in the report title and recreate the report parameters. If you try to simply re-upload the report design you will get an error. And no, there is no way to save the report parameter setup, so if you have three report parameters with a report that you have to re-upload, you will need to re-create the parameters manually.
  • When testing reports – add them with a name starting with letters near the end of the alphabet as reports are listed alphabetically. This way the test reports appear at the end of the report listing and most users will not even know they are available.
  • Once you have decided on the need for a specific report, think about the naming approach you want to use. Reports should be named so that they can be grouped easily. Making the reports easy to find starts the entire process off in the desired manner.
  • When adding and testing reports, take advantage of the ability to define workspaces. Open the Report Manager (you must be an admin to access this – Settings|Report Mgr) and assign it to a workspace. Next, open the Reports menu (Reports|Advanced Reports) and assign it to the same workspace. Arrange the two windows so that you can access them both (remember to use the small circle in the lower right corner to adjust the size so that both windows fit) and then add and test the reports by going back and forth between the two windows.

TIP:  If you are re-adding a report to test a specific part of it, and plan on re-adding it again, shorten the report parameter label (but the Parameter name must be exact!) enough so that you know what it is, but don’t retype the full label.

Coming Next:    Dates in HoudiniEsq