How to ignore hidden fields in group by?
At the moment I am trying to build a report containing one sub query. Let's say the main query contains a metric "Qty. items received" and the sub query contains a metric "Qty. items put on stock". I'd like to sum both metrics per hour, because I am interested in how many of the items I have received and how many have already put on stock.
Because I am only interested in items received in a specific way, I added a filter to my Master query to just get those items. I also left outer joined both queries on "Item code".
To achieve this, I add "Item code" to both queries and to the join condition. After that I hide both columns "Item code" as I do not want to display this field. The problem which arises here, is that "Item code" is now added to the GROUP BY clause of the query, and my report is no longer displaying a sum of "Items received" and "Items sold" per hour, but per hour and item code... which is not what I am looking for.
Is there a way to have a join between two sub queries, of which the columns (that are part of the join condition) are ignored in the group by?