SOLIDWORKS Electrical: Adding Item Numbers to your Bill of Material

I’ve had many questions from many SOLIDWORKS Electrical users on if they could have item numbers in the Bill of Materials (BOM). This is something that is traditionally only needed in mechanical BOMs, since the electrical counterparts have the component marks to differentiate parts. However, some people still like item numbers, and I’ve always said, “sorry, but we don’t have that capability yet”. That was apparently not true! A user on the electrical forum figured out some SQL magic that makes it happen! Credit to Johnathen Lieber. I’m spreading the word and adding to his instructions to be a bit more step-by-step so that you all can take advantage of this.

There is an SQL command ‘ROW_NUMBER‘ that can be used in the reports to create an integer variable based on another variable in your query. So as an example, in a cable report I can create a ROW_NUMBER statement based on the cab_tag variable that will produce a result like this:

You may notice that the item number is not in order. That is because it was created based off of the cab_tag, it follows the Mark orders, and that particular report was not sorted by cab_tag. So this will be something you’ll have to watch out for. You can always use the sort and break options to sort by the row number, but that may mess up your other preferred sorting.

Before I begin, you will be modifying the SQL query in the report configuration. I highly recommend making a copy of the report you are editing, and rename it to “<Old Name> with row numbers” or similar, so that if you accidentally break the SQL query you still have your old report to fall back on.

Add the Item Number to the SQL table

To get the row number in your cable report, go the options of the report and click the button at the bottom to activate expert mode. In the SQL query tab, click the edit button

If you are unfamiliar with how SQL queries work, at the most basic level there are two sections. The SELECT Statement and the FROM statement.

Paste the following as the first line of the Select statement:

ROW_NUMBER () OVER (ORDER BY cab_tag) AS row,

In that statement you can replace cab_tag with whatever variable you would like to use to differentiate item numbers. Also, row is the variable name we are giving our item numbers. You can replace that with whatever text you want (no spaces), so item_number, balloon, mongooses, or whatever your standard nomenclature is; all will work.

Here’s an example of what it could look like: (Notes: DON’T FORGET to add the comma noted with the red arrow)

Here is a link to a Microsoft article on the ROW_NUMBER statement: ROW_NUMBER

Once that line is inserted, click on the “Test” button, and if you don’t get any errors then it will work. If you get an error about the number of expressions, then you are probably modifying a report with multiple SELECT statements. In that case, you’ll need to modify the query a little.

The out-of-the-box reports for cables and wires have one select statement, but the BOMs sometimes have multiple select statements. If there are multiple select statements you have to switch to the statement DENSE_RANK (LINK).

Instead of the line above, insert the following as the first line of all of the SELECT statements:

DENSE_RANK () OVER (ORDER BY bom_reference) AS row,

In the Bill of Materials grouped by Manufacturer, for example, it will be

SELECT
DENSE_RANK () OVER (ORDER BY bom_reference) AS row,
…
FROM
…
UNION ALL SELECT
DENSE_RANK () OVER (ORDER BY bom_reference) AS row,
…
FROM

Add the Item Number to the Available Columns

Now that the query is good, click on the button to add a column.

Give your column a name, click on the Fx button and find the “row” variable (yours may differ if you changed the variable name in the query).

Click OK and it should automatically add it to the report. It will also appear in the column management button if you want to remove/add the column later.

Test it out and make sure it works. If all is good, then make sure to go to Project > Configurations > Reports to add the report to the Application so that it is available across all of your projects.

Brian Cooke
Electrical Application Engineer
Computer Aided Technology, Inc