NOT filter in QB?

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.

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.

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.

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? :)

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.

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.

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.

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.

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.

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.

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!  :)

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.