July 22, 2015 at 12:37 pm
Parameters:
MemberTypeId,FullName,Active
Active Parameter Properties:->Allow Multiple Values->Available Values:Active=0, Inactive=1
Query:
select UserId,FullName,Active from dbo.Users where MemberTypeId=@MemberTypeId and (Active=@Active) and (((FullName like '%'+@FullName+'%' or @FullName IS NULL) and @MemberTypeId=1) or @MemberTypeId=0)
So the active will be a dropdown of Select All, Active,Inactive.
Active and Inactive works but not select all. I would like to have an option of select all where it displays all active and inactive.
July 22, 2015 at 1:43 pm
one way to do it is to remove the Active=@Active parameter from your stored procedure and put it in your report as a filter. You can create a parameter, specify values etc, and then set the multi-select property to true and it will work.
July 22, 2015 at 1:56 pm
I am using Reportbuilder3.0. Wonder where can I find multi select property
July 22, 2015 at 2:16 pm
I would like to select Active/Inactive/All
July 22, 2015 at 3:07 pm
Steps to get this to work... (seems I always say that!)
1. Create your stored procedure...
ALTER PROC [dbo].[ReferralsData]
AS
SELECT ReferralID,
RefDate,
CASE RefType WHEN 1 THEN 'Internal' ELSE 'External' END ReferralType
FROM Referral;
Note that there is NO filter/WHERE clause in my stored procedure. If I do that, then dealing with multiple values is a headache.
2. Create a dataset based on your stored procedure.
3. Add a parameter of the same type as the column you're filtering. In my example, I created a parameter @prmReferralType that points to a text column, so it's text AND the "allow multiple values" checkbox is checked.
4. To attach the report-level filter (@prmReferralType) to my report's dataset,
a. right-click on dataset, select Dataset Properties from the menu.
b. click FILTERS.
c. Click ADD
d. Expression: [ReferralType] (choose your column from the dropdown).
e. Operator: IN (that's IMPORTANT!!!)
f. value: the name of the parameter you created, in my case it's @prmReferralType
g. to set the list of values for the parameter... Right-click the parameter, select "Available Values" from the menu... and either get them from a query or type them in...
July 23, 2015 at 3:08 pm
This doesnt seem to work.
can you run my example and see
July 23, 2015 at 5:33 pm
Sorry but "doesn't seem to work" doesn't explain much. Please read this article this article[/url] and post the table definition, some sample data (not real data, just representative is fine).
Then maybe someone can help you. If you're passing multiple values to a stored procedure parameter, then you have to change the filter from
WHERE ColumnName = @ParameterName
to
WHERE ColumnName IN (@ParameterName)
because in the second instance, @ParameterName is an not just a single value.
July 23, 2015 at 8:45 pm
Using the WHERE with the IN() will require a split function.
I'll get creamed for saying this but you could use a catch all query.
In your query choices '-1' AS Value, '-ALL-' AS Label
UNION your other two choices as a query. In the Param get values from query.
and then Active Inactive choices as well.
and (Active = '-1' OR ACTIVE =@Active)
SELECT All will still be there but this bypasses it other wise you need to use a Split8K. Search here for that. It will be used in your WHERE clause also on your Main data set you need to do a InStr(Join) to get those Multis working correctly. I'll walk you through it tomorrow if you want. I do hundreds of these.
select UserId,FullName,Active from dbo.Users where MemberTypeId=@MemberTypeId and (Active = '-1' OR ACTIVE =@Active) and (((FullName like '%'+@FullName+'%' or @FullName IS NULL) and @MemberTypeId=1) or @MemberTypeId=0)
***SQL born on date Spring 2013:-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply