Problem with grouping

[okuser]

I have a report that I am trying to create that will show the following information:

Mon Tues Wed Thurs Fri Sat Sun
3/22 3/23 3/24 3/25 3/26 3/27 3/28
8 4 8 8 3
4 sick 5 Vac


I am trying to pull the hours worked by transaction id in a day. I am grouping by Division, Lastnameid, clockindate, then transactionid. My problem is that the hours returned are showing on a different line for a each day as follows

Mon Tues Wed Thurs Fri Sat Sun
3/22 3/23 3/24 3/25 3/26 3/27 3/28
8
4
4 sick
8
8
3
5 vac

how do I suppress these blank areas? I tried grouping differently but can not get the second set of hours to show up. I do have a formula for the hours stating that if it matches that day, show the hours. Just need the report to read straight across like the top example.

Thanks
Gayla

 

[synapsevampire]

Try posting technical information, such as:

Crystal version
Database/connectivity used
Example data
Expected output

The output your experiencing sounds like you're placing everything in the detail band, which probably won't work depending upon the layout of the database. Of course without this information, it's difficult to assist, but I'll try.

Also you didn't state whether this is for the current week, last week, for any period of days, etc.

You can create 21 Running Total Formulas for a week, the first of which uses the Evaluate->Use a Formula criteria to identify it's type of hours, as in:

Regular hours
dayofweek= 1 and
time.type = "R"

Vacation hours
dayofweek= 1 and
time.type = "H"

Sick hours
dayofweek= 1 and
time.type = "C"

Now you can place these fields into their own sections in the lastnameid group footer (btw, a lastnameid may not be supplying uniqueness if 2 employees are named Smith).

You might also use a cross-tab to supply most of this data with very little effort, but the format would be different.

-k

 

[lbass]

I think you should try the crosstab idea, using {table.clockindate} as the column field and {table.transactionID} as the row, with hours as the summary field. Then place this in the Group 2 header (LastNameID). If you don't want the grid displayed, you can go to the customize tab within the crosstab expert->format grid lines-> and uncheck "Show grid lines." You can then suppress the detail section and Group Footer section, so that the crosstabs repeat consecutively per person within the division.

-LB

 

[okuser]

Ok, I have tried to do the crosstab and I like the way it is set up however, synapsevampire is correct, I will have to do some manipulation on it. I am a new user and am not real familiar with what I am trying to do. I will try to summarize this a little better.

Client needs a report that will show how many hours worked per day by transaction code. The software manufacter has it to where I can manipulate it but it is a vertical report not a horizontal report and hard to read. Not the way they are used to seeing there old reports. The payperiods flucuate, do not always start on the same day, number of days depends on weekends, etc. Not true semimonthly. This is what I am trying to accomplish, how I would like to see the end result.


Burkes, Larry Mon Tues Wed Thurs Fri Sat Sun
3/22 3/23 3/24 3/25 3/26 3/27 3/28
8 4 8 8 3
4 sick 5 vac


I have not set any criteria for the current week, last week or period of days. Not sure exactly how to do this. I am working on it. Was trying to get the other information pulled in first.

I think Monday-Saturday schedule as I indicated above would be easier to read, but their old report just gave all the days across in continuation. I would like to see the next "section" to be the next week. I do not know how to do this.

I also do not know what you mean by Evaluate-> Use a Formula criteria. I am using CR 8.5 but do not know what this criteria means or where to do it at.

As you can see, I really need some help. I did try the crosstab and set it up in the group section transid but still have the 2nd transaction type not showing up. The description will show up but the times will not.

I do not need to do running totals or any totals just hours per day per transaction.

Hope you can help.

Gayla

 

[lbass]

The crosstab should be placed in the LastNameID group header, not the transactionID header. I had suggested transactionID as a row WITHIN the crosstab. I was assuming that the transactionID distinguished regular, sick, and vacation time--is this the case? If not, then the row field within the crosstab should be whatever field does make this distinction, e.g., {table.timetype}. Then the crosstab would look something like:

Burkes, Larry Mon Tues Wed Thurs Fri Sat Sun
3/22 3/23 3/24 3/25 3/26 3/27 3/28
Regular 8 4 8 8 3
Sick 4
Vacation 5

In terms of the dates, what is the range you want in the report? If there were two weeks of data in the report, would you want the one week with all employees and their data followed by another week with all employees and their data? Or would you want one week followed by the next for each employee and then on to the next employee?

-LB

 

[okuser]

