Freehand SQL report has mixed parameter type
11 April, 2014
Hi,
I have faced with another problem maybe it is a bit related with the Tread=159464. The problem in YF 7.0 (2014.03.28 release) is that when there is custom field in SQL statement, i.e. CASE WHEN ... END as CustomField, YF incorectly detects the type of that custom field. I have tried to construct CustomFied with date format, but in the report it was assigned as Dimention.
Also there was a case when i tried to replace parameter type which passes through "{?}", YF always remembered the initial one. The workaround was to remove from FreehandSQL that parameter, then save and then again include and define the correct parameter type.
Please let me know if any additional information is needed.
Thanks in advance!
Raimondas
I have faced with another problem maybe it is a bit related with the Tread=159464. The problem in YF 7.0 (2014.03.28 release) is that when there is custom field in SQL statement, i.e. CASE WHEN ... END as CustomField, YF incorectly detects the type of that custom field. I have tried to construct CustomFied with date format, but in the report it was assigned as Dimention.
Also there was a case when i tried to replace parameter type which passes through "{?}", YF always remembered the initial one. The workaround was to remove from FreehandSQL that parameter, then save and then again include and define the correct parameter type.
Please let me know if any additional information is needed.
Thanks in advance!
Raimondas
Hi Raimondas,
I think there might be mix-up of terminology here, so I'd better clarify what I think the question is: the data-type called timestamp or datetime can be either a Dimension or a Metric, however in a Freehand SQL report the date is automatically created as a Dimension. So I'm assuming that you actually wanted the date to be a Metric but weren't able to. Is that correct? If it is indeed correct then I have to tell you that at the moment it is not possible to change a date obtained via a Freehand SQL report into a Metric. You would have to do this in a view, not a report.
However, if I'm wrong in my interpretation of the question and instead take it at face-value ("I have tried to construct CustomFied with date format"), then the good news is that it is in fact possible to construct a CustomField with the Date format. Here's how you do it, click the drop-down arrow of the field name so that it brings up a context menu, and then select "Format":
Once you've selected "Format" then a Column Formatting dialogue should appear, and there you should be able to select the Date format:
Regarding how to change the parameter type, yes you are correct, that is only way to change the data type of a parameter once it has been saved - i.e. you have to remove it from the SQL, save it, then add it back in.
regards,
Dave
I think there might be mix-up of terminology here, so I'd better clarify what I think the question is: the data-type called timestamp or datetime can be either a Dimension or a Metric, however in a Freehand SQL report the date is automatically created as a Dimension. So I'm assuming that you actually wanted the date to be a Metric but weren't able to. Is that correct? If it is indeed correct then I have to tell you that at the moment it is not possible to change a date obtained via a Freehand SQL report into a Metric. You would have to do this in a view, not a report.
However, if I'm wrong in my interpretation of the question and instead take it at face-value ("I have tried to construct CustomFied with date format"), then the good news is that it is in fact possible to construct a CustomField with the Date format. Here's how you do it, click the drop-down arrow of the field name so that it brings up a context menu, and then select "Format":
Once you've selected "Format" then a Column Formatting dialogue should appear, and there you should be able to select the Date format:
Regarding how to change the parameter type, yes you are correct, that is only way to change the data type of a parameter once it has been saved - i.e. you have to remove it from the SQL, save it, then add it back in.
regards,
Dave