Hello I am totally new to Flexcel, in fact just downloaded the demo and about to install into delphi.
My question is i have a access database which stores datetimes field like this...
USER | TIME
10 | 20/08/2010 14:58:03
10 | 20/08/2010 18:45:02
10 | 21/08/2010 14:40:34
23 | 21/08/2010 15:02:10
10 | 21/08/2010 18:40:59
etc.
so can I import the data and convert it from a column view to a row view by date and also make separate sheets for each user?
like this...
--------------------------------------------------
SHEET 1 = user 10
20/08/2010 | 14:58:03 | 18:45:02
21/08/2010 | 14:40:34 | 18:40:59
---------------------------------------------------
if I can do this with Flexcel & how would I do it?
Thanks
Phil
Hi,
Yes, you can do that, how you do it depends in what tools you want to use. If you want to use the API, you can use FlexCelImport.SetCellValue, and calculate everything with code. While sometimes this is the best solution (if it proves too complex to extract data to create a FlexCelReport), I think I would use FlexCelReport here.
This is how I would do it:
First of all, you need a dataset for the users. It you are using TQuery to access the db, then you shuold make a "Select distinct user from table".
Then, in a FlexCelReport component, set the properties:
"PagesDataSet" to the "User" dataset
"PagesDataField" to the string "user" or whatever the field is called in the dataset.
With this, you will have a sheet per user.
(you can try to run the report at this point to see how it is going, it should create a sheet per user, and name it as the user)
Now, you need to create a detail dataset that will have the records for every user. Normally this would be simple, just create a "select * from table" query and link it in master-detail to the "user" dataset.
But in your case, you will have to do some processing to the detail dataset so you can have one row every 2 records in the table.
You could do this by adding a TFlxMemTable and manually adding the records to the FlxMemTable, but probably it is easier to use a TFlxMemTable in virtual mode. (you can look at the virtual mode example to see how to use it.)
Drop a TFlxMemTable in a form, double click it, and add the "StartTime" and "EndTime" fields. Name the TFlxMemTable "Times"
Then assign the events:
procedure TForm1.FlxMemTable1VirtualRecordCount(Sender: TObject;
var RecordCount: Integer);
begin
RecordCount := TimesPerUser.RecordCount / 2;
end;
procedure TForm3.FlxMemTable1GetData(Sender: TObject;
const FieldName: WideString; const RecordPos: Integer; var Value: Variant);
begin
if (FieldName ='StartTime' then
begin
Value := GetFieldAtPos(RecordPos * 2);
exit;
end;
if (FieldName ='EndTime' then
begin
Value := GetFieldAtPos(RecordPos * 2 + 1);
exit;
end;
end;
Where "TimesPerUser" is a dataset with the query "select * from table where user = :user" in master detail relationship with the user table.
"GetFieldAtPos" returns the record a position p of the TimesPerUser dataset, how you do this depends on which db access layer you are using.
Once you have the 2 datasets "User" and "Times", then you just need to define a named range Times in the template, and write ##times##starttime and ##times##endtime in the template.
Regards,
Adrian.
Hello Adrian, thanks for the excellent reply to my question, I really appreciate your time in giving such a helpful and detailed answer.
The second part of your answer deals with just 2 in and out times per row.
Sometime there may be more than just an in and out, - start time > break start > break end > lunch start > lunch end etc, etc.
so in fact the days in and out times may be more on one day less on another. with no way of knowing
My feeble thoughts and programming skills would do something like a query which searches on a user for each day, the results from that query would then be loaded into a list and then the list would be loaded into the Flexcel row.
Then the query would increment the day and repeat the process to build up the report.
- not very elegant, must be a better cleaner way!
How would you suggest I do it in Flexcel?
Thanks again
Ah, ok, I thought the database had 2 records per event (start and end). If I understood correctly, what you have here is more like a "day", and you want to group per each day, minimum and maximum times?
If so, I would do a 2 level master detail:
Master: User
detail: TimeTable
User: Select distinct user as fuser from tabler
TimeTable: Select user as fuser, day(time) as fday, max(time) as endtime, min(time) as starttime from table group by user, day(time) where fuser = :fuser
Then link those 2 datasets in delphi, and then use the first one in the sheets, the second one for the data.
Of course, if for any reason this doens't work, then going through the table manually and selecting what you want is not such a bad idea. While being able to do htings declarative (with select) looks nices, sometimes when conditions are complex, a simple loop throught all the records where you process them will be faster and simpler, even whe it doesn't really look "right".