Hi,
I would like to know how I could manipulate some dataset data before sending to report. My idea was to use the GetText from a dataset field, but is not executing this event at all.
Thx
Hi,
OnGetText applies to the "Text" property, which is a string. FlexCel reads the value directly as a date, number, etc, and Text doesn't apply there.
There are many options to manipulate the data, but what to use depends a little on the case and on your tastes:
1)In some cases you might be able to manipulate it at the SQL itself. Say do a select name + ' ' + lastname from customer as FullName
2)In some others, you might use calculated field and an OnCalcField event to calculate them.
3)Many times, you might manipulate the dataset from the template itself. Say you have an employeeID and want the employee name, you could use a <#lookup()> tag. Or if you want the name + lastname, just write both tag in the file. You can also use <#if(...)> to do conditionals, and <#evaluate()> to use any of the 300 supported excel functions.
4)You might also use an user defined function: Define it in your code, and in the template use <#MyUdf(DataSet.Field)>
As said, there are many possibilities, but what to use depends on the case.
I will see if the option 2 is working but I think I tried and it didn't.
The change I want to do is mainly to grab (copy) part of the text of one specific dataset field (sometimes whole content, sometimes just part of it).
t this dataset I have mainly 3 fields (Timestamp, Error Description, Error ID). My export will contain thousands of rows and some or the errors are duplicate. Around 10% of the Error Description will have to be modified (show only part of the text).
e.g. Error Description: "Error ABC and $DEF" and I would like to show only "Error ABC"
Either I do this at the software or I do at the template file.
Doing at the template would be preferable, but I guess I will have to learn more how to use the lookup (not sure how complex it is).
I could have an additional worksheet (or maybe use the config?) to add a column with the Error ID's I want to change the text and two other columns with the start and end position of the text I want to show. This way I could do a lookup and read the start position and length of the text I would like to see.
Hi,
Calculated fields should work fine for this. You could have a "DisplayError" field, and on the OnCalcFields for the dataset set the value (note that the event is in the dataset, not in the field),
An user defined function would work too, and maybe it is what I would use myself if the logic is too complex. You would define something like:
type
TDisplayErrorImp = class(TFlexCelUserFunction)
public
constructor Create();
function Evaluate(const parameters: TFormulaValueArray): TReportValue; override;
end;
...
function TDisplayErrorImp.Evaluate(
const parameters: TFormulaValueArray): TReportValue;
begin
if Length(Parameters) <> 2 then raise Exception.Create('Bad parameter count in call to DisplayError() user-defined function.');
Result := GetFinalErrorMessage(Parameters[0], Parameters[1]);
end;
Then you would add the udf to the report like this:
Report.SetUserFunction('DisplayError', TDisplayErrorImp.Create());
And in the template, write:
<#DisplayError(<#Db.ErrorId>;<#Db.ErrorDescpription>)>
This will call your user function passing the errid and error message as parameters. Then you should code your function so it returns the new error message.
If you want to do it in the template it is doable too, but I am not sure if it won't make the template too complex. The advantage to do it in the template is that it is more dynamic: If there is a new error message, you can just add it to the template and not even recompile the app.
And indeed, what you would do is to add another sheet (or the use the config) with error ids and start, end position. You wouldn't use the <#lookup> tag since this is more for a lookup in the db, like for example you have employeeID and you want to get the EmployeeName from the same table. Here it is a lookup in the columns in a sheet, so we would use the standard Excel VLOOKUP function.
So the next step would be to do it with Formulas in Excel. You could write something like
=VLOOKUP(id,K:M,2,TRUE)
Note to use "TRUE" as the last parameter of VLOOKUP, since this does a binary search which is much faster. But this means the column with errorids (column K in the formula above) must be sorted in ascending order.
Then, to convert this to a tag, you would do:
<#evaluate(VLOOKUP(<#db.id>,K:M,2,TRUE)>
You define a report expression "StartError" in the config sheet with this.
and define "endError" as #evaluate(VLOOKUP(<#db.id>,K:M,3,TRUE)
Then you would define a report expression DisplayError, which first sees if the VLOOKUP exists, and if it does, returns a substring from starterror to enderror. You can do this all with standard Excel formulas, and use <#evaluate> to get their result in the report.
Similar to the udf, you would then write <#DisplayError(<#db.errorid>;<#db.ErrorMessage>)>
but as said, doing this in the report is more complex. It might be worth if you plan to modify the list often. If not, probably an user defined function is simpler to implement.