2661347 - Different number of records returned after converting a report from using the SAP BW Query Driver, to the SAP BW MDX Query driver in Crystal Reports

• Report based off the same BEx Query returns different number of records.
• Report converted from using the SAP BW Query Driver, to using the SAP BW MDX Query driver, returns different data set.
• After updating an old report based off an SAP BW BEx Query, to the SAP BW MDX Query driver in Crystal Reports, the report does not return the same number of records, as it used to.

• SAP Crystal Reports 2008
• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
• SAP Crystal Reports 2016

1. In Crystal Reports 10 or below, create a report off an SAP BW BEx Query using the SAP BW Query Driver.
2. Refresh the report, and take note of the number of records returned.
3. In Crystal Reports XI and above, convert the report to use the SAP BW MDX Query Driver, and map each fields.
4. Refresh the report, and noticed the number of records returned is different than when using the deprecated SAP BW Query Driver.

• In brief, the SAP BW MDX Query driver uses a different technology to connect to an SAP BW BEx Query than the SAP BW Query Driver, and depending on how the BEx Query was designed, and how the report was designed, it may cause the report to return a different number of records, as it shows a different view of the data.
          
• In more details:
  

  The SAP BW Query Driver was deprecated from Crystal Reports XI, released in 2004, and replaced by the SAP BW MDX technology.
  It was still included in the next couple of versions of Crystal Reports to help transition reports from the legacy driver to the SAP BW MDX Driver, and removed from Crystal Reports 2011 and above. For reference, see the SAP Knowledge Base Article:
  
    1716438 - Error: 'The Database connector crdb_bwquery.dll could not be loaded' when refreshing a report in Crystal Reports
  
  The drivers use a totally different technology to connect to SAP BW BEx Query, and therefore there are many differences in how it works.
  We will be listing a couple of the differences below to explains why the data set can be different, and what are the main differences between the 2 drivers:
    
    
  Database Fields
          
  First, when using the SAP BW MDX Query driver, you will notice it list more database fields for the SAP BW BEx Query. This is because the driver provide more detailed information than the deprecated SAP BW Query Driver.
    
  For example:
    
  If you had a BEx Query with Product and Product Price, then when using SAP BW Query Driver it will show the following fields in Crystal Reports:
  - Product
  - Product Text
  - Product Price
      
  But with the SAP BW MDX Driver, it will show the following fields in Crystal Reports:
  - Product:
  - Product Node ID
  - Product Parent Node ID
  - Product Key
  - Product Name
  - Product Medium Name
  - Product * (key )
  - Product * (Name)
  - ...
     
  With MDX, you have fields to be able to build a hierarchy, you have the key, the medium text, all the attributes, etc...
  One thing it does not show with the SAP BW MDX Driver is the following BEx Query information like:
  - Cached Data Used
  - Cache Refresh Time
  - Cube Type
  - Cube Creation Time
  - Schema Update Time
  - Data Update Time
       
  When performing a set data source location from the SAP BW Query Driver to the SAP BW MDX Driver, you need to make sure it is mapped to the right database fields.
        
       
  Show Free Characteristics
     
  Secondly, the SAP BW Query Driver was not adding any of the Free Characteristics that were in the SAP BW BEx Query, therefore if you had a BEx Query with the following:
  - Product
  - Product Price
      
  With the following Free Characteristics:
  - Product Type
  - Product Owner
      
  With the SAP BW Query Driver, it will only show in Crystal Reports the following:
  - Product
  - Product Price
      
  But with the SAP BW MDX Driver, it will show everything in Crystal Reports:
  - Product
  - Product Price
  - Product Type
  - Product Owner
       
  And depending which database field is used on the report, the result set will be different because like in BEx Analyzer, if you add the free characteristic to the initial view, it will show you a different view of the data set.
          
     
  No Default Values Concept
  
  Another difference, will be if you have Default Values set in the BEx Query, the SAP BW Query Driver would have the data filtered based on the Default Values.
  But with the SAP BW MDX Driver, it will ignore those Default Values. The reason, it's because the Default Values in a BEx Query are a way to show the information in BEx Analyzer filtered based on those default values, but it actually returns the whole data set to BEx Analyzer, and only show a filtered view of the data. With the MDX Query Driver, it will bring the whole data set based on the database fields selected on the report, because there is no concept of Default Values like in BEx Analyzer.
         
  For example:
     
  If we create a BEx Query with the following:
  - Product
  - Product Price
     
  And we have Default Values set to only return the Product: Soccer Ball
  If the SAP BW Cube have the following data:
  - Soccer Ball
  - Skate
  - Ski
     
  Then in BEx Analyzer, SAP BW will send all the data for the price for the product to the client computer:
  - Soccer Ball
  - Skate
  - Ski
    
  But will only show to the end user:
  - Soccer Ball
    
  because the Default Values is set to: Soccer Ball
    
  But all the data is available on the client computer, and can be filtered to a different product without having to request new data from the SAP BW Cube.
  This can be done to help performance in BEx Analyzer, as it does not need to communicate to the SAP BW System to retreive different data set.
  With the SAP BW MDX Driver, it will return all the data from the SAP BW Cube, as there is no concept of showing an initial screen with default value. You can then filter the information within Crystal Reports designer if needed, so it will show all the products, which are:
  - Soccer Ball
  - Skate
  - Ski
      
  The SAP BW MDX Driver retreive the same result set as in BEx Analyzer, despite the end user has the impression they have a different data set because of the Default Values simply filter the data locally.
       
  If you want the data to be filtered, then move the restriction from Default Values section to the Characteristic Restrictions in the BEx Query.
  For reference, see the SAP Knowledge Base Article:
    
    1900941 - BEx Query variable does not filter data on the report in Crystal Reports for Enterprise or in a Dashboard
     
       
  Enable you to add any database fields
  
  When reporting off a BEx Query in BEx Analyzer, you can see different view of the data by adding, or removing different dimensions and key figures to the initial views. With the SAP BW Query driver, it was only showing the data set you will see when seeing the intial view of the BEx Query in BEx Analyzer.
  But with the SAP BW MDX Driver, you can freely add any dimension and key figures to the report, which will query the SAP BW Cube to return the different data set reflecting your selection, like in BEx Analyzer. Therefore, reporting off the same BEx Query can return different data set depending on the database fields used on the report.
       
  For example:
     
  If we have a BEx Query that have the following:
  - Product
  - Product Type
  - Product Price
       
  When using the SAP BW Query Driver, it returns the data for the slice of the SAP BW Cube.
  Let's say it returns 5 records:
  - Soccer Ball (4)    Summer Sport  10$
  - Soccer Ball (5)    Summer Sport  15$
  - Skate (Small)     Winter Sport     30$
  - Skate (Medium)  Winter Sport     35$
  - Ski                     Winter Sport    40$
         
  Regardless of the database field used on the report, as it always shows the initial views.
  But when using the SAP BW MDX Driver, it only retreive the data based on the database fields used on the report, the same way you will see different data set in BEx Analyzer when adding or removing dimensions and key figures. Therefore, if we report from the same BEx Query, but we only add the fields:
  - Product Type
  - Product Price
        
  Then it will only return 2 row of data, like:
  - Summer Sport   25$
  - Winter Sport    105$
                  
  Therefore, even if you are reporting off the same BEx Query, you can have different result set. And this is another reason why when converting a report from the SAP BW Query Driver to the SAP BW MDX Driver that there is a potential that the data set return is different. Not because it is showing incorrect data set, but because it shows the data based on what is actually on the report.
         
        
  Decimals
     
  The SAP BW MDX Driver may also not show the same level of precision for Calculated Key Figure because when using the MDX BAPI technology to communicate with SAP BW BEx Query, the MDX BAPI call is returning the formatted value only for Calculated Key Figure. For reference, see the SAP Knowledge Base Article:
    
    1903060 - Calculated Key Figures does not show the same level of precision in Crystal Reports than in BEx Analyzer
       
  The suggestion is to format the key figure in the BEx Query to show greater precision.
        
  

• The report actually returns the correct data set in Crystal Reports, for the characteristics and key figures inserted on the report, but if you want to return the same data set as it used to when using the BW Query Driver, then the suggestion will be to make sure you have all the database fields that exist in the BEx Query to the report design, to the exception of the free characteristics because those weren't available when using the SAP BW Query Driver.
       
1. In BEx Query Designer, open the BEx Query on which the report is based on, and take note of all the rows, and columns. 
   ( Do not take note of the Free Charactristics, as the BW Query Driver ignored those one, and was not included. )
            
2. Still the BEx Query Designer, under the Default Filters, verify if there is any filter. If there is, add those under Filters, instead of Default Filters, and save the BEx Query.  ( Note, you may want to make a backup copy of the BEx Query before making any changes. )
              
3. In Crystal Reports, open the report, and insert an extra Details section, and suppress it.
          
4. Insert in the Details section all of the rows and columns listed in the BEx Query.      

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

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016