Error in top 10 sales list from dynamic list

ReportServer (EN)
1

I try to create a top 10 list of customers from a dynamic list on a postgres database. The dynamic list has a parameter on starting date. In the list I defined an Include Pre-Filter on column ‘lineprice’ as ‘${analytical.top(10)}’ and selected ‘exclude empty cells’. When I preview it fails with message:
‘Query could not be prepared: Error preparing statement for executing the report query.’
When I look in the database server log I notice it is missing the closing quote at the date filter:

    AND h.dtpakk >= '01-oct-2016) colQry

When I replace the analytical function with a fixed range it that runs without problem.

From the database log:

2016-12-06 11:04:07.053 CET,“postgres”,“DWDEV0”,26750,“10.236.80.192:49849”,5845
9355.687e,2,“idle”,2016-12-05 17:18:29 CET,9/15551,0,ERROR,42601,"unterminated quoted string at or near “”'01-oct-2016) colQry

(…)

"SELECT * FROM (SELECT * FROM (SELECT "“xx__rs_col_0"”, "“xx__rs_col
_1"”, "“xx__rs_col_2"” FROM ( SELECT * FROM (SELECT ““envkd”” AS "“xx__rs_col_0”
", ““lineprice”” AS "“xx__rs_col_1"”, ““kgpka9"” AS ““xx__rs_col_2"” FROM ( SELE
CT h.envkd,
h.dtpakk,
h.uwref,
h.vrnumm,
h.naam1,
h.kgpka9,
l.orderr,
l.artkd,
l.oraant,
l.vrtela,
l.kstprs,
l.vrtela * l.kstprs as lineprice
FROM ““KPIDW””.ce1_miuitk h,
““KPIDW””.ce1_miuitr l
WHERE
h.envkd = l.envkd
AND h.vrfsrt = l.vrfsrt
AND h.uwref = l.uwref
AND h.vrnumm = l.vrnumm
AND h.dtpakk >= '01-oct-2016) colQry
WHERE (( ““lineprice”” >= 0) AND ((NOT((”“lineprice”” IS NULL)))))) filterQry

WHERE (NOT(("“xx__rs_col_1"” IS NULL)))) aliasQry) orderQry
ORDER BY "“xx__rs_col_1"” DESC) limitQry LIMIT 50 OFFSET 0

2

Hello quintg,

we are taking a look to this issue and will update the forum when we have more information.

Cheers,
Eduardo

3

Could this be a problem with the postgres query engine? I also encounter a similar problem when creating a simple pivot on a postgres based dynamic lis (straight list is fine, also with grouping). Error in ReportServer.log:

…
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: ! integer
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 444
…

In the postgres database server log I notice it complains about the date filter clause:

 ...AND h.dtpakk >= !1!

Full error:

operator does not exist: ! integer
No operator matches the given name and argument type(s). You might need to add explicit type casts.
“select ““GenericDynaListCube””.”“envkd”" as ““c0"” from (SELECT * FROM (SELECT h.envkd,
h.dtpakk,
h.uwref,
h.vrnumm,
h.naam1,
h.kgpka9,
l.orderr,
l.artkd,
l.oraant,
l.vrtela,
l.kstprs,
l.vrtela * l.kstprs as lineprice
FROM ““KPIDW””.ce1_miuitk h,
““KPIDW””.ce1_miuitr l
WHERE
h.envkd = l.envkd
AND h.vrfsrt = l.vrfsrt
AND h.uwref = l.uwref
AND h.vrnumm = l.vrnumm
AND h.dtpakk >= !1!
) wrappedQry) as ““GenericDynaListCube”” group by ““GenericDynaListCube””.”“envkd”" order by ““GenericDynaListCube””.““envkd”” ASC NULLS LAST"

4

Hi quintg,

we are reproducing the issue in your post #3 and are looking how to correct it.
We don’t see the issue in #1, could you please try to make a simple testcase where this happens?

Best regards,
Eduardo

5

Hi quintg,

I have corrected this bug (#3), so this will be fixed in the next release.

Please note that we still have an incorrect behavior here:
Everything arrives as a Timestamp (i.e. with time component), and this is independent of the parameter configuration (date vs time vs date/time).
This is similar as the problem described here: https://forum.reportserver.net/viewtopic.php?pid=2668 and https://forum.reportserver.net/viewtopic.php?id=631
There, the problem is the other way around: the time component is lost.

I have raised two tickets for this and will keep you informed.

I have not been able to reproduce issue #1, please create a simple testcase for this.

Cheers,
Eduardo

6

Thanks Eduardo,

I tried to replay issue #1 with a small table but it returned me the top values without problems. Then I tried to replay the original issue and that one now also worked like a charm! Don’t know what happened in between, but you can close this post.