04 Parameters examples

Basic

Scenario 1 - static default value on server side

<srs label="Parameters defaults">
  <def>
    <itm model="param" name="param1">default</itm>
    <itm model="param" name="param2">default</itm>

    <itm>
      <![CDATA[
        select @param1 test1, @param2 test2
      ]]>
    </itm>

  </def>
</srs>

Scenario 2 - dynamic default value on server side

Sometimes it's necessary to assign default values to parameters dynamically based on business logic. This can be accomplished using the srssetup command, by matching columns with the corresponding variables, such as [[datefrom]].

<srs label="Parameters defaults">
  <def>
    <itm model="param" name="param1" type="date">[[datefrom]]</itm>
    <itm model="param" name="param2" type="date">[[dateto]]</itm>

    <itm model="command" name="srssetup" opts="server"><![CDATA[
    select 
      convert(varchar, DATEADD(day, -360, GETDATE()), 23) datefrom, -- last year
      convert(varchar, EOMONTH(getdate()), 23) dateto --end of month
    ]]></itm>

    <itm model="command">
      <![CDATA[
        select @param1 test1, @param2 test2
      ]]>
    </itm>
  </def>
</srs>

Scenario 3 - pass clipboard to parameter

This example requires system UI.

This example demonstrates how to use option:pa_post_clipboard.

Scenarios:

• Create an Excel file with a list of items filtered by the selected items.
• Execute business logic for all selected items.

<srs title="TEST">
  <def>
    <!--PARAMETERS-->
    <itm model="param"  name="clipboard" opts="json-xml-root"></itm>

    <!--OPTIONS-->
    <itm model="option" type="pa_post_clipboard" label="Download a file">
        <![CDATA[/api/srs/337/data.xlsx?pscope=data&filename=Filtered_Tables.xlsx]]>
    </itm>

    <!--COMMANDS-->
    <itm model="command" name="A" opts="clipboard">
      <![CDATA[
        SELECT 
          ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA, TABLE_NAME) AS ID,
          TABLE_SCHEMA,TABLE_NAME ,TABLE_TYPE 
        FROM INFORMATION_SCHEMA.TABLES;
      ]]>
    </itm>

    <!--COMMAND-->
    <itm model="command" name="data" opts="pscope"><![CDATA[

/*
      PRINT cast(@clipboard as varchar(max))  --> RESULT:
      <root>
        <row><ID>1</ID><TABLE_SCHEMA>accounting</TABLE_SCHEMA><TABLE_NAME>customeAccounting</TABLE_NAME><TABLE_TYPE>BASE TABLE</TABLE_TYPE></row>
        <row><ID>2</ID><TABLE_SCHEMA>accounting</TABLE_SCHEMA><TABLE_NAME>salesConfiguration</TABLE_NAME><TABLE_TYPE>BASE TABLE</TABLE_TYPE></row>
      </root>
*/

      declare @xml xml = @clipboard;     

      SELECT 
        ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA, TABLE_NAME) AS ID,
        TABLE_SCHEMA,TABLE_NAME ,TABLE_TYPE 
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME in (

        SELECT
          x.value('(TABLE_NAME)[1]', 'nvarchar(128)') 
        FROM
          @xml.nodes('/root/row') AS T(x)

      )

    ]]></itm>

  </def>
</srs>