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

All parameters as default

Using SSRS 2000, I want to be able to have all the results displayed as
default rather than a specific value set for default before the user clicks
on choosing a specific parameter value.
Is this possible?
Thanks for any suggestions!If what you want to do is use default parameter values and hide their
values from the user then open the Report Parameters dialogue box in
report designer and remove any values from the Prompt window. This
will cause the parameters to not display.|||Thank you for your reponse!
I actually want the user to be able to see and choose the parameters, but I
want the default to be all possible values and not one value for the cases
where they don't want to limit the results.
"toolman" wrote:
> If what you want to do is use default parameter values and hide their
> values from the user then open the Report Parameters dialogue box in
> report designer and remove any values from the Prompt window. This
> will cause the parameters to not display.
>|||I have on occasion provided an "ALL" option to my parameter and set the
default parameter to "ALL".
In SQL, the parameter list is loaded from a dataset as
SELECT <FieldName> AS OutName FROM <TableName>
UNION ALL
SELECT "All" AS OutName FROM <TableName>
In my data dataset the code becomes:
SELECT...
FROM ...
WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
"SK" wrote:
> Thank you for your reponse!
> I actually want the user to be able to see and choose the parameters, but I
> want the default to be all possible values and not one value for the cases
> where they don't want to limit the results.
> "toolman" wrote:
> > If what you want to do is use default parameter values and hide their
> > values from the user then open the Report Parameters dialogue box in
> > report designer and remove any values from the Prompt window. This
> > will cause the parameters to not display.
> >
> >|||Thank you very much William, for responding!
I think this is exactly what I was looking for!
I created a stored procedure in SQL Server and it works fine in the data
tab. When I run it the "Define Query Parameters" dialog box comes up and I
put 'All' and it works fine, giving me all the values. The problem is when I
preview it, it gives me only one value. I've put 'All' as the default under
the report parameter so I'm not sure what's wrong exactly!
"William" wrote:
> I have on occasion provided an "ALL" option to my parameter and set the
> default parameter to "ALL".
> In SQL, the parameter list is loaded from a dataset as
> SELECT <FieldName> AS OutName FROM <TableName>
> UNION ALL
> SELECT "All" AS OutName FROM <TableName>
> In my data dataset the code becomes:
> SELECT...
> FROM ...
> WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
>
> "SK" wrote:
> > Thank you for your reponse!
> >
> > I actually want the user to be able to see and choose the parameters, but I
> > want the default to be all possible values and not one value for the cases
> > where they don't want to limit the results.
> >
> > "toolman" wrote:
> >
> > > If what you want to do is use default parameter values and hide their
> > > values from the user then open the Report Parameters dialogue box in
> > > report designer and remove any values from the Prompt window. This
> > > will cause the parameters to not display.
> > >
> > >|||Have you altered your detail dataset to test the parameter for the "All'
option?
SELECT...
FROM ...
WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
AND ....
"SK" wrote:
> Thank you very much William, for responding!
> I think this is exactly what I was looking for!
> I created a stored procedure in SQL Server and it works fine in the data
> tab. When I run it the "Define Query Parameters" dialog box comes up and I
> put 'All' and it works fine, giving me all the values. The problem is when I
> preview it, it gives me only one value. I've put 'All' as the default under
> the report parameter so I'm not sure what's wrong exactly!
>
> "William" wrote:
> > I have on occasion provided an "ALL" option to my parameter and set the
> > default parameter to "ALL".
> >
> > In SQL, the parameter list is loaded from a dataset as
> >
> > SELECT <FieldName> AS OutName FROM <TableName>
> > UNION ALL
> > SELECT "All" AS OutName FROM <TableName>
> >
> > In my data dataset the code becomes:
> >
> > SELECT...
> > FROM ...
> > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
> >
> >
> >
> > "SK" wrote:
> >
> > > Thank you for your reponse!
> > >
> > > I actually want the user to be able to see and choose the parameters, but I
> > > want the default to be all possible values and not one value for the cases
> > > where they don't want to limit the results.
> > >
> > > "toolman" wrote:
> > >
> > > > If what you want to do is use default parameter values and hide their
> > > > values from the user then open the Report Parameters dialogue box in
> > > > report designer and remove any values from the Prompt window. This
> > > > will cause the parameters to not display.
> > > >
> > > >|||Yes, I tried it again and it gives me the correct number of records, but they
all have the same values when "All" is chosen. The data tab gives perfect
result, but as I found it often to be the case, the preview does not mirror
the result in the data tab!
"William" wrote:
> Have you altered your detail dataset to test the parameter for the "All'
> option?
> SELECT...
> FROM ...
> WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
> AND ....
>
> "SK" wrote:
> > Thank you very much William, for responding!
> >
> > I think this is exactly what I was looking for!
> > I created a stored procedure in SQL Server and it works fine in the data
> > tab. When I run it the "Define Query Parameters" dialog box comes up and I
> > put 'All' and it works fine, giving me all the values. The problem is when I
> > preview it, it gives me only one value. I've put 'All' as the default under
> > the report parameter so I'm not sure what's wrong exactly!
> >
> >
> >
> > "William" wrote:
> >
> > > I have on occasion provided an "ALL" option to my parameter and set the
> > > default parameter to "ALL".
> > >
> > > In SQL, the parameter list is loaded from a dataset as
> > >
> > > SELECT <FieldName> AS OutName FROM <TableName>
> > > UNION ALL
> > > SELECT "All" AS OutName FROM <TableName>
> > >
> > > In my data dataset the code becomes:
> > >
> > > SELECT...
> > > FROM ...
> > > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
> > >
> > >
> > >
> > > "SK" wrote:
> > >
> > > > Thank you for your reponse!
> > > >
> > > > I actually want the user to be able to see and choose the parameters, but I
> > > > want the default to be all possible values and not one value for the cases
> > > > where they don't want to limit the results.
> > > >
> > > > "toolman" wrote:
> > > >
> > > > > If what you want to do is use default parameter values and hide their
> > > > > values from the user then open the Report Parameters dialogue box in
> > > > > report designer and remove any values from the Prompt window. This
> > > > > will cause the parameters to not display.
> > > > >
> > > > >|||The reason it doesn't match is that the preview tab uses cached results
unless the value of the parameters you pick change. So if no parameters or
the parameters don't change then it will use the cached value. Look where
you rdl files are stored. You will see files called reportname.rdl.data,
these files have the cached data used by the preview tab. Delete the file
and it will requery the database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SK" <SK@.discussions.microsoft.com> wrote in message
news:327904F9-F352-4BE7-9887-88AC6F522979@.microsoft.com...
> Yes, I tried it again and it gives me the correct number of records, but
> they
> all have the same values when "All" is chosen. The data tab gives perfect
> result, but as I found it often to be the case, the preview does not
> mirror
> the result in the data tab!
>
> "William" wrote:
>> Have you altered your detail dataset to test the parameter for the "All'
>> option?
>> SELECT...
>> FROM ...
>> WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
>> AND ....
>>
>> "SK" wrote:
>> > Thank you very much William, for responding!
>> >
>> > I think this is exactly what I was looking for!
>> > I created a stored procedure in SQL Server and it works fine in the
>> > data
>> > tab. When I run it the "Define Query Parameters" dialog box comes up
>> > and I
>> > put 'All' and it works fine, giving me all the values. The problem is
>> > when I
>> > preview it, it gives me only one value. I've put 'All' as the default
>> > under
>> > the report parameter so I'm not sure what's wrong exactly!
>> >
>> >
>> >
>> > "William" wrote:
>> >
>> > > I have on occasion provided an "ALL" option to my parameter and set
>> > > the
>> > > default parameter to "ALL".
>> > >
>> > > In SQL, the parameter list is loaded from a dataset as
>> > >
>> > > SELECT <FieldName> AS OutName FROM <TableName>
>> > > UNION ALL
>> > > SELECT "All" AS OutName FROM <TableName>
>> > >
>> > > In my data dataset the code becomes:
>> > >
>> > > SELECT...
>> > > FROM ...
>> > > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
>> > >
>> > >
>> > >
>> > > "SK" wrote:
>> > >
>> > > > Thank you for your reponse!
>> > > >
>> > > > I actually want the user to be able to see and choose the
>> > > > parameters, but I
>> > > > want the default to be all possible values and not one value for
>> > > > the cases
>> > > > where they don't want to limit the results.
>> > > >
>> > > > "toolman" wrote:
>> > > >
>> > > > > If what you want to do is use default parameter values and hide
>> > > > > their
>> > > > > values from the user then open the Report Parameters dialogue box
>> > > > > in
>> > > > > report designer and remove any values from the Prompt window.
>> > > > > This
>> > > > > will cause the parameters to not display.
>> > > > >
>> > > > >|||That makes sense, thank you!
But, it still didn't seem to work in this case!
If I use the below in the dataset, I get the all the parameters right, excep
the "All" and
SELECT...
FROM ...
WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
AND ....
And if I call a stored procedure in the dataset, I get all the values and
not just the parameter value that I choose.
"Bruce L-C [MVP]" wrote:
> The reason it doesn't match is that the preview tab uses cached results
> unless the value of the parameters you pick change. So if no parameters or
> the parameters don't change then it will use the cached value. Look where
> you rdl files are stored. You will see files called reportname.rdl.data,
> these files have the cached data used by the preview tab. Delete the file
> and it will requery the database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SK" <SK@.discussions.microsoft.com> wrote in message
> news:327904F9-F352-4BE7-9887-88AC6F522979@.microsoft.com...
> > Yes, I tried it again and it gives me the correct number of records, but
> > they
> > all have the same values when "All" is chosen. The data tab gives perfect
> > result, but as I found it often to be the case, the preview does not
> > mirror
> > the result in the data tab!
> >
> >
> >
> > "William" wrote:
> >
> >> Have you altered your detail dataset to test the parameter for the "All'
> >> option?
> >> SELECT...
> >> FROM ...
> >> WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
> >> AND ....
> >>
> >>
> >> "SK" wrote:
> >>
> >> > Thank you very much William, for responding!
> >> >
> >> > I think this is exactly what I was looking for!
> >> > I created a stored procedure in SQL Server and it works fine in the
> >> > data
> >> > tab. When I run it the "Define Query Parameters" dialog box comes up
> >> > and I
> >> > put 'All' and it works fine, giving me all the values. The problem is
> >> > when I
> >> > preview it, it gives me only one value. I've put 'All' as the default
> >> > under
> >> > the report parameter so I'm not sure what's wrong exactly!
> >> >
> >> >
> >> >
> >> > "William" wrote:
> >> >
> >> > > I have on occasion provided an "ALL" option to my parameter and set
> >> > > the
> >> > > default parameter to "ALL".
> >> > >
> >> > > In SQL, the parameter list is loaded from a dataset as
> >> > >
> >> > > SELECT <FieldName> AS OutName FROM <TableName>
> >> > > UNION ALL
> >> > > SELECT "All" AS OutName FROM <TableName>
> >> > >
> >> > > In my data dataset the code becomes:
> >> > >
> >> > > SELECT...
> >> > > FROM ...
> >> > > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
> >> > >
> >> > >
> >> > >
> >> > > "SK" wrote:
> >> > >
> >> > > > Thank you for your reponse!
> >> > > >
> >> > > > I actually want the user to be able to see and choose the
> >> > > > parameters, but I
> >> > > > want the default to be all possible values and not one value for
> >> > > > the cases
> >> > > > where they don't want to limit the results.
> >> > > >
> >> > > > "toolman" wrote:
> >> > > >
> >> > > > > If what you want to do is use default parameter values and hide
> >> > > > > their
> >> > > > > values from the user then open the Report Parameters dialogue box
> >> > > > > in
> >> > > > > report designer and remove any values from the Prompt window.
> >> > > > > This
> >> > > > > will cause the parameters to not display.
> >> > > > >
> >> > > > >
>
>|||I am trying the same thing the problem is that I need to do this for
several different fields. If I select "all", all the records show. But
when I change one to something other then "All" I get zero records
shown..
Randy
--
Bruce L-C [MVP] wrote:
> The reason it doesn't match is that the preview tab uses cached results
> unless the value of the parameters you pick change. So if no parameters or
> the parameters don't change then it will use the cached value. Look where
> you rdl files are stored. You will see files called reportname.rdl.data,
> these files have the cached data used by the preview tab. Delete the file
> and it will requery the database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SK" <SK@.discussions.microsoft.com> wrote in message
> news:327904F9-F352-4BE7-9887-88AC6F522979@.microsoft.com...
> > Yes, I tried it again and it gives me the correct number of records, but
> > they
> > all have the same values when "All" is chosen. The data tab gives perfect
> > result, but as I found it often to be the case, the preview does not
> > mirror
> > the result in the data tab!
> >
> >
> >
> > "William" wrote:
> >
> >> Have you altered your detail dataset to test the parameter for the "All'
> >> option?
> >> SELECT...
> >> FROM ...
> >> WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
> >> AND ....
> >>
> >>
> >> "SK" wrote:
> >>
> >> > Thank you very much William, for responding!
> >> >
> >> > I think this is exactly what I was looking for!
> >> > I created a stored procedure in SQL Server and it works fine in the
> >> > data
> >> > tab. When I run it the "Define Query Parameters" dialog box comes up
> >> > and I
> >> > put 'All' and it works fine, giving me all the values. The problem is
> >> > when I
> >> > preview it, it gives me only one value. I've put 'All' as the default
> >> > under
> >> > the report parameter so I'm not sure what's wrong exactly!
> >> >
> >> >
> >> >
> >> > "William" wrote:
> >> >
> >> > > I have on occasion provided an "ALL" option to my parameter and set
> >> > > the
> >> > > default parameter to "ALL".
> >> > >
> >> > > In SQL, the parameter list is loaded from a dataset as
> >> > >
> >> > > SELECT <FieldName> AS OutName FROM <TableName>
> >> > > UNION ALL
> >> > > SELECT "All" AS OutName FROM <TableName>
> >> > >
> >> > > In my data dataset the code becomes:
> >> > >
> >> > > SELECT...
> >> > > FROM ...
> >> > > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
> >> > >
> >> > >
> >> > >
> >> > > "SK" wrote:
> >> > >
> >> > > > Thank you for your reponse!
> >> > > >
> >> > > > I actually want the user to be able to see and choose the
> >> > > > parameters, but I
> >> > > > want the default to be all possible values and not one value for
> >> > > > the cases
> >> > > > where they don't want to limit the results.
> >> > > >
> >> > > > "toolman" wrote:
> >> > > >
> >> > > > > If what you want to do is use default parameter values and hide
> >> > > > > their
> >> > > > > values from the user then open the Report Parameters dialogue box
> >> > > > > in
> >> > > > > report designer and remove any values from the Prompt window.
> >> > > > > This
> >> > > > > will cause the parameters to not display.
> >> > > > >
> >> > > > >