Loading Parameter Values – Hard Way vs Easy Way

There are many ways to do this, but I thought that I would write an article on how to load available values for a drop down list box parameter in reporting services. Let’s say that you wanted to provide a numerical value to represent status values. Perhaps your company has a status tracker for tasks and it could range from 0% - 100% complete. If you wanted to load the parameter to be in 5% increments the HARD WAY, you may add them all manually like I did in figure 1 below.

Figure 1: Manually Adding Available Values

Now maybe there is reason that you would want to do it the hard way, but just know that there is an easier way.

You may opt to do something a little quicker like generating the numbers via a script. I have a script that I am going to provide below, but let me tell you what it is doing so that you can implement it in your environment and make any changes that you need.

First, I am creating a table to hold a unique listing of values.

CREATE TABLE #Numbers (Value TINYINT) 

Next, I am declaring some of my variable to hold the starting number, ending number, current number and what number you want to increment by.

DECLARE @StartNum TINYINT

      , @EndNum TINYINT

      , @CurrentNum TINYINT

      , @IncrementBy TINYINT

Here, I am setting the default values. If you are using 2008, you should be able to set the values when you declare them.

SELECT @StartNum = 0, @EndNum = 100, @CurrentNum = 0, @IncrementBy = 5

Then, I am writing a while loop through from the starting number all the way to the ending number with inserting the current value into the temporary table created above.

BEGIN

WHILE @CurrentNum <= @EndNum

      BEGIN

            INSERT INTO #Numbers SELECT @CurrentNum

            SELECT @CurrentNum = @CurrentNum + @IncrementBy

      END

END

Finally, I am returning a list of the numbers that can be used by my parameter

SELECT * FROM #Numbers

Complete Script: Generate Numbers

--Temporary Table

CREATE TABLE #Numbers (Value TINYINT)

--Declaring My Variables

DECLARE @StartNum TINYINT

      , @EndNum TINYINT

      , @CurrentNum TINYINT

      , @IncrementBy TINYINT

--Setting the default values

SELECT @StartNum = 0, @EndNum = 100, @CurrentNum = 0, @IncrementBy = 5

--Loop Through from Beginning to End

BEGIN

WHILE @CurrentNum <= @EndNum

      BEGIN

            INSERT INTO #Numbers SELECT @CurrentNum

            SELECT @CurrentNum = @CurrentNum + @IncrementBy

      END

END

--Now Return My Results Please

SELECT * FROM #Numbers

Results of executing the entire script above should give you results as shown in figure 2.

Figure 2: Results

Now all you’ll need to do is create a new dataset named StatusValues and load the parameter based off of the query results of the StatusValues dataset as shown in figure 3. Note that this will look different if you are using 2005.

Figure 3: Parameter Available Values

Now that we’ve got that all done, let me suggest yet another option! You could even create a stored procedure with parameters for all of the parameters that we declared. Then from the dataset properties, you could set the values you want to send into your stored procedure. Like I said, it is yet another way to achieve the desired results.

Until next time, “keep your ear to the grindstone” – Good Will Hunting

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter