Summary linked to Details report and NULL values

(3 posts) (2 voices)
  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.


  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 '', 'sp_DBR_detail', '[Date]', 'inDate=Date', 'inOrder=nullcolumn';
    select '', '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 :)


You must log in to post.