Is it possible to conditionally skip or jump steps in a job without failing
a
step?
For example I don’t want to run a step if the are no records in a table.
I also need to loop based on a condition.About loops:
http://www.sqldts.com/default.aspx?246
About conditional execution:
http://www.sqldts.com/default.aspx?218
http://www.sqldts.com/default.aspx?214
Chris
"Dave" wrote:
> Is it possible to conditionally skip or jump steps in a job without failin
g a
> step?
> For example I don’t want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>|||You could place your (IF.. BEGIN.. END) condition within the T-SQL or stored
procedure beging called.
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:27AF395F-A67D-43E6-9F40-009ACCCB0877@.microsoft.com...
> Is it possible to conditionally skip or jump steps in a job without
> failing a
> step?
> For example I don't want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>sqlsql
Showing posts with label conditionally. Show all posts
Showing posts with label conditionally. Show all posts
Thursday, March 22, 2012
Conditionally showing/hiding a parameter
I have a report that in most cases takes 4 parameters. In a few special cases, though, it takes 5 parameters. I have my code modified enough such that it can handle this exception, however I cannot figure out how to conditionally show the 5th parameter based on the 4th parameter. Is this possible?
how to conditionally show the 5th parameter based on the 4th parameter. Is this possible?
Yes, this is possible. Lets say your 5th parameter is driven off of a query, then you can pass what ever is selected in the 4th parameter as an input to the query. Also in the report parameter your 5th parameter should be below the 4th parameter meaning the order of the parameters is important.
Let me know if you would want me to elaborate this further.
|||You're describing how to change the available values of the 5th parameter based on the choice made for the 4th parameter. I think the original question is it possible to show or hide the 5th parameter based on the choice made for the 4th parameter.
I have never found a way to conditionally hide parameters and don't believe it's possible. If I'm wrong then I'd love to hear how to do it. When we've encountered cases like this we've had to just change the values of the 5th parameter (as Techquest has described) to have only a single item available such as "n/a" and then defaulted to that value.
I'd also love to hear if there's a way to conditionally change the label for parameters. I don't believe this is possible either but there's a lot of smart people out there and it would be great if someone could show me that I'm wrong.
tia
-bruce
|||Bruce, that's what I wanted to do.I haven't been working with SSRS for too long, I wonder if it's possible to write a script or extension to allow more flexibility in the parameters.
I like the N/A idea though, that'll work if I can't figure out anything new.
Thanks,
Erik
Conditionally Send Email Subscription
Hi All,
I've written reports and deployed them with data-driven subcriptions. I
have a new requirement and would like to know if there is a solution I
can impliment to conditionally send an email to a recipient "ONLY" if
the report to this specific recipient contains data. If I'm correct,
the data-driven query will send an email to each in the result set of
the query whether the report contains data or not.
Am I on the right track and is this possible?
Thank in advance!
Kind regards - FredHi Igor,
> One solution would be if you can implement your data-driven (DD) query in a
> way that returns a user only if data for this user exists. Alternative is
> writing a custom delivery - then you have a complete control over the send
> logic. Otherwise you're right: email is sent to each in the result set of
> the DD query whether the report contains data or not.
I'll most liekly go with the DD query up front as a better solution.
Just wanted to see if there was something I'm missing. Thanks for your
input.
Regards - Fred
I've written reports and deployed them with data-driven subcriptions. I
have a new requirement and would like to know if there is a solution I
can impliment to conditionally send an email to a recipient "ONLY" if
the report to this specific recipient contains data. If I'm correct,
the data-driven query will send an email to each in the result set of
the query whether the report contains data or not.
Am I on the right track and is this possible?
Thank in advance!
Kind regards - FredHi Igor,
> One solution would be if you can implement your data-driven (DD) query in a
> way that returns a user only if data for this user exists. Alternative is
> writing a custom delivery - then you have a complete control over the send
> logic. Otherwise you're right: email is sent to each in the result set of
> the DD query whether the report contains data or not.
I'll most liekly go with the DD query up front as a better solution.
Just wanted to see if there was something I'm missing. Thanks for your
input.
Regards - Fred
Conditionally required field
How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds ou
t
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsRules.
Most people thing of rules in an IF.. THEN format which simply won't work.
Think of a rule as a boolean function where YES/TRUE accepts the row and
NO/FALSE rejects the row. Your requirements would lead to a rule like this:
Priv1 <> 0 OR Priv2<> 0 OR PRiv3 <> 0 OR Password <> ''
Look up CREATE RULE and sp_bindrule in BOL for syntax details.
Geoff N. Hiten
Microsoft SQL Server MVP
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
> How can I make a field required based on the status of other fields? I
> have
> a Users table for my app that I also reference in forms that are filled
> out
> by everyone. Most users don't need to use this table for login, so they
> don't require a password. Each user has a UserName, Password, and a bit
> for
> each privelege that I offer. If all priveleges are 0, I want to make the
> password an optional field so I don't have to some up with a bunch of
> passwords or use a random character generator. However, if they do have
> priveleges, they are required to have a password so that if someone finds
> out
> their UserName (not hard at all), they still can't log in under a
> priveleged
> account.
> Thanks in advance
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Look up CHECK constraints in SQL Server Books Online. You can easily write
one up based on the column values in a single row.
Anith|||Try:
create table t
(
PK int primary key
, UserID char (5) not null
, Password varchar (15) null
, priv1 bit not null
, priv2 bit not null
, priv3 bit not null
, constraint CK_t check (
case
when cast (priv1 as int) + priv2 + priv3 = 0 then 1
when Password is not null then 1
else 0
end = 1)
)
go
insert t values (1, 'Me', null, 0, 0, 0)
insert t values (2, 'You', null, 1, 0, 0) -- fails
insert t values (3, 'Him', 'pwd', 1, 0, 0)
go
drop table t
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds
out
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/Apps
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds ou
t
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsRules.
Most people thing of rules in an IF.. THEN format which simply won't work.
Think of a rule as a boolean function where YES/TRUE accepts the row and
NO/FALSE rejects the row. Your requirements would lead to a rule like this:
Priv1 <> 0 OR Priv2<> 0 OR PRiv3 <> 0 OR Password <> ''
Look up CREATE RULE and sp_bindrule in BOL for syntax details.
Geoff N. Hiten
Microsoft SQL Server MVP
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
> How can I make a field required based on the status of other fields? I
> have
> a Users table for my app that I also reference in forms that are filled
> out
> by everyone. Most users don't need to use this table for login, so they
> don't require a password. Each user has a UserName, Password, and a bit
> for
> each privelege that I offer. If all priveleges are 0, I want to make the
> password an optional field so I don't have to some up with a bunch of
> passwords or use a random character generator. However, if they do have
> priveleges, they are required to have a password so that if someone finds
> out
> their UserName (not hard at all), they still can't log in under a
> priveleged
> account.
> Thanks in advance
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Look up CHECK constraints in SQL Server Books Online. You can easily write
one up based on the column values in a single row.
Anith|||Try:
create table t
(
PK int primary key
, UserID char (5) not null
, Password varchar (15) null
, priv1 bit not null
, priv2 bit not null
, priv3 bit not null
, constraint CK_t check (
case
when cast (priv1 as int) + priv2 + priv3 = 0 then 1
when Password is not null then 1
else 0
end = 1)
)
go
insert t values (1, 'Me', null, 0, 0, 0)
insert t values (2, 'You', null, 1, 0, 0) -- fails
insert t values (3, 'Him', 'pwd', 1, 0, 0)
go
drop table t
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds
out
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/Apps
Conditionally referring to fields
Hi,
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.
When you use the Fields.Items syntax, what you are varying is the field name, not the underlying database query column name (called DataField in RDL). All columns returned by the query must be known and mapped in the RDL.
If you have a query that returns different columns, you need to add them both to the query and then conditionally switch between them.
Conditionally referring to fields
Hi,
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.
When you use the Fields.Items syntax, what you are varying is the field name, not the underlying database query column name (called DataField in RDL). All columns returned by the query must be known and mapped in the RDL.
If you have a query that returns different columns, you need to add them both to the query and then conditionally switch between them.
sqlsqlConditionally Parameterizing a SQLDataSource
I'm pararmeterizing a SQLDataSource, but I've encountered a problem. The users want all records returned if no ID is provided. So when they first hit the page, they will see all records, then they can filter down to one ID if needed. However, if I add a selectparameter of ID, I have to default it to a value, which won't give the users all records.
Suggestions?
The SQLDataSource has a CancelSelectOnNullParameter property and your parameters have a ConvertEmptyStringToNull property. These should help you achieve what you want. Read about these properties on MSDN.
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.
>
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.
>
Conditionally hiding values in a Matrix
Hi all,
I have a matrix which has a runningvalue in it's detail cell, so it looks
like this:
item, date1, date2, date3...datex
A, 1, 1, 1...1
B, 1, 2, 3...x
C, 1, 2, -1...y
D, 1, -2, -3...z
This works fine and all is well. However, I now wish to filter out all rows
that have no negative values (which in the above example will only leave C
and D). Since the runningvalues themselves are not in the dataset, is there
any way of basing a whole row's visibility or inclusion based on any value
in the detail cell?
If not could you suggest how I could do this?
Thanks!
ShakShak,
I've had this issue, I found that a stored procedure which works it out
before hand is the only feasible solution. Create a temporary table
containing rows for item A, B, C etc, and a flag to say whether they
have negatives. Then in the SP still, do the normal query but join the
temporary table on item so you can include the flag in the output.
Then you can suppress a row based on the the first flag at that level
using =First(Field!Flag.Value) in the groups filters, or more
efficiently you could remove it from within the SP.
Chris
Shak wrote:
> Hi all,
> I have a matrix which has a runningvalue in it's detail cell, so it
> looks like this:
> item, date1, date2, date3...datex
> A, 1, 1, 1...1
> B, 1, 2, 3...x
> C, 1, 2, -1...y
> D, 1, -2, -3...z
> This works fine and all is well. However, I now wish to filter out
> all rows that have no negative values (which in the above example
> will only leave C and D). Since the runningvalues themselves are not
> in the dataset, is there any way of basing a whole row's visibility
> or inclusion based on any value in the detail cell?
> If not could you suggest how I could do this?
> Thanks!
> Shak|||Hi Chris,
While waiting for a reply, that was the solution I worked on. It seems
precalculation is the only way to solve it, which is a shame - some kind of
"two pass" reporting generation might be complicated though.
My solution was slightly different in that I flag all rows with and ID that
may at any time have a runningvalue less than zero; it's then just easy to
filter the dataset on that flag when required.
Thanks for the reply though!
Shak
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:ubjeuVocFHA.2960@.TK2MSFTNGP09.phx.gbl...
> Shak,
> I've had this issue, I found that a stored procedure which works it out
> before hand is the only feasible solution. Create a temporary table
> containing rows for item A, B, C etc, and a flag to say whether they
> have negatives. Then in the SP still, do the normal query but join the
> temporary table on item so you can include the flag in the output.
> Then you can suppress a row based on the the first flag at that level
> using =First(Field!Flag.Value) in the groups filters, or more
> efficiently you could remove it from within the SP.
> Chris
>
> Shak wrote:
> > Hi all,
> >
> > I have a matrix which has a runningvalue in it's detail cell, so it
> > looks like this:
> >
> > item, date1, date2, date3...datex
> > A, 1, 1, 1...1
> > B, 1, 2, 3...x
> > C, 1, 2, -1...y
> > D, 1, -2, -3...z
> >
> > This works fine and all is well. However, I now wish to filter out
> > all rows that have no negative values (which in the above example
> > will only leave C and D). Since the runningvalues themselves are not
> > in the dataset, is there any way of basing a whole row's visibility
> > or inclusion based on any value in the detail cell?
> >
> > If not could you suggest how I could do this?
> >
> > Thanks!
> >
> > Shak
>
I have a matrix which has a runningvalue in it's detail cell, so it looks
like this:
item, date1, date2, date3...datex
A, 1, 1, 1...1
B, 1, 2, 3...x
C, 1, 2, -1...y
D, 1, -2, -3...z
This works fine and all is well. However, I now wish to filter out all rows
that have no negative values (which in the above example will only leave C
and D). Since the runningvalues themselves are not in the dataset, is there
any way of basing a whole row's visibility or inclusion based on any value
in the detail cell?
If not could you suggest how I could do this?
Thanks!
ShakShak,
I've had this issue, I found that a stored procedure which works it out
before hand is the only feasible solution. Create a temporary table
containing rows for item A, B, C etc, and a flag to say whether they
have negatives. Then in the SP still, do the normal query but join the
temporary table on item so you can include the flag in the output.
Then you can suppress a row based on the the first flag at that level
using =First(Field!Flag.Value) in the groups filters, or more
efficiently you could remove it from within the SP.
Chris
Shak wrote:
> Hi all,
> I have a matrix which has a runningvalue in it's detail cell, so it
> looks like this:
> item, date1, date2, date3...datex
> A, 1, 1, 1...1
> B, 1, 2, 3...x
> C, 1, 2, -1...y
> D, 1, -2, -3...z
> This works fine and all is well. However, I now wish to filter out
> all rows that have no negative values (which in the above example
> will only leave C and D). Since the runningvalues themselves are not
> in the dataset, is there any way of basing a whole row's visibility
> or inclusion based on any value in the detail cell?
> If not could you suggest how I could do this?
> Thanks!
> Shak|||Hi Chris,
While waiting for a reply, that was the solution I worked on. It seems
precalculation is the only way to solve it, which is a shame - some kind of
"two pass" reporting generation might be complicated though.
My solution was slightly different in that I flag all rows with and ID that
may at any time have a runningvalue less than zero; it's then just easy to
filter the dataset on that flag when required.
Thanks for the reply though!
Shak
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:ubjeuVocFHA.2960@.TK2MSFTNGP09.phx.gbl...
> Shak,
> I've had this issue, I found that a stored procedure which works it out
> before hand is the only feasible solution. Create a temporary table
> containing rows for item A, B, C etc, and a flag to say whether they
> have negatives. Then in the SP still, do the normal query but join the
> temporary table on item so you can include the flag in the output.
> Then you can suppress a row based on the the first flag at that level
> using =First(Field!Flag.Value) in the groups filters, or more
> efficiently you could remove it from within the SP.
> Chris
>
> Shak wrote:
> > Hi all,
> >
> > I have a matrix which has a runningvalue in it's detail cell, so it
> > looks like this:
> >
> > item, date1, date2, date3...datex
> > A, 1, 1, 1...1
> > B, 1, 2, 3...x
> > C, 1, 2, -1...y
> > D, 1, -2, -3...z
> >
> > This works fine and all is well. However, I now wish to filter out
> > all rows that have no negative values (which in the above example
> > will only leave C and D). Since the runningvalues themselves are not
> > in the dataset, is there any way of basing a whole row's visibility
> > or inclusion based on any value in the detail cell?
> >
> > If not could you suggest how I could do this?
> >
> > Thanks!
> >
> > Shak
>
Conditionally hiding column in matrix
I have a matrix with the following format
Date
Month RowGroup1 Group2 Amount
I have month in the rows because I want to page on month when
exporting to excel in order to produce a new sheet. This seem to work
ok except for the following.
1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
is displayed, I correctly get a report paged by month but the date
column shows all date between 7/1 and 8/31 on both sheets, regardless
of having a value in the amount field. So for July all August dates
are displayed and for July all August values are displayed. Can I
hide dates where the amount is null or missing?
2. When I export to excel the individual sheets are called sheet1,
sheet2... Is there a way to give the name of each sheet the
corresponding month value?
Thanks for your assistance?
DaveRather than putting the month in the matrix, put the matrix in a list which
groups by month.
You cannot control the Excel sheet names in the current version.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Dave" <davidbr93@.yahoo.com> wrote in message
news:703390f1.0408311012.2310ed6f@.posting.google.com...
> I have a matrix with the following format
> Date
> Month RowGroup1 Group2 Amount
>
> I have month in the rows because I want to page on month when
> exporting to excel in order to produce a new sheet. This seem to work
> ok except for the following.
>
> 1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
> is displayed, I correctly get a report paged by month but the date
> column shows all date between 7/1 and 8/31 on both sheets, regardless
> of having a value in the amount field. So for July all August dates
> are displayed and for July all August values are displayed. Can I
> hide dates where the amount is null or missing?
> 2. When I export to excel the individual sheets are called sheet1,
> sheet2... Is there a way to give the name of each sheet the
> corresponding month value?
> Thanks for your assistance?
> Dave
Date
Month RowGroup1 Group2 Amount
I have month in the rows because I want to page on month when
exporting to excel in order to produce a new sheet. This seem to work
ok except for the following.
1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
is displayed, I correctly get a report paged by month but the date
column shows all date between 7/1 and 8/31 on both sheets, regardless
of having a value in the amount field. So for July all August dates
are displayed and for July all August values are displayed. Can I
hide dates where the amount is null or missing?
2. When I export to excel the individual sheets are called sheet1,
sheet2... Is there a way to give the name of each sheet the
corresponding month value?
Thanks for your assistance?
DaveRather than putting the month in the matrix, put the matrix in a list which
groups by month.
You cannot control the Excel sheet names in the current version.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Dave" <davidbr93@.yahoo.com> wrote in message
news:703390f1.0408311012.2310ed6f@.posting.google.com...
> I have a matrix with the following format
> Date
> Month RowGroup1 Group2 Amount
>
> I have month in the rows because I want to page on month when
> exporting to excel in order to produce a new sheet. This seem to work
> ok except for the following.
>
> 1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
> is displayed, I correctly get a report paged by month but the date
> column shows all date between 7/1 and 8/31 on both sheets, regardless
> of having a value in the amount field. So for July all August dates
> are displayed and for July all August values are displayed. Can I
> hide dates where the amount is null or missing?
> 2. When I export to excel the individual sheets are called sheet1,
> sheet2... Is there a way to give the name of each sheet the
> corresponding month value?
> Thanks for your assistance?
> Dave
Conditionally hide 2 out of 6 rows in a matrix?
I just spent about 30mins searching through the forums for this and saw several posts, but I didn't find a straight answer that seems like it would work for my report. How can I add only 2 data rows to a group (to hide them via the group's visible properties) and keep the rest of the rows outside of the group, but still in the same column (vertical area), as shown:
This is the view of all rows:
And this is the view I'm seeking in some cases:
This question is not clear to me. So, do you want to hide two rows from each group? If so does it have to be any specific rows. If this is what you want then I think you can do this with ROWCOUNT
|||Each Data line that I have added is for a separate field from the dataset window and I need specific fields hidden, but if it makes things more workable for changing visibility, I could go by the row number on which that they are located (although I doubt [data] row number is an accessible value).
|||
Sorry, I meant to say RowNumber in my last post and instead I said RowCount. You might be able to do this with RowNumber(). This will give you the rownumber of the dataset. CountRows() will give you the total rows within the group.
See if you can use CountRows() and RowNumber() and develop a logic based on what you need.
|||It's not as simple as that because setting a row visibility hidden to true would just blank out the row and leave white space, and the only way I see to group the data to toggle group visibility would be to stuff all data pieces into the same group. Suggestions have been given to put the data you want to hide into a group, but that may only work to hide data pieces to the right of some other data (which would be in a separate group). As far as I can see, that won't allow me to get the result I've pictured above in my first post.
Conditionally hidden groups
Hi,
I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.
This worked for me.
I have two report parameters, Group1 and Group2, and the user selects what field they want to group on. One of the options for Group2 is "None", meaning that they do not want to have a second grouping for the report. In this case, I hide the group header and footer rows, and set the document map label to Nothing.
Edit the group, and set the document map label to something like:
=IIF (Parameters!Group2.Value = "None", Nothing, "My Document Map Label for Group2")
cheers,
Helen
|||Thanks for the reply, I'll give it a whirl.Conditionally hidden groups
Hi,
I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.
This worked for me.
I have two report parameters, Group1 and Group2, and the user selects what field they want to group on. One of the options for Group2 is "None", meaning that they do not want to have a second grouping for the report. In this case, I hide the group header and footer rows, and set the document map label to Nothing.
Edit the group, and set the document map label to something like:
=IIF (Parameters!Group2.Value = "None", Nothing, "My Document Map Label for Group2")
cheers,
Helen
|||Thanks for the reply, I'll give it a whirl.Conditionally hidden groups
Hi,
I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.
This worked for me.
I have two report parameters, Group1 and Group2, and the user selects what field they want to group on. One of the options for Group2 is "None", meaning that they do not want to have a second grouping for the report. In this case, I hide the group header and footer rows, and set the document map label to Nothing.
Edit the group, and set the document map label to something like:
=IIF (Parameters!Group2.Value = "None", Nothing, "My Document Map Label for Group2")
cheers,
Helen
|||Thanks for the reply, I'll give it a whirl.Conditionally Formating Subtotal Output
I created a subtotal for my column group by right-clicking on the group and selecting subtotal. I would like to add the condition to the subtotal, that if the value is less than 0 to print zero and not the negative subtotal amount. Is there a way to do this? I haven't been able to find a way to add an expression to the greyed out subtotal field. Thanks.
I would get rid of the autogenerated subtotal, create another matrix that sums the field and conditionally edit the expression of the Sum matrix.
I have also found that the autogenerated subtotal feature has much to be desired.
Conditionally format subtotal for matrix report
I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.
For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.
Any ideas anyone?
You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||Unfortunately, that doesn't work.
I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.
I am using the following in the color field in the properties of the subtotal:
=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")
And it is showing yellow for all months.
Any pointers greatfully received
|||Hi there,Did you find an answer to this? I am stuck with the same problem.
-Thanks a lot|||
Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.
|||Hi folks,
I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!
Can anyone give solution for this?
Regards,
karthik
|||Hi there,
This worked for me:
=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")
Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.
"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.
This can be a column group or a row group.
You should be able to format the subtotals to your heart's content with this beaut.
Tuesday, March 20, 2012
Conditionally format subtotal for matrix report
I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.
For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.
Any ideas anyone?
You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||Unfortunately, that doesn't work.
I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.
I am using the following in the color field in the properties of the subtotal:
=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")
And it is showing yellow for all months.
Any pointers greatfully received
|||Hi there,Did you find an answer to this? I am stuck with the same problem.
-Thanks a lot|||
Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.
|||Hi folks,
I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!
Can anyone give solution for this?
Regards,
karthik
|||Hi there,
This worked for me:
=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")
Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.
"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.
This can be a column group or a row group.
You should be able to format the subtotals to your heart's content with this beaut.
Conditionally format subtotal for matrix report
I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.
For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.
Any ideas anyone?
You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||Unfortunately, that doesn't work.
I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.
I am using the following in the color field in the properties of the subtotal:
=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")
And it is showing yellow for all months.
Any pointers greatfully received
|||Hi there,Did you find an answer to this? I am stuck with the same problem.
-Thanks a lot|||
Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.
|||Hi folks,
I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!
Can anyone give solution for this?
Regards,
karthik
|||Hi there,
This worked for me:
=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")
Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.
"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.
This can be a column group or a row group.
You should be able to format the subtotals to your heart's content with this beaut.
Conditionally format subtotal for matrix report
I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.
For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.
Any ideas anyone?
You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||Unfortunately, that doesn't work.
I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.
I am using the following in the color field in the properties of the subtotal:
=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")
And it is showing yellow for all months.
Any pointers greatfully received
|||Hi there,Did you find an answer to this? I am stuck with the same problem.
-Thanks a lot|||
Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.
|||Hi folks,
I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!
Can anyone give solution for this?
Regards,
karthik
|||Hi there,
This worked for me:
=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")
Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.
"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.
This can be a column group or a row group.
You should be able to format the subtotals to your heart's content with this beaut.
sqlsqlConditionally extraction of data from a view
Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file
I cannot make this work... I get an Error 0xC002F309 when executing the step...
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file
I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.
Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?
Any suggestions will ge greately appreciated
IgorBUse an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.|||
Phil Brammer wrote:
Use an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.
Can you please be more detailed?
I create a SQL Task with the select count(*).
how do i set the result set? as single row? and put the result into a variable?
Where do I check the result value?|||Single row result set. Set it to a user variable.
When you connect the Execute SQL task to the data flow, double click on the green connector line. In there set the Evaluation operation to "Expression and Constraint". Set the value to Success and the Expression to: @.Your_variable > 0|||
Single row result set. Set it to a user variable.
I cannot make this work... I get an Error 0xC002F309 when executing the step...
I create a new SQL Task, change the result set to "Single Value" and build the query in the SQL Statement (the query is simply SELECT COUNT(*) FROM RNFF_VIEW).
The view is on an Oracle DB and connect to it using OLE DB.
If I execute the query in query builder I get the correct result (rows = 10)
Then I go on the "Result set" pane of the SQL Task, Add a new line: in the "result name" I put a 0 and in the variable I select new variable, give the variable a name, leave the user namespace, and select a value type of Int32.
then I execute the task, and get an error:
Execute SQL Task: An error occurred while assigning a value to variable "Pippo": "Unsupported data type on result set binding 0.".
I tryed the same on a connection to SQL 2005 and there I have no errors... may be it has to do with the Oracle Provider?|||Finally I make it work!
On an Oracle Database, the result for a SELECT COUNT(*) FROM TABLE returns a data type of NUMERIC.
Looking at the "Integration Services Data Types" I found that I have to map it to a variable of a data type DT_NUMERIC.
Unfortunately this data type doesn't exist...
I make it work converting the COUNT(*) to a FLOAT and using a variable of DT_R8 (that maps to a variable of type Double).
I think that this is a bug of the SQL Server Business Intelligence Studio interface (the type Decimal doesn't appear in the interface).
May be assigning the type programmatically will work, but I am not able to do so...
Thanks Phil for your help!
Subscribe to:
Posts (Atom)