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.

1. Open a text editor such as Notepad.
2. 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 [ ]

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. 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 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.

@[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
]