search

Mgiepz / saiku-adhoc

An adhoc reporting client based on Pentaho Metadata Layer
32 stars 20 forks source link

Filter on numeric field fails #165

Open Dzo67 opened 11 years ago

Dzo67 commented 11 years ago

When I try to filter on a numeric field, I get the error 'ReportGenerator.ERROR_0001 - The CDA Datafactory could not process the query '

The catalina.out log says: [Fatal Error] :-1:-1: Premature end of file. 14:00:44,224 ERROR [ReportGeneratorService] org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed to send request 14:00:44,230 ERROR [QueryResource] Cannot generate report (647BB33E-E17C-E36F-9A0A-DE0C447D5500) org.saiku.adhoc.exceptions.SaikuAdhocException: ReportGenerator.ERROR_0001 - The CDA Datafactory could not process the query

and some rows more up: Caused by: pt.webdetails.cda.dataaccess.QueryException: ReportDataFactoryException : The generated SQL-query did not execute successfully.; Parent exception: ERROR: invalid input syntax for type double precision: ""

I am working on an xmi file generated with PME, but when trying the same filter on the same datamodel using the Pentaho waqr, I get no errors.

Any idea, anybody?

Welcome!

Mgiepz commented 11 years ago

see the last issue before yours: https://github.com/Mgiepz/saiku-adhoc/issues/163

i think it is not possible

Dzo67 commented 11 years ago

I do not understand: not possible to filter on a numeric column or not possible to have an error?

I prefer to work with Saiku instead of the waqr, but not being able to filter on numeric values would be a disadvantage.

Mgiepz commented 11 years ago

there has been a commit for selecting numeric values from a list: https://github.com/Mgiepz/saiku-adhoc/pull/159 this should be available in the latest ci build. maybe you can try that and see if it works. any further filtering options wont be available before the rewrite of saiku-reporting which is currently in progress

Dzo67 commented 11 years ago

I am running build 156, (27 sep), on top of Pentaho biserver-ce 4.5.0, and indeed I see the list with possible values to select, but when I select one and click SAVE, I get this error when querying a Postgres database. And now the weird part: when doing exactly the same on an Oracle database (datatype number), I get no problem but correct results. Does that make sense to you?

Running the same query in waqr against Postgress is also no problem.

Because the error message talks about 'invalid input syntax for type double precision: "" ', I have also tested changing the datatype on the Postgress db: double precision, numeric,integer: it all fails.

Could it be a cda problem? My cda files have a date stamp of 08-Mar-12.

And do you already have an about-date of the release of the rewrite?

Mgiepz commented 11 years ago

I work with oracle db only, never used postgres so i cant really tell. Only thing i could recommend is using a logging driver like http://code.google.com/p/log4jdbc/ which tries to give you the exact sql that the tool is sending to the db. I dont think that it is cda related

Mgiepz commented 11 years ago

i am afraid but i cant make any predictions about the rewrite. there are several compatibily issues with the latest reporting engine that we need (i.e. crosstabs) and current bi-server. so we are currently concentrating of on getting it to work standalone. currently i am pretty much a one-man-show on the reporting part of saiku and cant work on everything at once.

jobtorres commented 11 years ago

I am having exactly the same problem. Was anyone able to fix or workaround this issue?

Thanks a lot.

jobtorres commented 11 years ago

By the way, I'm on BI Server-CE 4.5, data source on Postgres 9.1

Dzo67 commented 11 years ago

I was able to avoid the problem by making my field a String (character varying in the db). My experience is that Oracle does a lot more implicit conversion then Postgress does (or more precise: the jdbc driver for Postgress), so I suppose Oracle corrects a type mismatch that is created by the adhoc-plugin while Postgress refuses to do so.

mehulsoni commented 10 years ago

Hello Sir,

Please help.Date filter is not working in saiku report mode. but when i switch to saiku table mode it is working.

pentaho-ce-4.8.0 stable version installation : ctool-installer.sh - stable

Thank You & Regards Mehul Soni

mehulsoni commented 10 years ago

Hello Sir,

I am using mysql database for connectivity and date formate is yyyy-mm-dd.

Thank You

AlekseyBykov commented 8 years ago

Hi folks, have a good day

The same problems.. Filtering works only with string types.

pentaho-ce-4.8.0 stable version saiku-adhoc-plugin-1.0-GA MySQL 5.7, MS SQL Server 2012

Best wishes, Aleksey