1. 

   Stuart Gibson, Member

   I have a Summary report with 2 linked columns (Date and Order) to the same generic Details sub report.

   If I click the Date column in summary report I would like the sub report to return all results for that Date. If I select Order in Summary report I would like to return all results in sub report for that Order only.

   So can a NULL value be passed from Summary report (If Order is selected then Date will be null and vice versa) and then how would I code the sub report using IF THEN ELSE for the select statement and the parameters passed to it from Summary report.

   Hope this makes sense.

   I am a newbie and cant figure it out.

   Thanks
   Stu
2. 

   myDBR Team, Key Master

   If you want to pass NULL values to a linked report, you might want to change myDBR default setting where strings pass empty strings pass and numeric pass zero when empty value is passed. You can do this by adding:

   $mydbr_defaults['parameters']['empty_numeric_results_null'] = true;
   
   $mydbr_defaults['parameters']['empty_string_results_null'] = true;

   Into your user/defaults.php.

   To pass NULL values into a linked report, just add a NULL column into your result set (you can hide it). So if you Date and Order column and you want to link same report to both columns, you would use:

   select 'dbr.report', 'sp_DBR_detail', '[Date]', 'inDate=Date', 'inOrder=nullcolumn';
   
   select 'dbr.report', 'sp_DBR_detail', '[Order]', 'inOrder=Order', 'inDate=nullcolumn';
   
   select 'dbr.hidecolumn', 'nullcolumn';
   
   select col1, col2, order_column as 'Order', date_column as 'Date', null as 'nullcolumn'
   
   from yourdata;
   
   

   In the detail report use the values in comparision if they exist:

   create procedure sp_DBR_detail(
   
   inDate date,
   
   inOrder varchar(20)
   
   )
   
   begin
   
   select col1, col2, col3
   
   from yourdata
   
   where  date_column = ifnull( inDate, date_column ) and order_column = ifnull( inOrder, order_column );
   
   end
   
   

   If you are on MS SQL Server or Sybase, you can use isnull() instead of ifnull().

   Alternatively you can just cretate own detail reports for both columns.

   --
   myDBR Team
3. 

   Stuart Gibson, Member

   Fantastic, exactly what I need. Thanks for your help and patience :)

Reply

You must log in to post.