SOLIDWORKS PDM Report Generator Instructions

Article by GoEngineer on Feb 04, 2019

Introduction

What is the SOLIDWORKS PDM Report Generator?  This is a tool in SOLIDWORKS PDM Professional that allows the possibility of any user executing a predefined SQL query – without the need to log-in to the SQL server.

Most people find that the built-in Search tool completely fulfills their need for searching.  However, some find that they need to do a custom search in the SQL database. If this search will be done more than once then making it into a Report might be beneficial.  Also, by making it into a report the query can be shared with any SOLIDWORKS PDM user.

The intent of this document is to help the reader build a custom Report that will meet their needs. There will be a few Report “templates” that the reader can modify to create their own Report.

Additional Information

For detailed information on creating reports for the Report Generator please look in the SOLIDWORKS PDM Administration Tool: Help menu: Administration Guide and do a search for “Report Generator”.  Also, there are examples in a file called “…\SOLIDWORKS PDM\Report Examples 1.crp”. NOTE: the extension “crp” is for report files.

Use Notepad to edit the reports.  Once ready, add them to the PDM Report Generator.

Starting the Report Generator

  • Log-in to your local vault view
  • Select the menu item “Tools: Report Generator”

Adding Reports to the Report Generator

The user adding the report must have the Administrative Permission “Can update report queries”

  • In the Report Generator select “File: Import Query” and then browse to a crp file to import
  • Drag a file from Explorer into the “Query” window in the Report Generator

The Report Generator window

SOLIDWORKS-PDM-Report-Generator-Instructions-Report-Generator-window

Building Reports

Reports are based on an SQL query. So, the first step is to build the SQL query and verify it runs properly in SQL Management Studio.  The next step is to determine what input (arguments) will be needed when the report is run.  Of course, some queries won’t need arguments at run time.

I will split the discussion below between reports that need arguments and those that don’t.

Template: Report without arguments

The template for a query without arguments is below between “Begin Query” and “End Query”. To use this, copy the text between the “Begin” and “End” lines and paste them into a Notepad document. In each section modify the text between [].  Be certain to leave the [].  Required replacements are in red text.

Save the document and then change the extension from “txt” to “crp”. You now have a report. Use the instructions above to add the report to the Report Generator tool. Check the box next to the report and select “Edit: Execute Query” or select the icon “Execute Query”.

Begin Query

@[Unique name for Query]
§Name [Name displayed in “Queries” box]

§Company [Optional]

§Description [Optional]

§Version [1.0]

§Arguments []

§Sql
[

SQL query goes here

]

End Query

SOLIDWORKS-PDM-Report-Generator

Example: Report without arguments

The query below will show all messages and notifications waiting to be sent.

Begin
@[Messages waiting to be processed]
§Name [Waiting notifications]

§Company []

§Description [All configurations including @ tab data]

§Version [1]

§Arguments []

§Sql
[
select *
from DocumentActionInfo
]
End

Reports with arguments

This section is for reports that will need additional information at run time. Perhaps the logged in user’s name will be needed or some file names. I will give some examples below but for a complete list of all the possible arguments and how to use them please refer to the “Administration Guide” as described in the “Additional Information” section. Note there are many more possible combinations than what I am covering here. They are in the section “Report Query Arguments” in the “Administration Guide”.

In the example below I am using simple substitution – I replace the argument in the SQL script with the variable enclosed in {}.  Please note that it is also possible to use an “OR” for multiple values in the argument.  Please refer to the documentation in the “Administration Guide” on how to use that.

The section below is copied from the “Administration Guide” on “Arguments”:

§Arguments
[
§type name[count] [instruct]
]

Where:

  • type is a predefined argument type. The possible types are described in the Administration Guide
  • name is a variable used in the WHERE element of the SQL query
  • [count] defines how values are returned to the SQL query
  • [instruct] appears in the Report Generator as a user instruction

A report query can have multiple arguments.

All components of the argument must be on one line.

 

Example:

In the example argument below, the number of groups to be selected is open.  The user can choose as many groups as he likes from the list of groups in PDM.  When the report is run a dialog will appear and the user can choose the groups to be added to the selection list.

Begin section
§Arguments
[
GroupID TheGroupID[N] [Select groups]
]
End section

Report example with an argument

In the example below, the argument is a single text entry – the filename.  The variable is called “theFileName”.  Note that the variable is enclosed in curly braces when it is used in the query: {theFileName}

Begin query
@[Find Referencing Checked Out files]
§Name [Files that reference this file that are currently checked out]

§Company [SolidWorks]

§Description
[This query will find all files that reference the target file and are checked out]

§Version [1.2]

§Arguments
[
String theFileName[1] [Enter filename]
]

§Sql
[
select b.DocumentID, a.Filename as ref_File
from documents a
join XRefs b on a.DocumentID=b.DocumentID
where b.XRefDocument=(select DocumentID from documents where Filename={theFileName})
and a.LockPath !=”
End query

Template: Report with arguments

In the “template” below the argument is a single string value that is assigned to the variable “theFileName”. You are welcome to change the name of the variable to whatever you desire.  If you want more than one value then change the [1] to the desired number.  You can also change the user prompt from “Enter filename” to whatever you like – just keep the brackets.

Begin query
@[Unique name for Query]
§Name [Name displayed in “Queries” box]

§Company [Optional]

§Description
[Optional]

§Version [1.0]

§Arguments
[
String theFileName[1] [Enter filename]
]

§Sql
[
SQL query goes here
]
End query

Conclusion

This has been an introduction into using and modifying the SOLIDWORKS PDM Report Generator tool. Hopefully, you will have found the article informative and the templates of use.  If you wish to investigate this issue in more detail please examine the “Administration Guide”

This link will provide 13 sample reports. You may need to modify some of the settings so it matches your environment.  The provided reports do the following:

File Name Description
Assembly file in a project This query lists all assembly documents & its configuration names under specified folder.
Folders with no files This query will list Vault Folder With No Files.
Get Config Name Get Configuration names in variable search result
List files in specific Workflow This query will list all documents in a workflow
Rename_files Get list of files renamed with their old name
Report non-deleted empty vault folders This query will list all empty, non deleted folders in Vault
Report of checked out files on specific machine This query will list all checked out files on specific client.
Report_workflow his query will list file details of the latest version of all files in the selected project.
Report of files that are not referenced by other documents This query will list Files that are not referenced based on File extension
Tree Structure Top Level This query lists the tree structure of an file at the top level only
Variables per File Per Configuration Inc Empty Lists columns of all values of chosen variables even if not set for all configurations of selected files
Variables per File Per Configuration Lists columns of latest values of chosen variables for all configurations of selected files
Version with rev This query will list File versions that has revision to it
 

About GoEngineer

GoEngineer delivers software, technology and expertise that enable companies to unlock design innovation and deliver better products faster. With more than 35 years' experience and tens of thousands of customers in high tech, medical, machine design, energy and other industries, GoEngineer provides best-in-class design solutions from SOLIDWORKS CAD, Stratasys 3D printing, Creaform & Artec 3D scanning, CAMWorks, PLM, and more

View all posts by GoEngineer