Cross Tab Help please...Blank Row

[AnnetteB1305]

Hello All,
Please help as I am pulling my hair out.
I have a report that has 4 sub reports in it and one of these sub reports is for third party calls. These are support calls for customers that are raised by a 3rd party. The calls are monitored in a cross tab report by logdate and by thirdparty. The original issue was that the customer wanted to show all of the previous 8 days regardless of whether there had been 3rd party calls in the day or not. I then removed the selection criteria only selecting 3rd party info on the report and instead creating a formula for the 3rd party and then having this formula as my row label. This solved the issue however is now showing a blank row for all those calls that are not covered in my thirdparty formula. I then thought I could create a command to have calendar dates and then link this to my logdate from the database table and use this as my column header and select dates from this table based on the last 8 days. However I can't get that to work as the logdate in the database is not a date field and therefore can't link.
So I don't know where to go from here. I've tried a manual crosstab but can't get this to work.

I've tried various suppression formulae and suppressing empty rows to no avail.

The formula for thirdparty is-
If {opencall1.suppgroup}='_THIRDPARTY' then {opencall1.owner} else
If {updatedb1.groupid}='_THIRDPARTY' then {updatedb1.aid}

Any help would result in my sanity being restored and would be gratefully received

If you need any more info please let me know

Thanks pleadingly

Annette

 

[lbass]

Please explain exactly how the crosstab is set up in the crosstab expert--rows, columns, summaries and show the content of any formulas.

Also clarify whether you have added your above formula as a row field.

-LB

 

[AnnetteB1305]

Of course apologies. Ok The crosstab is set up with @logdate as a column heading (formula @logdate datetime({opencall1.logdate})) and the above @Thirdparty formula as the row header and the distinct count of the @callref formula (If {opencall1.suppgroup}='_THIRDPARTY' or{updatedb1.groupid}='_THIRDPARTY'then {opencall1.callref} else 0) as the summarised field

Hope this is enough info

Thanks,

Annette

 

[lbass]

Change your summary formula to:

If {opencall1.suppgroup}='_THIRDPARTY' or
{updatedb1.groupid}='_THIRDPARTY' then
{opencall1.callref} else
tonumber({@null})

...where {@null} is a new formula that you open and save without entering anything. Be sure to have checked "suppress empty rows and suppress empty columns" in the customize style tab.

Note that a distinctcount of a conditional formula will count 1 for the defaultvalue--when records don't meet the conditional criteria. Using {@null} eliminates counting the default value.

-LB

 

[AnnetteB1305]

Hello,

I did this but this removed the dates with no calls which was the original problem, however after unchecking suppress empty columns it gave me precisely what I needed. So I would like to thank you, this has made my day. Thank you so much. Very grateful
Annette