Excel Parameter To Filter Epicor BAQ

Kpearson · May 2, 2024, 6:16pm

All,
Want to preface by saying I am VERY new to using epicor and writing queries. I imagine I am in over my head with this task, but hoping for some help on this regardless.

What I have is a BAQ that is pulling all of the ‘materials’ for a specific job. What I want is to set up an excel ‘dashboard’ that can filter the query to only pull materials / data from a specified Job, as determined by the user. I can set up this BAQ very easily on the epicor side, but I would need the ‘job’ field to be able to change based on user selection (i.e. what job they want to pull materials for). Currently I just pull all the data into excel and then filter it down a lot to get the data that is needed.

The reason it is in excel is because I am comparing a CAD (solidworks) bill of materials to the epicor bill of materials, in order to load our ERP system. The problem really is how slow it is to ‘update’ the data every time. It takes upwards of 30 seconds to pull all of that data in.

I am most comfortable with the excel stuff, but have not done anything with REST, or written too many queries.

Any help is much appreciated! I can give more details as needed.

CSmith · May 2, 2024, 6:26pm

There should be examples on here to grab OData results from a REST URL
Here is a sample Power M Functions/Query against the OData Feed parameterized to create Excel Dataset/Table…

Function p:

let
    Source = (paramName) => (Excel.CurrentWorkbook(){[Name=paramName]}[Content]{0}[Column1])
in
    Source

Function t:

let
    Source = (paramName) => (Text.From(paramName))
in
    Source

Function dt:

let
    Source = (paramName) => (Excel.CurrentWorkbook(){[Name=paramName]}[Content])
in
    Source

Table Query (Example uses above functions and named ranges to provide variability):

let
    baseTableData = Table.TransformColumnTypes(Table.AddColumn(Table.RenameColumns(Table.SelectColumns(
        OData.Feed(p("URI_Protocol")&"://"&p("Server_FQDN")&"/"&p("Epicor_Instance")&"/"&p("API_Version")&"/"&p("ServiceName")&"/"&p("BAQ")&"/"&"?ModelYr='"&t(p("ModelYr"))&"'&CostingSite='"&t(p("SiteCostID"))&"'&LaborRate="&t(p("LaborRate"))&"&BurdenRate="&t(p("BurdenRate")), null, [Implementation="2.0"])
        ,{"PartCost_CostID","ProdGrup_Description", "EZ_CfgOptionsTrailers_Trailer", "TrailerMasterPart_PartDescription", "Calculated_OptionType", "TrailerPart_PartNum", "PcConType_TypeDesc", "TrailerPart_PartDescription", "Calculated_IsDefault", "Calculated_TotalCost","Calculated_IncCost"}),{{"ProdGrup_Description", "Product Group"}, {"EZ_CfgOptionsTrailers_Trailer", "Trailer"},{"PartCost_CostID","Site CostID"}, {"TrailerMasterPart_PartDescription", "Trailer Description"}, {"Calculated_OptionType", "Option Type"}, {"TrailerPart_PartNum", "Option PartNum"}, {"PcConType_TypeDesc","Config Desc"},{"TrailerPart_PartDescription", "Option Description"}, {"Calculated_IsDefault", "Is Default Option"}, {"Calculated_TotalCost", "Item Total Cost"}, {"Calculated_IncCost", "Inc Cost"}}),"Trailer Model", each if Text.Contains([Trailer], "-01") then [Trailer Description]&" (Alum)" else if Text.Contains([Trailer], "-10") then [Trailer Description]&" (Galvanized)" else if Text.Contains([Trailer], "-12") then [Trailer Description]&" (Painted)" else [Trailer Description]&" (?)"),{{"Is Default Option", type logical}, {"Item Total Cost", type number}, {"Inc Cost", type number}}) 
in 
    baseTableData

Kpearson · May 2, 2024, 6:37pm

Sorry,
I am confused. Let’s just simplify it to that I have a value in cell ‘A1’ of my excel sheet that I want to use as an argument / parameter for my BAQ filter in epicor. What is the easiest way to accomplish this?

Right now, I just have a URL set up using an Odata connection type in excel.

NateS · May 2, 2024, 6:38pm

Welcome! You are in good company.

I would suggest finding a way to do this comparison in an Epicor Dash. Tell us more about the comparison. Do you have all the CAD BOM data available to pull into a UD table in Epicor? If so, it may be faster to do the dashboard in Epicor. Tell us more about your business problem. We are in over our hears too, but we can keep each other afloat!

Kpearson · May 2, 2024, 6:43pm

There is a complex VBA macro written to ‘combine’ and transform the SW data into materials and quantities the way epicor expects it to be. I want to keep it in Excel if I can help it.

CSmith · May 2, 2024, 6:47pm

Not sure about this, but I can pull a set of ~10k records (11M chars of data) in about 10-15 seconds (remotely) not sure how much data you are moving to compare against. Query takes 10.5 sec to run before delivering transferring the data copy to excel for same takes about 4-5 sec to deliver same dataset from BAQ.

CSmith · May 2, 2024, 6:55pm

The provided P function will grab named parameters using named cells in excel.

The T function is simply to turn a numeric value to text for use in the query.

Here is the parameter set page…
Cell B2 is a Named Cell(Range) called “URI_Protocol” and if you look in the code I provided I used the p(“URI_Protocol”) us allow a dynamic change in my query if needed I could hard code it too…

Here is another example of named range as the parameter for ModelYr in the Query above (You can see this highlighted):

CSmith · May 2, 2024, 8:50pm

Does any of this help?

klincecum · May 3, 2024, 11:57am

Yep, even those of us that seem like we know what we are doing.

Screen_Shot_2019-01-17_at_2.02.03_PM

NateS · May 3, 2024, 12:32pm

I can’t speak for the OP, but I don’t get it.

dcamlin · May 3, 2024, 12:58pm

Glad I’m not the only one!

Tfd108 Idk GIF by Tacoma FD

kve · May 3, 2024, 1:34pm

Hi @Kpearson,

I threw this Excel Spreadsheet together, I think it does what you’re looking for. You can paste in the BAQ Data to the BAQ sheet, and there will be a dropdown on the Report sheet with all the jobs in the BAQ. Select it with the dropdown, and it should fill with that Job’s BOM. You’ll have to do some tweaking for your own BAQ Columns, but as long as the JobNum is in the first column, this should work.
ExcelBOMFilter
xlJobBOMFilter.xlsx (22.3 KB)

Kpearson · May 3, 2024, 1:43pm

Kevin,
This is pretty much exactly what I am looking for as an end result, except instead of having all the info pre-populated in the excel sheet, I could run a BAQ that takes the input from your ‘select job’ field. That way, I am pulling much less data from epicor.

I have written SQL queries using a program called driveworks (and in Microsoft SQL Server Manager), and you can write the query to filter it based on a dynamic value.

select
[JobHead].[Company] as [JobHead_Company],
[JobHead].[JobClosed] as [JobHead_JobClosed],
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[PartDescription] as [JobHead_PartDescription],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[Description] as [JobAsmbl_Description],
[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[JobOper].[OpDesc] as [JobOper_OpDesc]
from Erp.JobHead as JobHead
left outer join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
left outer join Erp.JobOper as JobOper on
JobAsmbl.Company = JobOper.Company
and JobAsmbl.JobNum = JobOper.JobNum
and JobAsmbl.AssemblySeq = JobOper.AssemblySeq
where (JobHead.JobClosed = FALSE and JobHead.JobNum = ‘TEST’)

I would basically want the above, but where it says ‘TEST’, it would take the Select Job input. I can figure out how to get this set up pretty easily using the epicor BAQ, but when I try to do an Odata pull from excel using this BAQ I don’t see a way to enter a desired parameter for ‘TEST’

kve · May 3, 2024, 1:52pm

You should be able to do that using a defined parameter in the BAQ. If you go to Actions > Define Parameters, set up a nvarchar type parameter name jobNum, then in your subquery criteria, set a row where JobHead.JobNum = “Specified Parameter” and select the jobNum parameter. You can pass that with your oData Rest call.

CSmith · May 3, 2024, 2:14pm

I provided the needed code (Power M functions needed to do this from named cells) above.

CSmith · May 3, 2024, 2:18pm

Well, I just provided what he asked for, but it might not be what he really needs or knows how to implement…

Kpearson · May 3, 2024, 2:21pm

Can you explain it a bit more?

CSmith · May 3, 2024, 2:24pm

Agree, I have parameters in my BAQ. I provided info on using parameters in Excel like he asked.

He should have a BAQ the same as the one he provided then he can use the code I provided to do and OData.Feed pull of the data using parameters his BAQ should be available at a URL to simplify like this (see highlighted parameters):

CSmith · May 3, 2024, 2:25pm

There is a lot I could explain, but which part(s) are you needing explained…?

Kpearson · May 3, 2024, 5:26pm

The last image you sent looks like it is a REST web environment. Does that help me automatically pull the ERP data into excel? Or is the above just the way you write the query?

I am looking at the example you put above with p, t, and dt, and have a few questions… Is this in the query builder?

I have not used this much but I have heard the term ‘parameter’ thrown around. How can I feed a parameter from a named range into the query to filter it? Since I am doing an ODATA pull, it seems a lot less advanced:

How can I deliver the named range as a filter here?

AGAIN, very, very green when it comes to this. This project was tossed at me from my manager. Thanks for all the help so far!