How to use commands with View in reports in Jasper Server community?

[bender-c]

I am trying to view a report in jasperServer that has in its SQL command, a View, in this View has creation of temporary tables that make it impossible for JasperServer to read. How do I enable these reports to run?

[Alex42]

What is your database?

When you say view has temporary tables do you mean with statements?

Also, Unless your view is based on very small tables i do not recommed using views for reporting for performance reasons especially if it is a multiuser reporting environment with frequent concurrent access. Move the view to a Table and schedule a script to refresh it when needed 

[bender-c]

Thanks for the answer!

My database is PostgreSQL. The view refers to a function and in this function there is the creation of temporary tables.

The use of View with reference to functions is necessary due to the need to extract information from complex calculations. I would like to know if it is possible to allow jasperserver not to prohibit this type of query, because jasperStudio works correctly.

 

[Alex42]

Haven't been in your situation so far. But have a look over applicationContext-semanticLayer.xml file. There is a PostgreSQL section. If i remember corectly this file controls the SQL generation for several DBs. It might not be the correct answer but maybe its a step in the right direction to understanding how the SQL is generated. Be carefull what you edit!

Also....maybe a dumb question. Does the user that jasper uses to connect to the DB have the correct access rights to execute functions and create temporary tables?

Still, if i were you, unless you need to refresh data Live in your report, or the data refresh frequency is faster than it would take to create a table based on the view, i would still go for the create table with the contents of the function based view. Imagine you have just 100 users and all of them need to execute the same report. The function with the complex calculations will be executed 100 times(will create hundreds of temporary tables in the background and so on) instead of just once, if you create the table and then issue a simple select statements

[bender-c]

Error filling report

Rastreamento de erro

com.jaspersoft.jasperserver.api.JSException: Error filling report Arguments: at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$FillResultListener.reportFillError(EngineServiceImpl.java:1295) at net.sf.jasperreports.engine.fill.BaseFillHandle.notifyError(BaseFillHandle.java:210) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:134) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:923) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:164) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:879) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1775) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1119) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1048) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:945) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Pulver_estrutura. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:342) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:169) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1129) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:696) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:437) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:526) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:119) ... 10 more Caused by: org.postgresql.util.PSQLException: ERROR: cannot execute CREATE TABLE in a read-only transaction Onde: SQL statement "create temporary table view_estrutura ( produto_filtro character(16), nivel varchar, sequencia bigint, produto character(16), descricao character(50), quantidade_estrutura numeric(14,4), unimedida_estrutura character(2), quantidade_engenharia numeric(14,4), unimedida_engenharia character(2), custorep_unitario numeric(14,4), custorep_total numeric(14,4), percentual numeric(14,4) ) without oids" PL/pgSQL function fn_estrutura_nivel_a_nivel() line 11 at SQL statement at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:332) ... 16 more

 

[bender-c]

Okay, unfortunately I did not find this file.

I am using the jasperadmin user, I believe it is not permission (I did not learn to configure the permissions very well).

I do not know if I am correctly expressing my doubt.

Please, see if the error text that appears can help me better:

[bender-c]

With the above error message, can you give me a hint of what to do?