Skip to Content

Crystal Cross Tab, column with Zero values dropped

Experts,

I'm using a SQL View from which I pull Financial Data from several DB's into a Crystal Income Statement. The Columns and the different Companies.

I'm using several cross tab's for Revenue, Expenses ect. and total by them. This report needs to be exported into Excel - therefor I need the columns to line up. My problem is that when there is no Revenue recorded for a Company that column does not show, and for exporting to Excel this needs to show even if it is Zero.

I do not have the Suppression on the Cross Tab selected, as per screenshot.

Here are some screenshots:

5.png (83.9 kB)
6.png (138.0 kB)
7.png (218.8 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Mar 05 at 06:13 PM

    Hi Marli,

    Unfortunately, Crystal Reports can't show what's not available in the database.

    Company B does not appear in the first crosstab because there are no entries in the database for that company.

    To resolve this issue, you'd need to modify the View's SQL such that it always brings in all the Companies you need to display on the report.

    One way of doing this is to create a "Look-up" table that lists the companies. You can then "LEFT JOIN" from this look-up table to the Revenues table. This should list all companies irrespective of where there are any revenues or not.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Marli,

      The issue is that you have a selection formula that returns data between the selected date range.

      Since ST_Canada does not have any data between that date range, the report doesn't return it.

      Assuming your database does not have a "Calendar" table that lists ALL days, you could build one yourself in the View. The Modified SQL would look something like this:

      DECLARE @startdate date
      DECLARE @enddate date
      SET @startdate = '2018-01-01'
      SET @enddate = '2018-12-31'
      ;WITH Calndr AS
      (
          SELECT @startdate AS DateValue
          UNION ALL
          SELECT DATEADD(DAY, 1, DateValue)
          FROM Calndr
          WHERE DateValue < DateAdd(DAY,-1, @enddate)
      ),
      Comp AS
      (
      Select 'Inguran' As Company
      UNION
      Select 'ST_Canada' AS Company
      ),
      Compny AS
      (
      SELECT DISTINCT
      Comp.Company,
      Calndr.DateValue
      FROM
      Calndr
      CROSS JOIN Comp
      ),
      DataT AS
      (
      SELECT   
      'Inguran' AS Company, 
      T5.[U_Master], 
      T5.[U_Master_Name], 
      T5.[U_Inguran], 
      T0.FatherNum, 
      T0.FormatCode, 
      T0.AcctName, 
      SUM(T1.Credit - T1.Debit) AS Balance, 
      T3.OcrCode, 
      T3.OcrName, 
      T4.OcrCode AS Expr1, 
      T4.OcrName AS Expr2, 
      T2.RefDate, 
      T0.GroupMask, 
      T0.Segment_1, 
      T0.Segment_2, 
      T0.Segment_3
      FROM     
      Inguran.dbo.OACT AS T0 INNER JOIN
      Inguran.dbo.JDT1 AS T1 ON T0.AcctCode = T1.Account INNER JOIN
      Inguran.dbo.OJDT AS T2 ON T1.TransId = T2.TransId LEFT OUTER JOIN
      Inguran.dbo.OOCR AS T3 ON T1.ProfitCode = T3.OcrCode LEFT OUTER JOIN
      Inguran.dbo.OOCR AS T4 ON T1.OcrCode2 = T4.OcrCode LEFT OUTER JOIN
      Inguran.dbo.[@A_GL_MAPPING] T5 ON T5.[U_Inguran] = T0.FatherNum
      WHERE   (T0.GroupMask IN (8, 7, 6, 5, 4)) AND (T1.Debit <> T1.Credit) AND T3.OcrCode = 'SRVC'
      GROUP BY T0.GroupMask, T0.FatherNum, T0.AcctName, T0.FormatCode, T3.OcrCode, T3.OcrName, T4.OcrCode, T4.OcrName, T2.RefDate, T0.Segment_1, T0.Segment_2, T0.Segment_3, T5.[U_Master], T5.[U_Inguran], T5.[U_Master_Name]
      UNION ALL
      SELECT   
      'ST_Canada' AS Company, 
      T5.[U_Master], 
      T5.[U_Master_Name], 
      T5.[U_ST_Canada], 
      T0.FatherNum, 
      T0.FormatCode, 
      T0.AcctName, 
      SUM(T1.SYSCred - T1.SYSDeb) AS Balance, 
      T3.OcrCode, T3.OcrName, 
      T4.OcrCode AS Expr1, 
      T4.OcrName AS Expr2, 
      T2.RefDate, 
      T0.GroupMask, 
      T0.Segment_1, 
      T0.Segment_2, 
      T0.Segment_3
      FROM     
      ST_Canada.dbo.OACT AS T0 INNER JOIN
      ST_Canada.dbo.JDT1 AS T1 ON T0.AcctCode = T1.Account INNER JOIN
      ST_Canada.dbo.OJDT AS T2 ON T1.TransId = T2.TransId LEFT OUTER JOIN
      ST_Canada.dbo.OOCR AS T3 ON T1.ProfitCode = T3.OcrCode LEFT OUTER JOIN
      ST_Canada.dbo.OOCR AS T4 ON T1.OcrCode2 = T4.OcrCode LEFT OUTER JOIN
      ST_Canada.dbo.[@A_GL_MAPPING] T5 ON T5.[U_ST_Canada] = T0.FatherNum
      WHERE   (T0.GroupMask IN (8, 7, 6, 5, 4)) AND (T1.Debit <> T1.Credit)
      GROUP BY T0.GroupMask, T0.FatherNum, T0.AcctName, T0.FormatCode, T3.OcrCode, T3.OcrName, T4.OcrCode, T4.OcrName, T2.RefDate, T0.Segment_1, T0.Segment_2, T0.Segment_3, T5.[U_Master], T5.[U_ST_Canada], T5.[U_Master_Name]
      )
      SELECT
      Compny.Company,
      DataT.[U_Master], 
      DataT.[U_Master_Name], 
      DataT.[U_Inguran], 
      DataT.FatherNum, 
      DataT.FormatCode, 
      DataT.AcctName, 
      DataT.Balance, 
      DataT.OcrCode, 
      DataT.OcrName, 
      DataT.OcrCode AS Expr1, 
      DataT.OcrName AS Expr2, 
      Compny.DateValue RefDate, 
      DataT.GroupMask, 
      DataT.Segment_1, 
      DataT.Segment_2, 
      DataT.Segment_3
      FROM 
      Compny 
      LEFT JOIN DataT ON Compny.Company = DataT.Company AND Compny.DateValue = DataT.RefDate
      OPTION(MAXRECURSION 0)

      Notice that the StartDate and EndDate variables have to been harcoded to this year.

      -Abhilash

  • Mar 05 at 06:17 PM

    Hello Marli,

    If the data set in your sql view doesn't have revenue for "Comp B", the Cross-tab will not know there is data for "Comp B" and it will not have the "Comp B" column. The Cross-tab in Crystal Reports only takes what it returns from your sql view and then pivots the data.

    What you may try is to "fool" Cross-tab by adding a row with "0" revenue for "Comp B" in your sql view. This is like creating a place holder from your sql view. As you are consolidating financial numbers from multiple companies, you may add additional row with "0" revenue for all companies if needed.

    Cheers,

    Jimmy

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Marli,

      Sorry that I missed the "Select" in the beginning.

      Did the query add those two additional "0" revenue rows in the data set? If that went through, it could be some values hard-coded in those two rows didn't match the parameters (the "Sorting" values or the date format); and so these rows still got filtered out.

      May I ask what value will be passed to the report in the Rows section "@U_Master for Revenue"?

      And, I am just trying to troubleshoot based on the screenshots, in the date field, what format do other rows use? Will that help if you put "2018/02/01" instead of "01/02/2018 00:00:00"

      Thanks,

      Jimmy

  • Mar 07 at 07:56 PM

    Abhilash & Jimmy,

    You guys are great!

    Thank you so much for your help - I know it is not easy to support a relative newbie to SQL like myself.

    I have solved it with the following which might be overkill as it uses both your suggestions, but it is working.....

    USE [CompanyA]
    GO
    /****** Object:  StoredProcedure [dbo].[yRptSP_IncomeStatementService]    Script Date: 3/7/2018 12:00:28 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[yRptSP_IncomeStatementService] 
    --@BegDate as datetime = '01-01-2017',
    --@EndDate as datetime = '01-01-2018'
    @BegDate as datetime,
    @EndDate as datetime
    AS 
    
    
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    WITH DataT AS 
    (SELECT   'CompanyA' AS Company, T5.U_Master, T5.U_Master_Name, T5.U_Inguran, T0.FatherNum, T0.FormatCode, T0.AcctName, SUM(T1.Credit - T1.Debit) AS Balance, T3.OcrCode, T3.OcrName, T4.OcrCode AS Expr1, T4.OcrName AS Expr2, T2.RefDate, T0.GroupMask, T0.Segment_1, T0.Segment_2, T0.Segment_3
                         FROM      dbo.OACT AS T0 INNER JOIN
                                       dbo.JDT1 AS T1 ON T0.AcctCode = T1.Account INNER JOIN
                                       dbo.OJDT AS T2 ON T1.TransId = T2.TransId LEFT OUTER JOIN
                                       dbo.OOCR AS T3 ON T1.ProfitCode = T3.OcrCode LEFT OUTER JOIN
                                       dbo.OOCR AS T4 ON T1.OcrCode2 = T4.OcrCode LEFT OUTER JOIN
                                       dbo.[@A_GL_MAPPING] AS T5 ON T5.U_CompanyA = T0.FatherNum
                         WHERE   (T0.GroupMask IN (8, 7, 6, 5, 4)) 
    					 AND (T1.Debit <> T1.Credit) 
    					 AND (T3.OcrCode = 'SRVC')
    				     AND T2.RefDate Between @BegDate AND @EndDate
                         GROUP BY T0.GroupMask, T0.FatherNum, T0.AcctName, T0.FormatCode, T3.OcrCode, T3.OcrName, T4.OcrCode, T4.OcrName, T2.RefDate, T0.Segment_1, T0.Segment_2, T0.Segment_3, T5.U_Master, T5.U_CompanyA, T5.U_Master_Name
     
     UNION ALL
     select
    'CompanyB' AS Company, 
    'Sorting',--T5.[U_Master], 
    'Sorting',--T5.[U_Master_Name], 
    'Sorting',--T5.[U_ST_Canada], 
    'Sorting',--T0.FatherNum, 
    '',--T0.FormatCode, 
    '',--T0.AcctName, 
    0, --SUM(T1.SYSCred - T1.SYSDeb) AS Balance, 
    '',--T3.OcrCode, 
    '',--T3.OcrName, 
    '',--T4.OcrCode AS Expr1, 
    '',--T4.OcrName AS Expr2, 
    @BegDate,--T2.RefDate, 
    4,--T0.GroupMask, 
    '',--T0.Segment_1, 
    '',--T0.Segment_2, 
    '' --T0.Segment_3
    UNION ALL
    select
    'CompanyA' AS Company, 
    'Sorting',--T5.[U_Master], 
    'Sorting',--T5.[U_Master_Name], 
    'Sorting',--T5.[U_ST_Canada], 
    'Sorting',--T0.FatherNum, 
    '',--T0.FormatCode, 
    '',--T0.AcctName, 
    0, --SUM(T1.SYSCred - T1.SYSDeb) AS Balance, 
    '',--T3.OcrCode, 
    '',--T3.OcrName, 
    '',--T4.OcrCode AS Expr1, 
    '',--T4.OcrName AS Expr2, 
    @BegDate,--T2.RefDate, 
    4,--T0.GroupMask, 
    '',--T0.Segment_1, 
    '',--T0.Segment_2, 
    '' --T0.Segment_3
    
    UNION ALL
                         
    SELECT   'CompanyB' AS Company, T5.U_Master, T5.U_Master_Name, T5.U_ST_Canada, T0.FatherNum, T0.FormatCode, T0.AcctName, SUM(T1.SYSCred - T1.SYSDeb) AS Balance, T3.OcrCode, T3.OcrName, T4.OcrCode AS Expr1, T4.OcrName AS Expr2, T2.RefDate, T0.GroupMask, T0.Segment_1, T0.Segment_2, 
                                      T0.Segment_3
                         FROM     CompanyB.dbo.OACT AS T0 INNER JOIN
                                      CompanyB.dbo.JDT1 AS T1 ON T0.AcctCode = T1.Account INNER JOIN
                                      CompanyB.dbo.OJDT AS T2 ON T1.TransId = T2.TransId LEFT OUTER JOIN
                                      CompanyB.dbo.OOCR AS T3 ON T1.ProfitCode = T3.OcrCode LEFT OUTER JOIN
                                      CompanyB.dbo.OOCR AS T4 ON T1.OcrCode2 = T4.OcrCode LEFT OUTER JOIN
                                      CompanyB.dbo.[@A_GL_MAPPING] AS T5 ON T5.U_ComapnyB = T0.FatherNum
                         WHERE   (T0.GroupMask IN (8, 7, 6, 5, 4)) 
    					 AND (T1.Debit <> T1.Credit)
    					 AND T2.RefDate Between @BegDate AND @EndDate
                         GROUP BY T0.GroupMask, T0.FatherNum, T0.AcctName, T0.FormatCode, T3.OcrCode, T3.OcrName, T4.OcrCode, T4.OcrName, T2.RefDate, T0.Segment_1, T0.Segment_2, T0.Segment_3, T5.U_Master, T5.U_ST_Canada, T5.U_Master_Name)
    					 , 
    
    Comp AS  
    (SELECT   'Inguran' AS Company
        UNION ALL
     SELECT   
     'ST_Canada' AS Company)
     
     SELECT   Comp_1.Company, DataT_1.U_Master, DataT_1.U_Master_Name, DataT_1.U_Inguran, DataT_1.FatherNum, DataT_1.FormatCode, DataT_1.AcctName, DataT_1.Balance, DataT_1.OcrCode, DataT_1.OcrName, DataT_1.OcrCode AS Expr1, DataT_1.OcrName AS Expr2, DataT_1.RefDate, DataT_1.GroupMask, DataT_1.Segment_1, 
                    DataT_1.Segment_2, DataT_1.Segment_3
       FROM     Comp AS Comp_1 LEFT OUTER JOIN
                    DataT AS DataT_1 ON Comp_1.Company = DataT_1.Company
    	 
    END
    
    
    
    Add comment
    10|10000 characters needed characters exceeded