Conditionally load Drop downs in Parameter toolbar

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.
>

Conditional XQuery: How to select a desirable node when it occurs multiple times

I would very much appreicate if someone could help me with the following

Return CountryCodes node based on the following rules:

(1) Ignore <AlternativeState> completely

(2) When <CurrentEvent>MarketSize</CurrentEvent> get CountryCodes from <MarketSize> node only

(3) When <CurrentEvent>MarketShare</CurrentEvent> get CountryCodes from <OtherEvents> node only

(4) When <CurrentEvent> doesn't exist then xml would have only one CountryCodes; get that node

I have come up with the following so far which is far from what is desirable

SELECT UsageID, Countries.Code.query('

for $CountryCode in .

return data($CountryCode)

') AS CountryCodes

FROM UsageAnalysis

CROSS APPLY xmlState.nodes('//*[not(self::AlternativeState)]/*/CountryCodes') AS Countries(Code)

GO

Please keep in mind xml comes from a table column.

The following are three possible simplified cases

Case 1

<State>

<StatsState>

<CurrentState>

<MarketSize>

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

Case 2

<State>

<DefinitionState>

<CountryCodes>BR</CountryCodes>

</DefinitionState>

</State>

Case 3

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

<AlternativeState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

</OtherEvents>

<MarketSize>

<CountryCodes>FR,FP,FG</CountryCodes>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<CountryCodes>,FR</CountryCodes>

</MarketSize>

</CurrentState>

</StatsState>

</State>

Hope this solve your problem:

Code Snippet

declare @.x xml

set @.x =

'<State>

<StatsState>

<CurrentState>

<MarketSize>

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

<State>

<DefinitionState>

<CountryCodes>BR</CountryCodes>

</DefinitionState>

</State>

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

<AlternativeState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

</OtherEvents>

<MarketSize>

<CountryCodes>FR,FP,FG</CountryCodes>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<CountryCodes>,FR</CountryCodes>

</MarketSize>

</CurrentState>

</StatsState>

</State>'

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then $s/StatsState/CurrentState/MarketSize/CountryCodes

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then $s/StatsState/CurrentState/OtherEvents/CountryCodes

else $s//CountryCodes

)

')

|||

Should this also be returned?

<CountryCodes>,FR</CountryCodes>

Please excuse me because I am rather new to the XML sector. I am confused by the question and the answer. I coded this up:

declare @.x xml
set @.x =
'<State>
<StatsState>
<CurrentState>
<MarketSize>
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>
</MarketSize>
<CurrentEvent>MarketSize</CurrentEvent>
</CurrentState>
</StatsState>
</State>
<State>
<DefinitionState>
<CountryCodes>BR</CountryCodes>
</DefinitionState>
</State>
<State>
<StatsState>
<CurrentState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
<AlternativeState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
</OtherEvents>
<MarketSize>
<CountryCodes>FR,FP,FG</CountryCodes>
</MarketSize>
<CurrentEvent>MarketShare</CurrentEvent>
</AlternativeState>
</OtherEvents>
<CurrentEvent>MarketShare</CurrentEvent>
<MarketSize>
<CountryCodes>,FR</CountryCodes>
</MarketSize>
</CurrentState>
</StatsState>
</State>'

select coalesce (
nullif(t.c.query('./StatsState/CurrentState/MarketSize/CountryCodes').value('.','varchar(20)'), ''),
nullif(t.c.query('./StatsState/CurrentState/OtherEvents/CountryCodes').value('.','varchar(20)'),''),
t.c.query('./DefinitionState/CountryCodes').value('.','varchar(20)')

)
as CountryCodes
from @.x.nodes('State') t(c)

and received this result:

/*
CountryCodes
--
KT,LC,VG,SU,TT,UY,VE
BR
,FR
*/

Do the correct results need to include the markup such that the results should look more like this:

/*
CountryCodes
--
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes><CountryCodes>BR</CountryCodes><CountryCodes>,FR</CountryCodes>
*/

