Reporting Services Multi-Value parameter and Oracle database

Parameterization is not a difficult implementation with Reporting Services as most of the coding are automatically added either via the data source dialog box or the wizard. It adds necessary coding as well as report parameters. Not only single-select type parameters, it handles multi-select or multi-value parameters with appropriate filtering codes. However this becomes little bit complex when parameters are loaded from different sources. It becomes more complex (for me :)) when the selected values of parameters have to be passed to Oracle as the main source of the report is Oracle.

I had couple of parameters loaded from a source, to be precious, from Analysis Services dimensions and selected values have to be passed for a function implemented in a Oracle package for loading the main dataset. Similar implementation is required for few more reports hence I looked for a generic way to handle it. Searches made opened many ways of doing this, some of them are for specific scenario, some of them are shortcuts, some of them are combination of different approaches. Finally figured out a way to handle the situation, usefulness of solution for others is assumed, hence posting it.

Note that the way I have implemented Oracle objects is based on the knowledge gathered from internet without doing a study on Oracle in a structured and ordered manner, hence this will not be the best way of implementing this.

Okay, let's talk about the scenario again. I have two parameters, let's call them as rpProduct and rpCountry and "Allow multiple values" is checked on both. They are loaded from Analysis Services dimensions. Main dataset is loaded from an Oracle function called LoadSales which is in the package called pkgSales. The WHERE clause of the SELECT statement has to be handled for values coming from two parameters and better to have a generic way as same implementation is required for multiple reports.

This is what I did. Initially, I created a Type in Oracle database for holding multiple values. It is a Table type and I used varchar2(200) as the size for an individual item.

create or replace type t_string_table is table of varchar2(200);

Then I created a function that can be used for converting comma separated string values in to a collection. As you see, return type of it is the type I created with above code. I used some coding for this from: http://www.oratechinfo.co.uk/delimited_lists_to_collections.html

create or replace function Convert_stringlist_to_table(string_list in varchar2) return t_string_table is

   l_string varchar2(32767) := string_list || ',';
   l_comma_index pls_integer;
   l_index pls_integer := 1;
   l_tab t_string_table := t_string_table();
begin
  
         loop
           l_comma_index := INSTR(l_string, ',', l_index);
           EXIT WHEN l_comma_index = 0;
           l_tab.EXTEND;
           l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
           l_index := l_comma_index + 1;
         end loop;
      return l_tab;
end Convert_stringlist_to_table;

Next, changed the SELECT of LoadSales function. Function accepts two parameters, one for products and other for countries. Values are expected to be comma-separated. As you see, values are converted to collections using the function we wrote before using them with WHERE clause.

  function LoadSales
    (v_products in varchar2, v_countries in varchar2)
     return sys_refcursor
     is cur sys_refcursor;
  begin
    
    open cur for
      select {column list}
      from {table list}
      inner join .......
      where country_name in (select column_value  from TABLE(cast (Convert_stringlist_to_table(v_countries) as t_string_table)))
            and product_name in (select column_value  from TABLE(cast (Convert_stringlist_to_table(v_products) as t_string_table)));
    
    return cur;
  end;

Last step is configuring the dataset for parameters. Since we need to send selected values from Report Parameters as comma-separated string values, expression should be written as;

=Join(rpProduct.Lable, ",")

That is all. And good thing is, whole thing can be reused with all string type parameters related to other reports that require same implementation. You might have to change the code if you expect commas in parameter values.