ORA-01795 and Reporting Services multivalue parameters
I have an issue currently using Reporting Services with Oracle. I have a multivalue parameter and when that parameter returns more than 1000 selectiobns I get the following error:ORA-01795: maximum number of expressions in a list is 1000I've tried various ways around this such as placing an expression onto the parameter:=String.Join(",",Split(Parameters!customer.Value.ToString, ",",1000)).ToString()=Join(Split(Parameters!customer.Value.ToString, ",",1000))Neither of these worked! They got rid of the error but they didn't behave as intended.Part of my problem is I don't actually know what my expression should be returning. What is the type of a multivalue parameter? Is it a String? A String array? I simply don't know and the documentation does not tell me.So, a few things arising from this. Can anyone dream upa way around this Oracle limitation? What is the type of the object returned by a multivalue parameter and, more importantly,am I able to construct such an object using the expression language? [To the SSRS team] I would love a built-in function that returnsa subset of all the values selected in a multivalue parameter. It would behave similar to a SUBSTRING function in that it would allow me to specify the first value and the total number of values i.e. MethodName(MultiValueParamName, FirstValueToReturn, TotalCountOfValuesToReturn). Do you think that isa good idea? SHould I request it on Connect? Any other informationor words of wisdom around this incredibly annoying problem would be much appreciated.thanks in advanceJamiehttp://jamiethomson.spaces.live.com/ | http://blogs.conchango.com/jamiethomson
April 27th, 2009 6:55pm

Submitted to Connect due to lack of reply:SSRS: New function to to return subset of a Multivalue parameter(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=435700)-Jamiehttp://jamiethomson.spaces.live.com/ | http://blogs.conchango.com/jamiethomson | @jamiet
Free Windows Admin Tool Kit Click here and download it now
April 28th, 2009 1:24pm

What you can do is break up the list of parameters into sets - use cascading parameters.
April 29th, 2009 9:57am

What you can do is break up the list of parameters into sets - use cascading parameters. Thank you Shiv, any links that show me how to do that?-Jamiehttp://jamiethomson.spaces.live.com/ | http://blogs.conchango.com/jamiethomson | @jamiet
Free Windows Admin Tool Kit Click here and download it now
April 29th, 2009 11:21am

What you can do is break up the list of parameters into sets - use cascading parameters. Shiv,I'm reading:How to: Add Cascading Parameters to a Report (http://msdn.microsoft.com/en-us/library/aa337169.aspx)It states:"You can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter. For example, the first parameter is independent and might present a list of product categories. When the user selects a category, the second parameter is dependent on the value of the first parameter. "I'm not understanding how that might help in my situation. Can you explain?-Jamiehttp://jamiethomson.spaces.live.com/ | http://blogs.conchango.com/jamiethomson | @jamiet
April 29th, 2009 11:30am

Hello Jamie,Check this out:http://forums.oracle.com/forums/thread.jspa?threadID=233143http://www.orafaq.com/forum/t/41437/0/ Since Oracle is the backend tackling the issue at oracle might be a good idea.Cheers,forsayi
Free Windows Admin Tool Kit Click here and download it now
April 30th, 2009 9:27am

Let's see: - If the parameter list is just a static list, with no links to other tables/categories, then what you could do is to define your first parameter as a list from say, A to Z. Then the second parameter can take the value selected from the first parameter and then populate a list with the specified parameter (letter). - If there is a link to other tables/categories, then simply have a selection for one category and then use that to filter the second parameter set. See the links posted here for more info. Hope this helps.
April 30th, 2009 10:58am

Hello Jamie,Check this out:http://forums.oracle.com/forums/thread.jspa?threadID=233143http://www.orafaq.com/forum/t/41437/0/ Since Oracle is the backend tackling the issue at oracle might be a good idea.Cheers,forsayi Thanks Forsayi,At the moment I am going with the option of multiple OR predicatesin my WHERE clause but that's not ideal because now I have multiple dropdown boxes on my report from which the user has to pick the value(s) he/she is interested in.You also have to do a hacky workaround for the eventuality where the user doesn't pick anything from one of the dropdown boxes because Reporting Services expects that at least one value will be selected from every dropdown and it won't let you run the report until you do.It works but its not great.-Jamiehttp://jamiethomson.spaces.live.com/ | http://blogs.conchango.com/jamiethomson | @jamiet
Free Windows Admin Tool Kit Click here and download it now
April 30th, 2009 12:25pm

Let's see:- If the parameter list is just a static list, with no links to other tables/categories, then what you could do is to define your first parameter as a list from say, A to Z. Then the second parameter can take the value selected from the first parameter and then populate a list with the specified parameter (letter).- If there is a link to other tables/categories, then simply have a selection for one category and then use that to filter the second parameter set.See the links posted here for more info.Hope this helps. No, it doesn't unfortunately. Your solution still doesn't prevent the user from potentilly passing more than 1000 items into the dataset query's IN predicate.Stil waiting for some from Microsoft to answer regarding my idea of providing a function that would allow us to pull out a subset of a multivalue parameter list.-Jamie http://jamiethomson.spaces.live.com/ | http://blogs.conchango.com/jamiethomson | @jamiet
April 30th, 2009 12:31pm

Here are some failed solutions and a solution that works… This problem is caused by the fact that in Oracle a comma-delimited list of expressions can contain no more than 1,000 expressions. Microsoft SQL Server Reporting Services (SSRS) lets you run a query that populates a check-box drop-down, and when the user hits “Select All” the values get passed as a single-quoted comma-separated list of values to the parameter used in the SQL of the report, triggering the Oracle error when it exceeds 1,000. Changing SQL does not seem to be a solution. Defining a function that takes a string returning a TABLE TYPE or ARRAY, which then goes to a “in ( Select * from TABLE(CAST(” works for small sets but not when the literal string has more than 4,000 characters. Even literals in the form of Q’{…}’ (to hide the imbedded single quotes) can’t have more than 4,000 characters. The SQL-only method below converts the parameter to a table function fails when the literal string has more than 4,000 characters, which is typically the case when you have more than 1,000 items. For future reference here is that approach if you ever need it: --execute one-time definitions outside of SSRS CREATE OR REPLACE TYPE StringTable AS TABLE OF VARCHAR2( 250 ); / CREATE OR REPLACE FUNCTION f_string_table ( p_list IN VARCHAR2 ) RETURN StringTable IS v_delimiter CHAR(3) := ''','''; v_string LONG := SUBSTR( p_list || ',''', 2 ); v_pos pls_integer; v_data StringTable := StringTable( ); BEGIN LOOP v_pos := instr( v_string, v_delimiter ); EXIT WHEN( NVL( v_pos, 0 ) = 0 ); v_data.extend; v_data( v_data.count ) := trim( SUBSTR( v_string, 1, v_pos - 1 ) ); v_string := SUBSTR( v_string, v_pos + 3 ); END LOOP; RETURN( v_data ); END f_string_table; / --test SELECT * FROM TABLE( CAST( f_string_table( Q'{'aaaa','bbbb','c','xxxx'}' ) AS StringTable ) ); --in the SSRS SQL change this --IN(:myparm) --to this --IN(select * from table(cast(f_string_table(Q'{:myparm}') as StringTable)); Converting the parm string to a multi-row Table Value Constructor does not work either, due to the same 4,000 character limitation, and due to the fact that Oracle, unlike SQL Server, does not allow multi-row Table Value Constructors in the form of: SELECT * FROM (VALUES (101, 'Bikes'),(102, 'Accessories'),(103, 'Clothes')) AS Category(CategoryID, CategoryName); So it appears you need to change both the user interface in SSRS and the Oracle SQL, by either, (1) adding check-boxes for when a user wants to select all the values in a domain, or (2) alter the query that populates the drop-downs such that it adds a “ SELECT ALL” choice to the values that the final query uses to control predicates. Here is how to do (2): Add a new item at the head of the list with the literal value “ SELECT ALL” and make “ SELECT ALL” the single default value for the parameter, instead of checking ‘Select All Values’ as the defined default setting. My assumption is that no user would actually want to take the time to manually select a subset with more than 1,000 items for a set with more than 1,000 items unless they wanted to select all items. The exception to this, which is not solved below, is the situation where a user wants to select all 1,000+ items *except* a few items. In this solution the drop-down will show both “Select All” and “ SELECT ALL” but with “ SELECT ALL” checked by default. If the user ignores this and clicks on “Select All” (which actually causes all values to be passed to the query) no harm or error will be done unless the number of items exceeds 1,000, which will teach the user to put it back to “ SELECT ALL”. Users with a smaller domain of parameter values will not be impacted with either selection. A secondary impact of this approach below may be that queries run a little faster with “ SELECT ALL”. Find the parameters that exceed 1,000 items. For these parameters do the following: Insert the following line above the ORDER BY clause that provides the drop-down values to the user: UNION ALL SELECT ' SELECT ALL' FROM DUAL The initial space in the first character of ' SELECT ALL' is for the purpose of causing it to sort to the top and to prevent it from matching a value found in the data. (You may need to add a column with a -1 if your parameter returns an identifier and use -1 to signify the select all case.) In the report’s query, wherever you see the parameter, such as: AND M.MATERIALDESC IN (:MyParm) Change it to: AND (' SELECT ALL' IN( : MyParm) OR M.MATERIALDESC IN( : MyParm)) Note: keep the OR’d predicates in the order shown as Oracle processes OR left-to-right and eliminates checking the second predicate after the first TRUE. This helps performance. In the SSRS parameter definition make “ SELECT ALL” the single default value for the parameter, instead of checking ‘Select All Values’ as the defined default setting. Not pretty but it works.
Free Windows Admin Tool Kit Click here and download it now
July 18th, 2011 10:24am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics