3305 Main Street Ste 019 Vancouver, WA 98663

Liberty Reports Parameters May Be Interpreted As A Date

Scenario

Query parameters may be incorrectly interpreted as a date when using Liberty Reports against a Microsoft SQL Server database.

This could result in an error message similar to the following:

This issue is present in the following versions of Liberty Reports:

  • 1.00.0068
  • 1.00.0070
  • 1.00.0073

Cause

Liberty Reports uses the Visual Basic function, IsDate, to determine if a value can be interpreted as a date. This is regardless of the underlying data type of the cell value and regardless of the wide variety of patterns that can be considered dates by IsDate. Consequently, values that are not a date are interpreted as a date by IsDate.

For example, passing a change order parameter of "1075-005" in a WHERE clause, as shown below:

SELECT DISTINCT
     [dscrpt] AS [Description],
     [ntetxt] AS [Change_Order_Notes],
     [bdgprc] AS [Budget_Amount],
     [recnum] AS [Record_Number],
     [chgnum]

FROM
     [Reports_v1].[sbcgln]

WHERE
     [chgnum]=? AND
     [chgsts]<=3

ORDER BY
     [chgnum]

results in the parameter being interpreted as a date.

?IsDate("1075-005")
True

This results in the change order being interpreted as the date, 05/01/1075. This behavior will only present itself after the initial refresh.

Suggested Temporary Work-Around

Until a permanent change is made to the code supporting Liberty Reports, the following suggestion should resolve the issue.

Each parameter value should be converted to text. This will force IsDate to no longer consider the parameter a date value.

For example, if your parameter of "1075-005" is in cell B2, define a formula in a different cell such as Z2

="txt" & $A$2

Then amend the SQL text to also include the same prefix text:

SELECT DISTINCT
     [dscrpt] AS [Description],
     [ntetxt] AS [Change_Order_Notes],
     [bdgprc] AS [Budget_Amount],
     [recnum] AS [Record_Number],
     [chgnum]

FROM
     [Reports_v1].[sbcgln]

WHERE
     'txt'+[chgnum]=? AND
     [chgsts]<=3

ORDER BY
     [chgnum]

Then link your parameter to cell Z2 instead of A2. Since the value "1075-005" is not considered to be a date by the IsDate function, the value will not be reformatted and the query will work as expected.

Permanent Resolution

Once Liberty Reports has been updated to resolve the issue explained above, this article will be updated with instructions to update your installed version of Liberty Reports to the latest release.