Can we make parameters optional?

  • Hi All,

    I have a report with 5 parameters:

    Location,Users,Clients,FromDate and ToDate.

    They are dependent.

    now the req is changed and i must make the clients parameter OPTIONAL.

    The user may or may not choose from Client List.

    now i have linked all of them in such a way that, first user selects location. then all the users belonging to that location are shown.

    Then when he choses user, all the clients under that user are shown.

    now i must display all clients if the does not select from client drop down

    and if he selects, i must show data for ONLY THAT CLIENT.

    how can i do?

  • Hi

    You have to do it like this:

    The query which populates the ClientList dropdown you have to change such that it has the value <all> as the first value, then in the Default value for parameter section in the report, make it Default from query and select the field for ClientList.

    Inside the SP the WHERE condition is to be changed as

    WHERE ClientID LIKE @ClientID.

    So at the begining of the SP you have to write a condition as

    if (@ClentID = '<all>')

    set @ClientID = '%'

    so when the default value <all> is passed it will be considered as all the clients (because of %), and when you select one client the result will only for that client.

    Thanks & Regards,
    MC

  • Can i make the parameter multi-valued while doing this?

    actually there is a report which they have designed in .Net i must now bring in same features for this ....

    i need something like this:

    initially, the clientID will be masked. we can keep a check box(as in for Allow NULL option).

    if the check box is checked, then the multi-valued dropdown should be displayed for the user to select.

    with the above mentioned solution, i think multi-valued parameter is not possible. Kindly suggest on this....

  • Hi,

    I think you can do this , just refer the below link, which I have replied for another friend.

    http://www.sqlservercentral.com/Forums/Topic915160-150-1.aspx

    Thanks & Regards,
    MC

  • Hi,

    I tried some methods for this. I kept a 'Select ALL' option and tried. It works fine but in that case i cant make it multi-valued parameter.....

    And if i make it multi-valued, i cannot use the allow null option....

    could you please suggest me something so that i can make it optional but still keep it multi-valued?

  • No no, you don't have to check the box for Allow NULL , you keep it unchecked and in the Default value option make it nonqueried , and give the value as All, so that the when you click the preview , the value selected will be All.

    Note: The 'ALL' which is coming as the first value, and that you are giving in the default nonquerried should be of same case.

    Thanks & Regards,
    MC

  • Hi,

    That works well but my req is not that... sorry for this.

    the prob is the application they have made is such....

    Now, while choosing parameters itself i must write a condition.

    In that, it should work for ALL users and ALL clients....

    but this must run with an alternative query as there are over 2000 clients and passing all the client ids to the proc may cause performance issues.

    i think that ALL option should not be there bcoz it will pass all the client ids as comma separated values to the proc.

    can anyone suggest some alternative?

  • If you don't mind can you explain the exact requirement. 🙂

    In the first part you have mentioned, if client ID is not selected it should retrieve data for ALL the clients and if selected one client, it should be for only that client.(I hope by keeping ALL as the first record and keeping DEFAULT this will work fine), then you wanted to make it multivalue,I hope the last reply helps you for that.

    But it looks like still it is not met your requirement.

    Thanks & Regards,
    MC

  • Yes even i thought that would suffice the requirement...

    but what my manager says is, when you make default ALL and run report,

    2000 values will be passed to the proc.... this may slow down the report.

    so he wants to run the report for all users and all clients....

    the prob here is they are comparing whatever i do with the .net appln they have and are not sarisfied with this 🙁

    m not sure how to bring about this...

    the solution you gave works just fine... but he wants it like this....

    can i make a check before the parameters itself?

  • Hi All,

    For test purpose i tried the method we discussed above...

    i kept both USER and CLIENTS parameters default ALL.

    so my query run for ALL USERS and ALL CLIENTS for a given date range.

    but my problem is , for every field in this report, i have a linked report (using jump to report option). Now, master is working fine but the detail reports are not showing any data.

    I have made similar modifications for the parameters of detail reports too...

    what may be wrong?

  • Hi All,

    I would like to know if the above method causes performance issues....

    this is for a Resume Application and hence hundreds of users will try to generate the report simultaneously....

    Imagine this is a s/w which all the consultancies and employers use to fetch resumes suitable for the openings in their company...

    so, there will be hundreds of users(consultants) who will be looking for profiles that would match the requirements of thousands of employers (clients).

    so i have been asked NOT TO pass anything to the proc if i would like to generate the report for ALL USERS and ALL CLIENTS.

    Suggest me some method by which we can do this....

  • Hi,

    Based on what you have been stating here, it looks like you have 2 ways to execute the report, one is ALL USERS,ALL CLIENTS and the other option where you selectively pick and choose. Could you try have a parameter which would have an option for all users/clients and another one for pick and choose, if the first option is choosen then you in the other parameters you would set the values as accordingly and call the stored proc. for example:

    1. exec usp_retrieve_data @alluserclients = 'Y',@user=NULL,@client=NULL,@startdate='05/10/2010'

    2. exec usp_retrieve_data @alluserclients = 'N',@user='abc',@client='xyz',@startdate='05/10/2010'

  • Hi,

    Yes the requirement is the same as you have mentioned.

    I have written SP for that.

    Now, i am facing one more prob. I have kept the client id of each row of the result set in the main report's layout and made it HIDDEN.

    I have made this for my detail report purpose.

    For instance, if the users selects ALL option, the main report will then have hundreds of rows...

    But for detail report i need to send ONLY ONE CLIENTID AND ONE USERID.

    so i have placed both userid and clientid in the report and i am passing those values to the detail report instead of passing report parameters.

    now i am not able to see the result set of detail report. it is blank....

    but i am not getting any error messages either....

    can someone help?

  • Hi,

    Is the detail report launched based on a hyper link action, like when you hover the mouse over user id you can click on it open the detail report? what is being passed to the detail report (user id, client id), instead of passing the report parameters could you pass the fields user id/client id to the detail report.

    Thank you

  • Hi Ramdas,

    Thanks for the reply.

    Yes the report functions in the same way you mentioned it....

    i am passing fields userid and clientid instead of parameters.

    now i have 9400 clients in my DB....

    the report will be running for all the 9400 clients when ALL is selected....

    m worry is this may cause performance issues when a number of users try to generate the report simultaneously.....

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply