Custom Series Color Problem

I am using SP1 and am trying to use the new custom color feature for chart
series.
The problem is that I want to specify a transparent color (Alpha = 50%).
Is there any way of doing this? It seems that you can only enter colors that
have RGB values, not ARGB values. (The System.Drawing.Color.FromArgb function
does not work in an expression).
At the moment, I am using the Semi-Transparent palette, but would like to
choose the colors myself as opposed to having them chosen for me.
--
cjARGB colors are not supported.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"CJ" <CJ@.newsgroup.nospam> wrote in message
news:E7870F65-DE71-4D39-9D47-748EB3FE7444@.microsoft.com...
>I am using SP1 and am trying to use the new custom color feature for chart
> series.
> The problem is that I want to specify a transparent color (Alpha = 50%).
> Is there any way of doing this? It seems that you can only enter colors
> that
> have RGB values, not ARGB values. (The System.Drawing.Color.FromArgb
> function
> does not work in an expression).
> At the moment, I am using the Semi-Transparent palette, but would like to
> choose the colors myself as opposed to having them chosen for me.
> --
> cj|||Will this be available in a future version?
"Lev Semenets [MSFT]" wrote:
> ARGB colors are not supported.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "CJ" <CJ@.newsgroup.nospam> wrote in message
> news:E7870F65-DE71-4D39-9D47-748EB3FE7444@.microsoft.com...
> >I am using SP1 and am trying to use the new custom color feature for chart
> > series.
> > The problem is that I want to specify a transparent color (Alpha = 50%).
> > Is there any way of doing this? It seems that you can only enter colors
> > that
> > have RGB values, not ARGB values. (The System.Drawing.Color.FromArgb
> > function
> > does not work in an expression).
> > At the moment, I am using the Semi-Transparent palette, but would like to
> > choose the colors myself as opposed to having them chosen for me.
> > --
> > cj
>
>|||It is on the wishlist for a future version.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"CJ" <CJ@.newsgroup.nospam> wrote in message
news:B6B8C17A-19FC-4A5A-BDA3-24C7E5A962B4@.microsoft.com...
> Will this be available in a future version?
> "Lev Semenets [MSFT]" wrote:
> > ARGB colors are not supported.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "CJ" <CJ@.newsgroup.nospam> wrote in message
> > news:E7870F65-DE71-4D39-9D47-748EB3FE7444@.microsoft.com...
> > >I am using SP1 and am trying to use the new custom color feature for
chart
> > > series.
> > > The problem is that I want to specify a transparent color (Alpha =50%).
> > > Is there any way of doing this? It seems that you can only enter
colors
> > > that
> > > have RGB values, not ARGB values. (The System.Drawing.Color.FromArgb
> > > function
> > > does not work in an expression).
> > > At the moment, I am using the Semi-Transparent palette, but would like
to
> > > choose the colors myself as opposed to having them chosen for me.
> > > --
> > > cj
> >
> >
> >

Custom Series Color in SP1 Charts

