What am I doing wrong with "Run Self Service Report" macro setup?

[Paul LaBarbera]

Can a user of the "Run Self Service Reports" macro help me out with this?

TL;DR: What's the correct syntax to use to replace a parameter in a stored procedure while using the "Run with a user form and parameters" macro?  

In the macro, P1 is set to A.  I want to allow users to replace it with B or D with the macro... What's wrong with this?

Replace: p1:A:?Client:select::D::B

Background

We use the SQL macro to pull a list of open issues at each one of our clients. (We change the client using a parameter). I want to use the "Run Self Service Reports" macro to allow users to replace this parameter using a form... this is what the macro is designed to do, but I can't figure out the correct syntax. 

Setup

Here's a screenshot of the setup.  The query has p1 set to "A"... so the query pulls all data for A. This works as intended.

image2015-7-6 14:44:4.png

The Issue

I can't figure out the syntax of the "Replace" parameter in the "Run with a user form" macro. Can anyone tell me what I'm doing wrong? Nothing I try replaces the parameter (which is A).

image2015-7-6 14:46:17.png

Edit:

Here's the page code:

<ac:structured-macro ac:macro-id="b0ca30d3-0f70-456c-8816-180ee31b491c" ac:name="run" ac:schema-version="1">
          <ac:parameter ac:name="heading">Open Issues by Client</ac:parameter>
          <ac:parameter ac:name="hideExport">false</ac:parameter>
          <ac:parameter ac:name="replace">p1:A::hidden,$client::Client:select::D:D:B:B</ac:parameter>
          <ac:parameter ac:name="width">100</ac:parameter>
          <ac:parameter ac:name="showKey">true</ac:parameter>
          <ac:parameter ac:name="titleRun">View Open Issues</ac:parameter>
          <ac:parameter ac:name="showReset">true</ac:parameter>
          <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
          <ac:rich-text-body>
            <ac:structured-macro ac:macro-id="e1369be6-440b-43ec-ac48-8a942f316ff4" ac:name="sql-query" ac:schema-version="1">
              <ac:parameter ac:name="p1">$A</ac:parameter>
              <ac:parameter ac:name="noDataMessage">No records match criteria</ac:parameter>
              <ac:parameter ac:name="sortColumn">1</ac:parameter>
              <ac:parameter ac:name="dataSource">SQLServerProd</ac:parameter>
              <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
              <ac:plain-text-body><![CDATA[ exec sp_Open_Issues_by_Client ?]]></ac:plain-text-body>
            </ac:structured-macro>

[Paul LaBarbera · Accepted solution]

I got an answer on Bob Swift's board: https://bobswift.atlassian.net/wiki/questions/111575186/how-do-i-structure-replace-statement-when-using-stored-procedure

[xpauls]

Parameter syntax:

id:default value:Name:type::Value1:Label1:Value2:Label2:Value3:Label3   and so on

So first type is p1 which is hidden so it is written as

p1:A::hidden

p1 - as id, A - default value, no need for Name since it is hidden, and lastly type is hidden.

Second parameter type is select and is written as

client::Client:select::Value1:Label1:Value2:Label2

In your case Value and Label would be the same as A:A:B:B.

So when you put it together you get comma separated parameter list

p1:A::hidden,client::Client:select::Value1:Label1:Value2:Label2

To access parameter you use $id, so it is $p1 and $client.

Edit:

Picture belowrun_macro.png

[Paul LaBarbera]

Thank you for your response! This isn't working for me... I'm trying to figure out what I'm doing wrong. With "p1:A::hidden,client...", what is "client" doing? I feel like that may be where the problem is. Here's what I currently have in the "replace" box: $p1:A::hidden,$client::Client:select::D:D:B:B

[xpauls]

Don't use $ in replace box. $p1 and $client is used in the macro body. Try using p1:A::hidden,client::Client:select::D:D:B:B "client" is the "key" or "id" for the client parameter.

[Paul LaBarbera]

Thank you. I really really appreciate your help... But it's still not working. Where are you getting "client" as the key? This may be tricky because I'm using a stored procedure. The query is just "exec sp_Open_Issues_by_Client ?" and the SQL macro has p1 set to A. I tried setting P1 to $A, but that didn't change anything. Again, I really appreciate your help.

[xpauls]

Added a Picture. The "$p1" and "$client" are replaced when you click Run. Your query must contain these parameters somehow. Query "exec sp_Open_Issues_by_Client ?" doesn't contain them. For example, the query should look like: select * from table where param1="$p1" and param2="$client"

[Paul LaBarbera]

OK... I'm using the SQL macro to replace the ? in the stored procedure with $A. I'm trying to replace that $A with the values in the replace field of the "run" macro.