Solved

NOT filter in QB?

Posted on 2011-09-19
13
1,715 Views
Last Modified: 2012-05-12
I use the Class feature in QB only to identify expenses that I want excluded from certain calculations.  I would therefore like to run a report that shows all expenses without the EXCLUDE class.  What I need is a NOT EXCLUDE filter, but that doesn't seem to exist.  Any good suggestions how to easily remove EXCLUDE expenses from, say, a company P&L report?
0
Comment
Question by:RolandGarton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 8

Expert Comment

by:Annaliese Dell
ID: 36563332
You cannot filter by selecting all the classes because apparently the null class transaction lines will not be included in the report. This a tough one.

If you have Microsoft Excel and Access, you might try this. I tried it and it works though it's a PITA. I'm using QuickBooks Premier Contractor 2009 and Microsoft Access 2002.

 1.  Create a P&L report.
 2.  Create a second P&L report and modify by selecting the classes you want to EXCLUDE. To do that:
     a.  Create P&L report in QuickBooks
     b.  Select Modify Report from the Report menu bar
     c.  Select Class from the Filter listbox (left top) of the Modify Report window
     d.  Select Multiple Classes from the Class listbox (to the right of the filter listbox)
     e.  Scroll down the list of classes in the Select Class window checking all the classes to exclude
     f.   Memorize the report so you don't have to recreate it.
     g.  Make sure you repeat the modification steps if you add classes to exclude in the future
 
 3.  Export these two reports separately to Excel and save.
 4.  Import the two saved Excel spreadsheets into Access as Sheet1 and Sheet2
 5.  Unfortunately, QuickBooks writes some of the row headings to different columns so you will have to manually (or programmatically if you know how) copy those row headings to column E so all amounts have a field name in the table (part of the PITA). Make sure none of the cells in column E are null (the column with all the account names).
 6.  I used this query sql:
            SELECT Sheet1.Field5, Sheet1.[Jan - Dec 10], Sheet2.[Jan - Dec 10], Sheet1![Jan - Dec 10]+IIf(IsNull(Sheet2![Jan - Dec 10]),0,Sheet2![Jan - Dec 10]) AS Amount FROM Sheet1 LEFT JOIN Sheet2 ON Sheet1.Field5 = Sheet2.Field5;

     Explanation: what you are doing here in design view:
     
     a. join the two tables by Field5 taking ALL the records from Sheet1 (the table from the P&L report NOT modified) and ONLY the records from Sheet2 (the MODIFIED report) where the joined fields are equal

     b. QUERY COLUMN #1:  Select the field names from Column E of the first table (account names)
     c. QUERY COLUMN #2:  Select the amount column from Sheet1 (that will be a date range)
     d. QUERY COLUMN #3:  Select the amount column from Sheet2 (that will also be a date range)
     e. QUERY COLUMN #4:  Add the Second and Third columns together like this:
         
           Amount: Sheet1![daterange]+IIf(IsNull(Sheet2![daterange]),0,Sheet2![daterange])
           
          *this is saying if the value of Sheet2![daterange] is null, give it a value of zero

Now you can use this query to create a report.

As long as you: a) named the tables Sheet1 (NOT modified report) and Sheet2 (modified report) and b) made sure none of the account name column cells are null, you can just paste the SQL into a query in SQL view.

To check your figures and make sure the report reflects your intentions, go back to QuickBooks and add the Net Profit of the NOT modified report to the Net Profit of the MODIFIED report and the result should be equal to the NET PROFIT ROW, FOURTH COLUMN of the query (the column you named Amount).

Hope this helps. Or better yet, hope someone comes up with something better! Let me know how you make out.
0
 
LVL 8

Expert Comment

by:Annaliese Dell
ID: 36563575
I am sorry, Roland, I added where I should have subtracted. The column from the MODIFIED report should be subtracted from the column of the NOT modified report:

SELECT Sheet1.Field5, Sheet1.[Jan - Dec 10], Sheet2.[Jan - Dec 10], Sheet1![Jan - Dec 10]-IIf(IsNull(Sheet2![Jan - Dec 10]),0,Sheet2![Jan - Dec 10]) AS Amount FROM Sheet1 LEFT JOIN Sheet2 ON Sheet1.Field5 = Sheet2.Field5;

     e. QUERY COLUMN #4:  Subtract the Second from the Third column like this:
         
           Amount: Sheet1![daterange]-IIf(IsNull(Sheet2![daterange]),0,Sheet2![daterange])


My apologies.
0
 

Author Comment

by:RolandGarton
ID: 36563751
Interesting workaround to a problem that would not be a problem if only QuickBooks had a NOT filter.  The workaround is actually a little more complex than you have presented, because the report I want to export is a P&L report, which has account names in different columns depending on the level of indentation (subaccount).

Which gives me another idea, building on your 2-report concept.  I could export the two reports and concatenate the account names into a single column.  Then, in the first sheet, I could add a column that does a vlookup() for matching account names & amounts in the second sheet.  A simple subtraction ought to give the results I want.  It's essentially your SQL solution but done in Excel formulas, and might provide some nice added visibility as well.

Still klunky, though...will see if other brainstorms emerge.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Annaliese Dell
ID: 36563938
Yes, I did simplify by using a collapsed P&L report. Sounds like you know what you're doing. Your solution is a good one. Hopefully, someone will come along with something easier and less involved.

