ReportServer Forum

karolina

Member

Registered: 2014-08-09

jxls & parameters

Hi,

Is there a way to use parameters in jxls reports?
I would like to place a parameter value in report's output (let's say that I have 2 Date parameters and I would like to see them in top row of the report)
I would also like to use parameters in SQL query in directly generated jxls report. Copying SQL from other reports (like dynamic list) and pasting into rm.exec('.....') does not work (I get parsing error).

Karolina

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: jxls & parameters

Hi,

the parameters are available via the "parameters" object. That is, if you have a parameter with key p1 you can use ${parameters.p1} to output the parameter value. If you want to use a parameter within a sql query you can use

${rm.exec('SELECT A FROM B  where C = ' + parameters.p1)}

Note that you need to manually add quotes if the parameter is of a String type.

Cheers
Arno

karolina

Member

Registered: 2014-08-09

Re: jxls & parameters

Hi,

${parameters.p1} works fine in jxls reports - thanks

if I create template and put ${parameters.p1} in a cell and use it for export of parametrized dynamic list, the cell is empty after the export.

I managed to use SQL with text parameter in jxls report using

<jx:forEach items="${rm.exec("select A from B where A starts with '" + parameters.p1 + "' order by A asc")}">

but with date parameter I constantly get parsing errors...

Edited:

When I create text parameter and put date into it (RRRR-MM-DD format) and use this parameter in SQL query, everything works fine (no errors and data are correct; assuming that the text is written correctly).

Karolina

Last edited by karolina (2014-09-02 15:20:47)

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: jxls & parameters

Hi Karolina,

in dynamic lists the parameters are currently not available in templates. I created a ticket and I see what we can do about this.

As for the date parameter, I'll have a look at this and get back to you.

Cheers
Arno

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: jxls & parameters

Hi,

to follow up on this. We've implemented custom parameter handling to go with jxls. That is, instead of writing

<jx:forEach items="${rm.exec("select A from B where A = '" + parameters.p1 + "' order by A asc")}">

you can now additioanlly use

<jx:forEach items="${rm.exec('select A from B where A = ? order by A asc', parameters.p1)}">

This, on the one hand, protects from sql injection and also takes care of the parameter type (for example dates).

In addition we have added the parameters object also to templates for dynamic lists.

Cheers
Arno

karolina

Member

Registered: 2014-08-09

Re: jxls & parameters

nice - thank you! I'm looking forward to the next build.

karolina

Member

Registered: 2014-08-09

Re: jxls & parameters

Hi,

Following this topic: how to pass parameters' values to XDOC & Velocity templates?

Best wishes

Karolina

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: jxls & parameters

Ouch .. for some reason I've only added parameter access to JXLS templates. I will change that with the next version and then you'll have the same replacements also for Velocity and XDoc.

Sorry for that.

Arno

karolina

Member

Registered: 2014-08-09

Re: jxls & parameters

Thank you

Is there a way to get column names and their datatypes (SQL) to velocity output file (or, in general, any other output file from a dynamic list report (and a variant of this report))?

Best wishes

Karolina

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: jxls & parameters

Parameters will be available for XDoc and Velocity from the next build on.

As for the other question: what use cases do you have in mind? Technically this is possible but I am not sure I understand the benefits. Templates are usually very dependend on the variant configuration and thus a change in a column name or type usually also means that the template needs to be changed. So what I am saying is that column names and types are usally static when it comes to templates. Am I missing the point?

Best
Arno

karolina

Member

Registered: 2014-08-09

Re: jxls & parameters

Hi Arno,

Yes, I definitly should have explained that in a clearer way
I'll try to write it down when I have a free moment and I'll be back with it to you.

BTW: many thanks for the change in jxls parameters' handling - it is much easier to write SQL expressions in this way.

Cheers,

Karolina