Solved

NOT filter in QB?

Posted on 2011-09-19
13
1,560 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
  • 7
  • 6
13 Comments
 
LVL 8

Expert Comment

by:KoiGirl
Comment Utility
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:KoiGirl
Comment Utility
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
Comment Utility
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
 
LVL 8

Expert Comment

by:KoiGirl
Comment Utility
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:KoiGirl
Comment Utility
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:KoiGirl
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:RolandGarton
Comment Utility
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:
KoiGirl earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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:KoiGirl
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now