How to make a dynamic select with url parameter in a Run Self-Service Reports - Run Macro?

[Lars Lysenstøen]

I have a “Run with a user form and parameters” macro where I want to have a dynamic select that uses a url-inputparameter in like this:

"select name from areas where id = cast($departmentNumber as varchar(3))"

I am calling the page lik this: …/Test+Dynamic+select?run_1_departmentNumber=201.

When I do, I got this error message: “Please verify if you have installed/enabled SQL for Confluence, and that your query is valid.”

If I change my sql-query to “select name from areas where id = 201” then there is no error message and I got expected behavior.

What should be changed to get it working using a url-pram. ?

From source-editor:
<ac:structured-macro ac:macro-id="fd7579ef-ec79-4886-b5ff-9b1e108c8cd7" ac:name="run" ac:schema-version="1">
<ac:parameter ac:name="replace">departmentNumber::departmentNumber:dynamic-select::SAM:select%20name%20from%20areas%20where%20id%20%3D%20cast(%24departmentNumber%20as%20varchar(5))%0A</ac:parameter>
<ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
ac:rich-text-bodySelected value: $departmentNumber</ac:rich-text-body>
</ac:structured-macro>

[VENKATESWARLU KURUVA]

Hi @Lars,

The issue is that URL parameters aren't being passed into the dynamic-select SQL query properly. Here's the fix:

Use $departmentNumber without the cast() wrapper first to isolate the problem:

select name from areas where id = '$departmentNumber'
If that works, the issue is with cast(). Try this instead:

select name from areas where id = cast('$departmentNumber' as varchar(3))
Key things to check:

Wrap the variable in single quotes — '$departmentNumber' not $departmentNumber. URL params come in as strings and need quoting in SQL.

URL encoding — make sure your replace parameter properly URL-encodes the query. The %24 for $ looks correct, but double-check the full encoded string.

Parameter name match — your URL uses run_1_departmentNumber and your macro uses departmentNumber. The run_1_ prefix is correct for the first Run macro on the page, so that should be fine.

Execution order — dynamic-select might evaluate before the URL param is injected. If so, you may need to use a static default value as fallback:

departmentNumber::departmentNumber:dynamic-select:201:SAM:select name from areas where id = '$departmentNumber'
The 201 after the second :: sets a default so the query doesn't fail on first load.