Firedac Macro Functions

rbuser

Hi.

Now that you support Firedac. Do you support macro functions??

I'm testing your trial version RB 15.01 with the macro functions IFNULL from
Firedac and I can't make it works.

Do you have any samples using macro functions??

Thanks for your time.

Sorry for my terrible english.

nicocizik

Hi Rolphy,

FireDAC "Macro" functions are simply built-in routines that process text
before it is sent to the DB. ReportBuilder does not have any logic to
block this feature.

In my testing FireDAC macro functions worked correctly in
ReportBuilder/DADE.

As a simple test I added the following macro as a Calculated Field in
the Query Designer: {ifnull(MyField, 0)}

The resulting SQL created by RB was:

SELECT {ifnull(MyField, 0)} ifnull_MyField_0
FROM dbo.MyTable MyTable

This gave the expected result set without error.

rbuser

Hi.

Thanks Nico for the answer.

I forgot to said, that I'm testing the Macro Functions as Parameters not as
a field of the sql select.

Something like:

Select *
from MyTable
where MyField = {ifnull(:MyField,MyField)}

If the parameter is a Integer then RB pass cero as value, if the parameter
is a String then RB pass empty string as value.

Please, could you take a look??

TIA

nicocizik

Hi Rolphy,

You are correct, when a FireDAC "macro" function is used in a search
condition in DADE, it is recognized as a string. These types of
functions are not currently supported in the WHERE clause using DADE.
We will consider adding this as a feature for a later release of
ReportBuilder.

Currently if you would like to use macros inside the WHERE clause, you
will need to alter the SQL manually which can be done using the SQL tab
in the Query Designer.

rbuser

Hi Nico.

Thanks again for the answer.

As you point, that's what I do before posting here.

The steps:
1.- Manually alter the SQL using the SQL tab in the Query Designer.
2.- Go to the Design Tab
3.- Right click on the parameter section and declares all parameters.
4.- Assign the value to the parameters at Run Time.

With this steps my report get blank.

Can you provide an example?

TIA!

rbuser

Hi Nico.

Sorry for bothering you.

Do you have any sample code?? or Do you have a workaround??

Thank you.

Merry Christmas.



"Rolphy Reyes" escribió en el mensaje de noticias:52b874fd@mail....

Hi Nico.

Thanks again for the answer.

As you point, that's what I do before posting here.

The steps:
1.- Manually alter the SQL using the SQL tab in the Query Designer.
2.- Go to the Design Tab
3.- Right click on the parameter section and declares all parameters.
4.- Assign the value to the parameters at Run Time.

With this steps my report get blank.

Can you provide an example?

TIA!

rbuser

Hi.

As we own RB 12 Enterprise Edition, I made a little test with the ISNULL
function from MS SQL.

In the Data tab a put manually the next SQL statement:
SELECT *
FROM CLIENTE
WHERE CLIENTEID = ISNULL(:CLIENTEID, CLIENTEID)

And I created a parameter with the data type as Integer.

The result was that RB failure, when I pass null value to the parameter.

The reason lies in its method SetValue from the class TppParameter.
Code:
procedure TppParameter.SetValue(aValue: Variant);
begin

if IsUpdating then Exit;

BeginUpdate;

FNull := VarIsClear(aValue) or VarIsNull(aValue);

if not(FNull) then
begin
FValue := VarAsType(aValue, MapDataTypeToVarType(FDataType));

// udpate autosearch settings
if not (csReading in ComponentState) then
FAutoSearchSettings.SearchExpression := GetAsString;

end;

EndUpdate;

DoOnValueChange;

end;

As you may have noticed, when the value is Null the method do nothing. I
think that this method is the same in RB 15 as we are testing the trial
version, we don't have the source.

I will like to make a suggestion about this, put some property that allow
assign null value to the parameter or something like that.

Thanks!

nicocizik

Hi Rolphy,

ReportBuilder parameters do not support null values so the following
expression will not be evaluated.

{ifnull(:MyField,MyField)}

From what I can gather, the IfNull routine is meant to be used in the
Select clause to avoid null values from the DB. In the search
condition, you could simply pre-process the parameter (entered by a user
or other) before it is inserted into the query so you shouldn't need a
macro function in this case.

rbuser

Hi.

Thanks for the answer Nico.

If the Select is contain it in RB. How can I do that??

TIA

nicocizik

Hi Rolphy,

I was referring to processing the parameter (in Delphi code) before it
is used in your dataset.

For instance, imagine you have a parameter named MyParam which is
defined by your user. If your user does not give this parameter a value
or leaves it null, you could in code assign it a default value before
executing the report. Then when the parameter is used in your DADE
query, it has a valid value.

SELECT * FROM MyTable
WHERE MyField = :MyParam <-- no need to use ifnull() here because the
param value has already be processed in code.