Dang functions! You are determined to get me to learn them, huh?! I guess I’ll have to take a look again.
I am pulling together the beginnings of a function to save data to UD06. The data should come from my 4 BAQs. I have created an RDD to pull all the data together. How can I use that RDD to export the data in the function? Or do I need to somehow reproduce the BAQs in the function?
Use the Ice.Contracts.DynamicQuerySvcContract to pull the data into a DataSet for each BAQ.
1 Like
I am not sure how to do that in a function. I have still not yet made a successful function in Epicor.
You need to add a reference to the service in the function library:
Add the parameter for BaqID:
// assume no parameters
QueryExecutionTableset dsQueryExecution = new QueryExecutionTableset();
DataSet dsResults = new DataSet();
CallService<Ice.Contracts.DynamicQuerySvcContract>
(
bo => { dsResults = bo.ExecuteByID(BaqID, dsQueryExecution); }
);
dsResults will then contain all the columns and data from your BAQ.
1 Like
I use Executive Queries for several tasks like this, seems perfect for what you want to do.
2 Likes
I guess I don’t understand what makes an executive query the right choice for this. I don’t normally make them.
I was just looking there and it looks like you can only use one BAQ as the input. If I understand correctly, this is just to make a chart out of a BAQ, right?
Does the executive query somehow save data for historical views?
Thanks!
I think it runs the BAQ and summarizes it on a schedule, so it has so save the results to make the graph. I think the data is stored in the Ice.SysCube* tables. But I’ve never used them, I’d imagine @Joseph_Martin can give a more detailed response.
1 Like
@Evan_Purdy is correct, Executive Query lets you run a BAQ on a schedule, and stores the results in the SysCube table.
You can either have it delete the previous run data or append the new run data to the old.
You are limited to how many columns from your BAQ you can store, something like 10 character columns, 5 int, 10 decimal so if you need more than that then the UD table is the way to go
1 Like
Is there any way to use an RDD to feed an executive query or dashboard? The problem I am running into is that I have 2 queries that I want to show the summary data for, in the same chart. For example:
Ignoring the actual values (populated as random values for illustration). Here I have two sources of data.
- A list of each department, and the total Open Job Target Value for that department.
- A list of every open job and the current value, with the next department in the op sequence. In this example we are only looking at the sheetmetal department.
Each day I have to run the BAQ report that is based on my RDD, which contains all four queries needed to make this work together. In that RDL, one of the data sources was modified to include data from two queries, thus merging the results in the way that I need.
How To Merge These into One Report? - Kinetic 202X - Epicor User Help Forum (epiusers.help)
How do I include more than one BAQ in my executive query? I can’t join the queries in the dashboard, right?
I think that the executive queries will do it. I will post back when I get it all sorted out.
Thanks!
I don’t know of a way to use a RDD for the Cube process.
I think the easiest way would be to make a Executive Query / Cube for each data source BAQ, and then combine them in your BAQ that pulls your cube data for your report.
Hmm, I would think you would have to find a way to merge your two BAQ’s into one, or perhaps make a BAQ that joins to Cubes together. But perhaps if your data is complex enough it makes more sense to run BAQ’s from a scheduled function and write into UD tables like you were thinking.
You can see an example of running a BAQ in a function in this thread:
https://www.epiusers.help/t/code-review-epicor-function-for-sending-customer-statement-via-email/101500/11
I have done something similar with an executive query. The cube builder runs at a scheduled time to capture the history. When it finishes a BPM triggers the daily report that uses the history.
1 Like
How do you combine BAQs in a single BAQ? I just looked at the SysCube table for the first time. If I am understanding this correctly:
- I create an executive query with my existing BAQ. This ‘saves’ the data from the query into the SysCube table (based on the Delete Action).
- Repeat step 1 for every BAQ that needs to be included in the final executive graph.
- Create a new BAQ that pulls data only from SysCube using the right criteria to get only the data that I am interested in. This BAQ essentially ‘joins’ all the data from the various BAQs that I pushed to SysCube. When creating this final BAQ, do I put in multiple copies of the SysCube table widget into the designer view so that I can join them together and group/show the final data I need?
I think I am getting the idea. Thanks again everyone!
Maybe I missed something. I just created a new Executive Query with the CubeID: NateTest. It is fed by a BAQ (though the BAQ needs a parameter and it never asks for it). After I saved the executive dash and then submitted the process. I can see that the process finished. I did it twice just to make sure I did it right.
When I look in the SysCube table I don’t see any values with CubeID NateTest.
I also keep trying to add the decimal OpenValue to the Mapping Set Details, but it keeps saying a Valid Mapping Set is required when I try to save.
Did I do something wrong here, or am I just understanding this wrong?
Referring to the help files I found this graphic that is very confusing.
Why is address in Interger1, Decimal1 and Character1?
Thanks!
Nate
Your steps seem correct. It should be New Cube → Set BAQ ID and Delete Action → New Mapping → Set → Set the Dimension fields to roll up → done.
I wonder if nothing ran because of the parameter, running a BAQ on a schedule with the SysAgent will ignore any parameters in a BAQ which could return nothing depending on the setup. Can you try removing the Parameter and see if you get results then?
Also, that screenshot / graphic seems off, you would want to assign number fields to number dimensions, strings to text, etc. I would think that cube in that screenshot wouldn’t work at all.
I’m so confused. Are we still discussing saving historical reports or did we switch topics?