Linked Report Syntax for SQL Server

(3 posts) (2 voices)
  1. sdysart, Member

    I am in the process of transitioning a system from MySQL to SQL Server. Both systems are using MyDBR.

    I want to create a popup linked drilldown report from my summary report that passes in three parameters. The code from the summary report is:

    SELECT 'dbr.hidecolumns',2;

    SELECT '', 'sp_DBR_CollectionsDeltaDrillDown', '[rep]','popup','@Repname = [Rep]','@StartDate = [StartDate]','@EndDate = [EndDate]';

    SELECT r1.RepName AS 'RepName[Rep]',r1.OpenCollections AS Starting,r2.OpenCollections+r2.Net AS Ending,r2.OpenCollections+r2.Net-r1.OpenCollections AS Delta,@StartDate AS [StartDate],@EndDate AS [EndDate]
    FROM [Report].[CollectionsDelta] r1
    INNER JOIN [Report].[CollectionsDelta] r2
    ON r1.RepName = r2.RepName
    WHERE r1.ReportDate = @startDate
    AND r2.ReportDate = @endDate;

    When loading the summary report, I get:

    Unknown column reference: [rep]
    Unknown column reference: [StartDate]
    Unknown column reference: [EndDate]

    I can still click through to the drilldown, but of course my input parameters do not carry over. Code very similar to this would have worked in MySQL. How can I get the command to pass my parameters in SQL Server?

  2. myDBR Team, Key Master

    myDBR works in the same way in MySQL and in SQL Server. You have extra spaces in parameters and you do not need to specify the @-prefix. So, instead of:

    SELECT '', 'sp_DBR_CollectionsDeltaDrillDown', '[rep]','popup','@Repname = [Rep]','@StartDate = [StartDate]','@EndDate = [EndDate]';


    SELECT '', 'sp_DBR_CollectionsDeltaDrillDown', '[rep]', 'popup', 'Repname=[Rep]', 'StartDate=[StartDate]', 'EndDate=[EndDate]'

    Also, SQL Server does not require semicolons at the end of statement (statement before a CTE being an exception to the rule if the CTE is not the first statement).

    myDBR Team

  3. sdysart, Member

    Thank you. That solved my issue.


You must log in to post.