Dave's Adventures in Business Intelligence

Several years ago I wrote a post that has generated a fair number of follow-up questions and comments. The post was related to splitting user response values onto separate rows and it used some basic string operations to do that. The important distinction is that the values were on different rows but remained in a single cell, which meant the output was suitable for a header cell.

Recently I got a comment that posed this question:

In one of my reports there is prompt to select the Fiscal Year and the user can select multiple LOVs. Prompt Name is “Year”. Say for example the user enters 2011,2012 and 2013. On using the function userresponse the report will show 2011;2012;2013

My requirement is to identify minimum value from the LOVs that the user has entered. In this case the report should show the mininum value as 2011. Can you please guide me on how to achieve this?

Identifying The First Value In A Set

Hm. If I am able to redefined “minimum” to “first” then the question becomes trivial. All I have to do is use basic string operations to find the first delimiter and extract the text up to that point. For this example I created a very simple report from eFashion with a prompt on the Year object. The prompt text was “Years:” and the formula for the Selected Years variable looks like this:

=UserResponse("Years:")

The output from this variable looks like this: 2001;2002;2003

How do I find the first value? First use the Pos() function to obtain the location of the first delimiter.

=Pos([Selected Years];";")

Now I can easily extract the first year from the string using this:

=Substr([Selected Years];1;[First Delimiter]-1)

I subtract one from the [First Delimiter] value because I want to stop one character before the ; rather than include it. This will return 2001 based on my original data. However, if the user enters 2003;2002;2001 my variable will fail because it’s not really selecting the minimum value, it’s selecting the first value. If I knew that the values would always be in reverse order, I can use other string functions to return the last value rather than the first.

Finding The Minimum Value In A Finite Set

Even if I don’t know what order the values are in I can determine the minimum value of a finite list as long as I know how many values there are. In this example I have three values to pick from, and I need to extract each of them in order to compare. In the prior section I have already identified a formula that would return the first year value as follows:

=Substr([Selected Years];1;[First Delimiter])

To find the second value I first figure out how far into the rest of the string the second delimiter is using this:

=Pos(Substr([Selected Years];[First Delimiter]+1;999);”;”)

And then use that for the length of the second value as shown here:

=Substr([Selected Years];[First Delimiter]+1;[Second Delimiter]-1)

The last of the three values is easy because I already know where the second delimiter is so the formula becomes:

=Substr([Selected Years];[First Delimiter]+[Second Delimiter]+1;999)

At this point I have three variables called First Year, Second Year, and Third Year. Now the fun begins. 🙂 With three values I have the following options:

1 < 2 < 3
1 < 3 < 2
2 < 3 < 1
2 < 1 < 3
3 < 1 < 2
3 < 2 < 1

That’s six different cases to check! It’s possible with a nasty “If” statement that would look something like this:

=if [First Year] < [Second Year] and [Second Year] < [Third Year] then [First Year] else if [First Year] < [Third Year] and [Third Year] < [Second Year] then [First Year] else if [Second Year] < [Third Year] and [Third Year] < [First Year] then [Second Year] else if [Second Year] < [First Year] and [Third Year] < [Third Year] then [Second Year] else if [Third Year] < [First Year] and [First Year] < [Second Year] then [Third Year] else if [Third Year] < [Second Year] and [Second Year] < [First Year] then [Third Year]

Why don't I have to check less than or equal to? Remember that I am selecting from a list of values, which by definition will be a distinct list. There won't be any duplicates.)

It's ugly, but it works. No matter which way I enter my three years as a response to my prompt, the formula above returns the correct value of 2001. But I imagine that anyone looking at this solution is already a step ahead of me. There are two major issues with this solution. First, it requires me to know ahead of time exactly how many values can possibly be entered into the prompt. Second, it requires me to split the string into that exact number of variables (three in this case) and then build a nasty "If" expression to identify the smallest. Ignoring the splitting part for now, what would an "If" statement look like that had to compare four values? Five? Forty Two?

A Better Solution To Identify The Minimum Value From A Prompt

In the Web Intelligence document that I created to demonstrate the above issue I started with only one data provider. It returned the Year, the Store Name, and the Sales Revenue. The values selected in the prompt were included in a cell in the header of the report. Now I want to break out the values and show the minimum and maximum instead, and there's a better way to do it. I am going to create a second data provider.

The data provider described above has three objects and one condition. I am going to create a second data provider that includes only the Year object, and it shares the same prompt. When I run the query of course I only get prompted once. The new data is going to be included in the report but I am not going to display it as a separate block. I don't need it to be displayed, I just want the data. And with the data provider technique I no longer have a single string that I am worried about processing... instead I have rows of data.

With rows I can use standard aggregate functions like Min() and Max(). My smallest and largest year formulas become:

=Min([Year List].[Year]) In Report
=Max([Year List].[Year]) In Report

... where [Year List] is the name of the data provider that returns only the year values. The context of "In Report" is required in order to set a global scope for the calculation, ensuring that I do get the largest and smallest years across the entire data set, not just within a row.

I am making an assumption that the person asking the original question would probably want to add these values to a report header, so I tested this formula:

="From " + [Min Year] + " to " + [Max Year]

That formula plays fast and loose with some implied data conversion issues (the minimum and maximum years are numeric, and the other values are text strings) but it does work. The results would be "From 2001 to 2003" displayed in the header.

This is a perfect solution, right? It doesn't require me to do a massive "If" statement and it doesn't matter how many values are selected by the user. It works with a single value, two values, three values, or even forty two values. 😉

Well, no. There is at least one problem with this solution. If the user enters 2001 through 2005 as input values, then this formula will only output values that matched in the database. Since 2004 and 2005 don't exist in my version of the eFashion database, they won't be included in the min / max calculation, and the header string will show exactly what is returned in the report rather than what was requested. Is that a bad thing? I will leave that up to you to decide.