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
>

Conditional Navigation Action?

RS 2005.

I cant see a way of putting in a conditional navigation action on a table cell. e.g. some table cells should have a url action, others should not based on the data.

Any ideas?

Try setting the expression in your url to Nothing base on the data.

Ex. =iif(SomeField=0,"www.mywebsite.com",nothing)

Only if somefield as the value of 0 then if opens www.mywebsite.com.

conditional IF

Hello. I have a query whice look like this:
select a,b,c,d from table1;
Now - when pressing a cell in the first column we are jumping to another report with the value as parameter (called- p_param);.

In the other report the query is:
select * from table1 where a=::p_param.

What I want to do is taht in the first report I'll will check if thesecond query return any result and if so to leave it as a link. If thesecond query does't return any result (zero rows) to remove the link sothe user won't go to an empty report.

So first i need to know how to check in the first report what will be the result of the second query.

Can u help me?

Thanks in advance,
Roy.

Thank. My message already been replayed,

I just changed my query.

thanks :)

conditional IF

Hello. I have a query whice look like this:
select a,b,c,d from table1;
Now - when pressing a cell in the first column we are jumping to another report with the value as parameter (called- p_param);.

In the other report the query is:
select * from table1 where a=::p_param.

What I want to do is taht in the first report I'll will check if the second query return any result and if so to leave it as a link. If the second query does't return any result (zero rows) to remove the link so the user won't go to an empty report.

So first i need to know how to check in the first report what will be the result of the second query.

Can u help me?

Thanks in advance,
Roy.

You will need top include that within your query to let it be retunred in the same dataset as the link is filled from. Then you will be able to use it in any conditional expressions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||" top include that within your query to let it be retunred in the same dataset as the link is filled from"

Sorry but i didn't understod that.

Can you please try to explain again?

How can i get the value from the second query before the first one?

Thank for your help.
|||YOu will have to join the cuont results to your original query to have the value present in your used resultset, something like:

Select columnshere,Subqery.Counter
From SomeTable
Inner join
(
YourCOuntQuery
) Subquery
on JoinCOlumnshere

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

conditional IF

Hello. I have a query whice look like this:
select a,b,c,d from table1;
Now - when pressing a cell in the first column we are jumping to
another report with the value as parameter (called- p_param);.
In the other report the query is:
select * from table1 where a=::p_param.
What I want to do is taht in the first report I'll will check if the
second query return any result and if so to leave it as a link. If the
second query does't return any result (zero rows) to remove the link so
the user won't go to an empty report.
So first i need to know how to check in the first report what will be
the result of the second query.
Can u help me?
Thanks in advance,
Roy.What if you use some code that calls a stored procedure and it will return a
0 or a 1. Then you can return a link or a javascript alert indicating there
was no data to return so the report would be empty.
--
"Everyone knows something you don't know"
"nicknack" wrote:
> Hello. I have a query whice look like this:
> select a,b,c,d from table1;
> Now - when pressing a cell in the first column we are jumping to
> another report with the value as parameter (called- p_param);.
> In the other report the query is:
> select * from table1 where a=::p_param.
> What I want to do is taht in the first report I'll will check if the
> second query return any result and if so to leave it as a link. If the
> second query does't return any result (zero rows) to remove the link so
> the user won't go to an empty report.
> So first i need to know how to check in the first report what will be
> the result of the second query.
> Can u help me?
> Thanks in advance,
> Roy.
>|||What if you use some code that calls a stored procedure and it will return a
0 or a 1. Then you can return a link or a javascript alert indicating there
was no data to return so the report would be empty.
--
"Everyone knows something you don't know"
"nicknack" wrote:
> Hello. I have a query whice look like this:
> select a,b,c,d from table1;
> Now - when pressing a cell in the first column we are jumping to
> another report with the value as parameter (called- p_param);.
> In the other report the query is:
> select * from table1 where a=::p_param.
> What I want to do is taht in the first report I'll will check if the
> second query return any result and if so to leave it as a link. If the
> second query does't return any result (zero rows) to remove the link so
> the user won't go to an empty report.
> So first i need to know how to check in the first report what will be
> the result of the second query.
> Can u help me?
> Thanks in advance,
> Roy.
>

conditional IF

