Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Manipulate Excel Data Rows Post Reply Post New Topic
Author Message
Axiomcs
Newbie
Newbie
Avatar

Joined: 03 Dec 2014
Location: South Africa
Online Status: Offline
Posts: 3
Quote Axiomcs Replybullet Topic: Manipulate Excel Data Rows
    Posted: 18 Dec 2014 at 3:24am
Hi there. Can anyone shed some light please ... would be very grateful!

I have following data in flat Excel file from our Bio-metric Time & Attendance system:

Name        No        Date       Time
---------   --------- --------- ----------
John        1          1-1-2014   08:00
John        1          1-1-2014   13:00
John        1          1-1-2014   14:00
John        1          1-1-2014   17:00
Kate        2          1-1-2014   09:00
Kate        2          1-1-2014   12:00
Kate        2          1-1-2014   13:00
Kate        2          1-1-2014   18:00
...

What I am looking to do is pull it into Crystal as follows:

Name       No   Date          IN      OUT      IN       OUT    >>>
----------- ---   ----------    -----    -----      -----     ----
John        1     1-1-2014   08:00   13:00   14:00   17:00 >>>
Kate        2     1-1-2014   09:00   12:00   13:00   18:00 >>>

In other words:

1. I need the row data for each person for each DAY in one line
2. There could be up to 8 records per day per person - eg. In and OUT 4 times
3. All John's entries for the month will be grouped together, then all Dean's etc


I have tried a Cross-Tab table, but it doesn't work ... suspect I need a formula for each column's IN & OUT!

I am at a loss!

Any takers?

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 Dec 2014 at 3:42am
Is this really only about displaying on one line or is there an expecttion to do calculations from on this 'one row'?
IP IP Logged
Axiomcs
Newbie
Newbie
Avatar

Joined: 03 Dec 2014
Location: South Africa
Online Status: Offline
Posts: 3
Quote Axiomcs Replybullet Posted: 18 Dec 2014 at 3:45am
Thanks DBlank, I wish to calculate hours per day etc ..
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 Dec 2014 at 4:09am
Displaying is much simpler than also doin calculations.
If your desire to create the singular row is to simplify the calculation, IMO that just makes it more difficult. IN Crystal youa re not really altering data, just how a user sees the data.
The way you do the calculations (in crystal) still has to take into account the way the data is really set into the report. Many people seem to confuse that if they can make the data display in a way that simplifies their desired math/calculations then it will make the math/calculations easier to derive. In general it does not. Grouping and ordering impact it mroe than the display.
Is there any more data in the spreadsheet that is available to identify what it is? It looks like you have to just determine In and Out based on the sequence.
I have not used a Command object with excel as the data source nor have I tried a PIVOT in a command but that is where i would start if you want to manipulate the datainto one row with 4 dattime columns.
If you want to do it with crystal formulas I would focus
on shared variables  doing datediff calculations displaying a group footer
To do this you need the correct structure of grouping on the No field which seems to wrap one employee's full day of clocked times.
IP IP Logged
Axiomcs
Newbie
Newbie
Avatar

Joined: 03 Dec 2014
Location: South Africa
Online Status: Offline
Posts: 3
Quote Axiomcs Replybullet Posted: 18 Dec 2014 at 4:27am
We have an app purchased with the Bio-metric readers but alas, their reporting is shocking and instead of saving paper, we generate more! The supplier is not able (or willing) to adjust their reports, so my idea was to take their raw data in CSV format and rework it into a single page report per staff member instead of the current 6-8 pages per member. The example data above is as it comes off the server in CSV format. There are just way more records, way more. 35 people with 4 to 8 clockings per day over 31 days per month ... I was thinking of calculating each In & Out amount of hours together to get an amount worked per day and then evaluating any hours worked over weekends for the overtime calculations, but seems it's going to be quite a job!
I will look at your suggestion and play with some ideas ... Thank you for your time, I appreciate your input!
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.