In trying to incorporate business rules into my SSRS report, I need to be
able to conditionally load drop downs based upon what the selects for other
drop downs.
Can anyone tell me how? Example:
DropDown1 = Country
DropDown2 = State/Region
How Can i leave DropDown2 empty until they select from DropDown1?
Thanks.Hi JrMcG,
Thank you for your posting!
Based on my experience, you could do the following step to get the
Parameters related.
1. Create a dataset and add a Report Parameter named Country.
2. Create another dataset named States and use the parameter in the query
text. For example:
select State from tbl_Region where Country = @.Country
3. Create a new Report Patameter named State and in the Available values,
you need to use From query, and choose the dataset States, Value filed and
Label filed use State.
Then, in the preview, you could see the Parameter State could not get the
value untill you specify the value of Country.
Please try the above steps and let me know the result. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi JrMcG,
Have you got any chance to check this issue? Please let me know if you need
any help, thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||You are looking for a "Cascading Parameter" Report; there is a good
example in the sample set for SSRS 2005.
Dennis Graham
JrMcG wrote:
> In trying to incorporate business rules into my SSRS report, I need to be
> able to conditionally load drop downs based upon what the selects for other
> drop downs.
> Can anyone tell me how? Example:
> DropDown1 = Country
> DropDown2 = State/Region
> How Can i leave DropDown2 empty until they select from DropDown1?
> Thanks.|||My subject is very closeley tied to this one so i hope it's OK if I post
here...
I did the same thing but also added an 'all' option in my dataset. Selecting
'all' and a single option works but when selecting multi values the report
breaks. What can i do in my WHERE claus to get this working. Without it the
Bussiness Rules are useless.
"Wei Lu [MSFT]" wrote:
> Hi JrMcG,
> Have you got any chance to check this issue? Please let me know if you need
> any help, thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
Showing posts with label upon. Show all posts
Showing posts with label upon. Show all posts
Thursday, March 22, 2012
Wednesday, March 7, 2012
conditional grouping (to group or not to group)
I have 3 groups: region, district, facility.
Depending upon user input, I want to either display the data in a drilldown
such as:
region 1
district 1
facility A data data data
facility C data data data
facility F data data data
district 2
facility B
facility D
...etc
or I want to only display the facility data without the drilldown and
without displaying which region and district those facilities belong to.
Obviously, this could be done by using 2 separate reports, but I need to have
this functionality for all of my reports. So if there is a simple way to
allow for it in the same report, I would like to do that.
ThanksThe closest you can get is a conditional grouping expression like this:
=iif(Parameters!GroupOnRegion.Value = True, Fields!Region.Value, 1)
Note: Grouping on a constant value will just generate 1 group that contains
all values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stads" <Stads@.discussions.microsoft.com> wrote in message
news:CC17E391-B188-492F-980E-C8F3B4298F97@.microsoft.com...
> I have 3 groups: region, district, facility.
> Depending upon user input, I want to either display the data in a
drilldown
> such as:
> region 1
> district 1
> facility A data data data
> facility C data data data
> facility F data data data
> district 2
> facility B
> facility D
> ...etc
> or I want to only display the facility data without the drilldown and
> without displaying which region and district those facilities belong to.
> Obviously, this could be done by using 2 separate reports, but I need to
have
> this functionality for all of my reports. So if there is a simple way to
> allow for it in the same report, I would like to do that.
> Thanks
>
Depending upon user input, I want to either display the data in a drilldown
such as:
region 1
district 1
facility A data data data
facility C data data data
facility F data data data
district 2
facility B
facility D
...etc
or I want to only display the facility data without the drilldown and
without displaying which region and district those facilities belong to.
Obviously, this could be done by using 2 separate reports, but I need to have
this functionality for all of my reports. So if there is a simple way to
allow for it in the same report, I would like to do that.
ThanksThe closest you can get is a conditional grouping expression like this:
=iif(Parameters!GroupOnRegion.Value = True, Fields!Region.Value, 1)
Note: Grouping on a constant value will just generate 1 group that contains
all values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stads" <Stads@.discussions.microsoft.com> wrote in message
news:CC17E391-B188-492F-980E-C8F3B4298F97@.microsoft.com...
> I have 3 groups: region, district, facility.
> Depending upon user input, I want to either display the data in a
drilldown
> such as:
> region 1
> district 1
> facility A data data data
> facility C data data data
> facility F data data data
> district 2
> facility B
> facility D
> ...etc
> or I want to only display the facility data without the drilldown and
> without displaying which region and district those facilities belong to.
> Obviously, this could be done by using 2 separate reports, but I need to
have
> this functionality for all of my reports. So if there is a simple way to
> allow for it in the same report, I would like to do that.
> Thanks
>
Friday, February 24, 2012
Conditional Column Filter
Hi
I have a field (FieldA) which I need to Filter on, based upon a parameter,
however the filter is only a substring of the FieldA.
e.g
FieldA = H2/Q3/10
the filter supplied maybe supplied as Q3, H2,or 10, I how to split up FieldA
in it's constituent parts, however in a WHERE clause I don't know how to
represent this.
i.e
If the parameter = Q3
then I would do :
WHERE SUBSTRING(FieldA,4,2) = 'Q3'
However if the param supplied was H2, then :
WHERE SUBSTRING(FieldA,4,2) = 'H2'
would not work?
I would therefore need to change the Column on how its being filtered on, is
there a way I can do this?
Kind Regards
RickyTry:
...
where '/' + FieldA + '/' like '%/' + @.s + '/%'
go
Do not expect SQL Server using properly an index by [FieldA] in case it
exists. You can google for "search arguments", for more info.
AMB
"ricky" wrote:
> Hi
> I have a field (FieldA) which I need to Filter on, based upon a parameter,
> however the filter is only a substring of the FieldA.
> e.g
> FieldA = H2/Q3/10
> the filter supplied maybe supplied as Q3, H2,or 10, I how to split up Fiel
dA
> in it's constituent parts, however in a WHERE clause I don't know how to
> represent this.
> i.e
> If the parameter = Q3
> then I would do :
> WHERE SUBSTRING(FieldA,4,2) = 'Q3'
> However if the param supplied was H2, then :
> WHERE SUBSTRING(FieldA,4,2) = 'H2'
> would not work?
> I would therefore need to change the Column on how its being filtered on,
is
> there a way I can do this?
> Kind Regards
> Ricky
>
>|||Any special reason for breaking the normal form by storing three values in a
single column? Have you considered properly normalizing the model?
Anyway, how about using wildcards:
WHERE FieldA like '%H2%'
For a more helpful answer, please provide DDL, sample data and expected
results.
ML
http://milambda.blogspot.com/|||Hi guys
thanks for the replies, won't wildcards be slow? Incidentally, if I use a
wildcard, when I search for 1 in H1Q11, won't it get
with H1 or Q1?
Kind Regards
Ricky
"ML" <ML@.discussions.microsoft.com> wrote in message
news:E4D9EB2A-D620-4794-9220-3442B02B17C4@.microsoft.com...
> Any special reason for breaking the normal form by storing three values in
a
> single column? Have you considered properly normalizing the model?
> Anyway, how about using wildcards:
> WHERE FieldA like '%H2%'
> For a more helpful answer, please provide DDL, sample data and expected
> results.
>
> ML
> --
> http://milambda.blogspot.com/|||Using wildcards and functions in query conditions is slow, in most cases a
scan will be used. To improve the performance by using indexes first conside
r
normalizing the data.
ML
http://milambda.blogspot.com/|||ok ML, will do, thanks for the suggestion and tip.
Kind Regards
Ricky
"ML" <ML@.discussions.microsoft.com> wrote in message
news:547F6E76-0A5C-43ED-8649-34EEBD431307@.microsoft.com...
> Using wildcards and functions in query conditions is slow, in most cases a
> scan will be used. To improve the performance by using indexes first
consider
> normalizing the data.
>
> ML
> --
> http://milambda.blogspot.com/|||No, the answer Alesandro gave you handles that
(you specified slashes - where have they gone in this followup question?)
Bye
Jan
"ricky" <ricky@.ricky.com> wrote in message
news:Olme8JijGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Incidentally, if I use a wildcard, when I search for 1 in H1Q11, won't it
get with H1 or Q1?|||Hi Jan
You're quite right, I forgot to place them in.
Well spotted.
Kind Regards
Ricky
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message
news:O52SBcijGHA.3440@.TK2MSFTNGP02.phx.gbl...
> No, the answer Alesandro gave you handles that
> (you specified slashes - where have they gone in this followup question?)
> Bye
> Jan
> "ricky" <ricky@.ricky.com> wrote in message
> news:Olme8JijGHA.1204@.TK2MSFTNGP02.phx.gbl...
it
> get with H1 or Q1?
>
>
I have a field (FieldA) which I need to Filter on, based upon a parameter,
however the filter is only a substring of the FieldA.
e.g
FieldA = H2/Q3/10
the filter supplied maybe supplied as Q3, H2,or 10, I how to split up FieldA
in it's constituent parts, however in a WHERE clause I don't know how to
represent this.
i.e
If the parameter = Q3
then I would do :
WHERE SUBSTRING(FieldA,4,2) = 'Q3'
However if the param supplied was H2, then :
WHERE SUBSTRING(FieldA,4,2) = 'H2'
would not work?
I would therefore need to change the Column on how its being filtered on, is
there a way I can do this?
Kind Regards
RickyTry:
...
where '/' + FieldA + '/' like '%/' + @.s + '/%'
go
Do not expect SQL Server using properly an index by [FieldA] in case it
exists. You can google for "search arguments", for more info.
AMB
"ricky" wrote:
> Hi
> I have a field (FieldA) which I need to Filter on, based upon a parameter,
> however the filter is only a substring of the FieldA.
> e.g
> FieldA = H2/Q3/10
> the filter supplied maybe supplied as Q3, H2,or 10, I how to split up Fiel
dA
> in it's constituent parts, however in a WHERE clause I don't know how to
> represent this.
> i.e
> If the parameter = Q3
> then I would do :
> WHERE SUBSTRING(FieldA,4,2) = 'Q3'
> However if the param supplied was H2, then :
> WHERE SUBSTRING(FieldA,4,2) = 'H2'
> would not work?
> I would therefore need to change the Column on how its being filtered on,
is
> there a way I can do this?
> Kind Regards
> Ricky
>
>|||Any special reason for breaking the normal form by storing three values in a
single column? Have you considered properly normalizing the model?
Anyway, how about using wildcards:
WHERE FieldA like '%H2%'
For a more helpful answer, please provide DDL, sample data and expected
results.
ML
http://milambda.blogspot.com/|||Hi guys
thanks for the replies, won't wildcards be slow? Incidentally, if I use a
wildcard, when I search for 1 in H1Q11, won't it get
with H1 or Q1?Kind Regards
Ricky
"ML" <ML@.discussions.microsoft.com> wrote in message
news:E4D9EB2A-D620-4794-9220-3442B02B17C4@.microsoft.com...
> Any special reason for breaking the normal form by storing three values in
a
> single column? Have you considered properly normalizing the model?
> Anyway, how about using wildcards:
> WHERE FieldA like '%H2%'
> For a more helpful answer, please provide DDL, sample data and expected
> results.
>
> ML
> --
> http://milambda.blogspot.com/|||Using wildcards and functions in query conditions is slow, in most cases a
scan will be used. To improve the performance by using indexes first conside
r
normalizing the data.
ML
http://milambda.blogspot.com/|||ok ML, will do, thanks for the suggestion and tip.
Kind Regards
Ricky
"ML" <ML@.discussions.microsoft.com> wrote in message
news:547F6E76-0A5C-43ED-8649-34EEBD431307@.microsoft.com...
> Using wildcards and functions in query conditions is slow, in most cases a
> scan will be used. To improve the performance by using indexes first
consider
> normalizing the data.
>
> ML
> --
> http://milambda.blogspot.com/|||No, the answer Alesandro gave you handles that
(you specified slashes - where have they gone in this followup question?)
Bye
Jan
"ricky" <ricky@.ricky.com> wrote in message
news:Olme8JijGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Incidentally, if I use a wildcard, when I search for 1 in H1Q11, won't it
get
with H1 or Q1?|||Hi JanYou're quite right, I forgot to place them in.
Well spotted.
Kind Regards
Ricky
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message
news:O52SBcijGHA.3440@.TK2MSFTNGP02.phx.gbl...
> No, the answer Alesandro gave you handles that
> (you specified slashes - where have they gone in this followup question?)
> Bye
> Jan
> "ricky" <ricky@.ricky.com> wrote in message
> news:Olme8JijGHA.1204@.TK2MSFTNGP02.phx.gbl...
it
> get
with H1 or Q1?>
>
Subscribe to:
Posts (Atom)