Introducing data types in Custom report definitions

Starting from WebCenter 1.1.46 custom reports have a new feature that gives users more control over the output of the custom reports. This feature allows creators of the custom report definition files (CRD’s) to specify additional information about the data returned by the report, and how this data should be formatted. This additional information is a new tag called <DataType> that should be added to the Column section of the GenerateSqlStatement in the CRD file. A complete column tag should look like the below after adding this new tag.

<Column>
  <Name><![CDATA[Contact handled by]]></Name>
  <Tooltip><![CDATA[Contact handled by]]></Tooltip>
  <DataType>string</DataType>
</Column>

The value of the <DataType> tag can be any of the supported C# types from the table below:

table

https://msdn.microsoft.com/en-us/library/ya5y69ds.aspx

Only the supported names from „C# Type“ column are allowed, and the names are case-insensitive. But the <DataType>  tag and all attributes are case-sensitive, because XML itself is case-sensitive by design.

Compared to the list from the above link there are some differences in the above table. Two additional types are supported (DateTime and TimeSpan). These types are also standard .NET structures for storing date, time and time duration values.

Just adding the types above to the column definition would not really be useful, since there would be a lot of default initialization and formatting for these types. For example, if your custom report returns only a time value without the date information, and that column is defined as datetime in <DataType>  tag, then it will be created as a datetime value with current date, and the date part will be also displayed. For more details about handling time values, and mappings between SQL Server and .NET data types please see dedicated section below.

To add more flexibility and to allow you to format and display the data in the way you like additional attributes can be added to <DataType>  tag as shown below.

<Column>
  <Name><![CDATA[Contact Date]]></Name>
  <Tooltip><![CDATA[Contact Date]]></Tooltip>
  <DataType Format="d" ExcelFormat="14">datetime</DataType>
</Column>

The Format attribute is used to specify how a value should be presented when the report is generated in the WebCenter. And the ExcelFormat attribute defines in which format the value should be displayed when the same report is exported to Excel.

Since the WebCenter is a web application based on .NET the formatting values depend on the .NET culture being used. You can find out more about how the WebCenter is determining the culture being used in separate post.

Format attributes accept any valid .NET standard and custom formatting string. A complete list of standard date formatting strings can be found on the following link: https://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.100).aspx

A complete list of custom date and time formatting strings can be found on the following link: https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.100).aspx

All valid values for ExcelFormat attribute are predefined in the Office Open XML Spreadsheet Markup language specification. A Complete reference can be found on page 2128, and it contains over 5000 pages. The Web center is using a library called ClosedXML when manipulating Excel files. A List of predefined format values for the ExcelFormat attribute can be found on this library’s site: http://closedxml.codeplex.com/wikipage?title=NumberFormatId%20Lookup%20Table

For the bool data type there are additional attributes that allows specifying the custom string that will be displayed in case the value is true or false.

<DataType ExcelFormat="0" TrueString="Ja" FalseString="Nein">bool</DataType>

Samples

Here are a few samples of the <DataType>  tag with additional formatting options specified in attributes:

Sample 1: d, 14, datetime

<Column>
  <Name><![CDATA[Contact date]]></Name>
  <Tooltip><![CDATA[Contact date]]></Tooltip>
  <DataType Format="d" ExcelFormat="14">datetime</DataType>
</Column>

In this case a value in the column is a datetime type, and the Format attribute value indicates that the value in this column will be formatted using the standard Date and Time format string ‘d’, which means that datetime value will be displayed as short date pattern. The value of the ExcelFormat attribute indicates that the predefined formatID ’14’ (d/mm/yyyy) will be used to display this column in Excel.

Sample 2: g, 1 , int

<Column>
  <Name><![CDATA[Handled Contacts]]></Name>
  <Tooltip><![CDATA[Handled Contacts]]></Tooltip>
  <DataType Format="g" ExcelFormat="1">int</DataType>
</Column>

In this case the column value is defined as int (integer), and it will be formatted with the Standard Numeric Format string ‘g’ (as specified in Format attribute). For Excel the predefined formatID ‘1’ (0) is specified in ExcelFormat attribute.

Sample 3: p2, 10 , decimal

<Column>
  <Name><![CDATA[Unhandled Contacts Percentage]]></Name>
  <Tooltip><![CDATA[Unhandled Contacts Percentage]]></Tooltip>
  <DataType Format="p2" ExcelFormat="10">decimal</DataType>
</Column>

In this case the column value is defined as a decimal. For the presentation in the WebCenter the value will be formatted using Standard Numeric string and defines 2 numbers behind the decimal point. This is usually used for presenting percentage values. In excel this will be presented as predefined formatID ’10’ (0.00%), which also displays the value as a percentage.

Mapping data types between SQL Server and .NET

