0

I have this problem with the JasperServer report I'm trying to create: I have several db tables, named using a name and a date like this:

TABLE_NAME_YYYYMMDD

I want to be able to choose (and do a select from) the table which corresponds to the date submitted by the user from an ordinary Date input control.

I've tried creating a variable (called TABLE_NAME) which uses Java expressions for parsing the date like:

"MY_TABLE_" + new SimpleDateFormat("yyyyMMdd").format($P{RUN_DATE})

and when I print the value of the variable in the report it looks correct. But then I tried using that variable name in the SQL query like:

SELECT column1,column2.. from $V{TABLE_NAME}

but when I tried running the report in Jaspersoft Studio I got this Exception:

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: my_report_x.
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:511)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:486)

So it doesn't seem to be working.

I read about the case when the whole table name can be specified in a parameter, and you're supposed to use:

$P!{tableName}

First I tried using that '!' with the variable name like:

..from $V!{TABLE_NAME}

but I got the same Exception.

Then I tried creating a new parameter instead, where "Is For Prompting" is Not checked, and as default value expression I put the same expression as I used in my variable:

"MY_TABLE_" + new SimpleDateFormat("yyyyMMdd").format($P{RUN_DATE})

but I still get the same error when I try to run the report in Jaspersoft Studio.

Does anyone know if there is a way to solve this? -Preferably a way that doesn't take several days to implement since I don't have that time.

I'm using Jaspersoft Studio 6.1.1.final and running the reports in JasperServer 5.5.0.

Ylva D
  • 355
  • 1
  • 4
  • 10

2 Answers2

2

You should be able to get this to work by wrapping the whole of your FROM expression in the parameter e.g.

<parameter name="pTableName" class="java.lang.String">
    <defaultValueExpression><![CDATA["from MY_TABLE_" + new SimpleDateFormat("yyyyMMdd").format($P{RUN_DATE})]]></defaultValueExpression>
</parameter>

And then using this in your SQL as a string literal:

SELECT column1,column2
$P!{pTableName}
WHERE 1 = 1
philipobrien
  • 196
  • 8
  • I tried that, but I'm afraid I still get "Error executing SQL statement ..". – Ylva D Mar 14 '17 at 09:13
  • Though, my From statement is really a bit more complicated since I'm joining several tables using ",". I tried putting all of those tables in the expression so the next thing after using $P!{TABLE_NAME} is the Where statement, but it didn't work. – Ylva D Mar 14 '17 at 09:23
  • Okay, probably a silly question, but have you confirmed that it works if you simply hard-code the table name? – philipobrien Mar 14 '17 at 09:24
  • Yes, I tried that and that works. But as I explained, what I want to do is a bit more complicated. – Ylva D Mar 14 '17 at 09:33
  • I assumed as much, but it's always worth checking. I think without us seeing sample code for how your "more complicated" works it's quite hard to advise on this. Also, have you also run it in the SQL editor built into Studio to see if you get a better error message? – philipobrien Mar 14 '17 at 09:48
1

I just found out what I did wrong.

I admit it was rather stupid, but I only tried running the report in the Preview mode in Jaspersoft Studio. That's when I got the SQL error.

But I assume that the Preview mode does not support dynamic decisions about which table to read from, because when I ignored the Preview errors and published the report to JasperServer, I actually could run it there!

I ended up using the $P!{TABLE_NAME} parameter where the value is what I tried earlier:

"MY_TABLE_" + new SimpleDateFormat("yyyyMMdd").format($P{RUN_DATE})

I can print as much as possible of my SQL here (meaning that I have to replace names since this is a work report) if you want to see it:

    select c.column1,c.column2, h.column3 from $P!{TABLE_NAME} h, TABLE2 i, TABLE3 p, TABLE4 ca, TABLE5 c 
where h.P_ID = p.P_ID and h.A_ID = ca.A_ID and ca.C_ID = c.C_ID and ca.SOME_VALUE = 1 and ca.OTHER_VALUE = 1
and i.I_ID=h.I_ID  
and i.OTHER_ID=1
and h.VALUE_X > 0
order by c.VALUE_Y

So my advice to others who create Jasper reports is not to let yourselves get fooled by the fact that some things don't work in Preview mode. -That might just be the "preview limitations".

Ylva D
  • 355
  • 1
  • 4
  • 10