Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Bill of Materials - An...
Microsoft Dynamics GP (Archived)

Bill of Materials - An item's level code (Level0 in Report Modifier)

(0) ShareShare
ReportReport
Posted on by Jeff Titus 245

Hi, Does anyone know how to calculate the Level0 field like the 3rd Party Report called "BOM Report" (Multi-Level)? I already modified the multi-level report for the company I work for, and created calculated fields based on the Bill of Material File Temp Number 2 table to use for indenting sub-assemblies. That report works great and I have no complaints. The reason I ask the above question is because I was asked to research and implement, if possible, indenting BOM items in an SSRS report. Now I was easily able to indent any items that have sub-assemblies, but that isn't feasible. We want to be able to indent the sub-assemblies based on their Level Code, like we did in the Report Writer project previous mentioned. If there was a field that held the Level Code (I haven't found it if there is one), reporting in SSRS with BOM structure will be so much easier. If not, it's not the end of the world! Thanks for taking the time to read this.

*This post is locked for comments

  • Jeff Titus Profile Picture
    Jeff Titus 245 on at
    RE: Bill of Materials - An item's level code (Level0 in Report Modifier)

    Bron - Thanks again! My colleague suggested finding a different way to calculate an item's indent level in this case, after reading what you posted. So for anyone looking for a solution here is what I used: Place the first part in your SQL code and then paste the second part in the left padding property of the item number field you are looking to indent.

    First Part:

    @WorkOrderId char(31)

    AS

    Declare @LoopCounter as int = 0

    Declare @IndentLevel as int = 0

    Declare @TotalLoops as int = (Select MAX(SEQ_I) From PK010033 Where MANUFACTUREORDER_I=@WorkOrderId)- (Select MIN(SEQ_I) From PK010033 Where MANUFACTUREORDER_I=@WorkOrderId)

    Declare @CurrentSequence as int = (Select MIN(SEQ_I) From PK010033 Where MANUFACTUREORDER_I=@WorkOrderId)

    Declare @SubAssembly as varchar(31) = ISNULL((Select SUBASSEMBLYOF_I From PK010033 Where MANUFACTUREORDER_I=@WorkOrderId And SEQ_I=@CurrentSequence),'')

    Declare @LastSubAssembly as varchar(31)

    Begin

    Create Table #TempIndentLvlData (rownum int IDENTITY (1, 1) Primary key NOT NULL, PPN_I char(31), ITEM_NMBR char(31), ITEM_NMBR smallint, POS_NUM2 smallint, SEQ_I int, SUBASSEMBLY_OF_I char(31), INDENT_LVL int)

    While (@LoopCounter <= @TotalLoops)

    Begin

    If @SubAssembly = ''

    Set @IndentLevel = 0

    Else If @LastSubAssembly <> @SubAssembly and @SubAssembly <> ''

    Set @IndentLevel = @IndentLevel + 1

    Else

    Set @IndentLevel = @IndentLevel

    Set @LastSubAssembly = @SubAssembly

    Set @SubAssembly = ISNULL((Select SUBASSEMBLYOF_I From PK010033 Where MANUFACTUREORDER_I=@WorkOrderId And SEQ_I=@CurrentSequence+1),'')

    Insert Into #TempIndentLvlData Values (

    (Select PPN_I From PK010033 Where MANUFACTUREORDER_I=@WorkOrderId And  SEQ_I=@CurrentSequence),

    (Select ITEMNMBR From PK010033 Where MANUFACTUREORDER_I=@WorkOrderId And SEQ_I=@CurrentSequence),

    (Select POSITION_NUMBER From PK010033 Where MANUFACTUREORDER_I=@WorkOrderId And SEQ_I=@CurrentSequence),

    (Select POSITION_NUMBER2 From PK010033 Where MANUFACTUREORDER_I=@WorkOrderId And SEQ_I=@CurrentSequence),

    (Select SEQ_I From PK010033 Where MANUFACTUREORDER_I=@WorkOrderId And SEQ_I=@CurrentSequence),

    (Select SUBASSEMBLYOF_I From PK010033 Where MANUFACTUREORDER_I=@WorkOrderId And SEQ_I=@CurrentSequence),

    @IndentLevel

    )

    Set @CurrentSequence = @CurrentSequence + 1

    Set @LoopCounter = @LoopCounter + 1

    Select * From #TempIndentLvlData

    End

    End

    Second Part:

    =Fields!INDENT_LVL.Value & "2pt"

  • Bron Profile Picture
    Bron 4 on at
    RE: Bill of Materials - An item's level code (Level0 in Report Modifier)

    Jeff - thanks for additional information.

    Caveat - I think the Low level Code gets populated by the Manufacturing Utility (tools > utilities > Manufacturing > MRP Low Level Codes) it's not dynamic - it's static.

    So if an item 'moves' within a multilevel Bill of Material the code might be incorrect until it gets updated via the utility. I would consider that when you create your report............

    Good Luck

  • Jeff Titus Profile Picture
    Jeff Titus 245 on at
    RE: Bill of Materials - An item's level code (Level0 in Report Modifier)

    If you put the following nested IIF functions as the Expression for the Left Padding property for the Item Number and Item Description field, you can indent your BOM in SSRS in a tree style fashion. All you need to do is set the numbers to the intervals you want.

    =IIF(Fields!SUBASSEMBLY_I.Value=1 And Fields!LLC.Value=0,"12pt",IIF(Fields!SUBASSEMBLY_I.Value=0 And Fields!LLC.Value=0,"2pt",Fields!LLC.Value & "2pt"))

    I just implemented this, and the logic seems like it should work for any style BOM. Please let me know if you use this and run into any problems.

  • Jeff Titus Profile Picture
    Jeff Titus 245 on at
    RE: Bill of Materials - An item's level code (Level0 in Report Modifier)

    Bron,

    We are dealing with Manufacturing BOM's. The answer you gave about the LLC field in the IVR10015 table was exactly what I was looking for. I believe the tree had already been constructed by one of my colleagues when she imported the inventory. Thanks for all your help!

  • Verified answer
    Bron Profile Picture
    Bron 4 on at
    RE: Bill of Materials - An item's level code (Level0 in Report Modifier)

    Jeff - are you dealing with Inventory BOM's or Manufacturing BOM's?

    I've created several multi-level Bill of Material Reports (MFG) and normally you need to use a SQL cursor or SQL CTE to go through the BOM detail table and construct the 'tree'..........

    I would look at existing SQL stored procedures (part of the GP install) to get started.

    Regarding low level codes - there is a value stored in IVR10015 that I've never used but it is reflective of the item's lowest location in a bill of material. MRP uses it.

    Chances are the value you previously used (Bill of Material File Temp Number 2) was created 'on the fly' - calculated by dexterity and populated into a temp table.

    Good Luck

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard >

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans