Alternating background color

Got a dataset that is used to populate a table. Want to alternate the
background color on every other row in the displayed detail group. Easy
enough right? Here's the catch: the output is grouped at display time. A
query output might be:
KEY Value1 Value2
A 0 1
A 1 0
B 5 0
C 3 0
C 0 7
etc...
The DISPLAY output is grouped on the KEY, and the two values are summed to
give me a display such as:
KEY Value1 Value2
A 1 1
B 5 0
C 3 7
Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
"Grey")", it counts EVERY row returned from the original query, not the
grouped output, so I don't get a uniform white-grey-white pattern. Anyone
know a workaround for this?
TIA,
BrianOk, I found my workaround. Someone is bound to have this issue sometime in
the future, so I'll put the workaround here.
I created a little routine in the custom Code area of the report that simply
toggles and returns an integer value:
Dim Public bgColor As Integer = 0
Public Function alternateColor As Integer
If bgColor = 0
bgColor = 1
return bgColor
else
bgColor = 0
return bgColor
end if
End Function
When i put my method call in the background color on the entire table ROW,
the result was alternating COLUMN colors. This is because the method was
called for every cell (column) in the row. In order to get alternating ROW
color, I only called the alternateColor routine in the FIRST column in the
table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
column in the row would simply check the "Code.bgColor" value for its
current value, and base its color on that (iif(Code.bgColor = 0, "white",
"grey")).
Maybe this will come in handy for someone else someday....
Brian
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> Got a dataset that is used to populate a table. Want to alternate the
> background color on every other row in the displayed detail group. Easy
> enough right? Here's the catch: the output is grouped at display time. A
> query output might be:
> KEY Value1 Value2
> A 0 1
> A 1 0
> B 5 0
> C 3 0
> C 0 7
> etc...
> The DISPLAY output is grouped on the KEY, and the two values are summed to
> give me a display such as:
> KEY Value1 Value2
> A 1 1
> B 5 0
> C 3 7
> Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> "Grey")", it counts EVERY row returned from the original query, not the
> grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> know a workaround for this?
> TIA,
> Brian
>|||Great solution, I've been playing around with RowNumber for ages - this is
much better!!!
Thanks Brian!!!
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>|||This was just what i was looking for. My returned dataset sometimes Groups
so that rownumbers aren't in a consecutive order, giving some strange
alternate highlighting results using the conventional method. This should
work nicely, cheers
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>|||This didn't work for me since I am setting a row to hidden based on a value
in that row. SQL RS thinks that row is still there and displays two back to
back colors instead of alternating the colors.
Any ideas?
Thanks,
Don
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>

Alternate ways of deleting records - without logging

Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.All DELETE statements are logged and there is no way around that. The
"temp" table that you mention is actually a permanent table. Have you
considered going with an actual temp table - one whose name begins with#?
That would allow you to truncate the entire table (unlogged) without
affecting other users.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.|||Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||That's a toughie. The only other suggestion is to delete in chunks, say
10,000 rows at a time.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||I'm sure this is not a good solution, but you could change your database
recovery to simple.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Andrei" wrote:

> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary tabl
e.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write inf
o
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>
>|||Alternatively you could put the table in a database by itself and then set
that database to simple recover mode.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Greg Larsen" wrote:
> I'm sure this is not a good solution, but you could change your database
> recovery to simple.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Andrei" wrote:
>|||Deletes are still logged in simple mode.
This solution depends on what the OP wants. If he wants the operation to be
as fast as a truncate, simple mode won't help. If he wants to make sure the
log doesn't fill up during the delete, simple won't help.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
> Alternatively you could put the table in a database by itself and then set
> that database to simple recover mode.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Greg Larsen" wrote:
>|||Thank you, Tom, I'm going to try that.
Still leaves me with a big log file :(
I guess I'll have to clear it more often...
Andrei.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YLmjBXmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> That's a toughie. The only other suggestion is to delete in chunks, say
> 10,000 rows at a time.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only
> temporary from the point of view of the reporting action... we're so used
> to
> call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Ouch! That's a very unwieldly design issue.
Based upon your reply, it may be that your only viable option is DELETE
(which is logged).
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only temporary from the point of view of the reporting action... we're so
> used to call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Thank you, Greg and Kalen.
I need to get both of them - don't we all ? :) - less delete time and
smaller log size but in this specific order...
Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORvK%23LXmGHA.464@.TK2MSFTNGP05.phx.gbl...
> Deletes are still logged in simple mode.
> This solution depends on what the OP wants. If he wants the operation to
> be as fast as a truncate, simple mode won't help. If he wants to make sure
> the log doesn't fill up during the delete, simple won't help.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
> news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
>

Alternate row colors by group

I have a report in which the results are grouped. For example, the report
returnd 25 rows, the first 5 rows are one group (lets say all 'A's, the
second 5 rows are another group (lets say all 'B's), etc...
Can I add conditional formatting to alternate the backgroup color based on
groups?
I know I can alternate row colors using:
=iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
But I want to alternate by group. Is that possible and if so, how?
Thanks,I had a similar requirement recently but could find nothing in help or on the
net. I wrote this embedded code which works for me but I'm curious if there's
a simpler way.
Add this to the report's embedded code window:
Public GroupRowCounter As Integer = 0
Function IncrementCounter() As Integer
GroupRowCounter += 1
Return GroupRowCounter
End Function
Function GetCounter() As Integer
Return GroupRowCounter
End Function
Right click the header row you want the alternate coloring in and select
Insert Row Above. In the new, topmost header row set the Hidden property to
True and add this to one of the cells:
=Code.IncrementCounter
For the header/detail/footer portions of the group that will be visible, add
this to the BackgroundColor property to alternate the row (group) colors:
=IIF(Code.GetCounter Mod 2, "WhiteSmoke", "White")
"BillTWD" wrote:
> I have a report in which the results are grouped. For example, the report
> returnd 25 rows, the first 5 rows are one group (lets say all 'A's, the
> second 5 rows are another group (lets say all 'B's), etc...
> Can I add conditional formatting to alternate the backgroup color based on
> groups?
> I know I can alternate row colors using:
> =iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
> But I want to alternate by group. Is that possible and if so, how?
> Thanks,|||If you're grouping on Country, this is the code to use.
=Iif(RunningValue(Fields!Country.Value,CountDistinct, Nothing) Mod 2,
"LightGreen", "Cornsilk")
Mike Glaser
"BillTWD" wrote:
> I have a report in which the results are grouped. For example, the report
> returnd 25 rows, the first 5 rows are one group (lets say all 'A's, the
> second 5 rows are another group (lets say all 'B's), etc...
> Can I add conditional formatting to alternate the backgroup color based on
> groups?
> I know I can alternate row colors using:
> =iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
> But I want to alternate by group. Is that possible and if so, how?
> Thanks,

Alternate coloring in Groups

Hi,
im able to get the alternate coloring in tables using the code
=iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
when i insert a group in the table for the weekenddate.the alternate
coloring of rows has disappeared...
i cant get where im going wrong...
im grouping the record based on the weekend date...
Thanks in advamce for ur help,Using RowNumber can be a bit hit and miss.
I use a bit of code to handle this, it also works at any group level.
In report properties|Code type the following;
Public Dim Lv(4) As Boolean
Public Function Switch(ByRef Value As Boolean) As Boolean
Value = Not Value
Return Value
End Function
In the BackgroundColor property of the first cell on a row use the
following expression;
=IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
In all subsequent cells use;
=IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
Use different subscripts with Lv for different group levels.
This also works in matrix regions too.
Chris
CCP wrote:
> Hi,
> im able to get the alternate coloring in tables using the code
> =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> when i insert a group in the table for the weekenddate.the alternate
> coloring of rows has disappeared...
> i cant get where im going wrong...
> im grouping the record based on the weekend date...
> Thanks in advamce for ur help,|||Thank u very much Chris ,
It worked...
"Chris McGuigan" wrote:
> Using RowNumber can be a bit hit and miss.
> I use a bit of code to handle this, it also works at any group level.
> In report properties|Code type the following;
> Public Dim Lv(4) As Boolean
> Public Function Switch(ByRef Value As Boolean) As Boolean
> Value = Not Value
> Return Value
> End Function
> In the BackgroundColor property of the first cell on a row use the
> following expression;
> =IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
> In all subsequent cells use;
> =IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
> Use different subscripts with Lv for different group levels.
> This also works in matrix regions too.
> Chris
>
> CCP wrote:
> > Hi,
> > im able to get the alternate coloring in tables using the code
> > =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> > when i insert a group in the table for the weekenddate.the alternate
> > coloring of rows has disappeared...
> > i cant get where im going wrong...
> > im grouping the record based on the weekend date...
> >
> > Thanks in advamce for ur help,
>|||Chris,
Can i sort the records within the groups'
Thanks,
"CCP" wrote:
> Thank u very much Chris ,
> It worked...
>
> "Chris McGuigan" wrote:
> > Using RowNumber can be a bit hit and miss.
> >
> > I use a bit of code to handle this, it also works at any group level.
> >
> > In report properties|Code type the following;
> >
> > Public Dim Lv(4) As Boolean
> >
> > Public Function Switch(ByRef Value As Boolean) As Boolean
> > Value = Not Value
> > Return Value
> > End Function
> >
> > In the BackgroundColor property of the first cell on a row use the
> > following expression;
> >
> > =IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
> >
> > In all subsequent cells use;
> >
> > =IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
> >
> > Use different subscripts with Lv for different group levels.
> > This also works in matrix regions too.
> >
> > Chris
> >
> >
> >
> > CCP wrote:
> >
> > > Hi,
> > > im able to get the alternate coloring in tables using the code
> > > =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> > > when i insert a group in the table for the weekenddate.the alternate
> > > coloring of rows has disappeared...
> > > i cant get where im going wrong...
> > > im grouping the record based on the weekend date...
> > >
> > > Thanks in advamce for ur help,
> >
> >|||Sure, right click the row tag of the relevant group, click edit group
and you will see a sorting tab.
Chris
CCP wrote:
> Chris,
> Can i sort the records within the groups'
> Thanks,
> "CCP" wrote:
> > Thank u very much Chris ,
> > It worked...
> >
> >
> > "Chris McGuigan" wrote:
> >
> > > Using RowNumber can be a bit hit and miss.
> > >
> > > I use a bit of code to handle this, it also works at any group
> > > level.
> > >
> > > In report properties|Code type the following;
> > >
> > > Public Dim Lv(4) As Boolean
> > >
> > > Public Function Switch(ByRef Value As Boolean) As Boolean
> > > Value = Not Value
> > > Return Value
> > > End Function
> > >
> > > In the BackgroundColor property of the first cell on a row use the
> > > following expression;
> > >
> > > =IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
> > >
> > > In all subsequent cells use;
> > >
> > > =IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
> > >
> > > Use different subscripts with Lv for different group levels.
> > > This also works in matrix regions too.
> > >
> > > Chris
> > >
> > >
> > >
> > > CCP wrote:
> > >
> > > > Hi,
> > > > im able to get the alternate coloring in tables using the code
> > > > =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> > > > when i insert a group in the table for the weekenddate.the
> > > > alternate coloring of rows has disappeared...
> > > > i cant get where im going wrong...
> > > > im grouping the record based on the weekend date...
> > > >
> > > > Thanks in advamce for ur help,
> > >
> > >

Alternate Background Color in Row

Hi,
I am working on a report in the Reporting Services. I was wondering if we
can set alternate background color for a group of rows on the report. Let's
see the example below.
ID Name Month Field4 Field5
1 A Jan 8 9 -- Background color: red
1 A Jan 10 2 -- Background color: red
1 A May 3 3 -- Background color:
transparent
2 B Feb 2 4 -- Background color: red
3 C Apr 5 6 -- Background color:
transparent
3 C Apr 7 5 -- Background color:
transparent
4 D Mar 2 2 -- Background color: red
4 D Jun 3 1 -- Background color:
transparent
The background color will be set based on the group of ID, Name and Month
fields. Does anyone know if this is doable? If so, how to do it?
Any help would be greatly appreciated!
Thank you in advance.
Jeannetteto alternate colors in a table, place this in BackgroundColor expression:
=iif(RowNumber(Nothing) Mod 2, "AliceBlue", "White")
feel free to place whatever colors you want in there
"Jeannette" wrote:
> Hi,
> I am working on a report in the Reporting Services. I was wondering if we
> can set alternate background color for a group of rows on the report. Let's
> see the example below.
> ID Name Month Field4 Field5
> 1 A Jan 8 9 -- Background color: red
> 1 A Jan 10 2 -- Background color: red
> 1 A May 3 3 -- Background color:
> transparent
> 2 B Feb 2 4 -- Background color: red
> 3 C Apr 5 6 -- Background color:
> transparent
> 3 C Apr 7 5 -- Background color:
> transparent
> 4 D Mar 2 2 -- Background color: red
> 4 D Jun 3 1 -- Background color:
> transparent
> The background color will be set based on the group of ID, Name and Month
> fields. Does anyone know if this is doable? If so, how to do it?
> Any help would be greatly appreciated!
> Thank you in advance.
> Jeannette|||Thank you very much for your response! Carl. Although your answer is not
exactly what I am looking for, it really helps me to get to what I need for
my report. As I explained earlier in my previous message, the alternate
background color will be set based on the GROUP of ID, Name, and Color
fields. Below is what I found for my question. It really works!
In BackgroundColor expression:
=iif(RunningValue(Cstr(Fields!ID.Value) & CStr(Fields!Name.Value) &
CStr(Fields!Month.Value),CountDistinct,Nothing) Mod 2, "AliceBlue", "White")
Jeannette
"Carl Henthorn" wrote:
> to alternate colors in a table, place this in BackgroundColor expression:
> =iif(RowNumber(Nothing) Mod 2, "AliceBlue", "White")
> feel free to place whatever colors you want in there
> "Jeannette" wrote:
> > Hi,
> >
> > I am working on a report in the Reporting Services. I was wondering if we
> > can set alternate background color for a group of rows on the report. Let's
> > see the example below.
> >
> > ID Name Month Field4 Field5
> > 1 A Jan 8 9 -- Background color: red
> > 1 A Jan 10 2 -- Background color: red
> > 1 A May 3 3 -- Background color:
> > transparent
> > 2 B Feb 2 4 -- Background color: red
> > 3 C Apr 5 6 -- Background color:
> > transparent
> > 3 C Apr 7 5 -- Background color:
> > transparent
> > 4 D Mar 2 2 -- Background color: red
> > 4 D Jun 3 1 -- Background color:
> > transparent
> >
> > The background color will be set based on the group of ID, Name and Month
> > fields. Does anyone know if this is doable? If so, how to do it?
> >
> > Any help would be greatly appreciated!
> >
> > Thank you in advance.
> >
> > Jeannette

Alter table - add default value

Helo Group,
In my database I have table :
idDoc (int) IDENTITY (1, 1),
UploadDate (datetime)
DocName (varchar).
Now I ought too add default value (getdate()) for new document.
How I can use Alter table for update structure my table.
thx
PawelRHi Pawel
The Books Online page for ALTER TABLE has a section called "Adding a default
constraint to an existing column". Books Online should always be the first
place you look for syntax help. I realize that ALTER TABLE is a long
article, but the information you need is there.
ALTER TABLE my_table
ADD CONSTRAINT col_uploadDate_def
DEFAULT getdate() FOR uploadDate
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"PawelR" <pawelratajczak;-at-;poczta;dot;onet;dot;pl> wrote in message
news:umBxwWVbGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Helo Group,
> In my database I have table :
> idDoc (int) IDENTITY (1, 1),
> UploadDate (datetime)
> DocName (varchar).
> Now I ought too add default value (getdate()) for new document.
> How I can use Alter table for update structure my table.
> thx
> PawelR
>|||You can update default constraint using Enterprise Manager.
In table design, you can spcify default value for uploaddate column.
"PawelR"?? ??? ??:

> Helo Group,
> In my database I have table :
> idDoc (int) IDENTITY (1, 1),
> UploadDate (datetime)
> DocName (varchar).
> Now I ought too add default value (getdate()) for new document.
> How I can use Alter table for update structure my table.
> thx
> PawelR
>
>

Alter more than one view

Hi All,

I am new to this group and this is my first doubt i am facing at
present.

I am doing data migration. In this sequence i need to alter few views.
Alter in the sense, inside the existing query of view i want to include
one more column.

I want to do it inside one single script. If i run the script all views
should get updated.

Any help on this will be greatful.

my mail id is siddu.roy@.gmail.com.

Thanks in advanceYou can include GO batch delimiter following each CREATE VIEW statement.
Tools like OSQL, SQLCMD, SSMS and Query Analyzer send the preceding batch of
SQL statements whenever a GO is encountered.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Siddu" <siddu.roy@.gmail.comwrote in message
news:1167892621.237538.321780@.31g2000cwt.googlegro ups.com...

Quote:

Originally Posted by

Hi All,
>
I am new to this group and this is my first doubt i am facing at
present.
>
>
I am doing data migration. In this sequence i need to alter few views.
Alter in the sense, inside the existing query of view i want to include
one more column.
>
I want to do it inside one single script. If i run the script all views
should get updated.
>
Any help on this will be greatful.
>
>
my mail id is siddu.roy@.gmail.com.
>
Thanks in advance
>

|||SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.

That also means you cannot end it with a semi-colon and have to have a
keyword GO instead. That is another weird keyword in SQL Server; it
says make a batch out of the preceding statements.|||--CELKO-- wrote:

Quote:

Originally Posted by

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>

Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

BEGIN TRANSACTION
go
CREATE VIEW aaa
AS
SELECT 1 n
go
SELECT n FROM aaa
/*
n
----
1

(1 row(s) affected)
*/
go
CREATE VIEW aab
AS
SELECT n FROM aaa
go
SELECT n FROM aab
/*
n
----
1

(1 row(s) affected)
*/
go
ROLLBACK
go
SELECT n FROM aaa
/*
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'aaa'.
*/
go
DROP VIEW aaa
DROP VIEW aab
/*
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'aaa', because it does not exist in the system
catalog.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the view 'aab', because it does not exist in the system
catalog.
*/

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Alex Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

--CELKO-- wrote:

Quote:

Originally Posted by

>SQL Server is weird on this, but each VIEW statement has to be in a
>batch by itself. The reason is that VIEWs can be built on VIEWs, so
>you need to commit the first VIEW to do this.
>>

>
Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

Joe may have a point, even if did not hit the nail perfectly. Up to
SQL 6.5, there wasn't any deferred name resolution, so something like:

CREATE VIEW innerview AS SELECT 12 AS gurka
CREATE VIEW outerview AS SELECT gurka FROM innerview

would fail at compilation. For tables there were some special plumbing
to permit you to create a table and refer to it in the same batch, but
I guess they never found that worthwhile for views.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||--CELKO-- wrote:

Quote:

Originally Posted by

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>
That also means you cannot end it with a semi-colon and have to have a
keyword GO instead. That is another weird keyword in SQL Server; it
says make a batch out of the preceding statements.

Hi Joe,

Since we're picking on your answer here, can I also point out that GO
is a keyword for query analyzer (by default) and for the command line
tools. It is *not* a keyword for SQL Server, and is never sent to the
server.

This becomes obvious if ever you try to comment out a batch of code
that includes GOs. Because Comments are intepreted by SQL Server, but
the GOs are interpreted by the tool, you'll get error messages galore
(unterminated comments, unexpected * found, etc), plus whatever is
batched within the GOs within the commented out block still get
executed.

Damien|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Alex Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

--CELKO-- wrote:

Quote:

Originally Posted by

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>

Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

>
Joe may have a point, even if did not hit the nail perfectly. Up to
SQL 6.5, there wasn't any deferred name resolution, so something like:
>
CREATE VIEW innerview AS SELECT 12 AS gurka
CREATE VIEW outerview AS SELECT gurka FROM innerview
>
would fail at compilation. For tables there were some special plumbing
to permit you to create a table and refer to it in the same batch, but
I guess they never found that worthwhile for views.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Yeah, right, his post makes more sence if one replaces 'commit' with
'submit'.

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||
On Jan 4, 3:35 pm, "--CELKO--" <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>
That also means you cannot end it with a semi-colon and have to have a
keyword GO instead.

FWIW in SQL Server 2005 you can end a CREATE VIEW with a semi-colon but
it must still be "the first statement in a query batch".

Jamie.

--|||onedaywhen (jamiecollins@.xsmail.com) writes:

Quote:

Originally Posted by

FWIW in SQL Server 2005 you can end a CREATE VIEW with a semi-colon but
it must still be "the first statement in a query batch".

And still be the only.

(And I would suggest that ; is not a statement terminator in T-SQL - It's
statement initiator.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Fri, 5 Jan 2007 23:13:00 +0000 (UTC), Erland Sommarskog wrote:

Quote:

Originally Posted by

>onedaywhen (jamiecollins@.xsmail.com) writes:

Quote:

Originally Posted by

>FWIW in SQL Server 2005 you can end a CREATE VIEW with a semi-colon but
>it must still be "the first statement in a query batch".

>
>And still be the only.
>
>(And I would suggest that ; is not a statement terminator in T-SQL - It's
>statement initiator.)

Hi Erland,

I would have to disagree with that suggestion. The ; is statement
terminator in ANSI, and has been the (optional) statement terminator in
T-SQL since at least SQL Server 2000 (but I think it was allowed in
earlier versions as well). The fact that *some* statements now require
the preceding statement to be terminated doesn't change it into a
statement initiator.

Check out the location of the ; in the syntax diagrams in Books Online.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis