first of all: Thanks for this great tool. I like it!
I have a question regarding Paramter Query as this is, what i can't get to work:
In Report Writer, i create a report and insert a fiel with "Parameter Query". I set the Column to the parameter name. Then i define the parameter in Document->Parameter List-> Add.
I tried a lot of tricks, but i can't get the parameter query to work. There's nothing rendered.
I found a workaround by creating a query the does a simple SELECT <?value("param_name") ?> as col and then use the Query source as the field source, but that is propably not the way it should be.
Have you seen the OpenRPT documentation here: http://www.xtuple.org/docs/openrpt ? See if that helps.
sure, I've seen that. But there's no hint about using parameter queries.
I had a look into the sources, and until now, i see no point, where the parameter query is filled.
There's a -if "Context Query"- in the source code. But for parameter query, it seems, that the rednerer tries to search for a SQL value with column = parameter name. So what i think is, that this is not implemented, yet. Right ?
OK … after some intensive lookings into the code, i came to the conclusion, that "Parameter Query" is not implemented, yet.
Therefore i added some line to the code, to implement that feature:
In ORPreRenderPrivate::renderSection i added at line 781:
else if(f->data.query == "Parameter Query")
str = _lstParameters.value(f->data.column).toString();
d_val = _lstParameters.value(f->data.column).toDouble(&isFloat);
That does the trick. And i did the same for the text fields…
The Parameter Query is working correctly. The Parameter Query is actually built SQL when the report is first created and accessed like a normal query. Because of this you need to be connected to a database for the Parameter Query to give you the correct results. If you connect to a database do you then start to get the expected results?
Hm … no. Even when i am connected to a database, the parameter query does not work.
I am connected to MySQL, not PG SQL, maybe that's the issue?
But as i implemented the Parameter Query direct into the fiels (like the context query), that works fine for me.
Indeed it is failing on MySQL. Your suggestion would solve any database compatibilities but there is the backward compat support required for ordered parameters. I will have to see if I can modify your suggested change to support that or determine if we can reasonably drop that support. Thank you for bringing this to our attention.
Hm … is the parameter query failing because of a missing MySQL-Feature, or just missing MySQL support in OpenRPT? In the second case, maybe you could give me a hint, where the parameters are built into SQL. Then i can try to implement that for MySQL…
Btw: Are there parameter's available including arrays? How can i access them? (In fact, every row of a SQL query, needs another parameter index….)
OK … i think i found the right place for the parameter queries: So i changed orprenderer.cpp at line 1400 with
for(int t = 0; t < _internal->_lstParameters.count(); t++)
Parameter p = _internal->_lstParameters[t];
val = p.value().toString();
val = val.replace(re, "''");
if (_internal->_database.driverName() == "QMYSQL" )
tQuery += QString().sprintf(", \"%s\" AS \"%d\"", val.toLatin1().data(), t + 1);
tQuery += QString().sprintf(", text('%s') AS \"%d\"", val.toLatin1().data(), t + 1);
if (_internal->_database.driverName() == "QMYSQL" )
tQuery += QString().sprintf(", \"%s\" AS \"%s\"", val.toLatin1().data(), p.name().toLatin1().data());
tQuery += QString().sprintf(", text('%s') AS \"%s\"", val.toLatin1().data(), p.name().toLatin1().data());
So, this is where the queries get prepared with the parameter's from application. So the MySQL parameters now do work.
Now, i am searching for a way, to create parameter queries for every record set of a detailed section (as array parameters). But that seems not as easy as i thought at first.
I've included your latest suggested change to fix mysql parameter queries. see http://www.xtuple.org/issuetracker/view.php?id=11963. This will be available in the next release of OpenRPT.
As for the arrays there is a way to pass in QVariant<List> equivalents as parameters and then use MetaSQL to iterate over the array to build a query but OpenRPT doesn't understand or support dealing with arrays that come back from sql queries.
Similar Question. Just found this application, Looks great so far.
The Query I'm using is attached.
Question is, How can I ask for input variable to replac the '2016' (year) and ;05' (month) that I have hard coded?
Drop downs would be awesome.
You need to use MetaSQL parameters. Replace the hard-coded 2016 with <? value('year') ?> and 05 with <? value('month') ?> in the query. Then pass a ParameterList that contains parameters year and month populated with the right values.
<? value('year') ?>
<? value('month') ?>
The MetaSQL editor built in to OpenRPT has a feature to search for report parameters. Run that then open the list for editing (two separate menu selections).
See https://www.xtuple.org/sites/default/files/prodguide/openrpt/i553538.html and https://www.xtuple.org/sites/default/files/prodguide/openrpt/ch03.html
You should know that the latest version of the code is now hosted at https://github.com/xtuple/openrpt
I'm using the 3.3.0 version from here, I'm running Linux.
I made the changes to Query.
I setup the parameters, Run the Print Preview, it pops up the parameter window, fill them in, and get exaclty what I was after.
Now if we cound just add a dropdown list of the variables to the report itself.........
Figuring out the rptrender..... any help on the cli arrguments? Can I run a command, log in and pass parramenters all in one command to print report?
See https://www.xtuple.org/sites/default/files/prodguide/openrpt/ch04s05s04.html for info on setting parameters from the command line.
Log in to post a comment.