You could always fix the null cell and multiple column issues using VBA in the Access Report.

The NOT filter would be a handy feature with many uses in QuickBooks. Hopefully, they're listening? :)
0
 
LVL 8

Expert Comment

by:Annaliese Dell
ID: 36564197
Just thinking about your issue. I don't use QODBC reports feature but if you are familiar with QODBC, you could do the two reports with the "sp_report ProfitAndLossStandard" and Microsoft Access. If this is a report you run often, you could use VBA to:

1. create the pass thru queries for the two reports (not-modified and modified for classes)
2. put the results into two tables
3. create the query to use as the record source for the report (subtracting the class column from the other column)

You could create a form with listboxes from which to select DateMacros and classes.

That would eliminate messing around with the spreadsheets and would end with a user friendly form and mouse click report.

If you are not familiar with QODBC or sp_reports, I'll post more details. I don't know your circumstance, if you use Access or VBA or you're on a server or what. Just grasping at straws here. I'm interested to see if there is a simple solution to this problem.
0
 
LVL 8

Expert Comment

by:Annaliese Dell
ID: 36564434
This issue intriqued me so much I went ahead and created the form and wrote the code and it works beautifully. I'll post it on my blog. If you want to take a look at it, let me know and I'll post the link here. I think you can still get a free evaluation download of QODBC read version. You enter the dates on the form, select the classes to exclude, and click a button. Voila.
0
 

Author Comment

by:RolandGarton
ID: 36564496
A true geek!  I'm on a single-user system.  I'm familiar with VBA but not QODBC or sp_reports.  The functionality you describe could be useful in other situations, so it might be worth my seeing how it works.  If you can provide the links, plus some background information, I would appreciate that.  I probably won't get to it until later this week, though, given my schedule the next couple of days.
0
 
LVL 8

Accepted Solution

by:
Annaliese Dell earned 125 total points
ID: 36569296
QODBC free evaluation:

http://www.qodbc.com/qodbcDownload.htm

I was so impressed with the free evaluation that I bought the read/write version in 2006. Apparently, their new version is faster and better.

It's a driver that reads/writes to QuickBooks using Visual Basic and their syntax that is, IMO, easy to learn. This driver has added incredible functionality and automation to my record keeping.

I posted how to create a user input form that provides a P&L Report that excludes user selected classes at:

http://www.vbquick.com/2011/09/exclude-classes-from.html

I tested this and it's very pretty.    :)

I hope it works with QODBC 2011. Let me know how you make out if you try it.


0
 

Author Closing Comment

by:RolandGarton
ID: 36592704
You spent a lot of time on this and I appreciate it.  This is a somewhat complex solution to provide functionality that seems like ought to be built into QuickBooks, but it looks like it will work and be user-friendly once set up.
0
 

Author Comment

by:RolandGarton
ID: 36592763
Now, as I adapt this solution to my specific situation, I have some additional questions if you or any other readers might happen to know the answers.  I need to manipulate the results of these queries in Excel, so I'm trying to build Excel queries and formulas that do the job you've done with Access.

1. Do you know if there's a way to structure an SQL statement using sp_reports that will show a P&L report that also displays the BankNumber (called Note in QB) field for each account?  Presumably this would require a left join between the table produced by the sp_report creating the P&L query, and the Account table.  But I don't know if you can build that in the Microsoft query builder.

2. Is it possible to set criteria in the query builder that read from cells in the Excel spreadsheet?  If so, that would allow the Excel user to set values in the spreadsheet (such as start and end dates for a P&L report) that the query then uses in creating its recordset.

3. Do you know if QODBC can access custom fields created in QB?  For example, I have a QualityRating for each vendor in QB in a custom field.  Sure would be nice to have this rating appear in the results of a vendor query that show up in Excel.

4. I'm assuming that sp_reports cannot access custom, memorized reports in QB, right?

Again, you've alerted me to some functionality that I was not terribly familiar with, but that I will now be able to take advantage of in my work.  So I really appreciate it.
0
 

Author Comment

by:RolandGarton
ID: 36711666
By the way, I have solved item #2.  I can use VBA to read start and end dates from excel cells, and to update the SQL statement for the existing query.  Code available if anyone is interested.

Working on #3 now...ability to show QB custom fields in Excel query Tables.  Any suggestions would be appreciated.
0
 
LVL 8

Expert Comment

by:Annaliese Dell
ID: 36711866
You might get a better response by posting this as a new question since this one has been closed so no more points will be assigned for answers. A new question offers more incentive.

I'm not a huge Excel user and only use it when absolutely necessary or I'd help you out. I'm sure someone else would know more than I do on that subject. Access is just so much fun, I rarely use a flat file database like Excel.

Best of luck to you!  :)
0
 

Author Comment

by:RolandGarton
ID: 36714069
Thanks for the tip.  In case you ever wonder, I found the answer to my question:  If you create a a custom field called QualityRating, that field can be addressed in an SQL statement as CustomFieldQualityRating.
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By Linda Saltz Customized item descriptions from bills DO import into a customer invoice!   This is a wonderful tip for companies that want to use generic items like Hardware, Software, Mileage, Travel in their item list which helps keep the i…
You may need to view past transactions from previous QuickBooks files or other QuickBooks company files when:       1. you are working in another QuickBooks file       2. QuickBooks is closed QuickBooks users who would benefit from this artic…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question