Skip to Content

How to pass a parameter to a WEBI doc

Hi All,

Today I would to share a way to pass a parameter to a WEBI document through a prompt..

Here´s the cenário :

I would like to display in a chart with data from e-fasion a threshold of [Sales Revenue] so that, if the sales are above that threshold, I could give na alert..

Let´s work wth a threshold of 6,000,000.

Here´s how my query looks like

/wp-content/uploads/2014/12/query_603432.jpg

And my Report

/wp-content/uploads/2014/12/table_603433.jpg

One can easily achieve it by setting na alarm on the column like

/wp-content/uploads/2014/12/alert_603480.jpg

But what if this threshold value is dynamics so users wants to alter it whenever they need it. ?

This solution gets the user this ability by simply setting a prompt when refreshing the document.

To do so, I create a “dummy” query filter. What I call a “dummy” query filter is a filter that always evaluates true, independen of the value entered.

For the puspose of this example , I will create a “dummy” filter on State.

Here´s how it Works :

1 – Create a filter on State with a prompt

/wp-content/uploads/2014/12/query_603432.jpg

2 – In your report , create a measure variable [Threshold] = ToNumber(UserResponse(‘Threshold’);”00000″).

Since  a string starting with a number character is always less than any string starting with a letter, the “dummy” filter will always evaluates, independent of the value enterered.

Running a query with a threshold value of 1500000

running the query.jpg

3 Set the alert to

/wp-content/uploads/2014/12/alert2_603484.jpg

And that´s it

Cheers,

Rogerio

Comments are mostrly welcome

4 Comments
You must be Logged on to comment or reply to a post.
  • Hi Rogerio,

    Thanks for your post.  It is really useful.  Just for my understanding I have two queries:

    Q1:

    In screen shot 1 with title “1 – Create a filter on State with a prompt” – you have
    Prompt text = Enter State:

    In screen shot 2 – you have “In your report , create a measure variable [Threshold] = ToNumber(UserResponse(‘Threshold’);”0,000.00)”

    I think between braces of userresponse we should be having “Enter State:” or in the
    prompt text we should be having Prompt text = Threshold.  Both need to be equal and cannot have different text right?  Please correct me if I am wrong.

    Q2:

    Also can you please explain “ToNumber(UserResponse(‘Threshold’);”0,000.00)”” syntax.
    I am unable to understand it.

    Also from a user perspective – a user wouldn’t like to enter an amount with 3 extra zeroes (such as 1500000) he would rather would like to enter 1500 only. right?

    • Hi Monti,

      thanks for your comments.

      You was absolutely right in Q1, I have just corrected it , in fact the text for the prompt is Threshold, that´s why I used  UserResponse(“Threshold”). I also corrected the formula

      The reason for the formula ToNumber(UserResponse(‘Threshold’);”0,000.00″) to ToNumber(UserResponse(‘Threshold’);”0000″). The reason for the use of ToNumber is that UserResponse returns a text. So I need to convert it to a number.

      And yes, it´s better , from a user perspective to tpye 1500 instead of 1500000. But that´s just for purpose of explainig it, the Threshold could be any number. In my case, was 1500000, but could be 1500500.

      Cheers,

      Rogerio

  • Subverting a dimension to use as a ad-hoc numeric prompt is an interesting learning point, but I’d suggest it’s only ever going to be useful where a simple Input Control isn’t available, per your example.

    Plus, irresponsible choices of Dimension to hijack could introduce problems such as context selections, etc.

    Nice little hack though 😉

    • Hi Neil,

      thanks for your comments.

      This was initially thought to be used with OpenDocument and a more complicated parameter ( an array of data or something like that) in which an URL could be automatically generated by another application.

      Cheers,

      Rogerio