Creating Custom RDLC Reports in Tenrox

The following are the minimum server requirements for creating the custom RDLC reports in Tenrox:

• Microsoft SQL Server 2005 or 2008
• Microsoft Visual Studio 2008

This procedure describes how to design an SQL query in SQL Server Management Studio.

To design an SQL query:

1. Launch SQL Server Management Studio and connect to the Tenrox server.
2. Right-click the Tenrox database and then click New Query.
3. Create the SQL query to be used by the reports. The following is an example of an SQL query that returns a list of projects, the tasks that time is tracked against, as well as the users who track the time, and the total time tracked.

The queries used in the custom RDLC reports must be included in the stored procedures. This procedure describes how to create a stored procedure in SQL Server Management Studio.

To create a new stored procedure in the database:

1. Launch SQL Server Management Studio and connect to the server.
2. Create a new query for the database that you want to add the stored procedure to.
3. Use the CREATE PROCEDURE statement to create a new stored procedure. Note: The stored procedures used by the out-of-box reports by default use MSRS_procedure name as the naming convention; therefore, a similar naming convention that also includes the company name is recommended, for example, MSRS_company name_procedure name.
4. Add the following mandatory parameters to the stored procedure; otherwise, the reports will not work:
   • PREPID: parameter type = integer that represents the id of the user running the report
   • PREPGENID: parameter type = integer that represents the id of the report
   • PCURRENTDATE: parameter is type = date that represents the date of the report generation
   • PLANGUAGE: parameter type = integer that represents the language used for the report
5. Add the following date parameters to the stored procedures when a date or period range is required in the report:
   • PSTARTDATE: parameter type = date that represents the start date of a period
   • PENDDATE: parameter type = date that represents the end date of a period. Note: For the report to filter based on the period parameters, include a condition within the SQL.
6. Use either the single select or multi-select filter parameters to filter the reports according to the specified criteria. Note: For the report to filter based on the included single select or multi-select filter parameters, add a condition within the SQL.
   • Single Select Parameters: a single value is selected by the defined filter; for example, a project or a client
   • Multi Select Parameters: multiple values are selected by the defined filter; for example, multiple or clients or users
     1. TUSER.UNIQUEID IN: do not use the "=" operand since multiple records are returned
     2. (SELECT TREPSELECTED.VALLONG FROM TREPSELECTED: table TREPSELECTED holds the UNIQUEIDs of all selected values for the parameters in the report (in this example users) and the UNIQUEIDs are stored in the VALLONG column
     3. WHERE TREPSELECTED.REPID=@PREPID: return only matching records selected for this particular report (as the TREPSELECTED table holds the selected values of all the multi-select parameters for all the reports in Tenrox)
     4. AND TREPSELECTED.REPGENID=@PREPGENID: return only matching records selected by the user generating the report (as the TREPSELECTED table holds selected values by the user generating the report for all the multi-select parameters)
     5. AND TREPSELECTED.OBJECTTYPE=1): each object, such as user, project, client and task, in Tenrox has a code; in this example the id for the user object is 1, this condition is required since several multiselect parameters can exist in the same report and only the records selected for the particular type of parameter (object) need to be compared. Note: The values selected for a particular multi-select parameter are stored in the TREPSELECTED table. For a list of the available single select and multi-select parameters, as well as objects and codes, see the Viewing the Tenrox Parameter.
7. Add the check box parameter to filter report results by a parameter with a true or false value. The name of the check box parameter must include the prefix PCHK; for example, PCHKBILLABLE, and the parameter type SMALLINT (small integer).Note: The check box parameter PCHKBILLABLE example is used to filter the query results to only display the billable time.
8. Click Execute to create the stored procedure as an object in the Tenrox database after adding the required parameters to the stored procedure.

This procedure describes how to design the RDLC reports in Microsoft Visual Studio 2008. The design of the RDLC reports consists of the following steps:

• Creating Report Applications
• Setting up the Connection to the Tenrox Database
• Linking Reports to Stored Procedures Using Dataset Objects
• Designing and Formatting the RDLC Reports
• Defining the Stored Procedure Parameters in the Reports

This procedures describes how to create a new RDLC report in Microsoft Visual Studio 2008.

To create a report application:

1. Launch Microsoft Visual Studio 2008 and then click the Create Project link on the Start Page to create a new project.
   Note: The Start Page is displayed by default when Microsoft Visual Studio 2008 is launched. If the Start Page does not display, from the File menu, click New>Project to display the New Project dialog box.
2. Under Project types, click Reporting and then the Reports Application template.
3. Enter a name for the new project and then click OK.
   

To access the stored procedure, a connection must first be established to the Tenrox database. The connection to the Tenrox database is set up using the Server Explorer tool in Visual Studio.

To set up a database connection:

1. From the View menu, click Server Explorer.
2. In Server Explorer, right-click Data Connections and then click Add Connection to open the Add Connection dialog box.
3. In the Server name box, select or type the name and instance of the SQL server instance to connect to.
4. Under Log on to the server, select either Windows or SQL server authentication mode depending on your network configuration; for more information, contact your network administrator.
5. Under Connect to a database, select or type the name of the Tenrox database where the stored procedure is hosted
6. Click the Test Connection button to verify the connection.
7. Click the OK button to save the connection setup.

To create a link to the stored procedure, a dataset object is required. The dataset object is created using the Solution Explorer tool in Visual Studio.

To create a dataset object:

1. From the View menu, click Solution Explorer.
2. In Solution Explorer, right-click a project name and then click Add>New Item.
3. Under Categories, click Data and then the DataSet template.
4. Click the Add button to add the dataset to the project.
5. In Server Explorer, expand the created connection and then locate the required stored procedure in the Stored Procedure folder.
6. Drag and drop the stored procedure into the created dataset object.

The custom reports are designed and formatted using the Visual Studio report controls available from the Toolbox tab.

To design and format the RDLC reports:

1. From the View menu, click Toolbox to display the report controls.
2. From the Data menu, click Show Data Sources to display the Data Sources pane.
3. In Data Source pane, use the dataset object created to access the query columns of the stored procedure.
4. Drag and drop the report controls in the Toolbox to design and format the custom report.

All the parameters defined in the stored procedures used by the RDLC reports must also be defined in the reports.

To add the store procedure parameters to a report:

1. From the Report menu, click Report Parameters to view the list of report parameters.
2. Click the Add button to create a new report parameter and then set the following for the new report parameter:
   1. Name: type the name of the parameter; the spelling must match exactly to the corresponding parameter defined in the stored procedure; for more information, see Creating the Stored Procedures in the Database
   2. Data type: select the parameter type from the list
      1. If Integer or SMALLINT is defined as the parameter type in the stored procedure, then select Integer as the data type
      2. If the date is defined as the parameter type in the stored procedure, then select DateTime as the data type
   3. Prompt: type the name of the parameter shown on the report interface
   4. Default values: set a default value for all the parameters:
      1. If Integer or SMALLINT is defined as the parameter type in the stored procedure, then enter 0 as the value
      2. If the date is defined as the parameter type in the stored procedure, then enter any date in the format MM/DD/YYYY
      Note: Perform the same steps for all the parameters defined in the stored procedure to add them to the report.
3. Click the OK button after all the parameters for the stored procedure are defined in the report.

To run a custom RDLC report in Tenrox, the RDLC file needs to be uploaded and linked to a new report in the Tenrox.

The creation of the new reports in Tenrox consists of the following steps.

• Uploading the RDLC Files to Tenrox
• Generating the Reports

The RDLC file is to be uploaded and linked to a new report in the Tenrox from the Reports portal.

To upload the RDLC file to Tenrox:

1. Launch the Tenrox application and then click the Reports menu.
2. Right-click the folder that will contain the report and then click New Report>Local Microsoft Report (RDLC) to create the custom report.
3. Enter the name of the custom report in the Name box and the click on the Report file browse button (...) to open the Report Edit page.
4. Click the Report Upload File browse button to upload a report and then
5. Select the report File and then click Open.
6. Click the Save link to save the file.

The created reports are generated from the Tenrox Reports portal.

To generate a report:

1. Launch the Tenrox application and then click the Reports from the menu bar to display a list of all the reports.
2. Expand the folder that contains the created report and then click the report name to run the report.
3. Set the parameters for the selected report and then click the Preview link to generate the report.

The available single select and multi-select parameters, as well as objects and codes, are listed in the tables Single Select Parameters and Multi-Select Parameters respectively.