Crystal Reports XI - Crosstabs displaying null values

[jhooker]

Howdy,

I'm trying to create a crosstab type report that will display account activity (rows) by department (columns). I want to display the account and department even when there is no amount activity in a period.

Here's what I've tried.

I have the account and department values in two seperate tables, left outer joined to a report that has account and department values associated with a month's amount activity. I have the report checked to display null values and the crosstab checked and not to supress null values. None of this will display when a account/department shows no activity.

I've looked into isNull, but do not know (can't find) a good SQL statement that will help me out, and I don't really know which field to attach it to.

It seems like there is a solution to get these to display, based on the numerous threads in various forums that I've been looking at, but nothing has worked.

I started to do a manual crosstab, but got so close on the crosstab that I'm fixated on that solution at this point. If I can't make any progress with a formula or helpful tip, I guess I'll start over again trying to do a mock crosstab.

Long story short,

1. (Is there) what is the easiest way to get summarized fields to display a Null value when the column and row values are pulled from data tables?
1.b If there is a null formula to do so, what is the syntex (or can someone provide a link to such)?
2. If there isn't a way to do so in Crosstab, where can I find the best guide on how to draft a mock cross tab?

Thanks for your help!

-John

 

[pmax9999]

The problem is basically you are asking Crystal to report on data that doesn't exist.

The simplest (albeit inefficient) approach is probably to broaden the period enough to ensure that every department and activity has data and therefore gets included (perhaps 6 months or a year). Then, rather than doing a count or sum of a database field, create a formula that does a test on the period range and returns the field result if it is within the required period or a zero if it is outside the period.

Then use that formula as the basis of your sum/count in the crosstab.

Hope this helps

Cheers
Pete

 

[jhooker]

Hi Pete,

Yes, I can see how that would work. I've also considered creating a dummy ($0.00) value entry for every account/department combination as a default placement.

What I'm really looking for is a technical solution. The issues seems fairly mainstream, but I just can't find the methodology anywhere to do this.

I appreciate the consideration of your take on the solution.

Cheers,

-John

 

[IanWaterman]

A Mock/manual cross tab is the correct solution as if you group by your leading left joined table then you will still get null entries.

Unfortunately I think the Crystal Crosstabs helpfully suppress your null entries, I have not seen a way to stop this.

Ian

 

[jhooker]

Hi Ian,

I'm trying to avoid a manual cross tab at this point, until I try all I can at least.

I've seen some references to the IsNull formula and have worked up this - thinking that it should replace null values with zeros, but when I put it into my summary field, I still don't get the headers with the null values converted by this formula.

if IsNull ({Journals_by_Fund_2013_03.Monetary Amount}) then 0
else {Journals_by_Fund_2013_03.Monetary Amount}

If my row table is pulling all values for account, and my column table is pulling all departments, and I have this formula in for the summary of the amount field... I don't understand why this wouldn't work.

Cheers,

-John

 

[IanWaterman]

In a manual Crosstab it probably would work. But in Crystal Crosstabs I have never been able to stop it suppressing the null records.

You could try using a command instead of linked tables. You can then force the command query to return zeroes instead of nulls.

Paste your SQL query from crystal, I might be able to rewrite so you can use it as a command.

Ian

 

[jhooker]

Looking at this, I see that Crystal's default is an unenforced Inner Join. When I try to switch to a Left Outer Join on any of the element tables (department, account), I get an error message that the join expression is not supported. Is that maybe part of the issue?


