[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Dynamics GP - SQL/Crystal Report Multi Level BOM Report

Posted on 2011-02-22
20
Medium Priority
?
1,893 Views
1 Endorsement
Last Modified: 2012-05-11
I need a SQL view that i can use in crystal reports to display a multilevel BOM report.
1
Comment
Question by:jnikodym
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 2
20 Comments
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34951558
The requested Multi-Level BOM Crystal Report is attached.
BOM.rpt
0
 

Author Comment

by:jnikodym
ID: 34952032
can you email me the command?  i can't access it from the old question.  Thanks
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34952261
It's there in the Crystal report file.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:jnikodym
ID: 34952605
How are you concatenating your space formulas with the data objects?
0
 

Author Comment

by:jnikodym
ID: 34952977
this is still not achieving what i am looking for.  If you look at your report, CBA100 is a component of BA100G.  Then CBA100 is an assembly with components under it.  I would want the report to look like this

BA100G
   BELL100
   CBA100
        CAP100
        CB100
        RES100
        SOLDER
        etc.
   FTRUB
   KPA100
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34953749
I see, the report you're asking for would take time. As I'm busy, I can tell you how to accomplish it, but you must be very good in Crystal Reports.
0
 

Author Comment

by:jnikodym
ID: 34953752
can you tell me how to accomplish it or give me an example report?
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34953857
It's hard for me now to prepare an example. Here's how to do this:

1- The main report shows all of the sub-assemblies LEVEL-0 components.
2- Subreport shows subassemblies' components.

The main report passes the subassembly to the subreport to show its compnents.
0
 

Author Comment

by:jnikodym
ID: 34953889
Won't it be a problem when you get to the third level?  Because you can't have a subreport within a subreport.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34953938
The main report shows all of the subassemblies, even if it's Level-10, the subreport just shows the components. There won't be subreport within subreport.

This is the main idea of it, but you may need some formulas and conditional formating to accomplish the full report.
0
 

Author Comment

by:jnikodym
ID: 34953953
would you have time to build the report?
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34957331
Well, I will do my best to prepare it.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34975481
Tomorrow I'm gonna work on it.
0
 
LVL 10

Accepted Solution

by:
Abdulmalek_Hamsho earned 2000 total points
ID: 34978674
Report is attached. You need to change the ODBC connection to point to your DB in the Main report AND in the two SubReports.
BOM-Formatted.rpt
0
 

Author Comment

by:jnikodym
ID: 34982942
this is exactly what i'm looking for.  One more thing.  Is it possible to get a running total of all the components cost to appear on the main report?
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34983639
It depends upon which cost you're talking about.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34983652
Anyway, everything is possible.
0
 

Expert Comment

by:flg8rgal
ID: 35215421
I don't use Crystal Reports.  Any chance you can print off the code and pop it into a text format so that I can read it and modify it (if necessary) for use in SQL Server?  Thanks!
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 35216976
With BOM_No (PPN_I,CPN_I, QUANTITY_I, POSITION_NUMBER, BOM_LEVEL, Hirchy) AS
      (SELECT DISTINCT PPN_I,CPN_I, QUANTITY_I, POSITION_NUMBER, 0, cast(ltrim(PPN_I) as varchar(1000))FROM BM010115 WHERE PPN_I = '{?ItemNo}'
      UNION ALL
      SELECT BM010115.PPN_I,BM010115.CPN_I, BM010115.QUANTITY_I, BM010115.POSITION_NUMBER, BOM_No.BOM_LEVEL + 1, cast(ltrim(rtrim(BOM_NO.Hirchy)) + ltrim(rtrim(BM010115.PPN_I)) as varchar(1000))FROM BOM_No
      INNER JOIN BM010115
      ON BM010115.PPN_I = BOM_No.CPN_I)

SELECT BOM_No.PPN_I, BOM_No.BOM_LEVEL, Hirchy FROM BOM_No
OPTION (MaxRecursion 100)
0
 

Expert Comment

by:flg8rgal
ID: 35217162
Great, thanks!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question