Crystal Cross Tab, column with Zero values dropped

[former_member268870]

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:

[jimmyl]

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

[former_member268870]

Jimmy,

Thanks for the advice. How would I add that line in a SQL Union ALL query?

Your advice is much appreciated.

Marli

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]

[jimmyl]

Hi Marli,

Try to amend the following to your sql view query.

-- your code...

UNION ALL
'Inguran' AS Company, 
'',--T5.[U_Master], 
'',--T5.[U_Master_Name], 
'',--T5.[U_ST_Canada], 
'',--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, 
'',--T2.RefDate, 
'',--T0.GroupMask, 
'',--T0.Segment_1, 
'',--T0.Segment_2, 
'' --T0.Segment_3
UNION ALL
'ST_Canada' AS Company, 
'',--T5.[U_Master], 
'',--T5.[U_Master_Name], 
'',--T5.[U_ST_Canada], 
'',--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, 
'',--T2.RefDate, 
'',--T0.GroupMask, 
'',--T0.Segment_1, 
'',--T0.Segment_2, 
'' --T0.Segment_3

[former_member268870]

Jimmy,

I really appreciate your help.

I get an error in SQL about Incorrect Syntax on the first 'Inguran' and then the 'ST_Canada' on your code, but solved this with just adding a Select before those 2.

However, when I do the Cross Tab in Crystal I get blank results.

Here are some details:

• Date Range Parameter:

{yIncomeStatementServiceV3.RefDate} = {?DateRange}

• Formula for only pulling Revenue:

IF {yIncomeStatementServiceV3.GroupMask} = 4 THEN {_A_GL_MAPPING.U_Master_Name} ELSE " "

• SQL View:

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]


UNION ALL


SELECT 'Inguran' AS Company, 
'Sorting',--T5.[U_Master], 
'Sorting',--T5.[U_Master_Name], 
'Sorting',--T5.[U_Inguran], 
'Sorting',--T0.FatherNum, 
'',--T0.FormatCode, 
'',--T0.AcctName, 
0, --SUM(T1.SYSCred - T1.SYSDeb) AS Balance, 
'SRVC',--T3.OcrCode, 
'',--T3.OcrName, 
'',--T4.OcrCode AS Expr1, 
'',--T4.OcrName AS Expr2, 
'01/02/2018 00:00:00',--T2.RefDate, 
'4',--T0.GroupMask, 
'',--T0.Segment_1, 
'',--T0.Segment_2, 
'' --T0.Segment_3
UNION ALL
SELECT 'ST_Canada' 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, 
'01/02/2018 00:00:00',--T2.RefDate, 
'4',--T0.GroupMask, 
'',--T0.Segment_1, 
'',--T0.Segment_2, 
'' --T0.Segment_3

• Crystal Cross Tab screenshots:

[jimmyl]

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

[abhilash_kumar]

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

[former_member268870]

Abhilash,

How would I add that lookup table on a Union All SQL View?

In this example Company 'ST Canada' is not showing any results for Revenues.

I apprecieate your assistance,

Marli

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]

[abhilash_kumar]

Hi Marli,

Something like this should work:

WITH Comp AS
(
Select 'Inguran' As Company

UNION

'ST_Canada' AS Company
),

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
Comp.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, 
DataT.RefDate, 
DataT.GroupMask, 
DataT.Segment_1, 
DataT.Segment_2, 
DataT.Segment_3
FROM 
Comp 
LEFT JOIN DataT ON Comp.Company = DataT.Company

-Abhilash

[former_member268870]

Abhilash,

I appreciate your input, thank you!

When I Excecuted that query if gave an error that I solved with adding a 'Select' before the lookup portion for ST_Canada. When I use this View in Crystal it still does not show the ST_Canada portion. But it does show the Inguran Data.

WITH Comp AS
(
Select 'Inguran' As Company


UNION


Select 'ST_Canada' AS Company
),


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
Comp.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, 
DataT.RefDate, 
DataT.GroupMask, 
DataT.Segment_1, 
DataT.Segment_2, 
DataT.Segment_3
FROM 
Comp 
LEFT JOIN DataT ON Comp.Company = DataT.Company

• Here are the Formula I have in Crystal to show only Revenues:

IF {yIncomeStatementServiceV2.GroupMask} = 4 THEN {_A_GL_MAPPING.U_Master_Name} ELSE " "

• I also have a Date Range Parameter setup:

{yIncomeStatementServiceV2.RefDate} = {?DateRange}

[abhilash_kumar]

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

[former_member268870]

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

[jimmyl]

Hi Marli,

Good to know it works for you.

Cheers,

Jimmy