Every piece of information that is entered in SOLIDWORKS PDM is stored in Microsoft SQL. With the right tools, there is a wealth of information that can be mined.
All this data is maintained inside tables, within a Microsoft SQL database. This data can be mined through SQL queries. While these queries are powerful tools, it is unwise to allow users direct access to the SQL databases. The uninitiated could inadvertently cause significant damage to the databases. Also, creating an SQL query requires knowledge that most PDM users do not have.
SOLIDWORKS PDM allows users to access all the information contained in a database, without the need to touch the database directly. This is done by executing a report query through the SOLIDWORKS PDM report generator.
Formatted Report Query
A product data management (PDM)report is created by copying a SQL query into a text editor such as Notepad. The report needs to be formatted as shown below and contain all the listed elements.
Required elements as outlined in PDM Help.
The portion following §Sqlis where we copy the SQL query, which we will discuss next.
The SQL Query
SQL queries are executed through SQL Management Studio. This application is installed by default with SOLIDWORKS PDM Professional (SQL Standard), but not with SOLIDWORKS PDM Standard (SQL Express). SQL Management Studio can be installed for PDM Standard though. The installation files are in the SOLIDWORKS installation fileset in a subfolder called ‘sqlmngmnt.’
Once logged into SQL Management Studio, a query can be launched by right clicking on a vault
Starting a SQL query from the right mouse menu.
or from the SQL Management Studio toolbar.
Starting an SQL query from the toolbar.
In the query panel, enter the query elements that will retrieve the information you are after. The query must contain the elements “Select,” “From” and “Where.”
Required elements of an SQL query to be used in a PDM report query.
Run the query from the toolbar, pressing F5 on your keyboard, or from Query>Execute.
Executing a query.
Continue to run and edit the query until all errors have been corrected and you are achieving the results you are expecting. Once the SQL query is providing the desired results, copy the SQL query into a formatted report query.
Completing the Report Query
The SQL query is then copied into the §Sql [ ] section of the report query. Below is an example that illustrates what a properly formatted report query should look like. This query lists deleted files that have not been destroyed.
@[ListDeletedFiles
§Name [List Deleted Files]
§Company [SolidWorks]
§Description
[This query will list deleted files.]
§Version [1.1]
§Arguments
[
]
§Sql
[
–Query to list database deleted files
Select
P.Path + D.Filename as ‘Files that are deleted’
—–From
——Documents d, DocumentsInProjects Dp, Projects P
——Where
Dp.DocumentID= D.DocumentID and
——–P.ProjectID = Dp.ProjectID and
d.Deleted = ‘True’
Order by P.Path, D.filename
Once completed, the report query then needs to be saved as a .crp file.
Saving report query.
The crp extension does not show as an option from the ‘Save as type’ pull-down menu. The extension needs to be entered manually.
Running the Report Query
To be able to add or modify report queries in the report generator, a user needs to have the following administrative permission: “Can update report queries.” This permission is set in SOLIDWORKS PDM Admin for a user or group.
Setting update permissions for report queries.
Report queries are executed in the PDM Local Vault view using the Report Generator. The Report Generator is available from the Tools pull-down menu.
Launching report generator.
If the logged-in user has sufficient permissions, the user can import the queries that were created or downloaded. This is done from the File pull-down menu or from the Report Generator menu bar.
Importing a query.
After importing the report query, we assign which users and groups can have access to use this query.
Assigning Query User Rights.
Logged-in users will be able to see and run the PDM queries that they have permissions to access. These queries are listed in the Report Generator. The user can then select one or more report queries.
Selecting report queries to execute.
Once selected, the query is executed from the Edit pull-down menu of the Report Generator or from the Report Generator menu bar.
Executing query.
Depending on what report query is performing an interrogation process, some additional user input may be required.
Entering additional information for query.
The results of the PDM query are listed in the lower panel of the Report Generator and can be exported from File pull-down menu or menu bar of the Report Generator.
Exporting query results.
The export is saved in the CSV file format, which can be opened in a spreadsheet application, such as Microsoft Excel. If we then want to track changes in the report, the CSV can be checked into a workflow.
Here is one final note on running a report query through the Report Generator. If there is more than one vault, the user can choose which vault to run the report query on.
Choosing a different vault on which to run the query.
The permissions to run a report are set when the query is imported, not the permissions assigned through PDM Admin. The vault controls the ability to add or modify report queries, not who can access them
While it is possible to generate the SQL query and subsequent report query from scratch, it is often faster to use an SQL or report query or to modify existing queries. In the SOLIDWORKS PDM installation directory, there is a sample report query that lists all groups that the current logged-in user is a member of. There are also several report queries available from the SOLIDWORKS Knowledge Base, MySolidworks.com and several SOLIDWORKS forums and user groups. Often, as is the case with the SOLIDWORKS Knowledge Base, both the report and SQL query are available for download.
Keep in mind that SQL queries are always embedded in the report query and can be copied into SQL. This can be useful if you prefer to run the query from SQL or want to modify the underlying SQL query.
I have listed below some of report queries I find most useful. These queries are available from the SOLIDWORKS Knowledge Base.
- ShowDuplicateFilenames.crp: This is a good tool when you have bulk copied a large number of files into the vault and now want to find all duplicate file names, before disallowing duplicate file names, through PDM Admin.
- crp: This query will list all of the files that have been deleted, but not destroyed. Deleted files can prevent some modifications of a workflow, such as deleting a state.
- crp: This query lists all folder permissions assigned to a user or group. If you search for “Solution S-037428”in the SOLIDWORKS Knowledge Base, you will find similar SQL queries, which list both inherited and explicitly set folder permissions for users and groups. Inherited permissions are those that are inherited when a file passes through a workflow state.
- Database Emails Pending.crp: Lists all pending emails in the database.
- crp: Lists all transition actions defined in a workflow.
- crp: For replicated environments, this query shows all of the files that have not been replicated to a target server.
The above is only a small example of the report and SQL queries that are available for download. When you consider that an SQL query can report on every piece of information in a vault database and that accessing this information can be simplified through a report query, we can see that this is a powerful tool for datamining our PDM vault.
Joe Medeiros is a senior applications engineer at Javelin Technologies, a SOLIDWORKS reseller servicing customers throughout Canada. He has been involved with SOLIDWORKS since 1996. An award-winning blogger, he regularly writes about SOLIDWORKS products.