The functionality of Freehand SQL is built into Smart Reporting. Depends on what version you are running. In the newer version they have it hidden and you have to turn it on.
Read this article.
Thank you very much for your answer T.D. (and also thank you, Laurent, for moving my question to the correct place).
I knew the article you told me about, but it doesn’t resolve my issue. What I am trying to do is to create a view using freehand SQL, not a calcultated field for an existing view.
Anyway, thanks for your time.
2 of 2 people found this helpful
Below snippets explains it all on what you have requested for, i.e. we can have custom SQL at a VIEW level by using the Virtual Table option.
HOWEVER, since SmartReporting doesn't allow native SQL commands or functions, the usage of this option is very very limited.
For instance I have tried a simple CASE statement which wasn't working considering that SmartReporting is looking for a reference of FORM in the Remedy and not the TABLE in the Database.
Thank you very much, Ravi,that's what I was looking for.
I tried it yesterday and it seemed it was going to work. I had some problems whith the exact tables, but that's another story.
T.D. wrote “In the newer version they have it hidden and you have to turn it on”. Does somebody knows how I can turn it on or off?
I’m looking how I could turn it off because Freehand SQL for Views obviously does not work in Remedy environments. Correct? We just have upgraded to 19.02 and this option is still visible but in a new OOTB installation this option is not visible anymore and I would like to turn it off. Please be aware I am talking about Freehand SQL in the dialog Create New View and not dialog New Report. See picture below:
Thanks for your help!
log into AdminConsole as siadmin, launch BMC Remedy Smart Reporting and go to Administration ==> Roles, pick a role which you wish to restrict (eg. Report Writer), then look for Data Sources & Views among Functions -- you'll find a checkbox Freehand SQL Views there.
You need to login to Smart Reporting using System Administrator account i.e. "siadmin".
Once logged in go to Administration -> Admin Console -> Roles -> select the role that you want to turn on/off
Then expand the "Data Sources & Views" function list and select/deselect the "Freehand SQL Views"
Note that it has to be checked/done for other roles if required.
Many thanks to Abhay and Sinisa!
I am impressed how quickly I received an answer here! Now I found the setting "Freehand SQL Views" and was able to swich it on or off.
My problem was that I do not see any Edit context menu at existing Roles (not even with siadmin). Only if I copy the Role Admin for example then I can change the copy of this Role later again. Do you know how to Edit ootb Roles or do I have to copy each ootb Roles first and reassign all users to the new Role?
When you login using siadmin into Default Client organization, then you can edit the OOTB roles.
However, if you logged into an on-boarded client organization i.e. YourCompany, then it will will ask for a copy of the role.
Yes, you're right! I had logged in with MyCompany and not with Default. When I log in to the customer organization Default, the Edit menu is visible to OOTB roles. This question now is solved but leads me directly the next question. When I try to create a view using the Freehand SQL option, I get an error.
`HPD:Help Desk`.`Incident Number`
FROM `AR System Schema`.`HPD:Help Desk`
If I then press Validate, I get the following error message:
SQL statement is invalid.
The error message returned from the database was:
Does anybody know an example of a View based on Freehand SQL that works? Or does Freehand SQL not work at all for Smart Reporting Views in Remedy environments?
I would like to create a view based on Freehand-SQL
But this does not work. There is always an error message.
How to create a Freehand SQL view (example of a syntax) in Smart Reporting?
Does anybody know an example of a View based on Freehand SQL that works?
Or does Freehand SQL not work at all for Smart Reporting Views in Remedy environments?
Thanks a lot in advance
What is the SQL format that you tried? I mean, is it a plain SQL that normally written for DB?
Could you try the below query in Freehand SQL and see if you get error?
You may change the assigned group value if not present.
SELECT DISTINCT `HPD:Help Desk`.`Incident Number`, `HPD:Help Desk`.`Description`
FROM `AR System Schema`.`HPD:Help Desk`
WHERE ( `HPD:Help Desk`.`Assigned Group` IN ('Service Desk')
AND `HPD:Help Desk`.`Status` < 'Resolved' )
yes it was a normally SQL written for DB and a SQL according to the schema you suggest below.
I have inserted the SQL below and get the following error message:
SQL-Anweisung ist ungültig.
Die von der Database zurückgegebene Fehlermeldung lautet: