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
]