Yeah, I suspect some sort of caching issue. For this reason I am going to stay away form the {REPORT SQL:} tags, I found that by using the SQL helper inside some `[= ]’ script tags I was able to get a reliable output. For example:
My output: <bold>[=sql.Query("SELECT 'Hello world'").First]</bold>
BUT, your idea of using a StoredProcedure was really helpful for two reasons…
- It will help keep the code required for each label a lot cleaner
- It will allow me to do some date formatting within SQL, which was something I was originally doing in jScript
The stored procedure I created was as follows - You will see this either outputs a formatted date, or will pass the value of the field, so I can use the same SP for all fields and it will automatically give me an appropriate output…
CREATE PROCEDURE [dbo].[getCustomDataByEntityName]
@entityName varchar (50),
@fieldName varchar (50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @value AS VarChar(50) = (
SELECT jsonValue
FROM ENTITIES
CROSS APPLY OPENJSON(CustomData)
WITH (
jsonName varchar(50) '$.Name',
jsonValue varchar(1024) '$.Value'
) jsonData
WHERE
jsonName = @fieldName
and Name = @entityName
)
IF ISDATE(@value) = 1
SELECT FORMAT (CONVERT(date,@value), 'dd MMM yy') AS [Output]
ELSE
SELECT @value AS [Output]
END
GO
I am using this with the script tags I was using. For example…
Last Dive: <bold>[=sql.Query("EXEC dbo.getCustomDataByEntityName @entityName = '{SETTING:EQUIP-EntityName}', @fieldName = 'LastDive'").First]</bold>
This is working great.