#1 2016-12-06 10:38:17

quintg
Member
Registered: 2016-10-19

Error in top 10 sales list from dynamic list

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

Offline

#2 2016-12-07 14:54:09

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Error in top 10 sales list from dynamic list

Hello quintg,

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

Cheers,
Eduardo

Offline

#3 2017-01-20 16:18:37

quintg
Member
Registered: 2016-10-19

Re: Error in top 10 sales list from dynamic list

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"

Offline

#4 2017-03-02 14:28:38

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Error in top 10 sales list from dynamic list

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

Offline

#5 2017-03-03 13:35:00

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Error in top 10 sales list from dynamic list

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

Offline

#6 2017-03-03 15:27:42

quintg
Member
Registered: 2016-10-19

Re: Error in top 10 sales list from dynamic list

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.

Offline

Board footer

Powered by FluxBB