Cross-Tab Help! 1

[southbean]

Hello All,

I’m trying to create a report that replicates a spreadsheet. It’s fairly simple, but I’m having difficulty with one aspect. In searching this forum I think my answer may be found in something called a manual Cross-Tab. However, not having much experience with Cross-Tab reports I’m lost.

I have a number of batch jobs that run overnight. Each job is scheduled to execute at a certain time (with a 20 minute grace period). This is a monthly report so the report lists the job names in the rows and each business day in the column. I have this part working well enough.

The problem I’m having is this:

If a job is scheduled to run at 11:00pm (23:.00) and it does not execute until 11:30pm (23:30) then it is late. I need to count how many times a job ran late during the previous month.

I’m using the following formula (@LateJob) to count the jobs that are late:

 If {Table.ActualTime} > {Table.SchedTime}+1200 Then 1 Else 0

When I try to add this formula as a column in the Cross-Tab Expert, it fails and populates another Day date field.

What I want is a column for each day with “ActualTime” and @LateJob fields.

Any advice/assistance with this would be greatly appreciated!

- Tom

 

[WarrenRoss]

Don't add the {@LateJob} formula to the columns in the cross-tab expert, but to the summarised fields section. You will also need to add the ActualTime field to the summarised field section.

Then you'll need to place the "job names" field in rows, and whichever date field you have in the columns. Thereafter you'll need to click on the date field inside the columns section, and click on the "group options" button. Make sure that your date field is printing "for each day" (it's the default, so this should already be correct).

Hope that helps, unless I'm missing something.

 

[southbean]

WarrenRoss,

Thanks for your reply!

However, when I put the {@LateJob} in the summarised field section (along with the “ActualTime” date field) it returns "1" for all days. Less than a quarter of the jobs run late.

Am I doing something wrong?

Thanks again for your reply!

- Tom

 

[lbass]

Are you using a sum as your summary? You should be. A count will just count the instances of the formula, which will always be 1.

-LB

 

[southbean]

Hi LB,

Thanks for your post.

I've tried both Sum & Count. As you rightly point out, Count returns 1 for every day.

Sum returns 0 for every day.

I don't know if it matters (I don't think so) but the date (for the column) is also a formula because the field is formatted like this: 20050502 for 05/02/2005.

 //@Date

cdate(mid({Table.Date},1,4) & "/" & mid({Table.Date},5,2) & "/" & right({Table.Date},2))

Thanks again for your reply!

- Tom

 

[lbass]

I would try putting both the crosstab date formula and your conditional formula in the detail section along with your regular date field and time fields and troubleshoot it that way.

-LB

 

[southbean]

Hi LB,

Exactly right, LB!

When I put the formula in the detail section I got the same result. Not Good! So, I tweaked the formula a little and ended up with this:

 If CTime({Table.ActualTime}) > CTime({Table.SchedTime}+1200) Then 1 Else 0

That did the trick.

Thanks LB!

And thanks to you WarrenRoss as well!

- Tom