Group Footer to alternate color

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Group Footer to alternate color

Author

Topic

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-11-02 : 09:30:23

I'm trying to get every other group footer to be a different color, and can't seem to get the distinctcount() function to work. I also tried the count() function, but that's always giving me 1.

Any suggestions are welcome!

Thanks!

jhermiz

3564 Posts

Posted - 2005-11-02 : 10:44:12

quote:
Originally posted by bubberz

I'm trying to get every other group footer to be a different color, and can't seem to get the distinctcount() function to work. I also tried the count() function, but that's always giving me 1.

Any suggestions are welcome!

Thanks!

You have to be very tricky here...

Here is what you can do...

Do you know that RS allows you to get a sequential ID in a cell...well you can use that to help you get a number and then use that number to determine "every other row".

In a report's cell try this:

=RowNumber(Nothing)

And name that cell SomeID...

Now in your footer do this in the backcolor properties:

=IIF(SomeID%2, "Black", "Red")

What exactly does this do...The % is a modulus operator and gives you the remainder of integer division. If you take the remainder of a number it is either 0 or greater then 0. With this expression if the value of SomeID% 2 returns some remainder other then 0 it is essentially true and you get the value "Black". If the value is 0 then the expression is false and you get the value "Red".

HTH,

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-11-02 : 10:51:43

BTW, count should also work as I have done this in the past.
I think the problem is you are Counting() on the wrong row ?
How is your group by clause? Do you have a Group by clause, or are you manually doing the groupping on the report? Make sure you have a group header and a footer and that the footer contains =Count(YourID).

Then you can do:

=IIF(Count(YourID) % 2, "Blue", "Red")

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-11-02 : 11:12:25

Hi Jon!
The grouping is by a distinct / max field.

I'm obviously doing something wrong. I can't get past about 20 errors when I add the =rowcount(Nothing) in the detail band, then try the modulus in the group footer.

jhermiz

3564 Posts

Posted - 2005-11-02 : 11:20:26

Please read my post, there is no such RowCount function.

I said RowNumber

See RS BOL:

RowNumber Function
Returns a running count of all rows in the specified scope.

Syntax
RowNumber(Scope)
Parameters
Scope
(String) The name of a dataset, grouping, or data region that contains the report items to which to apply the aggregate function. If a dataset is specified, the running value is not reset throughout the entire dataset. If a grouping is specified, the running value is reset when the group expression changes. If a data region is specified, the running value is reset for each new instance of the data region. For more information about the Scope parameter, see Aggregate Functions.
Return Type
Returns an Integer.

Example
The following code example provides a running count of rows in the outermost data region:

RowNumber(Nothing)

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-11-02 : 11:25:56

if I use only use the following in the group footer band as the left most cell:
=runningvalue(Fields!My_Number.Value, count, "MyNumberGrouping")

..I get 1 on each footer instance.

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-11-02 : 11:33:15

In a report's cell try this:

" =RowNumber(Nothing)

And name that cell SomeID..."

...does this go in the detail or footer band?

Thanks for the help!

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-11-02 : 11:46:14

Hi Jon!

Yes, I did read your post, and have been looking into the BOL this morning. Thanks for checking on that!

What I did, but still get errors:
1. Added a left most column
2. In the details band for this new column, set the Name property to SomeID
3. In the properties for SomeID, I put the expression =rowcount(Nothing)
4. In the footer band, for the background color, I put the expression =IIF(SomeID%2, "White", "Gainsboro")

When I try to preview the report, I get errors for each of the footer columns.

Thanks!

jhermiz

3564 Posts

Posted - 2005-11-02 : 13:43:04

you are not doing it correctly, you need to use Fields!SomeID.Value...
not just SomeID, I was only giving you that for an example, I thought you knew the syntax for RS.

You may want to research google some more on your problem, just to get better with the syntax.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-11-02 : 16:15:17

I have a counter for the grouping, and the expression is:
=rownumber(Nothing)

This works, and it's in a textbox name textbox31.
I'm trying to get the value of the textbox for the background color expression, but can't seem to do it with: textbox31.value

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-11-02 : 16:35:10

Got it by simply adding this to the group footer Background color property:

= iif (rownumber(Nothing) mod 2, "White", "Gainsboro")

jhermiz

3564 Posts

Posted - 2005-11-02 : 17:22:12

quote:
Originally posted by bubberz

Got it by simply adding this to the group footer Background color property:

= iif (rownumber(Nothing) mod 2, "White", "Gainsboro")

Your quite welcome...

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources