Jul 06 2007

How many values were picked from a list?

Categories: Report Techniques, Variables! Dave Rathbun @ 1:06 pm

The Challenge

It is not uncommon for a report writer to want to know how many items were selected from a prompt. The challenge is that there really isn’t a way to do that, at least not one that is available as a function. So this article will show an interesting way to solve that challenge. It is not my original idea; it came from a post on BOB. At the moment I cannot find the post in order to give credit to the original author.

In a nutshell what is required is as follows:

  • Capture the user’s response to the prompt
  • Count the number of semi-colon characters found in that string

My first instinct was to look at the Replace() function as it is somewhat similar to the translate() function in Oracle. With the translate() function I can easily remove everything but the semi-colons. By taking the length of the resulting string I can determine how many semi-colons there were. Unfortunately the Replace() function in Business Objects does not work the same way. Instead of keeping the semi-colon I would have to throw everything else away, and that’s just not going to work.

So the trick is to throw away the semi-colons instead. :-) This trick works in both Web Intelligence and Business Objects / Desktop Intelligence. The syntax shown in this post is for Web Intelligence only.

The Solution

Here’s how it works. I created a report from the eFashion universe which should be available in any standard installation. In that report I prompted for a list of stores. The prompt text was Select Store(s). The UserResponse() function is available in both products, and is used to retrieve the value of a response that a user made in reaction to a report prompt. On the report I created a variable that captured the list of stores that were selected using the following syntax. This variable was saved using the name Selected Stores.

=UserResponse("Select Store(s)")

As an aside, I will often build variables in steps like this. It makes testing and debugging easier.

The next step is to figure out how many semi-colon characters appear in that string. This is where the trick comes in. The Replace() function allows me to take a character (or character pattern) and replace it with something else. In this case I want to take a semi-colon and remove it. To do that, I use the following syntax:

=Replace([Selected Stores];";";""))

This is not a complete solution but instead is the next step on the way. What this part does is take the “;” and replace it with “” which is an empty string, effectively removing the semi-colon from the string. But the semi-colon is how I separate one prompt selection from another. How does removing it allow me to count the number of values?

By comparing the length of the original user response string with the length of the string after the semi-colons have been removed, I can “count” the number of semi-colons. Consider the following list of values:

orange;red;blue;green

There are 3 semi-colon characters. Remove them, and you get this:

orangeredbluegreen

The second string is 18 characters long, the first is 21 characters long. If you subtract the smaller length (without semi-colons) from the longer (with semi-colons) you get 3. And that number matches the number of semi-colons in the string. Add one to that number and you get the number of selections that were made.

This does, however, assume that you will not have semi-colon characters in your data. If you do, this trick will not work. But if not, here is the final variable (which I called Selected Store Count):

=Length([Selected Stores]) - Length(Replace([Selected Stores];";";"")) + 1

Then to wrap up I create a new formula that will be used in the report header. In the eFashion database there are 13 stores. I have decided that if a user selects 6 or fewer stores I will list them, otherwise I will provide a simple statement that says how many stores were selected. Since the formula shown above is called Selected Store Count here is what that final formula looks like:

=if([Selected Store Count]>=7;"You selected " + [Selected Store Count] + " stores";[Selected Stores])

Summary

The challenge presented at the beginning of this post was to provide a way to count how many items a user selected in response to a prompt. As long as the data does not include any semi-colons this trick seems to work. The formulas here were presented in Web Intelligence format but should work (with proper adjustments) in the desktop products as well.

Functions Used

  • Replace()
  • UserResponse()