SELECT `Agency_Use`.`Agy Use`, `Departments`.`Department`, `Account_Structure`.`Account`, `Program_Codes`.`Program Code`, `KS_GL_Journals_by_Fund_2013_03`.`Monetary Amount`
FROM (((`Departments` `Departments` INNER JOIN `KS GL Journals by Fund 2013 03` `KS_GL_Journals_by_Fund_2013_03`
ON `Departments`.`Department`=`KS_GL_Journals_by_Fund_2013_03`.`Dept`)
INNER JOIN `Account Structure` `Account_Structure` ON `KS_GL_Journals_by_Fund_2013_03`.`Account`=`Account_Structure`.`Account`)
INNER JOIN `Agency Use` `Agency_Use` ON `KS_GL_Journals_by_Fund_2013_03`.`Agy Use`=`Agency_Use`.`Agy Use`)
INNER JOIN `Program_Codes` `Program_Codes` ON `KS_GL_Journals_by_Fund_2013_03`.`Program`=`Program_Codes`.`Program Code`
WHERE `Agency_Use`.`Agy Use`='0000000000'
AND (`Departments`.`Department`='3651310000'
OR `Departments`.`Department`='3651316000'
OR `Departments`.`Department`='3651318000'
OR `Departments`.`Department`='3651319000'
OR `Departments`.`Department`='3651319100'
OR `Departments`.`Department`='3651410000'
OR `Departments`.`Department`='3651510000'
OR `Departments`.`Department`='3651515000'
OR `Departments`.`Department`='3651516000'
OR `Departments`.`Department`='3651710000')
AND (`Account_Structure`.`Account`='463100'
OR `Account_Structure`.`Account`='463200'
OR `Account_Structure`.`Account`='463240'
OR `Account_Structure`.`Account`='463250'
OR `Account_Structure`.`Account`='463300'
OR `Account_Structure`.`Account`='463400'
OR `Account_Structure`.`Account`='463600')

 

[IanWaterman]

Yes that would be the case.

AND (`Account_Structure`.`Account`='463100'
OR `Account_Structure`.`Account`='463200'
OR `Account_Structure`.`Account`='463240'
OR `Account_Structure`.`Account`='463250'
OR `Account_Structure`.`Account`='463300'
OR `Account_Structure`.`Account`='463400'
OR `Account_Structure`.`Account`='463600')

Is over riding your Left Outer join

Impossible for me to test, but using this might work as a command

select q.8, d.Department
from Departments D
left outer join
(SELECT `Agency_Use`.`Agy Use`, `KS_GL_Journals_by_Fund_2013_03`.`Dept`, `Account_Structure`.`Account`, `Program_Codes`.`Program Code`, `KS_GL_Journals_by_Fund_2013_03`.`Monetary Amount`
FROM `KS GL Journals by Fund 2013 03` `KS_GL_Journals_by_Fund_2013_03`
INNER JOIN `Account Structure` `Account_Structure` ON `KS_GL_Journals_by_Fund_2013_03`.`Account`=`Account_Structure`.`Account`)
INNER JOIN `Agency Use` `Agency_Use` ON `KS_GL_Journals_by_Fund_2013_03`.`Agy Use`=`Agency_Use`.`Agy Use`)
INNER JOIN `Program_Codes` `Program_Codes` ON `KS_GL_Journals_by_Fund_2013_03`.`Program`=`Program_Codes`.`Program Code`
WHERE `Agency_Use`.`Agy Use`='0000000000'
AND (`KS_GL_Journals_by_Fund_2013_03`.`Dept`='3651310000'
OR `KS_GL_Journals_by_Fund_2013_03`='3651316000'
OR `KS_GL_Journals_by_Fund_2013_03`='3651318000'
OR `KS_GL_Journals_by_Fund_2013_03`='3651319000'
OR `KS_GL_Journals_by_Fund_2013_03`='3651319100'
OR `KS_GL_Journals_by_Fund_2013_03`='3651410000'
OR `KS_GL_Journals_by_Fund_2013_03`='3651510000'
OR `KS_GL_Journals_by_Fund_2013_03`='3651515000'
OR `KS_GL_Journals_by_Fund_2013_03`='3651516000'
OR `KS_GL_Journals_by_Fund_2013_03`='3651710000')
AND (`Account_Structure`.`Account`='463100'
OR `Account_Structure`.`Account`='463200'
OR `Account_Structure`.`Account`='463240'
OR `Account_Structure`.`Account`='463250'
OR `Account_Structure`.`Account`='463300'
OR `Account_Structure`.`Account`='463400'
OR `Account_Structure`.`Account`='463600')) Q
on d.Department = Q.Dept
where d.Department in ('3651310000'
, '3651316000'
, '3651318000'
, '3651319000'
, '3651319100'
, '3651410000'
, '3651510000'
, '3651515000'
, '3651516000'
, '3651710000')

 

[jhooker]

Ian,

There was a syntex error in the SQL code/query but I got that fixed readily. I tested it against the database directly directly to make sure it worked. The query works in Access but it is prompting for a parameter. It doesn’t matter what I put in for the parameter - it works fine - but the parameter makes the SQL not work in Crystal, so that’s what I’m trying to work around.

-John

 

[lbass]

Please see thread149-1567016.

-LB