A URL can be composed as a query column value to link to another report that will provide more detailed information. The report targeted by the link typically has parameters that are shared by the source report (the one that contains the URL). The best way to pass on the parameter values of the source report to the target report is to include the <%SOURCE_OBJREF_PARAM%> attribute in the URL. For those parameters that are not shared, however, the parameter name and value must be composed in the URL explicitly, often based on the value of a column in the result set row.

To explicitly include a date value in a URL, the date value can be selected in string form by the SQL that composes the URL, but the query must be informed of the format of the date (or date-time) string value. The current user's date format cannot be assumed. Therefore, the URL date parameter should be accompanied by another URL parameter that specifies the format being used for the date.

The following example is a query column definition that composes a URL calling a report.

'/analysis/report?queryGroupName=Custom-ProjectInfo&TimePeriodBeginning=', <%+%> FCI_CHARFMT(TIME_PERIOD_START_DATE),' <%+%>

'&TimePeriodBeginning_format=17&link_crumb=true'

This example provides a value for a date parameter with the name of TimePeriodBeginning. This parameter name must be the same as the name for the parameter that is defined in one or more of the queries in the Custom-ProjectInfo report. The value of TimePeriodBeginning is next in the URL. The date column TIME_PERIOD_START_DATE is being converted to a string using the widely supported database format “YYYY-MM-DD HH24:MM:SS” format. This format corresponds to a format that Sourcing code understands, which can be identified numerically as format 17. The next URL parameter specifies this format code of 17. This parameter name must be the same as the date value parameter name, with a suffix of “_format”. The code that evaluates this parameter value will truncate the time portion at midnight.

Date-time format 17 uses a four digit year, which avoids the century ambiguity. Other formats understood by Sourcing may be used, too. However, we recommend that the same format is used in all cases, for consistency.