18 Responses to “How many values were picked from a list?”

  1. Comment by Rhonda

    That’s an interesting idea. We’ve sort of gone with the standard that you get what will fit in the box, so this will be a nice way around that. Thanks for posting it. In 6.5 FC, you can’t use data that contains semi-colons in it, BTW. At least, you won’t get what you expect in the results, in my experience.

  2. Comment by Tamir Bar_Netzer, Israel

    hi,

    Copy and paste the following code for the After Refresh event of the document. Replace “Enter Sales Person” with the Text of the prompt it`s values you are trying to count.

    As a result a new variable named ‘Prompt Counter’ is created. You can use it where ever you like in the report

    Private Sub Document_AfterRefresh()
    Dim Val As String
    Dim i As Integer
    Dim counter As Integer
    Dim Flag As Boolean
    Dim DVs As DocumentVariables
    Dim DV As DocumentVariable
    counter = 1
    Val = ThisDocument.Variables.Item("Enter Sales Person").Value
    For i = 1 To Len(Val)
     If Mid(Val, i, 1) = ";" Then counter = counter + 1
    Next i
    
    Flag = False
    Set DVs = ThisDocument.DocumentVariables
    
    For Each DV In DVs
      If DV.Name = "Prompt Counter" Then Flag = True
    Next
    
    If Flag = False Then
      Set DV = DVs.Add(0, "Prompt Counter")
    Else
      Set DV = ThisDocument.DocumentVariables.Item("Prompt Counter")
    DV.Formula = counter
    
    End Sub
    
  3. Comment by Dave Rathbun

    I would be willing to bet that the simple string formula is faster. :) But thanks for sharing the idea.

  4. Comment by Dave Rathbun

    Well, color me embarrassed. :oops: There was another comment here and I deleted it when I meant to approve it instead. My apologies.

  5. Comment by Shiva

    Hi Dave,

    I’m quite New to BO still as i’ve worked only for 2 yrs or so… And Want to learn SDK .. Could you please guide me where to start as I find a lot of things that are possible and they are like miracle… but to do that i need SDK understanding.

    Just help me in getting started.

    I’m apologize if i’m posting it at a wrong place.. but i know i’ll get perfect answers here.

    Regards,
    Shiva

  6. Comment by Dave Rathbun

    Hi, Shiva, I appreciate your enthusiasm :) but as stated elsewhere my blog isn’t for personal support. I ask that your comments or questions be related to the post. For anything beyond that I suggest you look for information (or post your question) on BOB.

  7. Comment by Shiva

    Hi Dave,

    Thanks for the response… I checked on BOB forum.. got my answer… i saw a post which you’ve made sticky.

    Regards,
    Shiva

  8. Comment by M Mohammed

    Hi Dave,

    I have another way to do that and its very simple. I didn’t think above and beyond or pros and cons of this method. What if we have a blank cell with the formula as “=Count([Selected Stores]) In Report” or “=Count([Selected Stores]) In Block”

    I know, the keywords Report, Block etc are used in different scenarios, but this formula is short. Isn’t it?

    Let me know your thoughts.

    Thanks,
    M Mohammed

  9. Comment by Dave Rathbun

    Your formula works to count how many values appear in the report, not how many values were selected. Think about it a few minutes and see if you can pick up on what the difference is. :)

  10. Comment by Rajesh

    Dave,

    Is it possible to bring what values are not selected in the report from the user input values after the run ?

    Thanks.

  11. Comment by Dave Rathbun

    I’m not sure I understand your question. :-? If values are not selected in the prompt, how will they end up in the report?

  12. Comment by Rajesh

    Say you entered 10 values in a prompt and the report returns only 7 values as because it has no information to return on the 3 missing values. So my question here is, Is it possible to show those 3 missing values from the prompt in the report ? Hope you got my point.

    And thanks for all the work you been doing in the blog. Very Thanks.

  13. Comment by Dave Rathbun

    Ah, I see. You can count how many values were picked (10) and how many values were on the report (7, using a count formula) and easily subtract the two to get the number of values that did not show up. To get the values that didn’t show up you would have to get creative with some string functions, I expect. I’m not sure what that would look like right now.

  14. Comment by Chakri

    Hi Dave,Your blog helped me a lot in understanding the concepts and would like to thank for your effort to help the BO community. I think what M Mohammed said in his comment is correct. When you have applied a filter on stores (a prompt in this case) and have the stores object in the result query pane (Though not shown/used in the report) you will get exactly those store values which were selected while prompted. So you can simply use the count() function to count the number of stores (Which will be the same as the number of values selected). I think this is what M Mohammed intend to say.Ofcourse i dint find any reason to use “in report” or “in block” function. Having said that, this logic stands defeated if the user’s requirement forbids us to use stores object in the result query pane. Correct me if i am wrong.

  15. Comment by Dave Rathbun

    Hi, Chakri, welcome. There are actually two different things: how many values did a user select, and how many show up on the report. They are not always going to be the same. Suppose I have six months of data in my fact table, but I have twelve months in my calendar table. During the query I will select all twelve months in the prompt, but only six months can possibly show up on the query results because that’s all that exists. How many values are on the report? Six. How many values did the user select? Twelve. So you see how they can be different. 8-)

  16. Comment by Chakri

    Hi Dave, Thanks for the correction. I dint think about this scenario while commenting on this post.

  17. Comment by Pavan

    Hi Dave,

    Can we restrict the number of values to select in a prompt? say 3 for example.

  18. Comment by Dave Rathbun

    Unfortunately not. If you need to restrict to exactly three values, you can build three single-select prompts and combine them with an OR operation.
    table.column = @prompt1()
    or table.column = @prompt2()
    or table.column = @prompt3()

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:

                 [

Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.