I am in the process of creating the access script for a QV template file. The intention is that when I create the report for real, so as to ensure that I don't overlook changing the filename everywhere that I need to, I have declared it as a variable at the start of my script. In SQL, the script looks like this:
DECLARE @filename nvarchar(30) = 'GLMONEY',
@sql_string nvarchar(200);
SET @sql_string = 'SELECT CASE WHEN ' + @filename + ' = 0 THEN ''USER'''
+ ' WHEN ' + @filename + ' = 1 THEN ''ADMIN'''
+ ' END AS ACCESS, UPPER(ntname) AS NTNAME'
+ ' FROM Qlikview_Access'
+ ' WHERE ' + @filename + ' is not null';
exec sp_executesql @sql_string;
The Qlikview_Access table currently has this format:
[dbo].[Qlikview_Access](
[NTName] [nvarchar](50) NOT NULL,
[activityfeed] [bit] NULL,
[GLmoney] [bit] NULL
)
So consequently the above query returns this:
ACCESS NTNAME
ADMIN BUCKSHOSP\PAUL.GARDNER
USER BUCKSHOSP\HELEN.FRANKLIN
USER BUCKSHOSP\ALAN.HUDSON
USER BUCKSHOSP\GREG.MTUNZI
But when I paste the script into the Access section of the hidden script, it errors with
Unknown statement
exec sp_executesql @sql_string
How can I achieve this in QV? All my searches direct me towards creating a stored procedure and calling that using the execute command with @filename as a parameter. But that seems somewhat OTT for what is quite a straightforward select statement.
