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 'dbr.report', '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 dbr.report command to pass my parameters in SQL Server?

    Posted 1 month ago #
  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 'dbr.report', 'sp_DBR_CollectionsDeltaDrillDown', '[rep]','popup','@Repname = [Rep]','@StartDate = [StartDate]','@EndDate = [EndDate]';

    use:

    SELECT 'dbr.report', '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

    Posted 1 month ago #
  3. sdysart, Member

    Thank you. That solved my issue.

    Posted 1 month ago #

Reply

You must log in to post.