Combining numeric fields

Hello,
I use the following line in my sql view to bring 2 numeric fields together,
as one field (I'm using this to populate a listbox in vb.net).
This all works great, but I need to have the value 0 show up as 0.0. Right
now, it shows up as 0.
CAST(dbo.TDT_ROAD_SECTION.NUM_START AS varchar(10)) + ' ' +
CAST(dbo.TDT_ROAD_SECTION.NUM_END AS varchar(10))
Any suggestions?
TIA!
amberOn Tue, 20 Sep 2005 15:30:04 -0700, amber wrote:

>Hello,
>I use the following line in my sql view to bring 2 numeric fields together,
>as one field (I'm using this to populate a listbox in vb.net).
>This all works great, but I need to have the value 0 show up as 0.0. Right
>now, it shows up as 0.
>CAST(dbo.TDT_ROAD_SECTION.NUM_START AS varchar(10)) + ' ' +
>CAST(dbo.TDT_ROAD_SECTION.NUM_END AS varchar(10))
>Any suggestions?
Hi amber,
Use the STR function instead of CAST. See the documentation in Books
Online for details on usage.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If the original data type of those two columns is integer (int, bigint,
smallint or tinyint) then cast it as decimal before casting it to varchar.
Look into data types in Books Online for more details, or provide DDL and
some sample data and please explain what exactly your goal is.
ML|||Thank you!
That fixed it!
Amber

Combining a Stacked column and continous line graph

Hi All
I am trying a build a RS report which as a graph which combines two
series in to a stacked column and the remaining 6 data series as
continous line graphs.
I sort of managed to get it but it never works when compared to the
corresponding Excel graph.
Please help how do I build a graph report with combination of stacked
column and continous line graphs.
Thanks
KarenYou can combine a column and a line chart. Just set the chart type to column
and look for the "Plot data as line" checkbox on the data value appearance
tab. You may also be interested in this "how to" article:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
<karenmiddleol@.yahoo.com> wrote in message
news:1131153902.660326.3140@.g44g2000cwa.googlegroups.com...
> Hi All
> I am trying a build a RS report which as a graph which combines two
> series in to a stacked column and the remaining 6 data series as
> continous line graphs.
>
> I sort of managed to get it but it never works when compared to the
> corresponding Excel graph.
>
> Please help how do I build a graph report with combination of stacked
> column and continous line graphs.
> Thanks
> Karen
>

Combining a Line Graph and Scatter Graph

I have to make a report of a combination of salary trends of
employees. The idea is to show the salary of a employee for a certain
period of time, also showing where he stands when he compared to
salaries of other employees.
I will show the salary of the employee in a line Graph, thats fine I
have already done it. X axis -> Employment Time, Y-axis Salary The
qquery will be like
userid, date, salary
72, 01/01/2001, 1000
72, 06/01/2001, 1600
72, 01/01/2002, 6000
72, 06/01/2002, 7080
72, 01/01/2003, 8010
72, 06/01/2003, 10000
72, 01/01/2004, 10050
72, 06/01/2004, 15500
salary in the Values section
date in the Category Section
The Line Graph is done.
Assuming I have the same query with more userid values (For other
employees also). I have to plot scatter points around the Line Graph
showing the salaries of each other employee as against the employee
72.
So the Graph will have 1 line for userid 72, scatter points around
that line.
How can I do it. Working Ideas please.
Thanks !
Anand Sagar
Now I have to show scatter pointsanybody there !?

Combining 2 sql records on one detail line

Hi,
Is there a way to take 2 sql records from a data set and combine them
on one detail line. My data set looks like
sales lane store_no week year
9930.04 2 C196 50 2006
7276.24 3 C196 50 2006
In reporting services I want to have a table that shows store, lane2
sales, lane3 sales, week, year. When I do this now I get 2 detail rows
one that shows lane2 sales and another that shows lane 3 sales. I have
tried using a matrix which does a nice job of pivoting the data but
then throws off the way I want my report to be layed out. In Crystal
report I could do calculations and running totals behind the scenes
and then drop the result of that on the report the way I want it. How
can I do that with RS? As a side note I have tried using calculated
fields in RS but they crash my Visual Stuido if I do any kind of
calulation or IIF statement.
Any help is appreciatedHey you can do this pivoting using sql query itself, so that the result set
will look like the way you want
Amarnath
"mcgrawc@.checkers.com" wrote:
> Hi,
> Is there a way to take 2 sql records from a data set and combine them
> on one detail line. My data set looks like
> sales lane store_no week year
> 9930.04 2 C196 50 2006
> 7276.24 3 C196 50 2006
> In reporting services I want to have a table that shows store, lane2
> sales, lane3 sales, week, year. When I do this now I get 2 detail rows
> one that shows lane2 sales and another that shows lane 3 sales. I have
> tried using a matrix which does a nice job of pivoting the data but
> then throws off the way I want my report to be layed out. In Crystal
> report I could do calculations and running totals behind the scenes
> and then drop the result of that on the report the way I want it. How
> can I do that with RS? As a side note I have tried using calculated
> fields in RS but they crash my Visual Stuido if I do any kind of
> calulation or IIF statement.
> Any help is appreciated
>|||I realize that using SQL I could pivot the data and I have written
some code to do just that however I was hoping to find a way in
Reporting Services to do this just like I could in Crystal Reports. My
company wants to use RS to replace our crystal reports but I am
finding either through my lack of experience with RS that some of our
reports are beyond what RS can currently give. I personally love some
of the RS features but miss my the ease of my Crystal formulas,
running totals, etc. I have several other reports that I have stopped
working on temporarily that have this same problem and I really am not
looking to write code for all of them to pivot the data into something
RS can use. If anyone has a report solution I would love to hear it.
Chadwick|||Have you tried using the RuningValue function in RS?
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"chadwick" wrote:
> I realize that using SQL I could pivot the data and I have written
> some code to do just that however I was hoping to find a way in
> Reporting Services to do this just like I could in Crystal Reports. My
> company wants to use RS to replace our crystal reports but I am
> finding either through my lack of experience with RS that some of our
> reports are beyond what RS can currently give. I personally love some
> of the RS features but miss my the ease of my Crystal formulas,
> running totals, etc. I have several other reports that I have stopped
> working on temporarily that have this same problem and I really am not
> looking to write code for all of them to pivot the data into something
> RS can use. If anyone has a report solution I would love to hear it.
> Chadwick
>

Combined line and column graph?

I have a great column graph, but I was wondering if there were any options
on the graph so I add a trend line?
Or maybe there's an option I'm missing on the line graph.You can combine a column and a line chart. Just set the chart type to column
and look for the "Plot data as line" checkbox on the data value appearance
tab. You may also be interested in this how to article:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:e3c8GoorFHA.3264@.TK2MSFTNGP10.phx.gbl...
>I have a great column graph, but I was wondering if there were any options
> on the graph so I add a trend line?
> Or maybe there's an option I'm missing on the line graph.
>

Combined Chart (One series with Line and One Bar)

Is it possible to create combined charts with the Report Designer Chart?
I want to have one series charted as bar (sales) and the other series as
line (i.e market share).
Want to do it in one chart (not to overlay one chart over the other)
Thanx.Please read this related newsgroup posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Minas Papageorgiou" <MinasPapageorgiou@.discussions.microsoft.com> wrote in
message news:8AD71606-450E-4E5D-B4DD-12CA9A4A49C5@.microsoft.com...
> Is it possible to create combined charts with the Report Designer Chart?
> I want to have one series charted as bar (sales) and the other series as
> line (i.e market share).
> Want to do it in one chart (not to overlay one chart over the other)
> Thanx.
>

Combination charts issues

I have created a chart to chart a combinational chart including a
stacked column and the continous line charts.
But the problem is this combinational chart nowhere looks like the
Excel combinational chart for the same data as in the reporting
services report. Not sure if this is a problem in the RS charting
engine.
How do I create a stacked column and a continous like chart.
Thanks
KarenHi Karen
So far, I didn't find anything where you can mix charttypes. The only thing
is, you can define a Series to be drawn as a line. You can set that in the
properties of a value on the second tab there's a checkbox for that.
HTH
Luzia
<karenmiddleol@.yahoo.com> schrieb im Newsbeitrag
news:1131399473.731448.325340@.g49g2000cwa.googlegroups.com...
>I have created a chart to chart a combinational chart including a
> stacked column and the continous line charts.
> But the problem is this combinational chart nowhere looks like the
> Excel combinational chart for the same data as in the reporting
> services report. Not sure if this is a problem in the RS charting
> engine.
> How do I create a stacked column and a continous like chart.
> Thanks
> Karen
>|||Hi Luzia
I even dropped setting a combination chart of line and stacked column
instead I set multiple series all line graphs. The charts generated are
nowhere comparable to what I get in Excel not sure what is wrong.
I am frustrated.
Thanks
Karen

Colouring the chart plot area based on x-axis scale

Hi,

I have a report based on a line chart.

The values on X-axis are scaled from -100 to +100. I want to colour the band from -100 to 0 with red colour and that from 0 to +100 in green colour, and i want to provide legend for these bands/sections. How can i do this?

This doesn't look like a tough thing to do but i can't find a way to do it within the chart properties.

Basically i would like to divide the chart (plot area) into multiple vertical bars, each having a different colour. The colour is determined by the range that vertical bar covers on the x-axis

Thanks a lot!

Simranjeev

To color the bars on the chart, go to Chart Properties -> Data -> Edit button in the Values section -> Appearance Tab -> click the Series Style button -> Fill tab

You will have to enter an expression in the color property for the bar to be one solid color. The expression should look something like:
IIF(Fields!XAxisField.Value < 0, "Red", "Green")

You can do this if the axis value is a number or date. You may have to nest a couple of IIF statements if there are a lot of sections that you want to use. I don't think you can use the legend provided by reporting services for what you want it to accomplish since it is based on the series fields. You can create your own legend with a few lines, textboxes, and property changes though.

Colouring the chart plot area based on x-axis scale

Hi,

I have a report based on a line chart.

The values on X-axis are scaled from -100 to +100. I want to colour the band from -100 to 0 with red colour and that from 0 to +100 in green colour, and i want to provide legend for these bands/sections. How can i do this?

A prompt response would be really appreciated

Thanks a lot!!

Simranjeev

Hi!

This is a little urgent so i would really appreciate some response.

This doesn't look like a tough thing to do but i can't find a way to do it within the chart properties.

Basically i would like to divide the chart (plot area) into multiple vertical bars, each having a different colour. The colour is determined by the range that vertical bar covers on the x-axis

Thanks!

Simranjeev

Colors for Line Chart

Hi,
I have created a line chart with 3 different datafields. I want them to
have the colors yellow for the first, red for the second and blue for the
third. Is there anyway we can manipulate the colors on the chart? If there
is, can you kindly tell me how or point me in the direction of how to do
that?
Thank You,
RickyYou will need to have SP1 of RS 2000 installed on the server and designer
machines. In particular, read the following section of the SP1 readme file
about using colors in charts:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ricky" <kmeas1@.gmail.com> wrote in message
news:Oj5gfVHPFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have created a line chart with 3 different datafields. I want them to
> have the colors yellow for the first, red for the second and blue for the
> third. Is there anyway we can manipulate the colors on the chart? If
> there is, can you kindly tell me how or point me in the direction of how
> to do that?
> Thank You,
> Ricky
>

Coloring the mx value in a record set

Hi,
I have a simple report that displays several records having 2 fields, date
field and Quantity field
I ma,aged to alternate the colors of line (RosntBrown and black) bu I need
also to display the max Qty in a color different of the other ones, how this
can be achieved
ThanksHello eliassal,
You could add an Expression in the backgroundColor properties of the
textbox like this:
=IIF(ReportItems!Quantity.Value=MAX(Fields!Quantity.Value),"Black","Transpar
ent")
If is the max Qty, the background will be black and other will be
Transparent.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||So many thankls, it works like a charm. Now, how about displaying different
colors for Max and Min values for the same text box. can we use 2 expressions
at the same time on the same textbox containing thye field.
Thanks
"Wei Lu [MSFT]" wrote:
> Hello eliassal,
> You could add an Expression in the backgroundColor properties of the
> textbox like this:
>
> =IIF(ReportItems!Quantity.Value=MAX(Fields!Quantity.Value),"Black","Transpar
> ent")
> If is the max Qty, the background will be black and other will be
> Transparent.
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello eliassal,
Of course. You could do like this:
=IIF(ReportItems!Quantity.Value=MAX(Fields!Quantity.Value),"Black",IIF(Repor
tItems!Quantity.Value=min(fields!Quantity.Value),"Red","Transparent"))
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi, I was in vacation, I will check next week and let you know
Thanks
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Color of pens in Line chart ?

Hi,
how can I change the line color in line charts ?
RitchieTry:
Chart Properties > Tab Data > Edit Value > Tab Appearance
"Ritchie" <Ritchie@.discussions.microsoft.com> escribió en el mensaje
news:898D80BA-E315-4A1E-9D39-2463CBDCE482@.microsoft.com...
> Hi,
> how can I change the line color in line charts ?
> Ritchie|||Hi Mónica,
does this menue item also exists on a .NET 2003 Version ?
I do not saw any configured of the color for each pen ?
Ritchie
"Mónica" wrote:
> Try:
> Chart Properties > Tab Data > Edit Value > Tab Appearance
>
>
> "Ritchie" <Ritchie@.discussions.microsoft.com> escribió en el mensaje
> news:898D80BA-E315-4A1E-9D39-2463CBDCE482@.microsoft.com...
> > Hi,
> >
> > how can I change the line color in line charts ?
> >
> > Ritchie
>
>|||Ummm, I don´t know. I´m using .NET 2005.
I´m sorry I can´t help you :-(
"Ritchie" <Ritchie@.discussions.microsoft.com> escribió en el mensaje
news:04F4DEFE-A23D-420F-A30F-4EAB93C52307@.microsoft.com...
> Hi Mónica,
> does this menue item also exists on a .NET 2003 Version ?
> I do not saw any configured of the color for each pen ?
> Ritchie
> "Mónica" wrote:
>> Try:
>> Chart Properties > Tab Data > Edit Value > Tab Appearance
>>
>>
>> "Ritchie" <Ritchie@.discussions.microsoft.com> escribió en el mensaje
>> news:898D80BA-E315-4A1E-9D39-2463CBDCE482@.microsoft.com...
>> > Hi,
>> >
>> > how can I change the line color in line charts ?
>> >
>> > Ritchie
>>|||Thanks for your help anyway..
Ritchie
"Mónica" wrote:
> Ummm, I don´t know. I´m using .NET 2005.
> I´m sorry I can´t help you :-(
>
> "Ritchie" <Ritchie@.discussions.microsoft.com> escribió en el mensaje
> news:04F4DEFE-A23D-420F-A30F-4EAB93C52307@.microsoft.com...
> > Hi Mónica,
> >
> > does this menue item also exists on a .NET 2003 Version ?
> >
> > I do not saw any configured of the color for each pen ?
> >
> > Ritchie
> >
> > "Mónica" wrote:
> >
> >> Try:
> >>
> >> Chart Properties > Tab Data > Edit Value > Tab Appearance
> >>
> >>
> >>
> >>
> >> "Ritchie" <Ritchie@.discussions.microsoft.com> escribió en el mensaje
> >> news:898D80BA-E315-4A1E-9D39-2463CBDCE482@.microsoft.com...
> >> > Hi,
> >> >
> >> > how can I change the line color in line charts ?
> >> >
> >> > Ritchie
> >>
> >>
> >>
>
>