How can I execute Universe queries dynamically in Dashboards 4.1?

[Former Member]

Hi all,

I am building an application using Dashboards 4.1; up until now I have been connecting via a UNX file to the back-end (SQL Server Database) using Query Browser.

Depending on different actions by the user, I would like to run different queries against the database. For example, I have one query that returns amounts by volume (cubic metres), another by mass (KG). It is better for performance to use simple queries like this.

I am having some difficulty working out how to tell Query Browser to execute one query, and not the other, depending on the unit of measure selected. Note that the dashboards must be refreshed whenever the user selects a dimensional value (location, date range, etc.) - that's the real difficulty.

Could anyone point me in the direction of a design pattern or some examples that might help me out?

Thanks in advance.

[peter_warren_uk]

You should be able to use excel formula to perform the logic

use the result of the formula i A6 to trigger the query

I am assuming that you can get the location and the UOM back as part of the query result. You can then compare the last retrieved values with the values from the selector objects in the dashboard.

If the currently selected UOM and selected location are the same as those retrieved by the last query result do nothing, If either the selected location or the selected uom is different from the last query result run either query A or query B depending upon the UOM in the selector

hope this helps

[Former Member]

You can achieve this easily by using a trigger cell for your query browser queries and then having your components insert the desired value based on user selection into the designated cells..

search for  for trigger cells in Scn and you will get many blogs explaining them in details..

[Former Member]

Thanks Durgamadhab.

I understand about the use of trigger cells, although I will search SCN again as you suggest.

The problem is that the triggering conditions are quite complex.

Say I have query A and query B. Query A only is run when the user selects KG as unit of measure (UOM), Query 2 when cubic metres are chosen. That is easy to implement. However, Query A and B are also run when the user selects a new location. So the full conditions are:

Query A: run when UOM is first picked as KG or (a new location is picked and UOM is already KG)

Query B: run when UOM is first picked as cubic metres or (a new location is picked and UOM is already cubic metres)

Maybe I'm missing something obvious, but I'm finding it a struggle to write formulas to capture this logic.