topic Re: How to change data source dynamically in Getting Data In

How to change data source dynamically

aamelyan — Thu, 05 Jan 2017 23:34:31 GMT

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"

Re: How to change data source dynamically

niketn — Fri, 06 Jan 2017 03:51:43 GMT

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>

Re: How to change data source dynamically

aamelyan — Fri, 06 Jan 2017 14:54:11 GMT

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

Re: How to change data source dynamically

niketn — Fri, 06 Jan 2017 15:14:48 GMT

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>