jnikodym
asked on
Dynamics GP - SQL/Crystal Report Multi Level BOM Report
I need a SQL view that i can use in crystal reports to display a multilevel BOM report.
ASKER
can you email me the command? i can't access it from the old question. Thanks
It's there in the Crystal report file.
ASKER
How are you concatenating your space formulas with the data objects?
ASKER
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
BA100G
BELL100
CBA100
CAP100
CB100
RES100
SOLDER
etc.
FTRUB
KPA100
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.
ASKER
can you tell me how to accomplish it or give me an example report?
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.
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.
ASKER
Won't it be a problem when you get to the third level? Because you can't have a subreport within a subreport.
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.
This is the main idea of it, but you may need some formulas and conditional formating to accomplish the full report.
ASKER
would you have time to build the report?
Well, I will do my best to prepare it.
Tomorrow I'm gonna work on it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
It depends upon which cost you're talking about.
Anyway, everything is possible.
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!
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.CP N_I, BM010115.QUANTITY_I, BM010115.POSITION_NUMBER, BOM_No.BOM_LEVEL + 1, cast(ltrim(rtrim(BOM_NO.Hi rchy)) + 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)
(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.CP
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)
Great, thanks!
BOM.rpt