Add Custom Log Report - Advanced Topic - Report Design - Display Dates In User's Timezone and Filter By Date Range Using UTC Format

Since construction and engineering firms can work in multiple timezones, all dates are stored in Universal Time Coordinated (UTC) format. However, these dates can be displayed in the user's timezone using the following technique.

Add UserTimeZone Parameter:

1. Open the report in design mode and click the parameters icon on the right.
   
   
   
   
2. Click the 'New Parameter' link at the bottom.
3. Add UserTimeZone parameter. This should be string format and hidden per the image below.
   
   
   
   
4. Optional: Click the 'Assign Values' link below the Visibility section to add a default value.
5. Optional: Setting Default Value to user's timezone.
   
   Windows Default Time Zone Options
   
   
   
   
   
   
6. Click the 'Save' button at the bottom to save the UserTimeZone parameter.
   
   
   
   
7. Click the 'Save Report' link at the top to save these changes.

Add FromDate and ToDate Parameters for Filtering:

1. With Parameters section still open, click the 'New Parameter' link at the bottom to add FromDate parameter.
   
   
   
   
2. Click the 'New Parameter' link at the bottom to add ToDate parameter.
   
   
   
   
   
   
3. Click the 'Save Report' link at the top to save these changes.

Add Special Code to convert dates in a dataset or report parameters to the user's timezone or UTC format:

1. Click the gray area of the report then click the 'Properties' icon on the right. This will display the Report Properties.
   
   
   
   
2. Click the 'Code' button. See image above.
3. Enter the following two functions in the Code field.
   
   Report Code (Copy)
   
   
   
   
4. Click the 'Ok' button. See image above.
5. Click the 'Save Report' link at the top to save these changes.

Modify the main dataset that is used by the tablix control:

1. Click the 'Data' icon on the right to view the existing datasets used in the report.
   
   
   
   
2. Navigate to the dataset that is used by the tablix control (ex. dsPOs) and click the Edit option. See image above.
3. Scroll down to a data field that will be displayed in the report. Example: POIssueDate
   
   
   
   
4. Use the function that was added in the prior section to convert the date field (ex. POIssueDate) from UTC to the user's local timezone.
   
   
   
   
5. Optional: Repeat the prior step for any additional date fields that will be displayed in the report.
6. Scroll down to the bottom of the dataset to add the from and to date filters.
   
   Dataset SQL Filter (Copy)
   
   
   
   
7. Click the 'Finish' button at the top. See image above.
8. Click the 'Save Report' link at the top to save these changes.

Convert @FromDate and @ToDate Report Paramaters to UTC in the Dataset Parameters section:

1. Select again the dataset used by the tablix control and click the Parameters option.
   
   
   
   
2. Click the little square icon next to @FromDate parameter then click the Expression option.
   
   
   
   
3. Replace the expression with the following.
   
   From and To Expressions (Copy)
   
   
   
   
   
   
4. Click the 'Ok' button at the bottom. See image above.
5. Repeat the steps above to change the @ToDate parameter to UTC.
   
   From and To Expressions (Copy)
   
   
   
   
   
   
6. Click the 'Save Report' link at the top to save these changes.