Firedac Macro Functions

edited December 2013 in DADE
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.

Comments

  • edited December 2013
    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.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited December 2013
    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

  • edited December 2013
    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.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited December 2013
    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!



  • edited December 2013
    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!



  • edited December 2013
    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!

  • edited December 2013
    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.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited December 2013
    Hi.

    Thanks for the answer Nico.

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

    TIA

  • edited January 2014
    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.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
This discussion has been closed.