I'm trying to figure out how to create a custom series color in a bar chart,
and am unable to find documentation on it.
I read a post here on trying the Choose() function in the fill color portion
of the series value, but my chart goes gray in the layout mode, and then in
the preview mode it just shows the default colors.
Any documentation on this I can read?Keep in mind that expressions cannot be evaluated in design mode.
From the SP1 Readme about chart enhancements:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
"If you use an expression for fill color, the chart elements will be white
in Layout view, but will display properly when the report is run."
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:1AA68AB0-128E-44B2-8519-6357D7EBD465@.microsoft.com...
> I'm trying to figure out how to create a custom series color in a bar
chart,
> and am unable to find documentation on it.
> I read a post here on trying the Choose() function in the fill color
portion
> of the series value, but my chart goes gray in the layout mode, and then
in
> the preview mode it just shows the default colors.
> Any documentation on this I can read?|||That makes sense,
But still having an issue of what function to use in the fill color field.
I have
=IIf( Fields!descriptn.Value = "Self", "white","Transparent")
But it seems to ingore it.
"Robert Bruckner [MSFT]" wrote:
> Keep in mind that expressions cannot be evaluated in design mode.
> From the SP1 Readme about chart enhancements:
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
> "If you use an expression for fill color, the chart elements will be white
> in Layout view, but will display properly when the report is run."
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:1AA68AB0-128E-44B2-8519-6357D7EBD465@.microsoft.com...
> > I'm trying to figure out how to create a custom series color in a bar
> chart,
> > and am unable to find documentation on it.
> >
> > I read a post here on trying the Choose() function in the fill color
> portion
> > of the series value, but my chart goes gray in the layout mode, and then
> in
> > the preview mode it just shows the default colors.
> >
> > Any documentation on this I can read?
>
>|||There might be multiple issues here:
* A chart is similar to a matrix. Therefore a "cell" (datapoint) in a chart
can actually contain multiple rows depending on the groupings applied. Using
Fields!description.Value will always return the first value if multiple rows
are present.
If series groupings are present in the chart, you have to use
=First(Fields!description.Value, "NameOfInnermostSeriesGrouping") to get the
correct datapoint value in your expression. You might also want to check
this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=15f4e34b-345a-4876-8cbe-734b9e4dfa53&sloc=en-us
* Transparent is not a valid color. The only exceptions are currently the
chart area and the plot area fill colors (it's on the wishlist for
extensions in a future release).
Since transparent is not valid color, it gets ignored and the default color
(from the palette) applies. If you want to "hide" entire
datapoints/categories, you can filter them. If you have a solid plotarea
background color you could also assign the plotarea color to hide
datapoints.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:917930D0-5D0E-4231-A6BD-1B58602D4D9F@.microsoft.com...
> That makes sense,
> But still having an issue of what function to use in the fill color field.
> I have
> =IIf( Fields!descriptn.Value = "Self", "white","Transparent")
> But it seems to ingore it.
> "Robert Bruckner [MSFT]" wrote:
> > Keep in mind that expressions cannot be evaluated in design mode.
> > From the SP1 Readme about chart enhancements:
> >
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
> > "If you use an expression for fill color, the chart elements will be
white
> > in Layout view, but will display properly when the report is run."
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Matt" <Matt@.discussions.microsoft.com> wrote in message
> > news:1AA68AB0-128E-44B2-8519-6357D7EBD465@.microsoft.com...
> > > I'm trying to figure out how to create a custom series color in a bar
> > chart,
> > > and am unable to find documentation on it.
> > >
> > > I read a post here on trying the Choose() function in the fill color
> > portion
> > > of the series value, but my chart goes gray in the layout mode, and
then
> > in
> > > the preview mode it just shows the default colors.
> > >
> > > Any documentation on this I can read?
> >
> >
> >|||We create one particular line chart in which the number of lines will be
different based on what the user selects when printing the report. They graph
the performance of their portfolio to that of 1-5 different indices. So the
chart will have between 2 and 6 different lines on it.
In the data tab of the chart, we have one item listed under values, one
under category groups, and one under series groups.
Everything works great. Our problem is that when there are so many lines
that the chart uses a Green and LightGreen color for two of the lines, our
client can't tell those two colors apart and wants us to use a different
color than LightGreen.
We have tried to use the various suggestions seen in this and other blogs to
customize the color but it doesn't work.
Can anyone help us? I can provide more info if needed.
This is what we have tried under
Edit Value=>Appearance=>Series Style=>Fill=>Color=>Expression:
=Choose(Fields!uv_port_type.Value,"Red","Blue","Green","Yellow","Purple","Orange")
=CStr(Choose(Fields!uv_port_type.Value,"Red","Blue","Green","Yellow","Purple","Orange"))
= Choose(First(Fields!uv_port_type.Value,
"dsUnitValues"),"Red","Blue","Green","Yellow","Purple","Orange")
= CStr(Choose(First(Fields!uv_port_type.Value,
"dsUnitValues"),"Red","Blue","Green","Yellow","Purple","Orange"))
(We also tried "IIF" statements.)
Thank you!
Jen
"Robert Bruckner [MSFT]" wrote:
> There might be multiple issues here:
> * A chart is similar to a matrix. Therefore a "cell" (datapoint) in a chart
> can actually contain multiple rows depending on the groupings applied. Using
> Fields!description.Value will always return the first value if multiple rows
> are present.
> If series groupings are present in the chart, you have to use
> =First(Fields!description.Value, "NameOfInnermostSeriesGrouping") to get the
> correct datapoint value in your expression. You might also want to check
> this thread:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=15f4e34b-345a-4876-8cbe-734b9e4dfa53&sloc=en-us
> * Transparent is not a valid color. The only exceptions are currently the
> chart area and the plot area fill colors (it's on the wishlist for
> extensions in a future release).
> Since transparent is not valid color, it gets ignored and the default color
> (from the palette) applies. If you want to "hide" entire
> datapoints/categories, you can filter them. If you have a solid plotarea
> background color you could also assign the plotarea color to hide
> datapoints.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:917930D0-5D0E-4231-A6BD-1B58602D4D9F@.microsoft.com...
> > That makes sense,
> >
> > But still having an issue of what function to use in the fill color field.
> >
> > I have
> >
> > =IIf( Fields!descriptn.Value = "Self", "white","Transparent")
> >
> > But it seems to ingore it.
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Keep in mind that expressions cannot be evaluated in design mode.
> > > From the SP1 Readme about chart enhancements:
> > >
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
> > > "If you use an expression for fill color, the chart elements will be
> white
> > > in Layout view, but will display properly when the report is run."
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Matt" <Matt@.discussions.microsoft.com> wrote in message
> > > news:1AA68AB0-128E-44B2-8519-6357D7EBD465@.microsoft.com...
> > > > I'm trying to figure out how to create a custom series color in a bar
> > > chart,
> > > > and am unable to find documentation on it.
> > > >
> > > > I read a post here on trying the Choose() function in the fill color
> > > portion
> > > > of the series value, but my chart goes gray in the layout mode, and
> then
> > > in
> > > > the preview mode it just shows the default colors.
> > > >
> > > > Any documentation on this I can read?
> > >
> > >
> > >
>
>|||I found the following on SQLJunkies.. Seems to work:
--
Need a custom palette for your charts? Embed the function below into your
report, and set the Data > Values > Appearance > Series Style > Fill to the
custom function =Code.SetColor. Replace the nnnn's below with the hex codes
you need. See http://www.321webmaster.com/colorconverter.php for an RGB to
hex converter online. And hug the Reporting Services developers for adding
custom color functionality into SP1.
Function SetColor As String
Static ColorCode as Integer
ColorCode = ColorCode + 1: If ColorCode > 5 then ColorCode = 1
Select Case ColorCode
Case 1:
SetColor = "#00nnnn" ' Bluish
Case 2:
SetColor = "#78nnnn" ' Greenish
Case 3:
SetColor = "#E2nnnn" ' Reddish
Case 4:
SetColor = "#00nnnn" ' Navyish
Case 4:
SetColor = "#CCnnnn" ' Yellowish
Case 5:
SetColor = "#00nnnn" ' Light Blueish
Case Else:
SetColor = "#000000" ' Black (placeholder)
End Select
End Function
--
I have a where I need to show an average value in bar graph, but how many
values went into that average is also important. I pull back a percentage in
the query and then created a function that based on a percentage, returns a
color. Added =Code.FunctionName(pct) to the series fill and voila!
George
"Jen Rubin" wrote:
> We create one particular line chart in which the number of lines will be
> different based on what the user selects when printing the report. They graph
> the performance of their portfolio to that of 1-5 different indices. So the
> chart will have between 2 and 6 different lines on it.
> In the data tab of the chart, we have one item listed under values, one
> under category groups, and one under series groups.
> Everything works great. Our problem is that when there are so many lines
> that the chart uses a Green and LightGreen color for two of the lines, our
> client can't tell those two colors apart and wants us to use a different
> color than LightGreen.
> We have tried to use the various suggestions seen in this and other blogs to
> customize the color but it doesn't work.
> Can anyone help us? I can provide more info if needed.
> This is what we have tried under
> Edit Value=>Appearance=>Series Style=>Fill=>Color=>Expression:
> => Choose(Fields!uv_port_type.Value,"Red","Blue","Green","Yellow","Purple","Orange")
> => CStr(Choose(Fields!uv_port_type.Value,"Red","Blue","Green","Yellow","Purple","Orange"))
> = Choose(First(Fields!uv_port_type.Value,
> "dsUnitValues"),"Red","Blue","Green","Yellow","Purple","Orange")
> = CStr(Choose(First(Fields!uv_port_type.Value,
> "dsUnitValues"),"Red","Blue","Green","Yellow","Purple","Orange"))
> (We also tried "IIF" statements.)
> Thank you!
> Jen
>
> "Robert Bruckner [MSFT]" wrote:
> > There might be multiple issues here:
> > * A chart is similar to a matrix. Therefore a "cell" (datapoint) in a chart
> > can actually contain multiple rows depending on the groupings applied. Using
> > Fields!description.Value will always return the first value if multiple rows
> > are present.
> > If series groupings are present in the chart, you have to use
> > =First(Fields!description.Value, "NameOfInnermostSeriesGrouping") to get the
> > correct datapoint value in your expression. You might also want to check
> > this thread:
> > http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=15f4e34b-345a-4876-8cbe-734b9e4dfa53&sloc=en-us
> >
> > * Transparent is not a valid color. The only exceptions are currently the
> > chart area and the plot area fill colors (it's on the wishlist for
> > extensions in a future release).
> > Since transparent is not valid color, it gets ignored and the default color
> > (from the palette) applies. If you want to "hide" entire
> > datapoints/categories, you can filter them. If you have a solid plotarea
> > background color you could also assign the plotarea color to hide
> > datapoints.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Matt" <Matt@.discussions.microsoft.com> wrote in message
> > news:917930D0-5D0E-4231-A6BD-1B58602D4D9F@.microsoft.com...
> > > That makes sense,
> > >
> > > But still having an issue of what function to use in the fill color field.
> > >
> > > I have
> > >
> > > =IIf( Fields!descriptn.Value = "Self", "white","Transparent")
> > >
> > > But it seems to ingore it.
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > > > Keep in mind that expressions cannot be evaluated in design mode.
> > > > From the SP1 Readme about chart enhancements:
> > > >
> > http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
> > > > "If you use an expression for fill color, the chart elements will be
> > white
> > > > in Layout view, but will display properly when the report is run."
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > >
> > > > "Matt" <Matt@.discussions.microsoft.com> wrote in message
> > > > news:1AA68AB0-128E-44B2-8519-6357D7EBD465@.microsoft.com...
> > > > > I'm trying to figure out how to create a custom series color in a bar
> > > > chart,
> > > > > and am unable to find documentation on it.
> > > > >
> > > > > I read a post here on trying the Choose() function in the fill color
> > > > portion
> > > > > of the series value, but my chart goes gray in the layout mode, and
> > then
> > > > in
> > > > > the preview mode it just shows the default colors.
> > > > >
> > > > > Any documentation on this I can read?
> > > >
> > > >
> > > >
> >
> >
> >

