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
> >
>
>

Allow user to choose grouping order?

I have a report which is working off a dataset with 5 columns. Date, Type, Color, Value1, Value2
We would like to set up a report such the user viewing the report could drag and drop column headers and set up grouping in whatever order they want. What would be the best way to do this? Is it even possible, or do we have to create a separate report for each combination?

For example, One report just groups by Date and Type. They might also want to group by Type then Date. Other times we want Date, Type, then color.

I used to run the reports in a third party grid which supported this, but since it was all client side, it was very slow for large datasets.

You can change the grouping through report parameters. Chris has an example of this in his blog:

http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx

Allow reference to dynamically-created DataColumn from reportHelp?

Hi everyone,

I currently have a strongly-typed dataset that, in code, will expand tables according to relationships in that dataset in order to bind it to my ReportViewer. My problem now is whenever I try to run it, I get an error looking like the following:

An error occurred during local report processing.
The definition of the report 'Main Report' is invalid.
The Value expression for the textbox `textbox15` refers to the field `Parent_FullName`. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data scope.

The reason I'm dynamically creating this dataset on the fly is because I couldn't find how to do a one-to-one relationship (bind a column in one report table to a column in a different dataset table than everything else on that report table). I realize the problem is that in the report's "DataSets" node, it doesn't include a definition of these generated columns... but is there any way around this?

Time is of the essence, but I'd appreciate any help at all!

TIA! =)

Hello,

You may want to look into using calculated fields. These allow you to use expressions as the source of data fields and are used just like regular fields.

For more information about calculated fields:
http://msdn2.microsoft.com/en-us/library/ms156295.aspx

Also, you should find more helpful information if you search for "calculated fields" in this forum and the web.

Ian|||

Hi Ian,

That's exactly what I'm doing. When the dataset is passed in to the form, I have the form automatically expand on its existing relationships and add calculated fields to the datatables at runtime. I can't do this at design-time because I'm using this one form to eventually run 100's of reports--all using various strongly-typed datasets, etc. I think I found the problem, in that it holds the dataset information in the .rdlc file itself and expects ALL the fields to be there. Unless someone knows a way to override it -requiring- all fields to be there(?), I assume I'll have to parse the XML in the .rdlc file at runtime also and add in the XML for all the calculated fields I add as it adds them to the datatables themselves. Any better suggestions are appreciated though. =)

Thanks!

|||Okay, dynamically adding the columns worked for my immediate needs, but that soon passed. :-\ Is there ANY way at all to link different datatables in the same dataset together? I need to take a value from a field in the current scope (i.e. "FamilyID" in the dsMember_Entity dataset) and link it up with a field in another dataset ("FamilyName" in dsMember_Family where dsMember_Entity.FamilyID = dsMember_Family.FamilyID). There are a LOT of fields that will be worked this way, so are there any functions that would allow me to do this using calculated fields? If not, are there any suggestions how one might go about this?|||There are no built-in mechanisims in RS for joining multiple datasets together. Is it possible to join the entity and family table together in the SQL query, so that all fields are avaliable? If not, then you may want to look at writing a custom data extenstion that joins the data tables into one dataset. Or you may want to look into using custom code to return the appropriate value from one dataset given a value from another.

Let me know if you want more information about any of these topics.

Ian|||

Hi Ian,

The tables are joined in to one dataset. We're using strongly-typed datasets, and have all of the relationships already set up. Is there any way to use the existing relationships to get the values I'd like?

|||With the built-in data extensions, you can only access one table per dataset, so the tables need to be joined into one table based on the relationships already set up to be used in a data region. (This is without the use of custom lookup code and secondary datasets.) Did you join the tables on FamilyID, so each row would have the rows from the dsMember_Family table joined with the appropriate rows of the dsMember_Entity table? If so, the fields should be available in the same fields collection.

Also, how are you creating and accessing the strongly typed dataset?

Ian

|||

The dataset has multiple tables, as some of the results are many-to-one we can't just lop all the data onto one table. Foreign key relationships exist in that strongly-typed dataset, however.

We have an internal utility that creates the strongly-typed dataset from a schema it gets from stored procedures. I.e., a "GetEntityData" stored procedure will return several different resultsets--our schema generator checks out these results and writes a strongly-typed dataset based on it... then we just add them into our project and can do any changes we require.

The datasets are accessed in the WinForm's code; we want to use one form for reports instead of one form per report, so we just pass in the dataset we need directly to the form and it handles the binding.

All possible combination in a where condition

Dear all,

In the dataset of a report in the Reporting Services 2000, I need to write an SQL statment with a Where condition which makes all possible combination of 10 conditions.

Could that be done by any way except by ORing and ANDing all the conditions?

For example, If I have three conditions, A, B, and C, I need all possible combinations in a WHERE condition as follows

SELECT *
FROM table
WHERE A = @.A
OR B = @.B
OR C = @.C
OR A = @.A and B = @.B
OR A = @.A and C = @.C
OR B = @.B and C = @.C
OR A = @.A and B = @.B and C = @.C

Note: @.A, @.B, @.C are Report parameters

I need to do the same with 10 conditions, I think it's too much to do it that way.
My question is, is there any other way I can do that with the parameters of a Report in Reporting Services.

Any help is greatly appreciated.

Thank you.SELECT * FROM table t1 CROSS JOIN table t2

?|||How you write it your where clause will be true if any of A = @.A, B = @.B or C = @.C evaluates to true.
SELECT *
FROM table
WHERE A = @.A OR B = @.B OR C = @.C
Will be true for all combinations like (A + @.A and B = @.B) because first statement say that if only 1 is ok it will be true.

|||If it is a stored procedure, pass in -1 or '-1' for All values.
IF @.A = -1 SET @.A = null
IF @.B...
IF @.C...
SELECT *
FROM table
WHERE COALESCE(@.A,A) = A
AND COALESCE(@.B,B) = B
AND COALESCE(@.C,C) = C
COALESCE substitutes null values with the value specified as the second parameter. So A will always = A when @.A = null.
Maybe this will help?