Comparing Dates in Spotfire Information Links

One of the first temptations in using an information link is to incorporate a Document Property that you set in the analysis. This allows the user to input a date, refresh the data, and receive a subset of the data. This is especially valuable when dealing with data sets with rows that number in the millions. These parameterized information links become very important and I wanted to share with you a quick “gotcha” in the process.

Configure the Configuration Panel

Let’s say you want to add a Document Property that sets a minimum date. First we need to create the document property and configure a text area:

This will give us the input we’ll reference in the information link:

Setting up the Information Link

The tricky part is incorporating the Document Property into the Information Link. Let’s look at the setup:

After we have an Information Link setup we need to use the appropriate syntax to bring in a Document Property, this is usually written as follows:

?DocProp

As a string, the text will resolve to be encapsulated by single quotes (remember, SQL Server is reserving double quotes only for objects). Now let’s talk dates.

Consider the following WHERE CLAUSE:

-- your query
WHERE
SPUDDATE < "2018-01-01"
AND <CONDITIONS>

This will result in an XML error saying than an illegal character had been inserted. To remediate this you will need to convert this into characters. This is how Spotfire does it when you construct filters using their Information Designer interface.

-- your query 
WHERE
TO_CHAR(SPUDDATE,12) < "2018-01-01"
AND <CONDITIONS>

Now in Spotfire, it is converting the datetime data type into string and then comparing the dates based on sort value. Enjoy!

Leave a Comment

Your email address will not be published. Required fields are marked *