Crosstab to show dates even though no data for that date

[AnnetteB1305]

Hello,

I have a report to amend that has a crosstab and a graph. I did have to do the crosstab with an additional manual crosstab addition also.
My graph is On change of @logdate then show values of sum of @callcount, sum of @callsexclthirdparty, sum of @closedcalls and @avgcalls

The formulas are as follows:-
@logdate=If date({tablename.logdate}) >dateadd("d",-8,CurrentDate)then date({tablename.logdate}) else Date({@null})

@callcount is If date({tablename.logdate}) >=dateadd("d",-8,CurrentDate) then 1 else tonumber({@null})

@callsexclthirdparty is If {tablename.suppgroup}<>'_THIRDPARTY' and date({tablename.logdate}) >= dateadd("d",-8,CurrentDate) then 1 else tonumber({@null})

@closedcalls is If {@Closedate}={@logdate} and date({tablename.logdate}) >=dateadd("d",-8,CurrentDate) then 1 else tonumber({@null})

@avgcalls is If (date({tablename.logdate}) in dateserial(year(currentdate),month(currentdate)-1,day(Currentdate)-8) to
(dateadd("d",-8,CurrentDate))) then Sum ({@Day1MthCallCount})/31 else
If (date({tablename.logdate}) in dateserial(year(currentdate),month(currentdate)-1,day(Currentdate)-7) to
(dateadd("d",-7,CurrentDate))) then Sum ({@Day2MthCallCount})/31 else
If (date({tablename.logdate}) in dateserial(year(currentdate),month(currentdate)-1,day(Currentdate)-6) to
(dateadd("d",-6,CurrentDate))) then Sum ({@Day3MthCallCount})/31 else
If (date({tablename.logdate}) in dateserial(year(currentdate),month(currentdate)-1,day(Currentdate)-5) to
(dateadd("d",-5,CurrentDate))) then Sum ({@Day4MthCallCount})/31 else
If (date({tablename.logdate}) in dateserial(year(currentdate),month(currentdate)-1,day(Currentdate)-4) to
(dateadd("d",-4,CurrentDate))) then Sum ({@Day5MthCallCount})/31 else
If (date({tablename.logdate}) in dateserial(year(currentdate),month(currentdate)-1,day(Currentdate)-3) to
(dateadd("d",-3,CurrentDate))) then Sum ({@Day6MthCallCount})/31 else
if(date({tablename.logdate}) in dateserial(year(currentdate),month(currentdate)-1,day(Currentdate)-2) to
(dateadd("d",-2,CurrentDate))) then Sum ({@Day7MthCallCount})/31 else
If (date({tablename.logdate}) in dateserial(year(currentdate),month(currentdate)-1,day(Currentdate)-1) to
(dateadd("d",-1,CurrentDate))) then Sum ({@Day8MthCallCount})/31 else 0

My problem is the customer now wants to do this report for other customers who don't make calls at the weekend but they still want to show the dates and the report is not working to this requirement. i.e if there are no calls on the date then it doesn't show anything the date in the graph or the crosstab.

Can anyone help with this one, I had a similar problem with another section of the report but I just was able to amend the selection to show all data and then amend the formula to display however this isn't the solution for this part as need to have a months worth of data in the selection because the months average is required.

Thanks in advance

Annette

 

[MCuthill]

Also Posted Here: thread767-1661192

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Yes, please do not crosspost in multiple forums.

-LB

 

[AnnetteB1305]

I wasn't sure where to post

 

[lbass]

Someone will let you know if you are in the wrong forum.

-LB

 

[AnnetteB1305]

I have resolved this and it won't make sense to anyone if I post how I did it as it is data specific so this can be closed