I am seeing a difference between how QuickBooks summarizes classes in the Profit and Loss by Class report and the canned "profit and loss by class" pivot table in QQube. In QuickBooks the report lists each subclass and then provides a total for the whole class. In the QQube pivot table each subclass seems to be treated independently.
Now here is where it gets a little weird. I have simplified the names for the example. We have three classes ClassA, ClassB, and ClassC. Each has at least one subclass. For instance:
ClassA - SubClass1, SubClass2
ClassB - SubClass1
ClassC - SubClass1, SubClass3
As you can see, the SubClass names are not unique.
I was expecting the pivot output to be along the lines of:
ClassA ClassA-SubClass1 ClassA-SubClass2 ClassB ClassB-SubClass1 ClassC ClassC-SubClass1 ClassC-Subclass3
The columns in the QQube pivot table are:
ClassA ClassB ClassC SubClass1 SubClass2 SubClass3
SubClass2 and SubClass3 are unique so the values represented in the pivot table are correct. QQube is rolling all of the "SubClass1" data from the three different master classes into a single independent column. The canned report in Quickbooks maintains the relationship of the subclasses named "SubClass1" to the respective master class names but the QQube pivot does not. It looks like having re-used the same name in a subclass is causing the trouble. Right now, I need to parse the existing data. Since I use a combination of the reports from QQube and inside QB i just need them to match up. Is there an easy mod to the QQube pivot table report that will help me keep these straight? Do I need to pare down the pivot to a single class and its respective subclasses to accomplish that (not ideal..) In this case it would be 3 separate pivots, one for each master class. Is there some other approach I am not thinking of?
I am not looking for a religious war on DB design. :-) However, as a secondary ask I would appreciate guidance on how to best structure this for the future. It looks like I have some remodeling to do and I would prefer to do it just once! I assume using unique subclass names might be a simple fix.
If you use a class name and use it in a pivot table, it will group by that individual class name - even if it is repeated at different levels in other class names.
For example if you use class name Subclass1 and you have ClassA:Subclass1 and ClassB:Subclass1 then yes, that is by design and is proper.
If you want to keep the integrity of the class hierarchy, then you would use the Class Full Name field instead, as that contains both the ClassA and Subclass1 as ONE unit.
Choose a location