SAP Knowledge Base Article - Public

2604895 - No data displays for Date and Date Time fields, when reporting off MariaDB in Crystal Reports

Symptom

  • Date column empty.
  • Date or Date Time fields shows no data.
  • When reporting off MariaDB data source in Crystal Reports using an ODBC connection, Date and Date Time fields does not return any data. It only shows NULL values, despite there is date values in the data source.

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Reproducing the Issue

  1. In Microsoft ODBC Administrator, create an ODBC DSN to MariaDB
  2. In Crystal Reports, create a report using an ODBC connection to MariaDB
  3. Add database fields to the report, including Date and Date Time fields.
  4. When refreshing the report, notice the columns for the Date and Date Time fields are empty or NULL, despite there should be data for it.

Cause

  • MariaDB is not listed in the supported data sources for Crystal Reports.
      
  • For reference, see the Crystal Reports Supported Platforms found in the SAP Knowledge Base Article: 2859510

Resolution

  • As a potential workaround to be able to see data for Date and Date Time fields in Crystal Reports, use an OLEDB Provider for ODBC Drivers, and then set the option: "Overridden Qualified Table Name", to the same name as the table name.
       
    1. In Crystal Reports, create a connection to MariaDB using the OLE DB Provider for ODBC Drivers.
         
      1. 1.1 In Crystal Reports, under the menu "File", select "New - Blank Report..."
          
      2. 1.2 In the "Database Expert" window, under "Available Data Sources", expand "Create New Connection"
          
      3. 1.3 In the list of connection type, double click on "OLE DB (ADO)"
          
              MariaDB - 01.png 
               
          
      4. 1.4 In the "OLE DB (ADO)" window, select the provider: "Microsoft OLE DB Provider for ODBC Drivers", and click "Next"
           
               MariaDB - 02.png
                
          
      5. 1.5 Select the ODBC DSN that points to your MariaDB database, and click "Next"
          
      6. 1.6 Enter the user name, password, and click "Finish" 
          
      7. 1.7 Back to the "Database Expert" window, select the MariaDB tables to use for the report, and click "OK"
                 
              
               
    2. Set the option: "Overridden Qualified Table Name" to the same name as the table used on the report.
        
      1. 2.1 Under the menu "Database", select "Set Datasource Location..."
          
      2. 2.2 In the "Set Datasource Location" window, under "Current Data Source", display the properties of the table,
              by clicking on the plus sign of the table, and the Properties.
         
               MariaDB - 03.png
            
          
      3. 2.3 Right click on the option "Overridden Qualified Table Name", and select "Edit"
          
      4. 2.4 Enter the name of the table.
           
              For example: If the table name is: mytable, then enter: mytable.
          
                MariaDB - 04.png
               
          
      5. 2.5 Repeat the same steps as above for each table used on the report.
           
      6. 2.6 Once completed, click on "Close"
          
           
            
    3. Add the database fields to the report, including your Date and Date Time fields.
       
      Now, when refreshing the report, it will display values for the date fields.

Keywords

CR, MariDB , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016