How to change data source dynamically

[aamelyan]

Hi,

Is it possible to dynamically set the data source for the query below?
We have multiple environments and users want to run the same report against them by picking the environment from the drop down list on the report.

Production:
    | dbquery "InstrumentDB" "select top 10 * from Instrument.dbo.Program"

Staging:
    | dbquery "InstrumentDBStaging" "select top 10 * from Instrument.dbo.Program"

[niketn · Accepted solution]

Following is a sample you can modify <query> as per your need

<form>
  <label>Dropdown to switch testQuery based on Dropdown value</label>
  <fieldset submitButton="false">
    <input type="dropdown" token="selEnv">
      <label>Select Environment</label>
      <choice value="InstrumentDB">Stage</choice>
      <choice value="InstrumentDBStaging">Prod</choice>
      <default>InstrumentDB</default>
      <prefix></prefix>
      <suffix></suffix>
      <initialValue>InstrumentDB</initialValue>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query> | makeresults | eval testQuery="| dbquery "  + "$selEnv$" + "\"select top 10 * from Instrument.dbo.Program\"" | table testQuery</query>
          <earliest>-60m@m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</form>

[aamelyan]

Something else is missing. I am not getting actual data outputted but the query itself. Am I missing extra setting?

[niketn]

Yes that is because this example is just to show you that query is being passed as expected you will have to use the same in <search><query> tag in Simple XML.

For example:

 <search>
   <query> | dbquery  + "$selEnv$" + \"select top 10 * from Instrument.dbo.Program\"</query>
   <earliest>-60m@m</earliest>
   <latest>now</latest>
   <sampleRatio>1</sampleRatio>
 </search>