Hello. I have a query whice look like this:
select a,b,c,d from table1;
Now - when pressing a cell in the first column we are jumping to another report with the value as parameter (called- p_param);.
In the other report the query is:
select * from table1 where a=::p_param.
What I want to do is taht in the first report I'll will check if the second query return any result and if so to leave it as a link. If the second query does't return any result (zero rows) to remove the link so the user won't go to an empty report.
So first i need to know how to check in the first report what will be the result of the second query.
Can u help me?
Thanks in advance,
Roy.
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comWhat if you use some code that calls a stored procedure and it will return a
0 or a 1. Then you can return a link or a javascript alert indicating there
was no data to return so the report would be empty.
--
"Everyone knows something you don't know"
"roy mm" wrote:
> Hello. I have a query whice look like this:
> select a,b,c,d from table1;
> Now - when pressing a cell in the first column we are jumping to another report with the value as parameter (called- p_param);.
> In the other report the query is:
> select * from table1 where a=::p_param.
> What I want to do is taht in the first report I'll will check if the second query return any result and if so to leave it as a link. If the second query does't return any result (zero rows) to remove the link so the user won't go to an empty report.
> So first i need to know how to check in the first report what will be the result of the second query.
> Can u help me?
> Thanks in advance,
> Roy.
>
> From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>|||What if you use some code that calls a stored procedure and it will return a
0 or a 1. Then you can return a link or a javascript alert indicating there
was no data to return so the report would be empty.
--
"Everyone knows something you don't know"
"roy mm" wrote:
> Hello. I have a query whice look like this:
> select a,b,c,d from table1;
> Now - when pressing a cell in the first column we are jumping to another report with the value as parameter (called- p_param);.
> In the other report the query is:
> select * from table1 where a=::p_param.
> What I want to do is taht in the first report I'll will check if the second query return any result and if so to leave it as a link. If the second query does't return any result (zero rows) to remove the link so the user won't go to an empty report.
> So first i need to know how to check in the first report what will be the result of the second query.
> Can u help me?
> Thanks in advance,
> Roy.
>
> From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>

Conditional Formatting on a table

Hi all,

Any ideas how to apply conditional formatting depending on the value of a particular cell in a table. Basically i want to say that if a value is less than 0, then that figure should have a red background, if the figure is = 0, then leave it white, and then if the figure is more than 0, then put a green background on it. Any ideas how to do this??

Miles

Here is an article that may help you outhttp://blogs.msdn.com/swisowaty/attachment/661446.ashx

About half way into the article it talks about conditional formating with colors.

conditional formatting in reporting services (2005)

I am new to reporting services and am unable to get cell colouring conditional formatting to work in of a report. Basically I need to have the following:

>-5% = "Red"

Between -5% and -1% = "Green"

0% = "White"

Between 1% and 5% = "Green"

>5% = "Red"

Here is what I have tried to use:

=iif((Fields!TrueUpPercQ1.Value >0 and

Fields!TrueUpPercQ1.Value <=0.05) or (Fields!TrueUpPercQ1.Value < 0 and

Fields!TrueUpPercQ1.Value >=-0.05), "Green",

iif(Fields!TrueUpPercQ1.Value =0, "White","Red"))

Any ideas?

Thanks

Try this....

=iif((Fields!TrueUpPercQ1.Value > 0, "Red", IIF(Fields!TrueUpPercQ1.Value <=0.05, "Red", IIF(Fields!TrueUpPercQ1.Value < 0, "Blue,

IIF(Fields!TrueUpPercQ1.Value >=-0.05, "Green", iif(Fields!TrueUpPercQ1.Value =0, "White","Red")))))

You may want to tweak this to suit your need. Also make sure the number of closing brackets are correct. This is the way the IIF is used. Hope it helps. Let me know.

|||

Iif would work, but I think switch would be better

Try this...you have to have even number of arguments for it to work correctly. The first is your statement to evaluate to true then the second argument is executed, if false it proceeds to the next (3rd) argument until something evaluates as true.

=SWITCH(Fields!TrueUpPercQ1.Value <=0.05, "Red", Fields!TrueUpPercQ1.Value < 0, "Green", Fields!TrueUpPercQ1.Value =0, "White")

1 2 3 4 5 6

|||

Iif would work, but I think switch would be better

Try this...you have to have even number of arguments for it to work correctly. The first is your statement to evaluate to true then the second argument is executed, if false it proceeds to the next (3rd) argument until something evaluates as true.

=SWITCH(Fields!TrueUpPercQ1.Value <=0.05, "Red", Fields!TrueUpPercQ1.Value < 0, "Green", Fields!TrueUpPercQ1.Value =0, "White")

Conditional formatting for missing numeric data

I created a matrix report which includes numeric data to be formatted as
currency. In cases where the data is missing, the empty cell displays "$".
If I use the iif() function for conditional formatting, it inserts the entire
expression into all of the cells. Does anyone know how I can format the
cells that do not contain any data so that nothing will be displayed?
thanks!It sounds like you need Nothing:
=iif(Fields!MyValue.Value Is Nothing,"","C0")
Put the expression in the Custom field in the Textbox Properties window.
I think you can use "". If not, choose something else.
"Anna" wrote:
> I created a matrix report which includes numeric data to be formatted as
> currency. In cases where the data is missing, the empty cell displays "$".
> If I use the iif() function for conditional formatting, it inserts the entire
> expression into all of the cells. Does anyone know how I can format the
> cells that do not contain any data so that nothing will be displayed?
> thanks!