Cursors aren't recommended I know but...

Is there any other choice?
Here's what I'm working with. I have to concacentate together a series of
description fragments into one value. The table I'm working with has a
primary & foreign key, as well as a field containing the 'line' number, and
another containing the 48 character description. The descriotion could be
from 1 to 4 to 8 or any number of lines, where each line has a primary key
and ties into a foreign key.
Example:
FKey: 55
PKey: 20
Line: 1
Desc: Outside Delivery Services regarding Federal
FKey: 55
PKey: 21
Line: 2
Desc: Express Corporation.
Obviously you'd want to return the whole description as: Outside Delivery
Services regarding Federal Express Corporation.
Now imagine a description could be even 7 or 8 lines, or just 1. Without
using a cursor (which I did and it takes 2-3 minutes to return back), is
their a way to return back into a variable or result set the full
description?
Thanks!Why can't the client loop through the rows and do the concatenation itself?
Loop through, if it's the same FKey as the previous then append the line,
else write out the full row detail.
(Or, why not change the design so that the whole description is contained in
one column.)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
news:Oy%23LWgRQFHA.3140@.tk2msftngp13.phx.gbl...
> Is there any other choice?
> Here's what I'm working with. I have to concacentate together a series of
> description fragments into one value. The table I'm working with has a
> primary & foreign key, as well as a field containing the 'line' number,
> and
> another containing the 48 character description. The descriotion could be
> from 1 to 4 to 8 or any number of lines, where each line has a primary key
> and ties into a foreign key.
> Example:
> FKey: 55
> PKey: 20
> Line: 1
> Desc: Outside Delivery Services regarding Federal
> FKey: 55
> PKey: 21
> Line: 2
> Desc: Express Corporation.
> Obviously you'd want to return the whole description as: Outside Delivery
> Services regarding Federal Express Corporation.
> Now imagine a description could be even 7 or 8 lines, or just 1. Without
> using a cursor (which I did and it takes 2-3 minutes to return back), is
> their a way to return back into a variable or result set the full
> description?
>
> Thanks!
>|||Fair questions certainly.
I can't change the database structure as it is a vendor's database we use
for our accounting system, and changing the structure wold violate our
support agreement I'm sure.
The client program in this case is going to be an ASP.Net web application.
I've already got 99% of the sql stored procedure written and returning what
I want, just need to add the description. It was my plan to have the ASP.Net
Datagrid display the results of my stored proc (which I've done before and
have no problems handling).
So does SQL Server and T-SQL have another option for me to accomplish this.
All the logic doesn't *have* to stay in a procedure, but I would like it to
obviously.
Thanks
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etsjpjRQFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Why can't the client loop through the rows and do the concatenation
itself?
> Loop through, if it's the same FKey as the previous then append the line,
> else write out the full row detail.
> (Or, why not change the design so that the whole description is contained
in
> one column.)
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
> news:Oy%23LWgRQFHA.3140@.tk2msftngp13.phx.gbl...
of
be
key
Delivery
>|||Better if you do that in the client app. Here you will find some solutions.
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"Steve Schroeder" wrote:

> Is there any other choice?
> Here's what I'm working with. I have to concacentate together a series of
> description fragments into one value. The table I'm working with has a
> primary & foreign key, as well as a field containing the 'line' number, an
d
> another containing the 48 character description. The descriotion could be
> from 1 to 4 to 8 or any number of lines, where each line has a primary key
> and ties into a foreign key.
> Example:
> FKey: 55
> PKey: 20
> Line: 1
> Desc: Outside Delivery Services regarding Federal
> FKey: 55
> PKey: 21
> Line: 2
> Desc: Express Corporation.
> Obviously you'd want to return the whole description as: Outside Delivery
> Services regarding Federal Express Corporation.
> Now imagine a description could be even 7 or 8 lines, or just 1. Without
> using a cursor (which I did and it takes 2-3 minutes to return back), is
> their a way to return back into a variable or result set the full
> description?
>
> Thanks!
>
>|||You can try a kludge like this:
http://www.aspfaq.com/2529
However, you are likely going to notice some significant performance deltas
compared to doing this where it belongs...
In the meantime, you should talk to the vendor about having them hire some
database people.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
news:O36igoRQFHA.3144@.tk2msftngp13.phx.gbl...
> Fair questions certainly.
> I can't change the database structure as it is a vendor's database we use
> for our accounting system, and changing the structure wold violate our
> support agreement I'm sure.
> The client program in this case is going to be an ASP.Net web application.
> I've already got 99% of the sql stored procedure written and returning
> what
> I want, just need to add the description. It was my plan to have the
> ASP.Net
> Datagrid display the results of my stored proc (which I've done before and
> have no problems handling).
> So does SQL Server and T-SQL have another option for me to accomplish
> this.
> All the logic doesn't *have* to stay in a procedure, but I would like it
> to
> obviously.
> Thanks
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:etsjpjRQFHA.2604@.TK2MSFTNGP10.phx.gbl...
> itself?
> in
> of
> be
> key
> Delivery
>|||Heh...yeah, but they brag about how they have world-wide fortune 500
companies using their database...chumps.
Anyhow...looks like the best thing to do would be to return two datasets,
then populate the datagrid in ASP.Net with the descriptions. Anything else
to too slow or too kludgey. Thanks Billy <roll eyes>
But thank you Aaron & Alejandro
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ulOphtRQFHA.2136@.TK2MSFTNGP14.phx.gbl...
> You can try a kludge like this:
> http://www.aspfaq.com/2529
> However, you are likely going to notice some significant performance
deltas
> compared to doing this where it belongs...
> In the meantime, you should talk to the vendor about having them hire some
> database people.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
> news:O36igoRQFHA.3144@.tk2msftngp13.phx.gbl...
use
application.
and
line,
contained
series
a
number,
could
primary
>|||Actually...I could update the ado dataset using a loop before I bind the
result set to the datagrid couldn't I? Via VB.Net & ADO code. That would
probably be quickest, as you suggested.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etsjpjRQFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Why can't the client loop through the rows and do the concatenation
itself?
> Loop through, if it's the same FKey as the previous then append the line,
> else write out the full row detail.
> (Or, why not change the design so that the whole description is contained
in
> one column.)
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
> news:Oy%23LWgRQFHA.3140@.tk2msftngp13.phx.gbl...
of
be
key
Delivery
>|||Steve,
Another option to do is something like
select isnull(line1.desc,'') + isnull(line2.desc,'') + ... +
isnull(line8.desc,'')
from desc line1
left join desc line2 on line1.fkey=line2.fkey and line1.line=line2.line+1
left join desc line3 on line2.fkey=line3.fkey and line2.line=line3.line+1
..
left join desc line8 on line7.fkey=line8.fkey and line7.line=line8.line+1
where line1.fkey=55
Upside is great index usage. Downside is limited number of lines.
-- Alex Papadimoulis
"Steve Schroeder" wrote:

> Is there any other choice?
> Here's what I'm working with. I have to concacentate together a series of
> description fragments into one value. The table I'm working with has a
> primary & foreign key, as well as a field containing the 'line' number, an
d
> another containing the 48 character description. The descriotion could be
> from 1 to 4 to 8 or any number of lines, where each line has a primary key
> and ties into a foreign key.
> Example:
> FKey: 55
> PKey: 20
> Line: 1
> Desc: Outside Delivery Services regarding Federal
> FKey: 55
> PKey: 21
> Line: 2
> Desc: Express Corporation.
> Obviously you'd want to return the whole description as: Outside Delivery
> Services regarding Federal Express Corporation.
> Now imagine a description could be even 7 or 8 lines, or just 1. Without
> using a cursor (which I did and it takes 2-3 minutes to return back), is
> their a way to return back into a variable or result set the full
> description?
>
> Thanks!
>
>