Crystal Reports.Net is used to create and render reports in OfficeClip. The reports file (.rpt) files are created using the crystal reports designer (Most of the bundled reports are created using the crystal reports designer in Visual Studio.Net). Ado.Net dataset is used to link the reports with the dataset generated by the program. Following applications use reports in OfficeClip,

• Tracker - Reports on statistical information
• Time and Expenses - Administrative Reports

The datasets are stored in the schema file (.xsd extension). While modifying existing reports or creating new reports, care must be exercised so that the columns in the dataset and the columns in the reports exactly match.

Note: It is a good practice to give different names to the dataset (schema) and the report (.rpt) files. The .Net runtime framework generates another file with .xsx extension corresponding to each of these (schema & rpt) and one may override the other.

Given below is an example of the reports.xml file that can be used to create a crystal report and an OfficeClip report.

<?xml version="1.0" encoding="utf-8" ?>
 <reports>
  <report id="1" path="resources/reports/teadmin/" name="TasksR.rpt" program="te/reports/reportTasks.aspx" dataset="TasksD" category="teadmin">
   <title> Task Report    </title>
   <description> Displays the employee's name, job/client, and total hours tasks.    </description>
   <col1> Employee Name    </col1>
   <col2> Task Name    </col2>
   <col3> Number Of Hours    </col3>
   <sql>
<![CDATA[ select u.last_name+', '+u.first_name as 'Employee Name', tt.task_name as 'Task Name', sum(td.time_duration) as 'Number Of Hours' from timesheetdetails td, timesheets t, timesheettasks tt, users u where t.user_id = u.user_id and t.timesheet_id = td.timesheet_id and t.timesheet_template_id = tt.timesheet_template_id and t.group_id = :groupId and td.task_id = tt.task_id and tt.task_name like (':arg3') and td.task_date >
= convert (datetime, ':arg1', 101) and td.task_date <= convert (datetime, ':arg2', 101) group by u.last_name+', '+u.first_name, tt.task_name order by 'Employee Name', 'Task Name' ]]>
   </sql>
  </report>

  <report id="1004" path="custom/customer/teMonthlyReport/teMonthly/" name="monthly.xml" program="custom/customer/teMonthlyReport/reportCriteria.aspx" dataset="teMonthly" category="teMonthly">
   <title> Time Card Listing    </title>
   <description> Provides the cummalitive report for Time and Expenses    </description>
   <GroupPermission value="-1" />
   <sql>
<![CDATA[ select ISNULL(c.name, '***') as Client, ISNULL(p.name, '***') as Project, convert(char(10),td.task_date,101) as TaskDate, tt.task_name as TaskName, td.time_duration as Hours, td.description as Description from users u, timesheets t, timesheettasks tt, timesheetdetails td left outer join projects p on td.project_id = p.project_id left outer join customers c on td.customer_id = c.customer_id where t.user_id = u.user_id and u.user_id = :arg1 and t.timesheet_template_id = tt.timesheet_template_id and tt.task_id = td.task_id and t.group_id = :arg2 and td.timesheet_id = t.timesheet_id and td.task_date >
= convert (datetime, ':arg3', 101) and td.task_date <= convert (datetime, ':arg4', 101) order by td.task_date ]]>
   </sql>
  </report>
 </reports>

The first report is an OfficeClip internal report that is created using the Crystal report and the second report is a client report that is created using the OfficeClip internal report generator.The report id from 1-999 is reserved for OfficeClip reports, user report can be created from 1000 and upwards. Following are the explanation of the fields for the reports.xml file.

1. id - The id of the report (this value should be greater than 999 for custom reports).
2. path - The relative location where the report file is stored.
3. name - The name of the report file, currently all names that has extension as rpt is considered as a crystal report and all files that ends with xml is considered to be using the OfficeClip report engine.
4. program - The filter program that provides the filter condition of the report. Currently this file is an aspx file that needs to be created.
5. dataset - The dataset schema that returns the data from the database. This is required for crystal reports as the reports are generated using the ado.net dataset. This file also resides in the same directory as the respective report.
6. title - The title of the report. The title appears on all the report pages.
7. description - The description of the report, the description appears in the first page where the report is selected.
8. col1 - The first column of the report. If this is present then the first column name will be replaced by this.
9. col2 - The second column of the report. If this is present then the first column name will be replaced by this.
10. col3 - The third column of the report. If this is present then the first column name will be replaced by this
11. sql - The sql query for the report, note that this sql statement can pass arguments by specifying :groupId, :arg1, :arg2 and :arg3 parameters. These parameters whould be defined in the program specified in the program field as given above.

In the second report above, An xml file is specified as the report file. This file describes the layout of the report for all reports generated through the OfficeClip report generator. The specification of the file is shown below:

<?xml version="1.0" encoding="utf-8" ?>
 <Layout>
 <ColumnSelect>
  <Column cid="0" name="Client"/>
  <Column cid="1" name="Project"/>
  <Column cid="2" name="Date"/>
  <Column cid="3" name="Activity"/>
  <Column cid="4" name="Duration"/>
  <Column cid="5" name="Description"/>
 </ColumnSelect>
 <GroupColumn value="0" />
 <Total Columns="4" ShowGroupTotals="Y" />
 </Layout>