SSRS parameter

  • I need some help with a parameter.  I have the query below and I need to be prompted for either Users or Groups, which I could then select the User or Group from a drop-down list.  Sometimes I need to know what Groups a User is in, and other times I need to know all Users in a specific Group.

    Select Users, Groups

    FROM UserGroups

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • you wnat your SSRS report to have two text boxes or dropdowns for the  parameters, and to call a procedure.

    the procedure then might look something like this, so if they populate either or both of the values, it is double filtered:

    CREATE PROCEDURE [dbo].[MyProc] @GroupSearchTerm    varchar(128)  = NULL,
    @UserSearchTerm varchar(128) = NULL
    AS
    BEGIN
    SET NOCOUNT ON;
    --#################################################################################################
    --assign default parameters to nulls or invalids
    SELECT
    @GroupSearchTerm = '%' + ISNULL(@GroupSearchTerm,'') + '%',
    @UserSearchTerm = '%' + ISNULL(@UserSearchTerm,'') + '%'
    --get the data
    SELECT [arr].Users,[arr].Groups
    FROM [dbo].[UserGroups] AS [arr]
    WHERE [arr].Groups LIKE @GroupSearchTerm
    AND [arr].Users LIKE @UserSearchTerm

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I get an error trying to create the stored procedure

    Incorrect syntax near '@GroupSearchTerm'.

     

  • I found my mistake.

    But when I run the report I get "An error occurred during local procesing. The report parameter 'GroupSearchTerm' has a defaultvalue or a validvalue that depends that depends on the report parameter 'groupSearchTerm'. Forward dependencies are not valid"

  • OK, so I found the issue with my second error.  I needed a second dataset to populate the parameters in the first dataset.

    SELECT [arr].Users,[arr].Groups

    FROM [dbo].[UserGroups]

    The issue I am having now is that I am forced to use both parameters for the report to run, and I was hoping to be able to use either or - not both at the same time.

    I tried using an OR statement: "WHERE [arr].Groups LIKE @GroupSearchTerm OR [arr].Users LIKE @UserSearchTerm" and this didn't work.

    I then tried using NULLS and this didn't work also.

    Any thoughts?

  • right , the procedures are only part of the settings you need to use.

    you need to use both WHERE [arr].Groups LIKE @GroupSearchTerm AND [arr].Users LIKE @UserSearchTerm" in the proc, not or

    and you need to gracefully handle the null/blank values, so when only one is passed, the report returns the desired data.

    the piece you are missing here is the settings for your parameters in your report.

    you want to allow null values, and create a default parameters of empty string, i think.

    it sounds though that you are using drop downs with a cascade/load one value based on the other. in that case, your SELECT statement needs to have an additional return  default value like 'ALL' or something, and your procedure checks and says if the @Parameter = 'ALL' change to the default empty string.

    so when both are empty, you get results, or if one or the other is empty and the opposite parameter = 'ALL' or empty string, you still get results, because the WHERE statement

    ends up like this

    "WHERE [arr].Groups LIKE '%%' AND[arr].Users LIKE ''%TJ_T%'"

     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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