none
Unable to Pass more than 750 values in multiparameter

    Question

  • Hi ALL ,

    At max the multi paremeter drop down is supporting 750 distinct values .But my requirement is more than 6000 distinct values .

    I tried the Below suggested workaround as well but no success :


    <appSettings>
    <add key="aspnet:MaxHttpCollectionKeys" value="30000" />
    <add key="aspnet:MaxJsonDeserializerMembers" value="30000" />
    </appSettings>

    Kindly Help . Very urgent

    Thanks in Advance

    Priya

    Thursday, January 09, 2014 10:02 AM

All replies

  • There is limitation of values in the dropdown list box of SSRS. http://stackoverflow.com/questions/9113446/what-is-maximum-limit-of-items-in-ssrs-2005-multi-value-parameter

    Another alternative is to increase the number of parameter. You can use ROW_NUMBER() in the query and then use WHERE caluse to filter range in the Dataset while creating parameter.

    Parameter1 -  1 to 750

    Parameter2 - 751 to 1400 .....


    Regards, RSingh



    Thursday, January 09, 2014 11:38 AM
  • Even if it would be technically possible to put 6000 into a parameter, which is essentially a dropdown list, do you really think the end-user is going to appreciate this?  It will be practically impossible to find what you're looking for.

    It would be far better if you could come up with a design that doesn't need that many items in the list.  Perhaps the values can be grouped somehow?  If that's the case you should look into cascading parameters: the first one shows the value groups while the second one shows the actual values, filtered on what gets selected in the first one.

    Another option, depending on requirements, could be to provide a free-text filter through a parameter.  The user types part of a string and your dropdown only shows matching items (using LIKE in the parameter query).

    Some food for thought...


    MCITP SQL Server 2008 (BI & DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    Check out my articles at BI: Beer Intelligence?

    Thursday, January 09, 2014 12:21 PM
  • Hi Valentino Vranken

    How are you ? Thank yo so much for your answer .

    Actually the Multi Parameter is already a cascaded based  parameter so whatever Customer Sales Team select it shows Order Items for that particular

    Customer . To Further refine there search i have enabled another empty like condition based text box via which they can refine there search in the

    Multi Parameter Drop Down For specific Order . I tried to explain my seniors and Sales team about the inefficiency of the solution they are asking me to implement but still they want this to be implemented. 

    Kindly Help

    Friday, January 10, 2014 6:50 AM
  • I assume you're getting an error?  Can you copy/paste it here?  And when exactly do you get the error?

    MCITP SQL Server 2008 (BI & DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    Check out my articles at BI: Beer Intelligence?

    Friday, January 10, 2014 8:55 AM
  • Hi Valentino ,

    In BIDS the report is working perfectly fine but when i try to run the report in WEB Browser Mozilla, Internet Explorer

    IF i place a condition in the query of the OrderNumber one that Drop Down should consist of only 500 Order Number's it runs fine even in the Web Browser any where the values crosses  800 plus values in the Drop Down the report fails to run in the WeB Browser whereas in the BIDS its runs fine .

    My Query is  :

    Select Customer.OrderNumber (                                        -------  Fetches all the OrdersNumber for a Customer

    Select OrderNumber from Orders

    Where CustomerName  = @Customername)

    Where Customer.OrderNumber LIKE  '%'+@OrderNumber+'%' or @OrderNumber is null 

    The Version that works in the web browser  :

    Select  Top 500 Customer.OrderNumber (                                       --------------- Limiting the OrdersNumbers in the web browser

    Select OrderNumber from Orders

    Where CustomerName  = @Customername)

    Where Customer.OrderNumber LIKE  '%'+@OrderNumber+'%' or @OrderNumber is null

    Friday, January 10, 2014 9:30 AM