Open Forum

    Don't miss out on the 2017 DSLUG Fall Conference! Save $100 by registering through July 31.  Register Now!

1.  Quick Query Filtering

Posted 04-20-2017 17:52
I may have asked this question before but I can't seem to find anything on this.  Is there a wild card character that can be used in the Quick Query results filter?  I've tired *, ?, and nothing seems to impact the filter.  For example, I wanted to filter out all accounts that begin with 920.  I can do this pretty easily in SL report menu queries (GL Transactions for example) by using "Not Between" 920000,920999 but can't figure out how to filter that range out with QQ.

My starting point is all accounts greater than 400000 (which is a fictitious account but gets me to the start of the income statement accounts).  I filtered that with "Account is greater than and includes 400000".  Now I want to narrow that down by filtering out all accounts beginning with 920

Thanks,
Jon

------------------------------
Jon Wissler
Vice President and CFO
Pacific Northwest Generating Cooperative
Portland OR
------------------------------


2.  RE: Quick Query Filtering

Posted 04-20-2017 18:10

Jon,

 

You would do this with the advance filtering capabilities of QQ by clicking the little arrow to the left of the filter box top right in QQ.  Then click add filter and set it up in one of the two fashions below:

 

Use the Between operator

 

Use greater than AND less than in combination

 

_________________________________________

Joe Miller  |  Chief Information Officer

www.flotech.net

<a href=image007.gif@01CD718C.407C4AE0">

Spacer

<a href=image003.png@01D2036A.F5038130">

 






3.  RE: Quick Query Filtering

Posted 04-20-2017 18:15


------------------------------
Jonathan Van Houtte
Senior Consultant
XLstatements, LLC
Clifton Park NY
------------------------------



4.  RE: Quick Query Filtering

Posted 04-24-2017 13:59
Thanks Jonathan - I hadn't seen the initial "And Not" qualifier which seems to have done the trick.

As long as I'm asking low level questions is there anyway you can easily reorder the filter statements so that they have a logical flow?  I built one in steps to get down to the set I want but the filter statements aren't logically ordered since I was experimenting as I went.

Thanks again,

Jon

------------------------------
Jon Wissler
Vice President and CFO
Pacific Northwest Generating Cooperative
Portland OR
------------------------------



5.  RE: Quick Query Filtering

Posted 04-24-2017 14:23
It would be nice if there were a way to use parentheses with Or operators in Quick Queries. E.g., where one needs something like:

(Acct Begins With "1" And Sub Begins With "X") Or (Acct Begins With "2" And Sub Begins With "Y")

I don't know a way to do that with QQ unless one manually creates a new SQL view with it hard-coded.

If you know SQL or attend the SQL premium session in Tampa, I will show how to easily create such a report from within Excel at our sponsor session on Thursday.

------------------------------
Jonathan Van Houtte
Senior Consultant
XLstatements, LLC
Clifton Park NY
------------------------------



6.  RE: Quick Query Filtering

Posted 04-24-2017 14:47

There is a way to use OR and do completely different sets of criteria.  See the screenshot below for how I'm looking for inventory items using two different sets.  I've added the parenthesis in red to highlight the groupings.  Hitting the down arrow (where the red arrows are pointing) as you select criteria will allow you to build independent clauses.

 

 

 

_________________________________________

Joe Miller  |  Chief Information Officer

www.flotech.net

<a href=image007.gif@01CD718C.407C4AE0">

Spacer

<a href=image003.png@01D2036A.F5038130">

 






7.  RE: Quick Query Filtering

Posted 04-24-2017 14:52
Cool. Thanks, Joe!

------------------------------
Jonathan Van Houtte
Senior Consultant
XLstatements, LLC
Clifton Park NY
------------------------------



8.  RE: Quick Query Filtering

Posted 04-24-2017 14:56
Thanks Joe - looks like I have some more experimenting to do !!

Jon

------------------------------
Jon Wissler
Vice President and CFO
Pacific Northwest Generating Cooperative
Portland OR
------------------------------



9.  RE: Quick Query Filtering

Posted 04-26-2017 09:38
How did you access the "Either" operator?

------------------------------
Bill Degnan
Admiral Consulting Group
East Brunswick NJ
------------------------------



10.  RE: Quick Query Filtering

Posted 04-26-2017 10:20
Hi Bill,

Click on the drop down box for Add Filter and chose And Either......this will give you the either you are looking for....

Thanks,
Michael

------------------------------
Michael ONeill
Controller
Eastex Telephone Cooperative
Henderson TX
------------------------------



'