The SQL Server and .NET data types do not match exactly. SQL Server has recently introduced new data types for Time and Date. And these do not have matching .NET counterparts. In .NET there is only one datetime for handling all date and time values. But on the other side SQL Server does not have a matching data type for .NET TimeSpan, which represents time duration. Time data type is closest, but it does not accept values over 24 hours. Most other .NET data types have matching SQL Server types, and complete mappings can be found on the following link: https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx

It is important to note that a data type specified in CRD XML is expected by the WebCenter, and the SQL query should return the corresponding SQL data type for that column according to the mapping of the data types between SQL Server and ADO.NET. Every column value is being parsed to the data type specified in the CRD, and thus every value must obey the parsing rules for a certain .NET data type. For example if the CRD specifies bool data type for Column1, and the SQL query returns the integer value 1 for that column, it will be evaluated to false. This is because the value returned by the SQL query will be parsed as a string, and the result of the Boolean.TryParse operation will be false. So, even if it’s logically correct the returned integer value will be presented wrong.

For the above mentioned reasons there are certain conventions implemented in WebCenter related to handling date and time values.

Duration values

Duration of time can be presented in various ways. In one case duration may be displayed in common format with hours, minutes and seconds, while in another case it may be needed in minutes only. When a duration spans across more than one day it is often required that the hours part include the day duration. Since returning duration values from SQL Server may be complicated the WebCenter has conventions to use date time value of base SQL Server date January 1st 1900 at midnight as start time value for durations.

If you need to display duration values longer than one day then you have to add days to base date. For example if duration of value is 25 hours you have to return 1900-01-02-01:00:00 from your SQL query. The WebCenter will subtract the provided value from the base date and time value, and treat the subtracted value as TimeSpan data type. As a consequence the Format attribute value should be specified as it were TimeSpan data type, even though you actually have to specify datetime in CRD XML to comply with ADO.NET mappings and value returned from SQL Server.

<DataType Format="HH:mm:ss" ExcelFormat="46">datetime</DataType>

Since there is no supported string format to get the total hours from the TimeSpan value we have to go beyond the standard and custom format strings in .NET. To support this feature the WebCenter is using the NodaTime component and its formatting options. So, to specify duration in hours and without days you need to include capital ‘H’ for the hours placeholder. Formatting of these values is done by NodaTime Duration format strings, and more information about other formats can be found on the following link: http://nodatime.org/1.3.x/userguide/duration-patterns.html

Another way to display duration values is to return the value as a valid date time string that will be successfully evaluated to either TimeSpan or DateTime by the ADO.NET. But that is risky, and depends on the systems regional settings, and is not recommended.

Custom Excel formats

Predefined Excel formats may not suite your needs all the time, and there may be a need for some specific formats. In the ExcelFormat attribute value you can specify any custom string that is supported by Excel. You can read more about what is possible to define a format using the steps described on the following link: https://support.office.com/en-ca/article/Format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e

Please note that the format strings depend on regional settings. If you open the Excel on a German system the same strings displayed in the Type box of the Format cell dialog on link above will look different. For example date string dd.MM.YYYY will be TT.MM.JJJJ. But, when specifying a custom Excel format in the CRD files only English string formats (abbreviations) are supported.

It is also important to note that the same Excel file will display the data in different formats depending on the regional settings of the machine where it is being opened.

Predefined Excel values can be extended. Here are two samples of customized Excel formats:

Sample 1: dd.MM.YYYY

<DataType Format="dd.MM.yy H:mm:ss" ExcelFormat="dd.MM.YYYY hh:mm:ss">datetime</DataType>

In this case we want to include seconds in date and time value. Predefined FormatID ’22’ is missing seconds so we cannot use it, but we can use it as starting point. We can simply extend the predfined FormatID and set the ExcelFormat attribute value as dd.MM.YYYY hh:mm:ss to get the output like 07.03.2015 10:34:53.

Sample 2: € #,##0.00

<DataType Format="c" ExcelFormat="€ #,##0.00">Decimal</DataType>

In this case we are adding the EURO sign to predefined FormatID ‘4’ to get the output like € 456,0078.

The Web center is using the ClosedXML component for creating Excel files. Looking for some more information on component’s documentation pages (link can be found below) may be useful: http://closedxml.codeplex.com/documentation

Logging format errors

During creating and debugging custom report definitions it may be useful to log the format exceptions. To enable logging of such exceptions the web.config setting called ReportingLogParsingErrors must be set to true. When logging is enabled all format exceptions related to CRD data types are logged in the WEB10_Log database table. Entries related to CRD formatting errors can be filtered by starting CRD: characters. It is advisable to turn off logging in production because of possible performance issues, since every error is logged in a separate batch.

It is important to know that format exceptions are handled by the WebCenter, and the report will be generated even if there are errors. In that case the report values will be displayed as a string that was produced by the ToString() method of the column data type that raised an error.

Also, changes to the web.config values must be done with care, since any change will restart the application. It means that everyone logged in to the Web center at the time of the change will lose his session, and all the changes that are not saved before web.config change occurred.

– Orignal document created by Josip Klaric –

Leave a Reply