How to use commands with View in reports in Jasper Server community?
Posted on May 19, 2017 at 7:49am
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?
I tried to fit the 'applicationContext' file with the property: <Property name = "defaultReadOnly" value = "false" />. But by doing so, JasperServer does not start any more. Can someone help me?
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?
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
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.
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
