Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Reference blank/unselected/defaulted parameters in Open Formula?

  1. #1
    Join Date
    Aug 2016
    Posts
    142

    Default Reference blank/unselected/defaulted parameters in Open Formula?

    Hi,

    I have a report with several types of parameters. Some are text boxes where the user can enter a value whilst others are multi-selection buttons.

    All multi-selection button parameters are setup so that if none are selected, the report query runs for all. The default behaviour is for none to be selected.

    I now have a situation where I want some fields in the report to change depending on if one of the multi-selection buttons are selected.

    I originally started by doing an Open Formula IF statement like this in the field section of Attributes:

    =IF(OR(ISBLANK([parameter_1]);ISBLANK([parameter_2]));"field_1";"field_2")

    However for some parameters the ISBLANK returns true when it is not. It seems that "blank" multi-selection parameters return true regardless of any options being selected. Also for the text box ones with a default value, it returns true regardless also. In fact, the only ones it seems to work correctly on are Date Picker parameters.

    My assumption is that ISBLANK is the incorrect one to use but I have tried ISNA and ISEMPTYDATA and neither work too.

    Any ideas?

    BTW using version 5.4
    Last edited by Deanicus; 02-09-2017 at 04:45 AM.

  2. #2
    Join Date
    Aug 2016
    Posts
    142

    Default

    I am now seeing some confusing behaviour

    I have setup a check parameter which has a post processing formula of

    =ISBLANK([parameter_1])

    Within the preview screen of Report Designer, when the report first runs this says true as indeed the parameter is blank. If I select a parameter and click update, the report output updates but this check parameter still says "true" (hence the reason for this thread)

    However, whilst still in the preview screen if I add a new parameter, the report refreshes as expected but this time the check parameter shows the correct value

  3. #3
    Join Date
    May 2016
    Posts
    282

    Default

    I have observed this same behaviour in a text area parameter, so when I wanted to check if it was null I had to check two things, ISBLANK and [param]="", in a multi-selection I would change the param="" for something to convert the array to a text, like CSVTEXT([param])="" (I haven't checked it don't know if it would work), if that doesn't work, I would make my parameter mandatory and add a first value to select All Values.
    Regards

  4. #4
    Join Date
    Aug 2016
    Posts
    142

    Default

    I solved it (kind of anyway, my solution doesn't sufficiently answer the question I originally had but is a way around it)

    Thanks Ana GH for your suggestion but unfortunately it did not work. It did however set me down the path to the solution. Basically I used a combination of ISBLANK and COUNTA functions.

    For anyone who might face similar problems, below are my findings

    Date Picker parameters that are blank fail a COUNTA function i.e. they return 1 instead of 0 as I would expect. However they pass the ISBLANK test
    Multi-selection buttons with no default value pass a COUNTA test i.e. they get 0 but will not pass an ISBLANK
    For text box parameters with a default value, I simply tested the argument whether they equaled the default value

    Knowing all of the above I then was able to craft a boolean type parameter with a post-processing formula like this

    =AND(ISBLANK([Date_Param]);COUNTA([Multiselect_Param])=0;[text_param]=(default))

    The boolean will return true if all of the conditions are met. Since this is the default state for all parameters it will only return false if a user makes changes to any of them.

    Originally I wanted to replace fields with something else based on the above (I was going to change the field name to something invalid so the null value I set for the field would be displayed instead) but ultimately decided it was better to hide them

    Therefore for the fields I wanted to hide I simply put the below into the visible property

    =IF([boolean_parameter]="true";"true";"false")

  5. #5
    Join Date
    Aug 2016
    Posts
    142

    Default

    Also a note to my 2nd post in the thread regarding the boolean calculating only when I did something with the parameters

    I originally had this boolean parameter somewhere in the middle of the parameter list. However I discovered that if it is at the end it calculates correctly. Hence when I copied and pasted the boolean parameter it automatically went to the end.

    Having a parameter with a post-processing job to do at the end of the parameter list seemed to force the one in the middle to recalculate also. However the middle one was no longer needed so I deleted it and it's copy at the end works perfectly

    Perhaps this is something everyone knew but my understanding was that post processing jobs started after all all "normal" parameters were processed but my findings above suggest that all parameters are done sequentially in order of how they appear in the report. Makes sense to be honest, my own misunderstandings contributed to my issues

    As a general rule then parameters with post processing based on other parameters should be listed below the parameter they are dependent on.

  6. #6
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    No, parameters are evaluated in order and evaluated completely when it is their turn. Untrusted values from parameters that have not been validated are not available for use (or someone may do evil stuff). Post-processing may have changed/corrected/cleansed a value that is then needed in another parameter's validation.

    So if you have parameter A,B,C in that order on a report, then parameter A can only access its own given value, B can access the validated value of A, and C can access A and B.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  7. #7
    Join Date
    Aug 2016
    Posts
    142

    Default

    Thanks Taqua

    It was my own misunderstanding so thank you for clearing that up

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.