SQL Query with Variable (Parameter) in Excel

Off Topic
1

Hi,

My finance department asked for excel spreedsheet imitating the method tracer and cost workbench. Basiclly they want a flat table showing all components for top level part. I am in the progreess of creating the query but I would already like to know how to pass a value from field to query in excel as a parameter. When I use the query with “declare” excel is not recognizing it as a parameter. When I tried to use Microsot Query editor and just paste my query with adding the ? it has an issue with graphic presentation and cannot find the tables ( seems like it does not like the syntax).
Did you ever mangage to achive something like this? Should I save it as a stored procedure and then use it in excel?

Thank you
Aleksander

declare @TopPart nvarchar(20) = ‘396-00FDP-4600’

select PartNum_L1, RevNum_L1, MtlSeq_L1, MtlPartNum_L1, PartDesc_L1, TypeCode_L1, BOML_L1, PartNum_L2, RevNum_L2, MtlSeq_L2, MtlPartNum_L2, TypeCode_L2, BOML_L2, PartNum_L3, RevNum_L3, MtlSeq_L3, MtlPartNum_L3, TypeCode_L3, BOML_L3, PartNum_L4, RevNum_L4, MtlSeq_L4, MtlPartNum_L4, TypeCode_L4, BOML_L4,PartNum_L5, RevNum_L5, MtlSeq_L5, MtlPartNum_L5, TypeCode_L5, BOML_L5, PartNum_L6, RevNum_L6, MtlSeq_L6, MtlPartNum_L6, TypeCode_L6, BOML_L6, x.PartNum as PartNum_L7, x.RevisionNum as RevNum_L7, x.MtlSeq as MtlSeq_L7, x.MtlPartNum as MtlPartNum_L7, Part.TypeCode as TypeCode_L7, BOML_L7 = 7 from
(select PartNum_L1, RevNum_L1, MtlSeq_L1, MtlPartNum_L1, PartDesc_L1, TypeCode_L1, BOML_L1, PartNum_L2, RevNum_L2, MtlSeq_L2, MtlPartNum_L2, TypeCode_L2, BOML_L2, PartNum_L3, RevNum_L3, MtlSeq_L3, MtlPartNum_L3, TypeCode_L3, BOML_L3, PartNum_L4, RevNum_L4, MtlSeq_L4, MtlPartNum_L4, TypeCode_L4, BOML_L4,PartNum_L5, RevNum_L5, MtlSeq_L5, MtlPartNum_L5, TypeCode_L5, BOML_L5, x.PartNum as PartNum_L6, x.RevisionNum as RevNum_L6, x.MtlSeq as MtlSeq_L6, x.MtlPartNum as MtlPartNum_L6, Part.TypeCode as TypeCode_L6, BOML_L6 = 6 from
(select PartNum_L1, RevNum_L1, MtlSeq_L1, MtlPartNum_L1, PartDesc_L1, TypeCode_L1, BOML_L1, PartNum_L2, RevNum_L2, MtlSeq_L2, MtlPartNum_L2, TypeCode_L2, BOML_L2, PartNum_L3, RevNum_L3, MtlSeq_L3, MtlPartNum_L3, TypeCode_L3, BOML_L3, PartNum_L4, RevNum_L4, MtlSeq_L4, MtlPartNum_L4, TypeCode_L4, BOML_L4,x.PartNum as PartNum_L5, x.RevisionNum as RevNum_L5, x.MtlSeq as MtlSeq_L5, x.MtlPartNum as MtlPartNum_L5, Part.TypeCode as TypeCode_L5, BOML_L5 = 5 from
(select PartNum_L1, RevNum_L1, MtlSeq_L1, MtlPartNum_L1, PartDesc_L1, TypeCode_L1, BOML_L1, PartNum_L2, RevNum_L2, MtlSeq_L2, MtlPartNum_L2, TypeCode_L2, BOML_L2, PartNum_L3, RevNum_L3, MtlSeq_L3, MtlPartNum_L3, TypeCode_L3, BOML_L3, x.PartNum as PartNum_L4, x.RevisionNum as RevNum_L4, x.MtlSeq as MtlSeq_L4, x.MtlPartNum as MtlPartNum_L4, Part.TypeCode as TypeCode_L4, BOML_L4 = 4
from (select Level2.PartNum_Level1 as PartNum_L1,Level2.RevisionNum_Level1 as RevNum_L1,Level2.MtlSeq_Level1 as MtlSeq_L1,Level2.MtlPartNum_Level1 as MtlPartNum_L1,Level2.PartDescription_Level1 as PartDesc_L1,Level2.TypeCode_Level1 as TypeCode_L1,Level2.BOMLevel_Level1 as BOML_L1,Level2.PartNum_Level2 as PartNum_L2,Level2.RevisionNum_Level2 as RevNum_L2,Level2.MtlSeq_Level2 as MtlSeq_L2,Level2.MtlPartNum_Level2 as MtlPartNum_L2,Level2.TypeCode_Level2 as TypeCode_L2,Level2.BOMLevel_Level2 as BOML_L2, x.PartNum as PartNum_L3, x.RevisionNum as RevNum_L3, x.MtlSeq as MtlSeq_L3, x.MtlPartNum as MtlPartNum_L3, Part.TypeCode as TypeCode_L3, BOML_L3 = 3 from
(select Level1.PartNum as PartNum_Level1, Level1.RevisionNum as RevisionNum_Level1, Level1.MtlSeq as MtlSeq_Level1, Level1.MtlPartNum as MtlPartNum_Level1, Level1.PartDescription as PartDescription_Level1,
Level1.TypeCode as TypeCode_Level1, Level1.BOMLevel as BOMLevel_Level1, x.PartNum as PartNum_Level2, x.RevisionNum as RevisionNum_Level2, x.MtlSeq as MtlSeq_Level2, x.MtlPartNum as MtlPartNum_Level2, Part.TypeCode as TypeCode_Level2, BOMLevel_Level2 = 2 from (select PartMtl.PartNum, PartMtl.RevisionNum, PartRev.Approved, PartMtl.MtlSeq, PartMtl.MtlPartNum, Part.PartDescription, Part.TypeCode, BOMLevel = 1
from erp.PartMtl join erp.PartRev on PartMtl.PartNum = @TopPart and PartRev.Approved = 1 and PartMtl.PartNum = PartRev.PartNum and PartMtl.RevisionNum = PartRev.RevisionNum
join erp.Part on PartMtl.MtlPartNum = Part.PartNum) as Level1
left join (select PartMtl.PartNum, PartMtl.RevisionNum, PartRev.Approved, PartMtl.MtlSeq, PartMtl.MtlPartNum from erp.PartMtl
join erp.PartRev on PartRev.Approved = 1 and PartMtl.PartNum = PartRev.PartNum and PartMtl.RevisionNum = PartRev.RevisionNum) as x on Level1.MtlPartNum = x.PartNum
left join erp.Part on x.MtlPartNum = Part.PartNum) as Level2
left join
(select PartMtl.PartNum, PartMtl.RevisionNum, PartRev.Approved, PartMtl.MtlSeq, PartMtl.MtlPartNum from erp.PartMtl
join erp.PartRev on PartRev.Approved = 1 and PartMtl.PartNum = PartRev.PartNum and PartMtl.RevisionNum = PartRev.RevisionNum) as x on Level2.MtlPartNum_Level2 = x.PartNum
left join erp.Part on x.MtlPartNum = Part.PartNum) as Level3
left join (select PartMtl.PartNum, PartMtl.RevisionNum, PartRev.Approved, PartMtl.MtlSeq, PartMtl.MtlPartNum from erp.PartMtl
join erp.PartRev on PartRev.Approved = 1 and PartMtl.PartNum = PartRev.PartNum and PartMtl.RevisionNum = PartRev.RevisionNum) as x on Level3.MtlPartNum_L3 = x.PartNum
left join erp.Part on x.MtlPartNum = Part.PartNum) as Level4
left join (select PartMtl.PartNum, PartMtl.RevisionNum, PartRev.Approved, PartMtl.MtlSeq, PartMtl.MtlPartNum from erp.PartMtl
join erp.PartRev on PartRev.Approved = 1 and PartMtl.PartNum = PartRev.PartNum and PartMtl.RevisionNum = PartRev.RevisionNum) as x on Level4.MtlPartNum_L4 = x.PartNum
left join erp.Part on x.MtlPartNum = Part.PartNum) as Level5
left join (select PartMtl.PartNum, PartMtl.RevisionNum, PartRev.Approved, PartMtl.MtlSeq, PartMtl.MtlPartNum from erp.PartMtl
join erp.PartRev on PartRev.Approved = 1 and PartMtl.PartNum = PartRev.PartNum and PartMtl.RevisionNum = PartRev.RevisionNum) as x on Level5.MtlPartNum_L5 = x.PartNum
left join erp.Part on x.MtlPartNum = Part.PartNum) as Level6
left join (select PartMtl.PartNum, PartMtl.RevisionNum, PartRev.Approved, PartMtl.MtlSeq, PartMtl.MtlPartNum from erp.PartMtl
join erp.PartRev on PartRev.Approved = 1 and PartMtl.PartNum = PartRev.PartNum and PartMtl.RevisionNum = PartRev.RevisionNum) as x on Level6.MtlPartNum_L6 = x.PartNum
left join erp.Part on x.MtlPartNum = Part.PartNum

