cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

Define SQL Variable in Reports Not Working

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.

Labels (1)
0 Kudos
14 Replies
Level 10

How about using the "AS" statement and naming it right in the query? It would give the Title as you would want it to read?

0 Kudos

I changed the SQL query to:

${SQL: SELECT Title AS 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'}

and I still get the body of my email as:

${SQL: SELECT Title AS 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'}

0 Kudos

I'm currently experiencing this same exact issue (we are still on NPM 11.5.3, won't be able to upgrade to NPM 12 until either this or next weekend).  Did you ever get a fix for this?

SOLVED: Well, not really solved I suppose.  More like, figured outWhen the Trigger Condition is a custom SWQL condition, SQL macro variables in the alert action message don't work for some reason (at least when the type of object to be alerted on is "Volume Forecast Capacity".  Maybe this problem isn't prevelant with other object types, but I suspect that it is.).  I imagine this will get fixed if/when they allow custom SWQL macro variables in alert action messages instead of just SQL.  It's kind of curious how when creating the web alert engine they added the new Custom SWQL Trigger Condition option, but didn't also add custom SWQL alert action variables as well.  Anywho, once I changed the condition to SQL, converted my SWQL code to SQL (which was kind of a pain since "Volume Forecast Capacity" isn't a choice of Object to alert on when using SQL, but it is when using SWQL...), and redid my test action message, all was well.

Level 13

Hello,

Unfortunately it doesn't work. There shouldn't be SQL variable options at all. I created issue for that and we will see what we can do.

Apologize

Lada

0 Kudos

shouldn't be it, but have you tried "html" rather than "plain text"?

I've only ever used html so not sure.

0 Kudos

Yes. I originally had it set to HTML and it did not work.

0 Kudos
Level 17

also, try no space between sql: and select ${SQL:SELECT

I've never used alerts around reports but other alerts allow SQL for sure.

0 Kudos

Thanks

0 Kudos

I attempted removing the space between SQL: and Select. That still did not fix the issue.

0 Kudos

Hey tscrip‌,

What version of NPM are you using, and is this an alert crafted in the web alert manager (new in NPM 11.5) or the now legacy GUI Advanced Alert Manager?

My first thought is that you're using NPM 11.5, and I believe you need to use SWQL, rather than SQL for it to work.

- Jez Marsh
0 Kudos

I am on NPM 11.5.1. The report was generated via the win32 "Report Writer" application. When I browse to the Report, it looks great. The issue is in the Scheduled Report Email that I have sent out. It just shows SQL code.

I will look into SWQL. Thanks for the help.

0 Kudos

I attempted to convert my query to SWQL, but it still doesn't seem to work. Does this look correct to you guys? I do have a Recipient and SMTP Server specified, but I remove them for this screenshot.Email Config.png

0 Kudos

Query looks good in SQL Server Management Studio. When you trigger this action does it just outputs the query in plain text in the email message? Does it output an error?

0 Kudos

Just read the bit where you said the engine wasn't parsing the SQL Variable. I was also able to replicate the issue myself as well. I would suggest taking Lada's lead and open a support ticket for this.

0 Kudos