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?
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
3. 

   sdysart, Member

   Thank you. That solved my issue.

Reply

You must log in to post.