cross tab missing values 1

[swiss2007]

Hi,
CRXI R2,Oracle 9i,
I have a cross tab that`s going to be run for a time period of any 6 months during a year.If there is no data for any months, I want to show that as "The "xxx","xxx" Months have no data.
For ex,If I am running from jan till jun and there is no data for mar,apr,jun, I want to show

"The "mar","apr","jun" Months have no data.
Is this possible.
Thanks.

 

[Madawc]

Are you able to get tables / datasets added to the database? If you got a table of months set up, this could be linked to the activity using LEFT OUTER and you'd still get a slot when there was no activity. (But note that LEFT OUTER does not work if you also do a selection on the table receiving the LEFT OUTER link.)

If you can't add a table, you'll be stuck with creating a 'Mock Crosstab'. Crosstabs are fine if you don't mind the absence of rows or columns with no data, but you said you wanted empty slots to show.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back.

Each running total will count the record if it was within the criteria - in your case, month.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You could just make a statement that "Months that do not appear in the crosstab have no data" that would be obvious in relation to a report title that designated the date range used.

Otherwise, you could make a separate formula per month, e.g.,:

//{@Feb}:
if month({table.date}) = 2 then {table.amt}

Then create a formula for the report header (or footer):

stringvar x;
if maximum({@Jan}) = 0 then
x := x + "Jan, ";
if maximum({@Feb}) = 0 then
x := x + "Feb, "; //etc. for all months
if len(x) > 2 then
"The following months contain no data: "+
left(x, len(x)-2)

-LB

 

[Craig350]

Ibass, straightforward answer but one question - {table.amt} would refer to what - probably dumb question, but I am missing it.

Thanks

 

[lbass]

I just meant for that to stand for whatever you are summarizing. If you are counting something instead of summing some amount field ({table.amt}) then change the formula to:

//{@Feb}:
if month({table.date}) = 2 then 1

-LB

 

[Craig350]

Ibass - again, great insight and what I thought but I should have more clearly stated the question - if my cross tab columns are currently determined by something akin to {table.date}, my rows are by employee but each employee has multiple summarized fields, should I just choose one to represent the {table.amt} while selecting / using a formula akin to your "stringvar x" as the "use a formula as a group sort order" under cross tab group options?

Thanks again for the huge help.

C

 

[lbass]

If you have multiple summaries, then you might want to change the approach a bit. Assuming that only dates that contribute to the crosstab summaries are allowed into the report, then try a formula like this instead (place this in the suppressed detail section):

whileprintingrecords;
stringvar x;
if instr(x,monthname(month({table.date}),true)) = 0 then
x := x + monthname(month({table.date}),true)+", ";

Then in the report footer use:
whileprintingrecords;
stringvar x;
stringvar array y := ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"];
numbervar i;
stringvar z;

for i := 1 to 12 do (
if y in x then
z := z else
z := z + y+", "
);
if len(z) > 2 then
"The following months contain no data: "+
left(z, len(z)-2)

-LB

 

[Craig350]

LB,

I follow the logic and even traced pieces of it to my Crystal XI book where I wasn't clear, but both formulas when placed in the section you instruct under "Suppress (No Drill Down)" result in the following error message that prevents the formulas from being accepted and any results from being calculated - last question - promise!

"The formula result must be a boolean"

Thanks!

 

[lbass]

These formulas should be created in the field explorer->formula->new and then placed on the report in the report sections I mentioned (NOT in the section expert).

It actually doesn't matter whether the detail section is suppressed or not (I had been thinking you were only displaying the inserted crosstab when I referenced the suppressed detail section--but it is irrelevant).

-LB

 

[Craig350]

Ibass - oh so close - footnote worked fine though I had to make the last part of the formula -1 vs -2 (least of my concerns). Followed the corrections and it is still not forcing my cross-tab to show all months - especially those with 0 values which was the original goal. The detail section when not supressed does show simply the months that have data - for example, since there is data in Jan & Feb but not Mar, the first detail line shows "Jan," the 2nd detail line shows Jan,Feb, but the 3rd detail line shows nothing - not sure if it should or not but I'm guessing this relates to the cross tab's columns still being based on what they have been all along - Loan_Master_FILEDATE. Nothing I have done is forcing it to show the empty months. Any final thoughts? Can't thank you enough.

 

[lbass]

This solution was not intended to force all months to show, but instead to provide a note identifying those months with no activity.

If you don't have all the dates in the data, then you can either add a table containing all dates with a left join from that table to the other table (and no selection on the right hand table), and then use the new table for your column fields, or you would have to create a manual crosstab.

-LB

 

[Craig350]

lbass, thanks again for all of the ideas - even had a table created just with dates and did a left join and used that table as the corss tab column basis and it still didn't work (still only getting columns with data in them). Looks like we're off to manual corss tabs, but is there anything special that comes to mind about a "date table" that wouldn't make it work?

Can't thank you enough,

C

 

[lbass]

If you make any selection on the right hand table, it will essentially undo the left outer join and you will use the columns. You could however handle this in a command where you add the selection criteria on the right hand table in the From clause instead of in the Where clause. Is using a command as your datasource an option?

-LB