backgroundcolor in groups

Hello,
There are 3 groups in my table. The second and third group are hidden and
can be drilled down. I have a code to set alternate color for backgroundcolor
of each rows (the first group). The code is "= iif(RunningValue(
Fields!uniqueID.Value, CountDistinct, Nothing) mod 2, "white", "yellow")".
However, some rows can not return alternate color. The report looks like that:
row1 (backgroundcolor is white)
row2 (backgroundcolor is yellow)
row3 (backgroundcolor is white)
row4 (backgroundcolor is white)
row5 (backgroundcolor is yellow)
Does anybody know how to set alternate color for groups?
Thanks!The RunningValue runs based on the details, but you are using the value to
determine the color of groups. If one group contains an even number of
details, the next group will have the same backgroundcolor as the previous
group.
At this point, there is no predefined aggregate function available that
would just count the groups. However, you may want to read this article
which explains the steps necessary to get this working:
http://blogs.msdn.com/chrishays/archive/2004/08/30/223068.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:C44CDF74-4505-47C7-B7EB-F53F788698EF@.microsoft.com...
> Hello,
> There are 3 groups in my table. The second and third group are hidden and
> can be drilled down. I have a code to set alternate color for
backgroundcolor
> of each rows (the first group). The code is "= iif(RunningValue(
> Fields!uniqueID.Value, CountDistinct, Nothing) mod 2, "white", "yellow")".
> However, some rows can not return alternate color. The report looks like
that:
> row1 (backgroundcolor is white)
> row2 (backgroundcolor is yellow)
> row3 (backgroundcolor is white)
> row4 (backgroundcolor is white)
> row5 (backgroundcolor is yellow)
> Does anybody know how to set alternate color for groups?
> Thanks!
>
>|||Thanks for your help! It works after I change my code to
"= iif(RunningValue(Fields!uniqueID.Value & Fields!group1.Value,
CountDistinct, Nothing) mod 2, "white", "yellow")".
Jean
"Robert Bruckner [MSFT]" wrote:
> The RunningValue runs based on the details, but you are using the value to
> determine the color of groups. If one group contains an even number of
> details, the next group will have the same backgroundcolor as the previous
> group.
> At this point, there is no predefined aggregate function available that
> would just count the groups. However, you may want to read this article
> which explains the steps necessary to get this working:
> http://blogs.msdn.com/chrishays/archive/2004/08/30/223068.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jean" <Jean@.discussions.microsoft.com> wrote in message
> news:C44CDF74-4505-47C7-B7EB-F53F788698EF@.microsoft.com...
> > Hello,
> >
> > There are 3 groups in my table. The second and third group are hidden and
> > can be drilled down. I have a code to set alternate color for
> backgroundcolor
> > of each rows (the first group). The code is "= iif(RunningValue(
> > Fields!uniqueID.Value, CountDistinct, Nothing) mod 2, "white", "yellow")".
> > However, some rows can not return alternate color. The report looks like
> that:
> >
> > row1 (backgroundcolor is white)
> > row2 (backgroundcolor is yellow)
> > row3 (backgroundcolor is white)
> > row4 (backgroundcolor is white)
> > row5 (backgroundcolor is yellow)
> >
> > Does anybody know how to set alternate color for groups?
> >
> > Thanks!
> >
> >
> >
> >
>
>sql

Average on a calculated column in matrix

Hi,

i have some data i am showing in a matrix. The first row group is on month, the second row group is on year, in this way i get year on year reporting of my sales data.

One of the columns i have in the matrix is a "average $ per hour". The calculation for this works nicely on each row, i just get the total sales, and divide by the number of hours in that particular month (which i calculate). So it looks like this (these figures are invented):

Month Year Total sales Avg $ per hour May 2005 $123,456 $20.15 2006 $129,112 $21.56 June 2005 $100,449 $18.45 2006 $130,889 $20.57 Total $483,906 $20.10

Now i need to also put a average dollar per hour into the total row, but i cannot work out how to calculate the number of days covered by all the months listed in the matrix. Is there a way i can calculate this with an expression on the report, or will i have to resort to calculating the days per month as part of the mdx query that gathers the data?

Thanks!

sluggy

How do you calculate the number of hours in a particular month? If it is a calculated field in the dataset, you can change the expression you use for the average $ per hour column to be =Sum(Fields!Sales.Value)/Sum(Fields!Hours.Value). The value of Sum(Fields!Hours.Value) in the detail cell would be the hours for the particular month year, the value of it in the total cell should be the hours for the entire matrix.|||

Hi Fang,

the calculation to get the number of hours in a month is performed inline in the report, it is:

Sales / (24 * DateDiff("d", CDate("2006-" + Fields!Calendar_Month.Value + "01"), DateAdd("M", 1, CDate("2006-" + Fields!Calendar_Month.Value + "01"))))

(i know this doesn't allow for Feb, that is still to be fixed :) ). In the detail rows, this works nicely, but i can't see a way to implement this type of calculation in the total row.

What would be the best way to do this?

Thanks,

sluggy

|||For the total row, you'll need to sum together the hours for all the months and eliminate the duplicate periods. One way to do is through custom code. Suppose the custom function is called GetHours(), you'd call it in the expression in the total cell like this: =Sum(GetHours(Fields!Calendar_Month.Value, Fields!Calendar_Year.Value)). Inside the function, you can check the current month/year against a look-up table (which keeps track of the different periods you've seen), if it's a dup, return 0, if not, return the hours for that month.|||

Hi Fang,

thanks, that's the approach i ended up taking, here was the result (i thought i had a bug, but it was my own fault).

sluggy