2

I would recommend a stored procedure and then call it using a button and VBA.

Josh Owings

3

Are you by chance on Version10.1.5>? If so I’d recommend you don’t try to write VBA or do anything like that in excel and instead use the REST API to execute a BAQ. It is trivial and requires no coding

4

Hi Josh,

Thank you for your reply. As I am proficient in Sql but not in VBA. Do you have any code example where you pass the field value to query as a parameter and execute it from “Button”(?).

Thank you!
Aleksander

5

We are on 10.1.400.16 so no REST Services :(.

6

Bummer! May be worth upgrading just for this feature! (An many others)
See a demo below of how it works, it’s pretty sleek

7

Looks great Jose,

We will upgrade soon for the purpose of integration with Dynamics 365 by REST.

The purpose of using sql and excel is to pull the data directly from db and process it further in excel for some finance reporting purpose. If the would like to use Epicor I could just create a dashboard with external source :slight_smile:

Kind regards
Aleksander

8

Well right but that’s exactly what this allows you to do. You pull the data into Excel (from a BAQ) and then you can do with it whatever you’d like :-), at that point is just another bunch of excel data. You can create custom pivots, reports, formulas etc…

1 Like
9

We are doing exactly what Jose is suggesting. We write a BAQ (or more), create an ODate feed in Excel. Boom. It runs the BAQ and loads it into an Excel tab. If you click Refresh Data, it re-runs the BAQ. It’s wonderful.

Mark W.

1 Like
10

Aleksander here is how I created did this in excel.

First create the stored procedure.

Then create a data source in excel. The command text calls the stored proc with variable.

Capture
Capture.JPG498×516 43.7 KB

Then define the parameter
Capture2

1 Like
11

Hi Ken,

This is exactly what I wanted! Cheers Bud! :rofl

Thank you
Aleksander