1. 

   spyhunter88, Member

   Hi, when I use Parameter passing, I face a problem when parameter is null or empty. Example:
   (param1) Report A ---(param1) --> Report B --(param1)--> Report C

   In Report A I use:
   select 'dbr.report', 'sp_DBR_Report_B', 'new_popup', '[Col]', 'param1=(param1)';

   and in Report B I pass it to C:
   select 'dbr.report', 'sp_DBR_Report_C', 'new_popup', '[Col]', 'param1=(param1)';

   In Report A, the param1 come with Parameter so it's empty, that's not problem when passing to Report B. But in Report B, the report AUTOMATICALLY convert empty to NULL, so param1 is null inside Report B.
   And after that, the Report C failed because error type or something else I can't remember. The work around I chose is use this in B to prevent null value:
   

   SET param1 = IFNULL(param1,'');
   select 'dbr.report', 'sp_DBR_Report_C', 'new_popup', '[Col]', concat('param1="',param1,'"');

   Of course, the statement SET param1 = IFNULL(param1, ''); not affect when use 'param1=(param1)' and it still passing null and get error type.

   Can you just simple do not convert empty to null while passing, leave it null when null? I think that's cause the problem when Report B get Null when A passing empty.

   Thanks,
2. 

   myDBR Team, Key Master

   We'll take a look at it. For a meanwhile, you can use the parameter in a column and refer it as a column parameter.

   --
   myDBR Team
3. 

   myDBR Team, Key Master

   Hi,
   myDBR 4.3.1 is now relased. The version contains a fix for the empty parameter passing.

   --
   myDBR Team
4. 

   spyhunter88, Member

   Hi, I don't know what exactly did you fix? There are 2 problems here:
   1. When I pass empty value, the report receive as null value.
   2. When I pass null, the report does not understand its type.

   So how system work in these situation because? Did you fix all or one of them?

   Thanks,
5. 

   myDBR Team, Key Master

   Hi,
   did you run the updater?

   When a linked report has a non-editable parameter linked to a column which is null, myDBR treats this the same way as user would have run a report with optional parameters and left them empty. If the parameter is a string, parameter gets an empty string, if it is a number 0 is passed. For dates a null is passed.

   --
   myDBR Team
6. 

   spyhunter88, Member

   I will update later.
   Thanks for this information!
7. 

   spyhunter88, Member

   Hi, I have an issue relate to parameter passing. These function above work great and help me reduce a lot of code.

   My scenario:
   Report A --> call store of Report B --(param*)--> Report C.

   Report A only call Report B with some specific parameter that I want to hide from user:
   

   call sp_DBR_Report_B(param1, param2, param3, 'Daily');

   and Report B call Report C in linked:
   

   select 'dbr.report', 'sp_DBR_Report_C', 'new_window', '[Col1]', 'param1=(param1)', 'param2=(param2)', 'param3=(param3)', 'param4=(param4)';

   And Report B does not pass param4 to Report C and get Non-supported datatype. Of course, I explain my purpose: the report B and C are main reports with many options. But user will choose some fixed parameters and I have to separate by Report A, A1, A2 ..., create fixed parameters and pass them into Report B.

   Of course, I can make it visible and linked-parameter but it's hard to control and User only want to click on Daily Report -> Run Report, not Report A -> choose 'Daily' value -> Run Report.
   Or I can hide param4 in Report A somewhere, so other parts'll work ( I guess).

   Thanks,
8. 

   myDBR Team, Key Master

   Please note that (Parameter) reference always references the original report's parameters.

   If you have a structure where you have Report A calling procedure B calling procedure C and you try to use (parameter) reference, the reference is always made against Report A's parameters.

   To utilize the parameters for procedure B, include the parameters to the query as hidden columns.

   select 'dbr.report', 'sp_DBR_Report_C', 'new_window', '[Col1]', 'param1=param1', 'param2=param2', 'param3=param3', 'param4=param4';
   
   select 'dbr.hidecolumns', 'param1';
   
   select ..., param1 as 'param1', param2 as 'param2', param3 as 'param3', param4 as 'param4'
   
   from ...
   
   where ...
   
   

   
   --
   myDBR Team

Reply

You must log in to post.