Formatting a Report Query

Report queries contain structured information that displays the report in the Report Generator and one or more SQL queries that retrieve information from the SQL database.

You create the queries in a text editor such as Notepad and save them as .crp files in Unicode format.

To format a report query:

  1. Open a text editor such as Notepad.
  2. Create a report query using the following elements:
    @[query_identifier]

    Example:@[GroupMembers]

    §Name [display_name]

    Example: §Name [User details of selected groups]
    §Company [company_name]

    Example: §Company [SOLIDWORKS]
    §Description [description]

    Example: §Description [Lists all members of the selected groups.]
    §Version [version_number]

    Example: §Version [1.1]

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

    Where:

    • type is a predefined argument type

    • 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: 
    §Arguments 
[
GroupID TheGroupID[N] [Select groups]
]
    		 
    §Sql
[
]
    Example: 
    §Sql
[
]
  3. In Microsoft SQL Server Management Studio, create a T-SQL query to retrieve information from the SQL database.
  4. Copy the SQL query and paste it into the §Sql [ ] section of the report query.
  5. Optional: Modify the WHERE element of the SQL query if you want an argument to be dynamically evaluated when the report runs in the report generator.
    In step 2, the Argument is [GroupID TheGroupID[N] [Select groups]]. You can use the variable name TheGroupID to return data based on the group selected by the user when the report runs.

    To accomplish this, in the following T-SQL query syntax, you replace G.GroupID = 2 with {G.GroupID, TheGroupID, =, OR}.

    T-SQL query syntax
    WHERE G.GroupID = 2 AND
               G.GroupID = M.GroupID AND 
               U.UserID = M.UserID
    Report query syntax
    WHERE {G.GroupID, TheGroupID, =, OR} AND
       G.GroupID = M.GroupID AND
       U.UserID = M.UserID]
  6. Save the query as a .crp file.
  7. Import the .crp file into the Report Generator to run the report.

Report Query Example

The following report query is from Report Examples 1.crp, which is installed to install_dir, by default C:\Program Files\SOLIDWORKS PDM .

@[GroupMembers]
§Name [User details of selected group(s)]

§Company [SOLIDWORKS]

§Description
[This query will list all members of the selected groups.]

§Version [1.1]

§Arguments
[
GroupID	TheGroupID[N] [Select group(s)]
]

§Sql
[
SELECT U.Username, G.Groupname, G.Description
FROM Users U, Groups G, GroupMembers M
WHERE {G.GroupID, TheGroupID, =, OR} AND
      G.GroupID = M.GroupID AND
      U.UserID = M.UserID
]

This file contains additional report queries that a user can import into the Report Generator.