Sorry I incorrectly stated what I did. I did set the crosstab up in the lastnameid group and created the crosstab with the row to be transid and the column to be clockindate. However the manipulating that I will have to do is that I can not really use the clockindate as this would create the report different for people that did not have time for that day. I will have to use the periodstartdate+1, etc. to get all dates and "holes" to show. On my crosstab, I did get it to look like what you stated with the transid's showing each down the row such as you indicated with regular, sick, vacation. However, the times associated with that are missing, not showing. For example on 3/23 I have 4 hours (regular) then the sick label but not the 4 hours. The only way I can get the times to show is if I move the crosstab to the transid group and of course that screws up the crosstab report. The original submission that I did with the staggered example is how the transid grouping looks. If I could get the "staggered" look out of it and all times to print on the same line, that is what I am after. I am not sure if this is where sections comes in to play because I do not know anything about sections.

As far as how I would like the weeks printed, I would like them to be by employee, week1 then week2, then next employee, week1 then week2, etc. So, the answer is one week followed by the next, then on to the next employee. This report will later have to be duplicated on a monthly timeline also.

I am under the gun on this report as my boss has told the client we would get him an example this week. I have told him in the past to not promise anything until he talks to me because he knows nothing about Crystal Reports and my knowledge is limited as I have only taking the Crystal Reports beginner class and did not learn anything because the teacher kept messing up all the examples. I actually got a refund on the class if that tells you how much I learned.

Thanks for sticking with me. I really need help.

Gayla

 

[lbass]

Then I guess you do need a manual crosstab. First, group on Division, LastNameID, and then create a formula {@weeknumber}:

datepart("ww",{table.clockindate},crMonday)

Insert a group on {@weeknumber} so that it is group #3. Then group on {table.transactionID} (grp#4), if this is what distinguishes sick, regular, and vacation time.

Next, create seven formulas, one for each day of the week, like:

//{@Mon}:
if dayofweek({table.clockindate},crMonday) = 1 then {table.hours}

//{@Tues}:
if dayofweek({table.clockindate},crMonday) = 2 then {table.hours}

For each day, increment the number by 1. The crMonday stays the same, since it states that Monday is the first day of the week.

Next, place these formulas in the detail section, right click on each, and insert a summary (sum). Drag the summaries to the group header #4 and then suppress the details.

Next, to add dates as headers for each day, create seven formulas like:

//{@dateMon}:
totext({table.clockindate} - dayofweek({table.clockindate},crMonday)+1,"MM/dd/yyyy")

//{@dateTues}:
totext({table.clockindate} - dayofweek({table.clockindate},crMonday)+2,"MM/dd/yyyy")

Each time you should increment the number by 1. Place these header formulas in the Group header #3 (weeknumber).

You can limit the overall report to a certain number of weeks in the record selection formula in a number of ways. You could specify a date range or you could use a formula that specifies the year and the weeknumber, although this last method won't pass to the SQL statement:

year({table.clockindate}) = 2004 and
datepart("ww",{table.clockindate},crMonday) in 1 to 4

-LB

 

[okuser]

I feel stupid but I got lost on the

"Next, place these formulas in the details section, right click on each, and insert a summary (sum). Drag the summaries to the group header #4 and then suppress the details"

Please explaing the insert a summary. How do I do that? Do not see "insert a summary".

Gayla

 

[okuser]

Disregard the getting lost question, had pulled in wrong field.

Gayla

 

[lbass]

I'm unclear about whether all of your questions have been answered.

-LB

 

[okuser]

You have been very good in helping me but I cannot get your suggestion to work. I have tried this through my extract file which is what I am trying to manipulate. I get a "bad-date type" returned. When I try to go around the extract file and link directly to the database, I get a linking problem. So, I have tried both ways and cannot get it to work. I will continue to play with it and see what I can come up with but may have to try to hire someone to come build it for me. Will ask questions later if I have more. Thanks for trying to help me but apparently the way it is build into my software, I cannot make certain things work.

Gayla

 

[lbass]

I did test my suggestion above, so I think the problem must be with the datatype of your date field. We could continue working on this, if you provide the datatype for {table.clockindate}. You can determine this by running the cursor over the field in the report--the datatype will appear in parentheses after the field name in the tooltip. Or you can right click on the field and choose "browse field data" and the datatype will be displayed as "type." If it is a string, you should also give an example of how the field displays when you place it on the report. But I leave it up to you to decide whether to move forward.

-LB

 

[okuser]

Yes it is a string format as indicated below:

03/23/2004

Gayla

 

[synapsevampire]

Create a formula to use as the date as follows:

Insert->Field Object->Right click Formulas and select New and name it: Clock in date

Place the following (substituting your field name):

cdate({table.clockindate})

Now reference this formula as you would your date field.

And walk by and give the dba a good twack on the eyeball for saving a date as a string

-k

 

[lbass]

So in my earlier formulas, wherever it says {table.clockindate}, use {@date} instead, where {@date} is:

cdate({table.clockindate}) //as SV indicated

This should allow the manual crosstab to work as you would like.

-LB