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

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Mohit Saini

    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?

    (0) 
    1. Rogerio Plank Post author

      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

      (0) 
  2. Neil Mitchell-Goodson

    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 😉

    (0) 
    1. Rogerio Plank Post author

      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

      (0) 

Leave a Reply