We have an inventory report that runs each night and emails the results to our inventory manager. The result is our inventory values for the 4 main groups we stock, fabricated parts, purchase parts, rollers and raw material. The report is a single line excel file with the date and the sums of all the part classes in stock in the 4 different groups.
The manager copies this data into a larger spreadsheet that holds a running totals and builds charts to manage the inventory
I want to bring this all back into Epicor by somehow getting the report data into a UD table then building a dashboard from the data.
It seems easy enough on the surface but I just can’t wrap my head around to get the data into the UD table.
Anyone ever done something like this before?
At the risk of making this too long to follow The SSRS report is made from multiple BAQs on a report data definition. I am totaling inventory dollars by part class then summing those in one of the 4 groups. So Raw Material could have a dozen part classes in it.
This all came to head when I learned the inventory manager was using a script in Outlook to automatically download the attachment and copy it over to the main spreadsheet. We don’t allow Outlook rule script for security reasons.
Not an automatic way, but you could build a connection to Epicor in Excel and give him a button to add the row. 
I guess I could make some type of API from Excel to the report. I was just thinking since the BAQs are in Epicor and the UD table is in Epicor how could I get the data from the SSRS report to the UD table without using Excel at all?
Well, you are on prem, so you could probably do something in SQL.
I can’t think of any time the data is found in a method that you could grab. I am only aware of the data being in the SSRS Reports database.
If I’m understanding this correctly… you have a report that runs based on (4) BAQs.
Can you utilize the newer BAQ designer to pull those (4) BAQs into a 5th (new) BAQ as reference queries? Then you can use that 5th BAQ to do the summing that the report was doing on the SSRS side?
Yes, I’m thinking something like this but I’m not sure how to make the 4 separate BAQs connect with the 5th one. In the report they act as data sources.
So let me step back and re-word what I need.
I need to sum the dollars of the different part classes in inventory that make up the 4 different groups, i.e. all the part classes of purchase parts are summed, all the part classes of raw materials are summed, etc., then write those sums to fields in a UD table with the date to create historical, running table of inventory value. I could then create BAQs and dashboards from this table. Does that sound about right?
My 4 separate BAQs gather the data for the 4 groups.
Couldn’t you just do one BAQ with subqueries joining on the company, call that from a scheduled function on a daily basis then insert the results into a new row of the UD table.
Then you could have a second BAQ that returns the results from the ud table and use an odata call in the spreadsheet to refresh.
You could use standard functionality which is to build a cube in Epicor.
The executive dashboard allows you to run a baq on a schedule and populate the results into a cube that has “UD Like Fields”
there are a four key fields, 10 - decimal, 5 - intergers and 10 shortChars
I have done this to determine how many PO Suggestions buyers were getting every day so we could determine their workload.
Sounds like the perfect use case for a CTE!!
I’ve not work with executive BAQs and Cubes before. You say these UD like fields, so they are not really UD fields? I wonder if I could do something like this then use a BPM to set the data to the UD fields I have so its saved for posterity.
I need to research Cube maintenance.
I hadn’t thought of using a CTE to consolidate my BAQs. Thats a great idea. I think I’ll try to get my 4 BAQs into 1 first then look for a way to work with the data the way I need. Thanks
All you have to do is come up with the common field and use that as your anchor for all 4 queries.
Could that be as easy as company or plant ID or should it be more specific?
Looks like it would be date for what you are doing. I always try to be more specific.
So I’ve rebuilt my BAQ and now have everything in one query with a single row returning the sums of the 4 groups I need.
Now I want to get that data into my UD table where Key1 is the date and Numbers 01, 02, 03, 04 are the group totals. My goal is to have the query run each night after business hours and populate the UD table. What would be a good way to do this?
I use this type of a routine to do this. It is post processing in GetList of the baq, because it uses UpdateExt. You could run the 4 BAQS from a scheduled function and add data using the same keys. I got the function from the forum.
/* Update UD28 for WIP Charting */
Ice.Diagnostics.Log.WriteEntry(" In Update UD28");
using (var updater = this.getDataUpdater("Ice", "UD28"))
{
var resultQuery = result.Results;
//.Where(row => !string.IsNullOrEmpty(row.RowMod) && row.RowMod != "P");
DateTime todaysDate = DateTime.Now;
Ice.Diagnostics.Log.WriteEntry($"Updating wip Today is {todaysDate} ");
foreach (var ttr in resultQuery)
{
string saveDateKey = String.Format("{0:MM-dd-yy}",todaysDate);
var ds = new Ice.Tablesets.UpdExtUD28Tableset();
// Query to object mapping
//{
var UD28 = new Ice.Tablesets.UD28Row
{
Company = Constants.CurrentCompany,
Key1 = "WIP",
Key2 = ttr.PartClass_ClassID,
Key3 = ttr.Calculated_FiscalPD,
Key4 = ttr.Calculated_FiscalQtr,
Key5 = saveDateKey,
};
UD28.SetUDField<System.Decimal>("Number01" , ttr.Calculated_WipMtl);
UD28.SetUDField<System.Decimal>("Number02" , ttr.Calculated_WIPLabor);
UD28.SetUDField<System.Decimal>("Number03" , ttr.Calculated_WIPBrdn);
UD28.SetUDField<System.Decimal>("Number04" , ttr.Calculated_WIPFOH);
UD28.SetUDField<System.Decimal>("Number05" , ttr.Calculated_WIPTotal);
UD28.SetUDField<System.Decimal>("Number06" , ttr.Calculated_WIPDays);
UD28.SetUDField<System.DateTime?>("Date01" , todaysDate);
UD28.SetUDField<System.String>("ShortChar01" , ttr.PartClass_ShortChar01);
UD28.SetUDField<System.String>("ShortChar02" , ttr.Calculated_ProdGroup);
ds.UD28.Add(UD28);
BOUpdErrorTableset boUpdateErrors = updater.Update(ref ds);
// if (this.BpmDataFormIsPublished()) return;
Ice.Diagnostics.Log.WriteEntry($"Updating Today is {saveDateKey} WIP { Math.Round(ttr.Calculated_WIPTotal,2)} for period {ttr.Calculated_FiscalPD}");
}
}
I’ve never used a function before but its never to late to learn. I’ve gotten my 4 BAQs consolidated down to 1 now with a single row of data. I’ll look into the code to see what I can do.
You can also do a scheduled ubaq, I wanted to do several so I put them into one function.
I am using this function from @kve
https://www.epiusers.help/t/helper-function-library-to-get-data-from-baqs-excel-files/110864