2458569 - Cross-tab truncated when inserted in a subreport in Crystal Reports

• Cross-tab truncated.
• Cross-tab not displaying all the columns when inserted in a subreport.
• In Crystal Reports, when inserting a cross-tab in a subreport, it truncates the cross-tab columns, but when drilling-down on the subreport it shows all the columns.

• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020

1. In Crystal Reports, create a report based on any data source.
2. Insert a subreport in the Report Footer section.
3. In the subreport, insert a cross-tab.
4. When viewing the report, the cross-tab columns are truncated, or not all displaying. 

• A cross-tab is a special object that can grow horizontally based on the data.
    
• The cross-tab columns are truncated when inserted in a subreport because a subreport has a fix width, and cannot grow horizontally.
      
• This is normal because a subreport is a report within a report, and it prints in the defined space on the main report. And since a cross-tab can grow horizontally, it will truncate the cross-tab, if it grow past the width size defined for the subreport.

• Verify if using a subreport is necessary, and if it isn't, insert the cross-tab on the main report instead.
    
• If the use of a subreport is necessary, then a potential workaround is to create a group containing N column values, and insert the cross-tab in the group header. Below are the general steps on how to generate a dynamic group that limits the number of columns a cross-tab will display.
       
1. Save the subreport that contains the cross-tab.
      
   Note: We will use the copy of the subreport to store the column names, to be able to generate group of N number of columns.
          
2. Insert the copy of the subreport that contains the cross-tab in a section above, the current subreport.
       
3. Edit the subreport, and suppress all the sections, and remove the cross-tab.
      
4. Still in the subreport, insert a group based on the database field used in the column section of the cross-tab.
     
5. Create a formula to create a string of all the column values. The formula will also divide the string in group of X number of values, and share the string of values to the main report. The formula will look like:
     
   WhilePrintingRecords;
   
   Local StringVar currentValue   := <INSERT YOUR DATABASE FIELD NAME HERE>;  // Database Field used in the column section of the cross-tab
   Local NumberVar valuesPerGroup := 5;    // Values per group.
   
   Shared StringVar listOfValues;   // Shared variable to concatenate all the column values
   NumberVar i := i + 1;
   
   If i = 1 Then
      listOfValues := currentValue
   Else If Remainder(i-1,valuesPerGroup) = 0 Then
      listOfValues := listOfValues + "~" + currentValue
   Else
      listOfValues := listOfValues + "^" + currentValue;
             
      
6. Still in the subreport, insert the above formula in the Group Header.
     
7. In the main report, create a formula outputing the column values accumulated from the copy of the subreport. The formula will look like:
          
    WhilePrintingRecords;
    Shared StringVar listOfValues;   // Shared variable containing all the column values, shared from the copy of the subreport.
        
8. Edit the original subreport that contains the cross-tab, and create a string parameter, called: listOfValues 
    
   Note: This parameter wil be used to pass the list of the column names from the shared variable of the main report to the subreport.
       
        
9. Still in the orignal subreport, create a formula that will be use to create a group that will dynamically show X number of values per group based on the list of column names. The formula will look like:
       
   WhileReadingRecords;
   Local StringVar currentValue := <INSERT YOUR DATABASE FIELD NAME HERE>;  // Database Field used in the column section of the cross-tab
   Local StringVar groups       := {?listOfValues};   // Parameter
     
   Local NumberVar numberOfGroups := uBound(Split(groups,'~'));
   Local BooleanVar found := False;
   Local StringVar myGroupName := "";
   Local NumberVar i := 1;
     
   While not found and i <= numberOfGroups do
   (
      If currentValue in Split(Split(groups,"~")[i],"^") Then
      (
         myGroupName := "Group #" + ToText(i,'00',0,"");
         Found := True;
      );
      i := i + 1;
   );
   
   myGroupName; 
          
10. Insert a group on the above formula to dynamically group the values used by the cross-tab columns in group of X number of values.
     
11. Move the cross-tab in the group header section.
      
12. Finally, on the main report, link the formula that contains the list of values for the columns (created in step 7), to the subreport parameter: listOfValues (created in step 8)
      
    There is some formatting that will need to be done.
                 
          
• Limitation
  The workaround is limited to 1,000 values, because it uses arrays in the subreport, and an array is limited to 1,000 values. If you expect the group to have more than 1,000 values on your report, then store the values in multiple strings of 1,000 values each, and link each of those strings to different subreport parameters, as demonstrated in the above technique.
             
         
• Sample report demonstrating the above technique available in the Attachements section:
  Subreport - Cross-tab - Workaround.rpt  

CR, xtab, crosstab, Cross-Tab , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020