Background color for each first row in groups

I have a report that groups on departments. I want the first row for each
group to be light gray, while the row below it are white. Any suggestions?
ThanksIt has been a little while since you posted this question, but I think I have
an answer I discovered while researching a solution to one of my problems.
The RowNumber aggregate function might be helpful to you. Try the following
somewhere in your report to verify this will help:
RowNumber("group_name")
Try concatinating that to a text field on the row you want the coloring to
ultimately be on; I believe you will find that the first record in each group
has a 1. Presuming it does give you a 1 where you want it to be, all you
need to do then is select the row in question, and place the following
expression in the Background Color field (or any othr formatting field, like
Font Weight or Font Style):
=IIF( RowNumber("group_name") = 1 , "Light Gray" , "White" )
"PatNUFC" wrote:
> I have a report that groups on departments. I want the first row for each
> group to be light gray, while the row below it are white. Any suggestions?
> Thanks

Backcolor property

Is there a simple way to make the backcolor of a table
object alternate? If there was a row number global I could
just use an expression.Try something like this for the background color on the row...
=iif(RowNumber(Nothing) Mod 2, "white", "gainsboro")
"John Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:1f44601c4579a$fa975ba0$a501280a@.phx.gbl...
> Is there a simple way to make the backcolor of a table
> object alternate? If there was a row number global I could
> just use an expression.sql

Avoiding temp tables

Dear all,
I'd like to rewrite this update statement without using a temp table.
For each row with duplicate my_id's, the reference_no field should be
set to the number of duplicates for that id.
When I try rewriting this as a single statement I have problems getting
'at' the calculated duplicates field.
Cheers!
select my_id, count(*) as duplicates
into #tmp
from my_table
group by my_id
having count(*) > 1
update my_table
set my_table.reference_no = #tmp.duplicates
from #tmp, my_table
where #tmp.my_id = my_table.my_idOn 7 Mar 2005 23:53:24 -0800, davidol@.hushmail.com wrote:

>I'd like to rewrite this update statement without using a temp table.
>For each row with duplicate my_id's, the reference_no field should be
>set to the number of duplicates for that id.
Hi Davidol,
This version uses only ANSI-standard constructions. You need to use the
column(s) that make up the primary key of the table; I've assumed a
compound primary key on column PK01 and PK02 for my example:
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE EXISTS (SELECT *
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id
AND ( m2.PK01 <> my_table.PK01
OR m2.PK02 <> my_table.PK02))
If you don't have a primary key, you should change your design. In case
you can't do that right now, try the following query (still ANSI
compliant, but probably slower than the first query):
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id) > 1
Finally, if you don't care about portability, you could use the
proprietary UPDATE FROM syntax, as below. Performance might be better
than the ANSI-compliant version (but test it out to be sure). Don't
forget to document the use of a non-ANSI compliant construction (and
include a commented ANSI-compliant version in the code, or include it in
external documentation, so that you don't have to redo the thinking when
you do have to port your code).
UPDATE m
SET m.reference_no = a.cnt
FROM my_table AS m
INNER JOIN (SELECT my_id, COUNT(*) AS cnt
FROM my_table
GROUP BY my_id
HAVING COUNT(*) > 1) AS a
ON a.my_id = m.my_id
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this also
update my_table set reference=T.Count from (
select my_id,count(*) as 'Count' from my_table group by my_id having
count(*)>1)
T , my_table A where A.my_id=T.my_id
Madhivanan

Average problem

Hi,
I've a subtotal row in a matrix. I do an average of a column but the avg
function return me the average of all results of each rows. In fact, I want
do the average of each colums except where the results is equal to 0.
How can I do that?Aggregate functions like Sum, Count, Avg, etc. only consider values which
are not null.
Try this:
=Avg(iif(Fields!X.Value = 0, Nothing, Fields!X.Value))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Seb" <Seb@.discussions.microsoft.com> wrote in message
news:AA16A434-5BB4-4D03-B9E8-4C4D5224BB1D@.microsoft.com...
> Hi,
> I've a subtotal row in a matrix. I do an average of a column but the avg
> function return me the average of all results of each rows. In fact, I
want
> do the average of each colums except where the results is equal to 0.
> How can I do that?

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

autonumber is resetting

i am using autonumber as a primary key in one table. in my app., row from that table will be moved to another table after some time but that autonumber is important. when there are no any data in first table, and new row is inserted, autonumber starts from 1 which i don't want as there will be data redundancy when it will be moved to another table. so is there any way i can force the autonumber to start from previous value rathe than 1?Are you using an IDENTITY column? If so, I do not understand why you would have the problem you describe.|||i am using identity datatype.

yeah, it's strange. i added that data via webform interface and it was 1. then i tried adding directly in db but it gave me 10 as new value which is correct.

so just wondering if there is any settings i need to use just to make sure that it doesn't happen again.