background color in matrix control

[zerkat]

Hi

- I am using a matrix in sql reporting services to build a crosstab report. I set the background color in an expression on the background color property of the row. It works fine as long as the database returns a value. If one cell is null then the color is not applied to that cell. Reporting Services just skips over it so there's a nice white space in the middle of a row.

Any one have any work arounds for this?

 

[RiverGuy]

What is your expression?

 

[xlbo]

Simple answer is that in your query, use an ISNULL expression to return a 0 rather than null:

Select x,y,z ,sum(isnull(FieldName,0)) as SumField.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[zerkat]

Tried IsNull already and does not work because there are no nulls in the data I am retreiving. The empty cells happen when the matrix creates the crosstab report - where there is no data for a column. I didn't explain that very well in my initial posting - I see that I called it null instead. Sorry.

The expression I am using to set the color is:

=Switch(First(Fields!Agency.Value) = 1, "SkyBlue", First(Fields!Agency.Value) = 3, "Yellow")

 

[xlbo]

Could try setting the visibility for the column group based on a test for 0 value...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[zerkat]

I am not having a problem with setting visibility - it is not showing the background color in empty cells, the cells themselves are visible but the background color is not displaying. The matrix displays like a patchwork quilt. Where there isn't a value the background color does not display.

 

[xlbo]

sorry - was thinking it would be easier just to hide the offending column if it has not data in it

what determines Fields!Agency.Value ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[zerkat]

It is a value coming from a column in the database.

 

[xlbo]

...and does the column that has the blank data also have no data for the Agency.Value?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[zerkat]

It will not have a value for agency. I was just working with one of our senior programmers and we were trying to figure out if there is a way to access the adjacent cell in the row and set the color to that cells background color if there is no agency value. We couldn't figure out how to access that property though....even if it is accessible.

 

[xlbo]

Are all the background colours the same for each field? so either all yellow or all skyblue?

if so then you can look at a global colour based on a seperate calc - if they differ by field in the matrix, what colour would you want the null one set to?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[zerkat]

Pretty much - a whole entire row will be yellow, blue or white.

I tried to find the global color property but didn't see it. Where would that be?

 

[xlbo]

All I was thinking was that you could hold a calc in a textbox which gives you the colour you want

Hide that textbox then reference it using the reportitems collection so:

In a new textbox, create a calc which returns "Yellow" or "SkyBlue".

Call that textbox "tbColourRef"

For the backgroundcolour expression, simply use

=ReportItems!tbColoursRef.Value

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[zerkat]

Tried this out - not sure if this is working quite the way I expected or I am doing it wrong.

This is what I did -

Opened the rdl. Right clicked on the details cell added column, made column invisible. Set value of new column to function that returns color then set the row to the value of the column. Ran report and received error that report item expressions can only refer to other report items within same grouping scope.

Where do I add this invisible text box? Do I need to add another grouping or row? Looked through my options all I see are insert group, add column, add row, edit group. Or did you meant to add this textbox any where in the report - not necessarily within the matrix control?

 

[xlbo]

Yup - add it to the report outside of the matrix

As it's going to be invisible, it shouldn't impact on the rest of your report layout

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[zerkat]

That works - just one problem. Since I am using the value of a column to determine the color, the value of the textbox does not change per value of the column. It uses the first value it receives from the dataset then my whole matrix displays in one color.

I have tried writing a function and returning the values plus a switch and IIF expression and they all work the same way.

When I go into expressions it does say that the textbox is not linked to the dataset. The option to link it to the dataset is disabled when I look in the textbox properties. I would think that if it were linked to the dataset that it might work but it's not giving me the option.
Is there a way to tell the textbox to loop through the dataset manually?

 

[xlbo]

I only suggested that as a fix because you said that the whole row will be either yellow or blue or white...

The point about using a textbox is exactly because it is NOT linked to the dataset so it gets around the issue of contextual calculation (ie where you have a null value in the column, the Agency Value is also null)

If this is not the case then tbh, your best bet will be to force a value into the agency field in the query using isnull()

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[zerkat]

I think we are talking about the same thing -

A single row will be one color (i.e. blue, yellow or white) but what it's doing is turning every single row one color, (i.e. all rows in the report appear blue).

IsNull does not work with this query because there are no nulls being returned from the query itself - there are no nulls in the table we need to use. The empty/null cells are generated when the query is used in the matrix control. Since matrix control generates a cross tab query, where there are no values for a column that cell is blank. I have to handle this on the report end. I have tried setting a value for these empty cells; although the value does display the background color will not, even if I perform a function to check for that given value.

I really do appreciate your help thus far. It's sometimes difficult to figure out the tone of these postings so please don't think I am upset or anything.

 

[xlbo]

S'ok - we'll get there!

If you have managed to set a value for the empty cells, you should be able to reference whatever logic you used to create the value to also set the background colour

Can you post what you used to se the value in the empty cells?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[zerkat]

=IIF(NOT(SUM(Fields!Hours.Value) Is Nothing, Sum(Fields!Hours.Value), "0")

Just to clarify what the report is actually reporting on - the report displays positions along with hours spent on a given project. The agency column is just to determine the color coding per request of management.