SourceForge has been redesigned. Learn more.
Close

Parameter Query

Help
2010-08-29
2016-06-23
  • Mathias Krause

    Mathias Krause - 2010-08-29

    Hi,

    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.

    Any suggestions?

    regards,
    gizzmo

     
  • Pierce Tyler

    Pierce Tyler - 2010-08-29

    Hi, gizzmo:

    Have you seen the OpenRPT documentation here: http://www.xtuple.org/docs/openrpt ? See if that helps.

    Regards,
    Pierce

     
  • Mathias Krause

    Mathias Krause - 2010-08-29

    Hi,
    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 ?

     
  • Mathias Krause

    Mathias Krause - 2010-08-29

    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…

     
  • Chris Ryan

    Chris Ryan - 2010-08-30

    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?

     
  • Mathias Krause

    Mathias Krause - 2010-08-30

    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.

     
  • Chris Ryan

    Chris Ryan - 2010-08-31

    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.

     
  • Mathias Krause

    Mathias Krause - 2010-09-02

    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….)

     
  • Mathias Krause

    Mathias Krause - 2010-09-03

    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);
        else
            tQuery += QString().sprintf(", text('%s') AS \"%d\"", val.toLatin1().data(), t + 1);
        if(!p.name().isEmpty())
        {
            if (_internal->_database.driverName() == "QMYSQL" )
                tQuery += QString().sprintf(", \"%s\" AS \"%s\"", val.toLatin1().data(), p.name().toLatin1().data());
            else
                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.

     
  • Chris Ryan

    Chris Ryan - 2010-09-09

    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.

     
  • Aaron Mills

    Aaron Mills - 2016-06-22

    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.

     
  • Aaron Mills

    Aaron Mills - 2016-06-22

    I'm using the 3.3.0 version from here, I'm running Linux.
    I'm Close......
    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.........

    Thanks

     
    Last edit: Aaron Mills 2016-06-22
  • Aaron Mills

    Aaron Mills - 2016-06-22

    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?

     

Log in to post a comment.