Tasked to create a BAQ Dashboard that shows invoiced Sales and Backlog Orders for specified criteria. These are 2 very disparate items, in that, the invoices are from the invoice tables joined to the order tables; and the backlog orders are the order tables for quantities not yet invoiced.
I elected to do this using a technique in the BAQ Course - Create a top level query, inner subquery, and union the remaining queries based on their unique criteria. I will screenshot the BAQ pages to present some clarity.
Ultimately, I would like to get the invoiced values and backlog values on the same line.
Here is the dashboard output:
To Continue, here is the subqueries list. There are more BKLGs (one for each of the top queries).
Invoice Query:
Display Fields:
Calculated fields:
The BacklogValue field is set to 0. In the BKLG queries, I do the opposite. Set the Invoices = 0 and sum(OpenValue).
If I were doing this as a SQL, I would select From the entire BAQ and link on the facility value, but I don’t know if the BAQ allows me to nest that far down. Thanks Tony.
Instead of the union, bring both of the subqueries into a top level query and join on Facility.
1 Like
Hi Brandon, Thanks for looking at this BAQ. I already have a Top Level Query for the data. The remaining subqueries are unions to it. The Facility field is a calculated field created by me to sort the data as well as designate the criteria. How would I be able to create another top level query? I need the union to represent the disparate types of data. They are even using different date ranges. I have 4 parameters (invoice start / end dates, backorder start / end dates). Not a standard type of reporting to put backlog and invoices together, but… do you have time for call / TEAMS or is it usual to continue in the forum?
Usually it just continues on the forum, (we all have day job right??)
I don’t know that I would have constructed the query in that way, but you could probably set the top level to a subquery and make another top level that groups and sums the columns to get them on a single row.
You’ll have to add some parenthesis in the subquery list to make that work.
The way I have done this in the past is to create several CTE Queries to gather up the data summarized by Facility… then have a top query that selects the facilities, and do a join to the sub queries to retrieve the sub total from each one.
Thanks Brandon. I will consider trying this.
Hi Tim, I was considering using CTE queries, but this is an area of BAQs that I have little experience. Do you have any examples you would be willing to share? Keep in mind, the facility information is created as calculated fields in the subqueries being unioned as I tried to show in screenshots.
sorry, no examples… i was using “facility” in your example… I had a similar where we created calculated field that group various product types into a summary… but it was all done in calculations.
In this case, just use a CTE as a subquery. In the case of unions it has to roll up to a Top Level, or a CTE. So if you just use Subquery it won’t work. CTE’s are usually talked about with hierarchical data, because it can do that, but it doesn’t have to do that.
2 Likes
^^^
Body seems unclear, is it a complete sentence?
Thanks Brandon. I have looked at some CTE examples and they are all hierarchical with one subquery and the recursive union. The CTE query sets the calculated field to 1 and the Union query has the where clause “CTE.calculatedfield + 1”. My Union queries all require different where criteria. Would that still work using the CTE / Unions approach. In addition, I have 2 subqueries: 1 for invoice data and 1 for backlog orders. Any insight here is greatly appreciated. Hope I have explained it more clearly.
1 Like
Well I did a little more research; and found that I could create mulitple CTE queries with the Union queries. I then created the Top Level query to query the CTEs linked by Facility Type. Voila! Now to tidy up the loose ends. Thanks everyone for your ideas and guidance. Have a great weekend!
1 Like