(Trying to learn what is going on -- and I'm a bit confused.)

I appreciate the help.

|||

Jinghao, thanks very much. Your provided snippet does exactly what I have been trying to achieve. The only change I decided to introduce is to use data() so that I could get the scalar values for country codes as follows:

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then data($s/StatsState/CurrentState/MarketSize/CountryCodes)

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then data($s/StatsState/CurrentState/OtherEvents/CountryCodes)

else data($s//CountryCodes)

)

')

/*

Result set from your query:

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

<CountryCodes>BR</CountryCodes>

<CountryCodes>FR</CountryCodes>

Results after introducing data()

KT,LC,VG,SU,TT,UY,VE BR FR

*/

Now I could use a function call to return a list of country codes.

Thanks again for your help.

|||

Kent,

I must say that it took me a while to fully understand the solution you suggested by clever use of COALESCE. It did exactly what I was trying to achieve. i.e get a list of selected country codes.

/*

KT,LC,VG,SU,TT,UY,VE

BR

FR

*/

I just wanted to have a list of countries, without having any markups. i.e. just the scalar values of <countryCodes>

Your response has shown me another use of COALESCE function and I very much appreciate your help

Concurrency problems

Hi all,
Suppose that client A and client B read a record and B begins editing that.
Meanwhile A attempts to delete the record. Our project's rules says that the
record must not be deleted while it is being edited by other user. Whereas
our clients are disconnected, client B cannot lock the record. How can I
solve this problem?
Any help would be greatly appreciated.
Leila
I'm not sure what you mean by 'our clients are disconnected' but you
could throw the Primary Key value of the row being edited into a table.
A "this row is locked" table of sorts. Then any other user reading
that row you require your application to check your Lock table for the
Primary key value, if it is found then you return a message saying "you
can't delete this record, it's being edited by another user." Or
something like that, we've done that for our OLTP system in the past.
|||One way to solve this is to never delete a row. Add a column called
'Visible' that will act as a boolean for the UI to display or not display
the row. When a user 'deletes' a row, the row should really just be marked
as not visible. Then you can implement some logic (via a trigger) such that
if a row is updated when it's marked 'not visible', it will be marked
'visible' again.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Leila" <Leilas@.hotpop.com> wrote in message
news:eU2lWsE5EHA.1596@.tk2msftngp13.phx.gbl...
> Hi all,
> Suppose that client A and client B read a record and B begins editing
that.
> Meanwhile A attempts to delete the record. Our project's rules says that
the
> record must not be deleted while it is being edited by other user. Whereas
> our clients are disconnected, client B cannot lock the record. How can I
> solve this problem?
> Any help would be greatly appreciated.
> Leila
>
|||Adam Machanic wrote:
> One way to solve this is to never delete a row. Add a column called
> 'Visible' that will act as a boolean for the UI to display or not
display
> the row. When a user 'deletes' a row, the row should really just be
marked
> as not visible. Then you can implement some logic (via a trigger)
such that[vbcol=seagreen]
> if a row is updated when it's marked 'not visible', it will be marked
> 'visible' again.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eU2lWsE5EHA.1596@.tk2msftngp13.phx.gbl...
editing[vbcol=seagreen]
> that.
that[vbcol=seagreen]
> the
Whereas[vbcol=seagreen]
can I[vbcol=seagreen]
Adam,
I have one question about this method, and I'm not questioning the
validity of this solution so please don't take my question the wrong
way. My question is about spliting the table by using the Visible bit
and querying on the table later. Wouldn't you have to always use that
bit on your Selects and therefore not having the most effecient "index"
of sorts to use when you are retrieving data? I'm very interested in
hearing your opinion on this one, we've had developers in the past rely
on an "Active" bit for rows in certain tables we use. For instance, an
operation location around the country, making it accesible to the
application via the active bit. I don't particularly like doing this
and have advised not doing it. Thanks for your time.
Mark
|||Thanks Adam,
But how the visibility helps me? Should client B(who is editing the record)
mark the record as invisible? What if client B crashes while editing and the
record remains invisible in table?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#t8hQQF5EHA.1976@.TK2MSFTNGP09.phx.gbl...
> One way to solve this is to never delete a row. Add a column called
> 'Visible' that will act as a boolean for the UI to display or not display
> the row. When a user 'deletes' a row, the row should really just be
marked
> as not visible. Then you can implement some logic (via a trigger) such
that[vbcol=seagreen]
> if a row is updated when it's marked 'not visible', it will be marked
> 'visible' again.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eU2lWsE5EHA.1596@.tk2msftngp13.phx.gbl...
> that.
> the
Whereas
>
|||<myelton1@.Lincare.com> wrote in message
news:1103302784.609659.76270@.z14g2000cwz.googlegro ups.com...
> way. My question is about spliting the table by using the Visible bit
> and querying on the table later. Wouldn't you have to always use that
> bit on your Selects and therefore not having the most effecient "index"
> of sorts to use when you are retrieving data? I'm very interested in
> hearing your opinion on this one, we've had developers in the past rely
> on an "Active" bit for rows in certain tables we use. For instance, an
> operation location around the country, making it accesible to the
> application via the active bit. I don't particularly like doing this
> and have advised not doing it. Thanks for your time.
Yes, you would always have to use that column in your selects. Note, it
doesn't necessarily have to be a BIT. A lot of developers prefer CHAR(1)
NOT NULL CHECK (Visible IN 'Y', 'N'). Whether it will cause problems? It
depends on how many deleted columns there are, how selective the rest of the
columns in the queries are, etc. I probably wouldn't even bother adding it
to any indexes (except maybe covering indexes), as SQL Server can seek using
the keys from the queries and then filter the rows where Visible = 'N' quite
easily. Again, though, it depends. As always, test heavily
Another option is to store PKs from deleted rows in another table and
then you can query like:
SELECT *
FROM YourTable
WHERE NOT EXISTS
(SELECT *
FROM YourTableDeletedRows T1
WHERE T1.PK = YourTable.PK)
I don't know how that will perform, but it may solve the issue if you're
getting index scans due to the 'boolean' column.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Thanks,
Actually I thought about that but I don't know what to do if the client
crashes? Because the PK remains in that table and no longer will be
deleted..
<myelton1@.Lincare.com> wrote in message
news:1103300244.952647.179750@.z14g2000cwz.googlegr oups.com...
> I'm not sure what you mean by 'our clients are disconnected' but you
> could throw the Primary Key value of the row being edited into a table.
> A "this row is locked" table of sorts. Then any other user reading
> that row you require your application to check your Lock table for the
> Primary key value, if it is found then you return a message saying "you
> can't delete this record, it's being edited by another user." Or
> something like that, we've done that for our OLTP system in the past.
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:ukmR7uF5EHA.3120@.TK2MSFTNGP12.phx.gbl...
> Thanks Adam,
> But how the visibility helps me? Should client B(who is editing the
record)
> mark the record as invisible? What if client B crashes while editing and
the
> record remains invisible in table?
ClientA is looking at the record.
ClientB is editing the record.
ClientA hits the "delete" button on the UI. This flips the "Visible"
column on the row.
ClientB submits the edits...
And now the "Visible" column gets flipped back.
You will probably also want to investigate how to periodically delete
all of the rows marked "not visible" so that your table isn't full of too
much unused legacy data.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Leila wrote:
> Thanks Adam,
> But how the visibility helps me? Should client B(who is editing the record)
> mark the record as invisible? What if client B crashes while editing and the
> record remains invisible in table?
The real problem here is that you didn't provide much real detail so
people have to make guesses.
However, you can implement date/time stamps to track when rows are
"checked out" and create an interface to override checked out rows if
there is a crash.
But, you said your clients are "disconnected". If that is indeed the
case, and both clients can have the same data locally, how do you expect
ANY kind of concurrency checking to take place? Your design doesn't
really allow for it. One solution that does come to mind is that you
create a system to manage changes/deletes each time a client reconnects
to the source data.
But, back to your lack of problem description. When you say that A
cannot delete while B is editing, is that ONLY during the exact time
that B is editing? Honestly, why does it matter? If you're going to let
A delete the data anyway, you're not gaining much by adding in this
check. In fact, you should create a system so that if A does in fact
delete a row while B is editing it, and B then saves the data, they are
notified that the data was deleted by another client and then allow be
to with discard their data (and thus totally deleting the data) or
optionally read the data back to the database. Imperfect solution but it
sounds like you have an imperfect design :D
You may want to post some additional information about the rules that
govern the whole deletion/editing process. Like, how often do the client
connect to the database to sync their data sets? What happens when two
clients edit the same data, who takes precedence? How do you handle
duplicate data? etc.
Zach

> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:#t8hQQF5EHA.1976@.TK2MSFTNGP09.phx.gbl...
>
> marked
>
> that
>
> Whereas
>
>
|||If client B crashes, it cannot submit the changes and make the record
visible again. I mean the record will remain invisible
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O5#E6xF5EHA.1976@.TK2MSFTNGP09.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:ukmR7uF5EHA.3120@.TK2MSFTNGP12.phx.gbl...
> record)
> the
>
> ClientA is looking at the record.
> ClientB is editing the record.
> ClientA hits the "delete" button on the UI. This flips the "Visible"
> column on the row.
> ClientB submits the edits...
> And now the "Visible" column gets flipped back.
> You will probably also want to investigate how to periodically delete
> all of the rows marked "not visible" so that your table isn't full of too
> much unused legacy data.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>