YOUR SOURCE FOR SOLIDWORKS 3D CAD ENGINEERING SOFTWARE

SOLIDWORKS PDM – Creating Custom Reports

SOLIDWORKS PDM Tech Tip

Written by: Bryce Hooper, Application Engineer

The report generator can be a very powerful tool, provided you know how to wield it. It allows us to query the database for information without the use of SQL Management Studio. We’ve explored adding reports to the Report Generator in a previous blog article, but we haven’t explored creating them from scratch.

Doing this requires a little bit of SQL knowledge and a little bit of understanding of where to look for things in the PDM Database. Meaning, some of this knowledge can be taught but things like the structure of the database and information you are looking for can likely only come through experience.

It’s okay to play around and potentially make bad queries here, we won’t be changing any values in PDM. If we mess up, we can remove the bad query and then replace it with an altered/corrected version. The report generator inherently doesn’t support any T-SQL that would perform any changes to the database.

To create reports, we start with either an existing CRP file or a blank text file. In the case of a blank text file, we save it as a CRP extension and edit it with notepad.

Each query that we add to this file will follow a similar format.

Query Format

  • The @ and brackets at the top is a unique identifier for the query. This should be a string of text without spaces. This will only be seen on the properties dialog for the query and on “add to set” permissions.
  • The Name label can be any text that you’d like. This is what you will see in the top-left pane where all reports are listed before querying.
  • The Company and §Version arguments are, again, just for informational purposes. If we don’t share or iterate versions, this information may not be essential. It is, however, an option to help us track changes and origin.
  • The Description field is a brief explanation of what we are querying. This is any text, and will be displayed in the properties under Description. Much like §Company and §Version, this is informational but non-essential.
  • Arguments is where we list any inputs that should be prompted on execution. There are some types here that we can utilize.
    • FileID – Document ID of the selected file
    • UserID – UserID of the selected user
    • ProjectID – ProjectID of the selected folder
    • String – A text string
    • GroupID – GroupID of the selected group
    • StatusID – StatusID of selected workflow state

Each of these items can be defined as variables on the execution to take an input. Each time we specify a type, we then give it a name, a count and a prompt in that order. Counts can be a hard coded value or the characters N, S, or M.

  • N – We have a variable count and the user will be prompted to enter values or select from a list.
  • S – On an argument of FileID means that the argument will be fetched from the selection list (files added to the selected files window). Results will display a different table for each file.
  • S – On an argument of UserID retrieves the ID of the currently logged in user.
  • M – On an argument of FileID is the same as S, except that all files will be put into an array and the result will be displayed in one table instead of one result for each file.
  • SQL is where the bulk of the work comes in. This portion requires some SQL knowledge and some knowledge of the PDM database structure. Queries written here will largely follow standard T-SQL protocol with some slight changes for the report generator.
    • Variables defined in the arguments section can be placed into the query as {VariableName} and will be substituted with the proper value on execution.

With all these notes on the separate clauses, there are also a few rules to follow when writing these reports.

  • Each query in a CRP file starts with ‘@’ followed by a unique name within brackets.
  • Each query contains each section listed above, written with the symbol ‘§’ the keyword, and enclosed in square brackets [ ].
  • The order of the sections however, may vary and names are case-insensitive.

If you’re struggling with basic knowledge on SQL, I recommend brushing up using W3 Schools. The tools and guides here can show a novice user how to start to put queries together.

Furthermore, if we are not quite sure where to get information from our PDM database, this is a trickier question. There are several examples of where to find certain pieces of information in the example reports. Further, you can explore your own database (with caution and recommended as a read-only user) to begin to understand the database structure using SQL Management Studio. A search through the knowledge base will also result in several other examples of what can be retrieved (and how) through the report generator.

For information on sharing and using these reports please see our blog entry on using the report generator. 

This entry was posted in Tutorial and tagged , , . Bookmark the permalink.