Jul 06 2007
How many values were picked from a list?
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()
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.
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 SubI would be willing to bet that the simple string formula is faster. 🙂 But thanks for sharing the idea.
Well, color me embarrassed. 😳 There was another comment here and I deleted it when I meant to approve it instead. My apologies.
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
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.
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
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
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. 🙂
Dave,
Is it possible to bring what values are not selected in the report from the user input values after the run ?
Thanks.
I’m not sure I understand your question. 😕 If values are not selected in the prompt, how will they end up in the report?
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.
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.
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.
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. 😎
Hi Dave, Thanks for the correction. I dint think about this scenario while commenting on this post.
Hi Dave,
Can we restrict the number of values to select in a prompt? say 3 for example.
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()
Length([OBJECT NAME]) gives you the length of the object which is a “TEXT” data type.
What is the option if it is a “NUMBER” data type