September 25, 2019 at 6:32 pm
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
September 26, 2019 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 26, 2019 at 9:19 pm
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
September 27, 2019 at 2:46 pm
I get an error trying to create the stored procedure
Incorrect syntax near '@GroupSearchTerm'.
September 27, 2019 at 8:25 pm
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"
September 29, 2019 at 11:36 am
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?
September 29, 2019 at 12:22 pm
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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply