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.
To format a report query:
- Open a text editor such as Notepad.
- Create a report query using the following elements:
Syntax |
Example |
@[query_identifier]
|
@[GroupMembers]
|
§Name [display_name]
|
§Name [User details of selected groups]
|
§Company [company_name]
|
§Company [SOLIDWORKS]
|
§Description [description]
|
§Description
[Lists all members of the selected groups.]
|
§Version [version_number]
|
§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.
|
§Arguments
[
GroupID TheGroupID[N] [Select groups]
]
|
§Sql
[
]
|
§Sql
[
]
|
- In Microsoft SQL Server Management Studio, create a T-SQL query to retrieve information from the SQL database.
- Copy the SQL query and paste it into the §Sql [ ] section of the report query.
-
Optionally, modify the WHERE element of the SQL query if you want an argument to be dynamically evaluated when the report is run 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 is run.
To accomplish this, in the following T-SQL query syntax, you would replace G.GroupID = 2 with {G.GroupID, TheGroupID, =, OR}.
T-SQL query syntaxWHERE G.GroupID = 2 AND
G.GroupID = M.GroupID AND
U.UserID = M.UserID
Report query syntaxWHERE {G.GroupID, TheGroupID, =, OR} AND
G.GroupID = M.GroupID AND
U.UserID = M.UserID]
- Save the query as a .crp file.
- 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 Enterprise 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.