BOM (Bill of Material) Report RRS feed

  • Question

  • User-1387735012 posted

    I have a table called Materials (SQL Server 2012). In that table there is a field called PartNo and one called SubPartNo. If a SubPartNo has its own SubPartNo then it will be listed further down the table. For example, PartNo 555-123 has a SubPartNo of 111-222. Part 111-222 also has its own sub parts so 111-222 is listed in the PartNo column. This will continue until all the sub parts have been listed.

    <div>PartNo</div> <div>SubPartNo</div> <div>Description</div> <div>ItemNo</div>
    <div>555-123</div> <div>111-222</div> <div>Motor Assembly</div> <div align="center">1</div>
    <div>555-123</div> <div>121-333</div> <div>Bearing Shaft</div> <div align="center">2</div>
    <div>555-123</div> <div>131-444</div> <div>Coupler</div> <div align="center">3</div>
    <div>555-123</div> <div>141-555</div> <div>Bearing Wheel</div> <div align="center">4</div>
    <div>111-222</div> <div>232-999</div> <div>Motor</div> <div align="center">5</div>
    <div>111-222</div> <div>232-888</div> <div>Motor Mount</div> <div align="center">6</div>
    <div>232-999</div> <div>751-012</div> <div>Motor Shaft</div> <div align="center">7</div>
    <div>232-999</div> <div>761-099</div> <div>Motor Housing</div> <div align="center">8</div>

    I need the report to look like this...

    <div>555-123</div> <div></div> <div> </div> <div> </div>
    <div> </div> <div>111-222</div> <div>Motor Assembly</div> <div> </div>
    <div> </div> <div> </div> <div>232-999</div> <div>Motor Shaft</div>
    <div> </div> <div> </div> <div>232-888</div> <div>Motor Mount</div>
    <div> </div> <div>121-333</div> <div>Bearing Shaft</div> <div> </div>
    <div> </div> <div>131-444</div> <div>Coupler</div> <div> </div>
    <div> </div> <div>…</div> <div> </div> <div> </div>
    <div> </div> <div>…</div> <div> </div> <div> </div>

    Basically, while compiling the report I need Crystal Reports to...

    1. "Print" the current record.
    2. Check if the SubPartNo in the current record is listed somewhere else in the PartNo field.
      1. If so, go to that record and print it
        1. Return to Step 2.
      2. If not, move to next PartNo.
        1. Return to Step 1.

    I can figure out how to indent the records but I can't figure out how to get Crystal Reports to follow the logic while its compiling the report.

    Any help would be greatly appreciated.

    Monday, February 13, 2017 3:31 PM

All replies

  • User1415287965 posted

    I have virtually no knowledge of Crystal reports, while I have briefly worked on SSRS. What I don't understand is, why not do all this juggling at back-end itself? Is it SQL Server?

    Furthermore, what if indentation grows beyond 2 level? Is that not possible ever?

    Tuesday, March 14, 2017 10:31 AM