Open Forum

Quick Query Connection

  • 1.  Quick Query Connection

    BRONZE CONTRIBUTOR
    Posted 10-01-2018 02:02 PM

    Hello,
    I am hoping someone can help me figure out when a Quick Query is ran and the selection of connection is picked in order for the Excel file to be refreshed how does the end user update the filters they have set at the Quick Query level (ie.. Period to post, or date fields) when refreshing the connected Excel file?  I found where the command text under the connection properties is, but this is not a feasible solution that the end users can use.  They are not systems people and do not understand SQL statements.

    I appreciate any suggestions the group may have.

    Thank you,
    Melanie




    ------------------------------
    Melanie Howell
    Financial Systems Analyst
    LifeLink Foundation
    Tampa FL
    ------------------------------


  • 2.  RE: Quick Query Connection

    BRONZE CONTRIBUTOR
    Posted 10-02-2018 08:36 AM
    Melanie,

    I've always preferred to show the users how to use the QQ interface to modify a filter and how to export to Excel.  Sometimes I will also save a predefined filter (if you're changing lots of columns and such) so that they only have one filter value to modify.  I do this not only because there's not an easy way to accomplish filter changes on the Excel side, but because once the users get the concept of QQ they find other uses for it.  I reserve Excel refresh only spreadsheets for tasks where the filters don't change.

    With that said, in order to accomplish your goal I would suggest making a custom QQ that inspects the current date and turns it into a static value that you can filter on.  So for instance, if you wanted to always pull the current period respective of the system date make a column resulting in True/False using the SQL CASE statement that compares the Period in the row to Today's Date converted into an SL style period.  Then when you setup your QQ, filter on this new column for TRUE.  It's silly that we have to go to these lengths for such a simple thing, but they don't currently support expressions in filters for QQ.  There are several good QQ presentations in the file libraries that cover making a custom QQ (including by yours truly!).

    Joe

    ------------------------------
    Joe Miller
    Director of Systems Architecture
    Harrison, Walker & Harper
    Paris TX
    ------------------------------



  • 3.  RE: Quick Query Connection

    BRONZE CONTRIBUTOR
    Posted 10-03-2018 01:45 AM
    Melanie,

    You can edit the connection query in Excel to use parameters, by adding a where clause with  'field' = (?).

    For example, adding the red-underlined text to the Journal Transactions QQ will allow you to add a parameter on the Period to Post field.
    pJiEBGBqT6uoIMIsPO8Q_Untitled picture.png
    After clicking OK, you will be prompted for the parameter value and options to always refresh using that value and automatic refresh.


    TjK7hnAgQg23q4FyPKrv_Untitled picture2.png
    As you can see, you can set the parameter to reference a cell in the worksheet, allowing the user to easily change the parameter directly in the worksheet.  With "Refresh Automatically..." checked, whenever they change the value of that cell, the query will refresh.


    Hope this helps. 



    ------------------------------
    John Lian
    Principal Consultant
    The ERP Group, LLC
    ------------------------------



  • 4.  RE: Quick Query Connection

    Posted 10-04-2018 08:48 AM
    Thank you John.  I was editing the query each time.  I like this option better.  It worked great.

    ------------------------------
    Jenny Dinh
    Noble Resources Corp
    Tampa FL
    ------------------------------



  • 5.  RE: Quick Query Connection

    BRONZE CONTRIBUTOR
    Posted 10-08-2018 07:40 AM
    ​John,
    Thank you for the info.  I knew someone from the group could help me.


    ------------------------------
    Melanie Howell
    Financial Systems Analyst
    LifeLink Foundation
    Tampa FL
    ------------------------------