Date parameters fail on a dynamic list pivot

ReportServer (EN)
1

We recently upgraded to RS2.2.2-5639 to take advantage of the new pivoting feature, but have run into a severe problem that makes it almost impossible to use.

If we take a working dynamic list with DATE parameters, and pivot it, we get errors similar to the following:


SELECT
{Hierarchize({[SaleDate].[sale_date].Members})} ON COLUMNS,
{Hierarchize({[Campaign].[campaign_type].Members})} ON ROWS
FROM [GenericDynaListCube]
Oct 12, 2015 10:06:29 AM net.datenwerke.rs.saiku.service.saiku.OlapQueryServiceImpl execute
INFO: runId:39  Exception: MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '! and !2!
AND product_code != 'VERI'
GROUP BY sale_date, campaign_type
UNION
SEL' at line 28
Oct 12, 2015 10:06:29 AM net.datenwerke.rs.saiku.server.rest.resources.QueryResource execute
SEVERE: Cannot execute query (928FBC91-74EA-78AB-9941-1CACBB12ABB6)
org.saiku.service.util.exception.SaikuServiceException: runId:39        Can't execute query: 928FBC91-74EA-78AB-9941-1CACBB12ABB6

The problem only occurs with DATE parameters. If we change the parameter to type TEXT, it works perfectly. But this isn’t ideal since we cannot rely on end-users entering correctly formatted dates.

Another example with a different query:

MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '! and !3! group by cl.agentid, cr.digiacrcategory,cl.callresultstring, cr.callre' at line 7

This issue occurs with all instances of reports with data parameters and seems to have something to do with date variable substitution in the underlying SQL query.

2

Are we the only ones having this problem?
How can we get assistance in resolving this? I don’t mind paying for support here.

3

Hi Tristan,

the current Pivot mode is a bit quirky here and there as it converts a dynamic list into an OLAP cube behind the scenes. We are currently working on a reimplementation that does not require to go via OLAP and which should thus be much more stable for also more advanced uses. In any case, a possible workaround for you would be the following. You could have a script parameter, that depends on the date parameter but transforms it into simple text. In that way the transformation from date to text is done before the OLAP conversion but your users could still use the date picker.

As for paid support. Have a look at https://reportserver.net/en/services/ and feel free to contact us at info@datenwerke.net to request a quote.

Cheers
Arno

4

Hi Arno,

Thanks for this very useful hint for a workaround (I have exactly the same issue with pivot & dates & Firebird).
Concerning the script parameter: in this case it will be a hidden one, just Groovy script without javascript? Or is the javascript needed anyway?

Cheers,
Karolina

5

Hi,

that’s actually way more complicated than necessary. To convert the parameter value to text, just use it like this in your query:

select * from T_AGG_ORDER WHERE OR_ORDERDATE < ${dutils:format(key1, “yyyy-MM-dd”)}

Cheers,
Thorsten

6

Hi Thorsten,

Thanks. Converting the parameter value to text is way simpler. And it works perfectly.

Regards,
Tristan