I am trying to figure out a way to get Vantage 8.03.404 to combine BAQs. Is it possible in an Executive Dashboard?
To explain what I need, here is an example of what I would do in Microsoft Access:
1. I create a Pass-through SQL query to pull data from the PRCheck, PREmpMas, and PRChkDtl tables. I use SUM and Group By to add up the numeric fields in PRChkDtl so that I summarize all the PRChkDtl records into 1 record for each PRCheck.HeadNum.
3. I create 3 more Pass-through SQL queries all pulling from the PRChkDed table. Each of these 3 queries pulls the same field: PRChkDed.DeductionAmt. What makes each of these 3 queries different is that 1 query has a WHERE clause of PRChkDed.DeductionID = 401KE; 1 query has WHERE PRChkDed.DeductionID = 401KL; 1 query has WHERE PRChkDed.DeductionID = 401KM. Each of these Pass-through SQL queries renames the PRChkDed.DeductionAmt field to a different name: EmpDef, EmpLoan, & EmpMat.
4. Then I create 4 Make Table Microsoft Access queries to turn each of the 4 Pass-Through Queries into Tables.
5. Finally, I create another Microsoft Access query on all 4 tables which pulls all the matching data for each payroll check header from all 4 tables into 1 row for each PRCheck.HeadNum.
Then I run everything with a single Macro.
Will a Vantage Executive Dashboard do this?
In the Epicor Tools User Guide on page 405, it reads, "You can create separate BAQs that display the sales order table and the field service tables, but the BAQ functionality does not allow you to display this data together. By using cube tables, however, you can populate an executive dashboard's data by running both queries, one after the other, to first consolidate and then view the final revenue numbers."
However, I have read the entire chapter and I cannot figure out how to accomplish my goal using 4 BAQs that I have written in Vantage.
Lynn
To explain what I need, here is an example of what I would do in Microsoft Access:
1. I create a Pass-through SQL query to pull data from the PRCheck, PREmpMas, and PRChkDtl tables. I use SUM and Group By to add up the numeric fields in PRChkDtl so that I summarize all the PRChkDtl records into 1 record for each PRCheck.HeadNum.
3. I create 3 more Pass-through SQL queries all pulling from the PRChkDed table. Each of these 3 queries pulls the same field: PRChkDed.DeductionAmt. What makes each of these 3 queries different is that 1 query has a WHERE clause of PRChkDed.DeductionID = 401KE; 1 query has WHERE PRChkDed.DeductionID = 401KL; 1 query has WHERE PRChkDed.DeductionID = 401KM. Each of these Pass-through SQL queries renames the PRChkDed.DeductionAmt field to a different name: EmpDef, EmpLoan, & EmpMat.
4. Then I create 4 Make Table Microsoft Access queries to turn each of the 4 Pass-Through Queries into Tables.
5. Finally, I create another Microsoft Access query on all 4 tables which pulls all the matching data for each payroll check header from all 4 tables into 1 row for each PRCheck.HeadNum.
Then I run everything with a single Macro.
Will a Vantage Executive Dashboard do this?
In the Epicor Tools User Guide on page 405, it reads, "You can create separate BAQs that display the sales order table and the field service tables, but the BAQ functionality does not allow you to display this data together. By using cube tables, however, you can populate an executive dashboard's data by running both queries, one after the other, to first consolidate and then view the final revenue numbers."
However, I have read the entire chapter and I cannot figure out how to accomplish my goal using 4 BAQs that I have written in Vantage.
Lynn