CrossTab Report/Showing Mths with No Incidents

[Kdunn]

Hi - I have a question about CrossTab Reports. I am using Crystal 9. My report is pulling all Incidents with a SEV. 1 or 2, by VP. It has been working great, until this month, where there were no Incidents with these Sev. levels. The reports shows nothing for the Month of Oct. My rows include VP_Name, Nubmerpgrn, SEV_LVL_NUMBER, and CAUSED_INCIDENT_NUMBER. My columns include @mthname, and I do have a Sumarized Field. It looks something like this: Jul Aug Sept YTD
2 1 1 4
VP Name Nubmerpgrn# Sev. Level Caused Incident#


Since there were no Incidents for OCT, I can not get the month to show 0.

Any help would be appreciated.

 

[synapsevampire]

That's true, if you don't have data, you can't report on it.

There are different approaches to this, but one that might resolve would be to add in the table containing the date again (this will prompt to use an alias), and left outer join your current data set to it, adding a record selection criteria to the newly added table which simply selects the {table.date} = maximum({table.date}), and turn on the Database->Select distinct records.

There may be potential problems as a result of the current record selection because you need to make sure that whatever criteria you're using allows for nulls now as well.

If you're using your own SQL, or want to by building off of the existing SQL, select Database->Show SQL Query, then modify it accordingly and use the Add Command to supply the datasource.

I resolve this long term by taking a data warehouse approach wherein you set up a Periods table and LO join the data to it.

Here's my FAQ:

faq767-4532

-k

 

[Kdunn]

So, there is no simple way to just have the month (that has no data) appear with a 0 in a Crosstab?

 

[lbass]

If you don't take the periods approach, I think the only way you'll get a 0 for periods with no records is by using a manual crosstab where you use detail level formulas like:

//{@Sept}:
if month({table.date}) = 9 {then table.amt} else 0

You would create a separate formula for each month. Then you would insert a summary (sum) on these and suppress the details section.

-LB

 

[Kdunn]

I inherited this report, I did not actually write it, so I have gotten more information. I can not get any of the below options to work. I am assuming it is because there is something in the fields for Oct and it is not null, because the field holds 'f' or 't', based on a check box in the database.

The Caused an Incident field in the database holds either a 'f' or 't'. The formula that the report is using to get the number is: if {cm3rm1.CAUSED_INCIDENT} = "t" then 1 else 0

So when I put in my parameter field of 01/01/04 - 10/31/04, it is pulling nothing for Oct.

I hope this better explains.

 

[lbass]

You could still create a manual crosstab that would give you a zero value for October as I suggested in my earlier post.

-LB

 

[Kdunn]

I created a manual crosstab, and created a seprate formula for each month. Inserted the summary on these, and still no column for Oct is showing. Is there something else I can look for that I might be doing wrong?

 

[lbass]

You should have a formula for October that looks like:

if month({table.date}) = 10 {then table.amt} else 0

Then you insert a summary (sum) on this and suppress the details section. Please show the formula you used.

As long as there are incidents in SOME month for the particular row, you will get a result for October. If the group has no incidents in ANY month, then you would not get an October result.

-LB

 

[Kdunn]

Is the summary(sum) I am inserting the summarized fields in the Crosstab Expert? Here is the forumla I am using for Oct:

if month ({@Actual Outage End DateAdjATL}) = 10 then{@Caused An Incident} else 0

These are the formulas used in the above formula for Oct:

@Actual Outage End DateADJATL -
if time ({cm3rm1.ACTUAL_OUTAGE_END})<TimeValue({@Actual Outage End DayLite},00,00)then
DateTimeValue ({@Actual Outage End Date}-1,{@Actual Outage End Time})else
DateTimeValue ({@Actual Outage End Date},{@Actual Outage End Time})

AND

@Caused An Incident -
if {cm3rm1.CAUSED_INCIDENT} = "t" then 1 else 0

-kdunn

 

[lbass]

A manual crosstab means creating a crosstab without using the crosstab expert. If you place {@Actual Outage End DateADJATL} on the report, is it ever null? You have to address all potential nulls. You might want to try changing {@Actual Outage End DateADJATL} to:

if is null({cm3rm1.ACTUAL_OUTAGE_END}) then 0 else
if time ({cm3rm1.ACTUAL_OUTAGE_END})<TimeValue({@Actual Outage End DayLite},00,00)then
DateTimeValue ({@Actual Outage End Date}-1,{@Actual Outage End Time})else
DateTimeValue ({@Actual Outage End Date},{@Actual Outage End Time})

This is just a guess, as I don't know the contents of your other formulas within the formulas, or whether they can be null. You might also change {@Oct} to:

if isnull({cm3rm1.CAUSED_INCIDENT}) then 0 else
if month ({@Actual Outage End DateAdjATL}) = 10 then{@Caused An Incident} else 0

-LB

 

[Kdunn]

{@Actual Outage End DateADJATL} is never null. This is a date field that will always have a date in it. I think I understand what you are talking about. However, I don't think I have ever seen a manual Crosstab report, or would even know how to build one. I am going to try your last suggestion now. You don't know how much your help is appreciated.


-kdunn