14 Replies Latest reply on Aug 9, 2016 2:05 PM by xtraspecialj

    Define SQL Variable in Reports Not Working

    tscrip

      I am trying to define a SQL variable inside of a report email alert. The end goal would be to have the Report Name show up in the email and not the Schedule Name.

       

      I crafted my query and verified it is working with Database Manager. I attempted to insert my crafted query into my alert, but the engine ends up writing the whole entire SQL query into the email. Seems like the engine is ignoring the fact that it is a SQL query and is interpreting it as just text.

       

      After reading some forum posts, I simplified the query down to something extremely simple, and this still did not work. Below is the SQL query that I started out with:

      ${SQL: SELECT Title FROM [ReportDefinitions] JOIN [ReportJobDefinitions] ON [ReportDefinitions].[ReportID]=[ReportJobDefinitions].[ReportID] JOIN [ReportJobs] ON [ReportJobDefinitions].[ReportJobID]=[ReportJobs].[ReportJobID] WHERE [ReportJobs].[Name] = ${N=Reporting;M=ScheduleName}}

       

      And I have even tried hardcoding the value like such:

      ${SQL: SELECT Title FROM [ReportDefinitions] JOIN [ReportJobDefinitions] ON [ReportDefinitions].[ReportID]=[ReportJobDefinitions].[ReportID] JOIN [ReportJobs] ON [ReportJobDefinitions].[ReportJobID]=[ReportJobs].[ReportJobID] WHERE [ReportJobs].[Name] = 'Every Monday Morning at 8:00 AM'}

       

      This is the simplified query that I was using to verify the engine was interpreting the SQL correctly (which it isn't):

      Select [Name] FROM [ReportDefinitions] WHERE [ReportID] = '248'

       

      Any help would be greatly appreciated.