SSRS parameter

Question

Post reply

SSRS parameter

TJ_T

Old Hand

Points: 352
More actions
September 25, 2019 at 6:32 pm

#3683411

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

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

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

Site Owners SSC Guru Points: 80382 More actions · Answer 1

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

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

Lowell SSC Guru Points: 323547 More actions · Answer 2

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!

TJ_T Old Hand Points: 352 More actions · Answer 3

I get an error trying to create the stored procedure

Incorrect syntax near '@GroupSearchTerm'.

TJ_T Old Hand Points: 352 More actions · Answer 4

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"

TJ_T Old Hand Points: 352 More actions · Answer 5

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?

Lowell SSC Guru Points: 323547 More actions · Answer 6

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!