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]
   쬛ame [display_name]	쬛ame [User details of selected groups]
   쬍ompany [company_name]	쬍ompany [SOLIDWORKS]
   쬎escription [description]	쬎escription [Lists all members of the selected groups.]
   쬥ersion [version_number]	쬥ersion [1.1]
   쬋rguments [ 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.	쬋rguments [ GroupID TheGroupID[N] [Select groups] ]
   쬢ql [ ]	쬢ql [ ]

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 쬢ql [ ] 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]
쬛ame [User details of selected group(s)]

쬍ompany [SOLIDWORKS]

쬎escription
[This query will list all members of the selected groups.]

쬥ersion [1.1]

쬋rguments
[
GroupID	TheGroupID[N] [Select group(s)]
]

쬢ql
[
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
]