running total question using crystal 10.0 1

[espltd]

Hi, I have the following data and would like to set up a count as follows. The data in the details section of the report is the first 3 columns and I would like to add the fourth column called count.

user start time end time count

A 10:00 11:00 1
A 10:00 11:00 2
A 10:00 11:30 1 (reset count)
B 1:00 2:00 1 (reset count)
B 1:00 2:00 2


so it counts when there is the same user and start and stop times.

I also have a crosstab set up and would like to display

A 2
B 2
so it only displays the user and the max count. Thanks.

 

[lbass]

Don't use running totals. Instead, insert groups on user, start time, and end time. Then insert a count on user at the endtime group level.

For the crosstab, create a SQL expression {%cnt} like this:

(
select count(`User`)
from Table A
where A.`User` = Table.`User` and
A.`StartTime` = Table.`StartTime` and
A.`EndTime` = Table.`EndTime`
)

Then in the crosstab choose max of {%cnt} as your summary field, and use user as the row.

-LB

 

[espltd]

thanks for the quick reponse. I am just not sure where to create the SQL expression {%cnt}, would this just be as a new formula or would it be done from the cross tab expert when creating a new crosstab?

 

[espltd]

looks like the sql expression is built in the crosstab as you stated. I also need to count things a bit differently than what I previously stated. I need to count unique time segments for each user so for example for A I would need a 2 and for B I would need a 1. Also there are many more users than just A and B of which can change.
Thanks again.

 

[espltd]

I have something working for counting the unique time segments, just ended up using a running total distinct count on end time and reseting with new user. So I think if I just use max of this count in the crosstab it might work!

 

[lbass]

You can't insert maximums on running totals, which was why I suggested a SQL expression--because you can. To create a SQL expression, you go to the field explorer-> SQL expression->new and enter it there. The punctuation/syntax depends upon your datasource.

I am not clear on how you now want to count things. Note also that counting a unique endtime per user wouldn't necessarily result in the correct count. Please clarify what you want to do and identify your database.

-LB

 

[espltd]

thanks for the additional feedback. Yes I am trying to count the number of unique time segments for each user so for example

User start time end time
Tom 10:30 11:00
Tom 10:30 11:00
Tom 10:45 11:00
Tom 10:30 11:00
Paul 9:30 10:00
____________________________________
so my crosstab would look like

Tom 2
Paul 1

At first I thought just a distinct count on the end time would not always count correctly but I tested it for a large dataset and it seems to be working, but I could be missing something. I have the groups set up as suggested, username, starttime, endtime. I then have the distinct count set up as a running total, field to summarize as end time, evaluate for each record and reset for group username.
I placed the count in the end time group header section. I thought this might not work for different start and same end times for the same user but it seems to increment correctly
distinct count
1
user Tom 10:30 11:00
user Tom 10:30 11:00

user Tom 10:45 11:00 2

then in the cross tab I have username for Row and distinct count for the summary field.

 

[lbass]

I can't tell what you are actually doing. When you say you want to count unique time segments, does that mean either the beginning or the end time could be different? If so, you could create a formula like this:

totext({table.starttime},"hh:mm")+" "+{totext({table.endtime},"hh:mm")

Then you could insert a distinctcount on this at whatever group level you want and also use it in your crosstab.

-LB

 

[espltd]

The unique time segments just means that the start or end times differ (both start and end times do not match other start and end times for the same user). Since it is grouping on different start times and I am counting the different end times I think it may be capturing the unique time segments correctly. I will also add the formula you have provided to help in verifying the data.
Thanks.

 

[lbass]

Inserted counts will work if you have the groups I suggested. The problem was when you said you wanted the maximum of a set of counts in your crosstab. Is that no longer the case?

-LB

 

[espltd]

yes I am not quite sure why but the cross tab value is just displaying the max value which is what. I have some additional parameters on the crosstab as well. I have something called an action type(fast or slow) as a column in the cross tab as well as a sum of hours formula in the summary field in addition to the #count-(distinct count for end times). So my output in the crosstab looks like

Fast Slow

Tom 2 (# of unique time seg for Tom)
2.0 (from the totalhours formula)
Paul 1 (# of unique time seg for Paul)
2.0 (from totalhours formula)

Anyhow the crosstab is only displaying the last distinct count value for that user which is the maximum. I also have the data sorted by start time, end time, username.

 

[lbass]

So you feel this is working okay for you?

-LB

 

[espltd]

I still have one problem, in some cases the user can be null, but there are still hour entries. In this case the report does not seem to work correctly but I think I might try to insert a string like "unkown" for the user if null in the query. Thanks.

 

[espltd]

Actually still have a few problems with the logic. The count that counts the distinct time segments can fail like you thought, for example for 15:15-1800 followed by 17:00-18:00 in the same username group, the count does not increment since it is a distinct count for end times. Also if there time segments in different categories for the same user, fast and slow they times are not handled correctly. The data sample I was using happended to make everything work. I may try to handle some of the logic in the program before I write it out to the report. Thanks.

 

[espltd]

I tried the totext({table.starttime},"hh:mm")+" "+{totext({table.endtime},"hh:mm")formula that you mentioned above and am using it in a summary. Is there a way to reset the summary value to 0 based on a group (user in this case)? Also is there a way to have this count seperate for slow and fast categories, one formula for each?
Thanks.

 

[dixie617]

Maybe you could do it another way. I have a similar report I just completed with the same problem. I created a formula to place a count if the previous record does not equal the current record.

somthing like this:

if Previous({starttime}) <> ({starttime})
and if Previous({endtime}) <> ({endtime}) then
+1

 

[espltd]

thanks for the response. I ended up creating a formula, custom running total simular to what you have described that looks like it is going to work. I re-initialize the variable in the group header and display it in the group footer. I have seperate categories that I need to count for so have seperate running totals. For the slow category I have
if onfirstrecord and {table.category}="Slow" or previous {table.starttime}<>{table.starttime}and{Table.category}="Slow" or previous ({table.endtime})<>{table.endtime} and
{table.category}="Slow"
Numbervar X := X+1;
I think it is pretty close.

 

[lbass]

You should be able to take this formula:

totext({table.starttime},"hh:mm")+" "+{totext({table.endtime},"hh:mm")

...and use it as a summary field in your crosstab, using a distinctcount. Add the user as a row and your fast/slow field as the column field.

-LB

 

[lbass]

PS. I meant for you to use this formula directly--without incorporating it into a running total.

-LB

 

[espltd]

I ran into another problem with the custom running total, a data condition that causes the count to be incorrect! Since with the running total I can only look at the current and previous row which may not be sufficient for all data cases. So using the formula directly I think you mean just place it in the details section and then perform a distinct count, will give it a try. thanks!