Draw marking line on Chart

I want to mark a value on the x-axis of a (stacked) bar chart. (So I want a horizontal line that is fixed on a chart.)

What I tried so far is adding an expression that says: =260 to the dataset, and I added that field to the Data Area of the chart. Then I plotted that value as a line.

This approach leaves me with 2 problems:
1. When the category grouping only has one group, there is no line, just a dot (or another marker you can select).
2. Because of the extra field in the Data Area of the chart, the legend holds useless information.

Isn't there another way to plot a horizontal line on a fixed value of the X-axis ?

I also took some screenshots so you can see for yourself. The charts display the ManDays per Year. The green color are the productive days and the blue are the non productive. I made charts of how they look right now, and how I want them to look. http://users.telenet.be/master/ict/charts.html

Owkey I couldn't make the line go all the way through but I managed to edit the legend by an example of Brain Welcker on custom legends. It can be found here: http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx

So to draw a straight marking line on a chart: Add a field to the dataset containing the value where there has to be marked. Add that field to the data area of the chart. Set it so it will be displayed as a line. Remove the legend. Add a custom legend.

|||

My first question is how you plotted that field values - let's say we say that field name is Target Value right, how you plot that value as a Line in Bar Graph, I can't see in the Data area of that chart.

Can you explain in detail.

Thanks

|||

When I click twice on the chart, I see 3 area's: The Data, Series and Category area. In my data area there are two fields. One field is a sum of a database field, so that will create the bars.

The other field is a calculated field with expression "= 260" (because that is the value I want to mark on the chart) the field is called Marker. This field is added to the dataset which is used by the chart.

Then when you have dragged that field onto the data area, double click it and check these settings:
On the Values tab: the value property is =Fields!Marker.Value (when field is called marker)
On the Appearance tab: check the "show markers" and "plot data as line" checkboxes. This will draw a line with height= 260 on the x-axis (horizontal).

I hope this is clear enough, otherwise let me know ;)

|||

I am using Crystal Report 8.5... you are doing the same activity on Sql Server Reporting service ?

Thanks

NOOR

Doubts regarding Sales Forecast model

I have few more clarification regarding time series.

Firstly

In my model the month level product sales value represented across 1^st day of every month.So that the key time column is of datetime datatype containing a sequence of dates representing the 1^st day of every month of the year.

Eg: 2006-01-01, 2006-02-01………. etc. all in (yy-mm-dd) format

But

when I make prediction for next five months, though it makes monthly

predictions the date part for the months are random whereas I expect

the date part to be 1^st of every month.What is the reason for this and how can I overcome it.

Secondly

Predicted

sales values for some time period are negative though I do not have any

negative value in the training data. What is the reason for this and

how can I rectify it?

Thirdly

In

one of your earlier posts you had said that the time series algorithm

does not have any built in time intelligence but uses the key time

column as a time sequence stamp. So If have to make predictions for a

particular time period where the time slice for each time period is 25

days or 50 days etc, then I understand that the input data used to

train the model should be in the same time sequence.

Or

Can I specify the span of the time period according to which the prediction needs to be made?

Basically

how can I use the same time series model to make monthly, yearly,

quarterly, daily or predictions or for custom time period like I have

mentioned above.

1: There really isn't a workaround - you should use your own logic for dates

2: There is a parameter MINIMUM_PREDICTION_VALUE (or something like that) that you can set to 0, and you won't get negative predictions.

3: You should make unique models for each period you want to analyze - the patterns and periodicities are different, and need different analyses.

|||Thanks Jamie

Doubt in select query

my table ha follow structure..
SalaryTable:
Value Salary
--
Jan 1000
Feb 2000
Mar 3000
Apr 4000
i want the o/p as..
Jan Feb Mar Apr
1000 2000 3000 4000
Pls give me a query for this
Regards,
SatheeshHi,
It is not straight forward in SQL 2000, you need to write logic. See the
below URL:-
http://www.sqlteam.com/item.asp?ItemID=2955
Thanks
hari
SQL Server MVP
"BabuLives" <satheeshbabu.b@.gmail.com> wrote in message
news:1130138243.780789.195160@.g43g2000cwa.googlegroups.com...
> my table ha follow structure..
>
> SalaryTable:
>
> Value Salary
> --
> Jan 1000
> Feb 2000
> Mar 3000
> Apr 4000
>
> i want the o/p as..
>
> Jan Feb Mar Apr
> 1000 2000 3000 4000
>
> Pls give me a query for this
>
> Regards,
> Satheesh
>

Double Space Character

Hi,
I have table having a column whose value is the following expression.
="' " & Fields!AgencyNumber.Value & " ' ' " & Fields!MessageNumber.Value
I want to keep the double " " space characters but when the report is
rendered all is converted to single spaces.
Am I missing anything?
Thanks...You may be running into this IE limitation:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=25f13795-932d-4eaa-adf0-464c07398865&sloc=en-us.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"eralper" <eralper@.discussions.microsoft.com> wrote in message
news:07ED8D5D-C98F-4C47-9016-135B161D5A72@.microsoft.com...
> Hi,
> I have table having a column whose value is the following expression.
> ="' " & Fields!AgencyNumber.Value & " ' ' " &
Fields!MessageNumber.Value
> I want to keep the double " " space characters but when the report is
> rendered all is converted to single spaces.
> Am I missing anything?
> Thanks...

Double quotes in ASCII file

I've an issue with double-quotes in CSV file. One of the columns may contain this kind of value: "STATUS ""H"" "

I've got quote set to "

The file source fails on such records.

I found this thread and Scott tells us there that the file can't contain " in data.

Is this 100% correct?

I've got mutliple text columns and the pain is that I don't know which column might have these cases in future. To create a script means to write my own file parser for all files I use.

Any ideas?

Dima.

Hi,

If you have a CSV file, do you actually need to specify the text delimeter - or do you have some text that has commas in the string as well?

If your source file contains things like

"Status ""H"" ", "Next string", 1234, etc,

then I would try to solve the problem at source. That formatting suggests to me that the data is from another database where the actual data is ,Status "H", and the CSV export is trying to escape the " character by doubling it up.

Perhaps if you can export the CSV with no text delimiters, or with a character that is guaranteed not to appear, you may be OK.

Hope this helps,

Richard

|||

Richard,

you are correct in your gueses. I do have data like this:

"Status ""H"" ", "Next string", 1234, etc,

I can't remove quoation mark since I may have data like this:

"Status ""H"" ", "Next,string", 1234, etc,

So, if I removed " I'd have another error like this:

Status "H", Next,string, 1234, etc,

So, it wouldn't parse it correctly.

I can't change quotation, symbol since I don't control source system...

Now, what I could change is data within the column. So, I could replace:

Status "H"

with something like

Status &quot;H&quot;

Then when I read this - I'd convert it back to:

Status "H"

What do you think?

Dima

|||

Dima,

That looks like a solution to me - assuming you are going to make this change prior to bringing the data into the database, ie by pre-processing the CSV file.

What process are you going to use to make the changes? I'm sure you've thought of this, but if you were just to globally change "" to &quot then it will fall over when you three or more " in a row.

eg. """H"" is my status" should become "&quotH&quot is my status" , not &quot"H&quot is my status"

I've not tried it, but you can probably do this with an SSIS script of some sort, passing regular expressions. That would be better than having an external process change the file.

Regards,

Rich

|||

The file I'm dumping is on Unix. So, I was thinking about creating sed script, using regular expressions to post-process the file after it's dumped...

Thank for the feedback BTW it helped me think this thru

Double Pivot

Hello everybody,
I used to have a cross-tab query which gives me the number of orders
and the total value per year for each customer in the northwind
database. The result looks like this:
Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997
Now I try to rewrite it using the new PIVOT operator. While I succeed
in having the count per year or the sum, I can't figure out how to
combine both aggregates in one query. BOL online aren't much help and
so far in all the articles I haven't seen an example using more than
one.
Does anybody knows if this is possible ?
Thanks MarkusIt is not possible :(
Send an e-mail to sqlwish@.microsoft.com ...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
<m.bohse@.quest-consultants.com> wrote in message
news:1131479880.917386.253790@.g49g2000cwa.googlegroups.com...
> Hello everybody,
> I used to have a cross-tab query which gives me the number of orders
> and the total value per year for each customer in the northwind
> database. The result looks like this:
> Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997
> Now I try to rewrite it using the new PIVOT operator. While I succeed
> in having the count per year or the sum, I can't figure out how to
> combine both aggregates in one query. BOL online aren't much help and
> so far in all the articles I haven't seen an example using more than
> one.
> Does anybody knows if this is possible ?
> Thanks Markus
>|||m.bo...@.quest-consultants.com wrote:
> Hello everybody,
> I used to have a cross-tab query which gives me the number of orders
> and the total value per year for each customer in the northwind
> database. The result looks like this:
> Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997
> Now I try to rewrite it using the new PIVOT operator. While I succeed
> in having the count per year or the sum, I can't figure out how to
> combine both aggregates in one query. BOL online aren't much help and
> so far in all the articles I haven't seen an example using more than
> one.
> Does anybody knows if this is possible ?
Join the 2 simpler pivot queries?|||Check out the RAC utility for all kinds of static/dynamic
pivoting made easy.
www.rac4sql.net
<m.bohse@.quest-consultants.com> wrote in message
news:1131479880.917386.253790@.g49g2000cwa.googlegroups.com...
> Hello everybody,
> I used to have a cross-tab query which gives me the number of orders
> and the total value per year for each customer in the northwind
> database. The result looks like this:
> Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997
> Now I try to rewrite it using the new PIVOT operator. While I succeed
> in having the count per year or the sum, I can't figure out how to
> combine both aggregates in one query. BOL online aren't much help and
> so far in all the articles I haven't seen an example using more than
> one.
> Does anybody knows if this is possible ?
> Thanks Markus
>|||> Join the 2 simpler pivot queries?
Yes that's an option, but I'm afraid that my final query won't be any
shorter than the original one using CASE statements. And I don't think
it's such an unusual request to have two aggregates (or more) in a
cross-tab report.
>Check out the RAC utility for all kinds of static/dynamic pivoting made easy.[/colo
r]
I checked it out some years ago and wasn't too impressed with it back
then. Also I don't need these kind of things too often, I just created
this query as an example/exercise during classes. But maybe it's time
to have another look at RAC, since there should be a newer version by
now.
Thanks for the comments anyway.
Markus|||<m.bohse@.quest-consultants.com> wrote in message
news:1131528859.873667.16660@.g14g2000cwa.googlegroups.com...
> then. Also I don't need these kind of things too often, I just created
> this query as an example/exercise during classes. But maybe it's time
I have taken the PIVOT slides out of my T-SQL enhancements for SQL
Server 2005 talk. The three groups I showed it to all ended up asking, "can
it do (multiple aggregations, dynamic columns, etc)" -- all things that
would make perfect sense. And the answer in every case was, "no... I guess
it's not really that useful yet... but MS tells me that it WILL BE in a
future version!" So perhaps it will get a place in my T-SQL enhancements
for SQL Server 200x talk :)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Adam Machanic wrote:
> I have taken the PIVOT slides out of my T-SQL enhancements for SQL
> Server 2005 talk. The three groups I showed it to all ended up asking, "c
an
> it do (multiple aggregations, dynamic columns, etc)" -- all things that
> would make perfect sense. And the answer in every case was, "no... I gues
s
> it's not really that useful yet... but MS tells me that it WILL BE in a
> future version!" So perhaps it will get a place in my T-SQL enhancements
> for SQL Server 200x talk :)
Here is a solution, which I shamelessly plug in from my book
(therefore, the lecturing tone:-)
SQL Server 2005 introduced pivot operator as syntax extension for
table expression in the from clause
select * from
(Sales pivot (sum(Amount) for Month in ('Jan', 'Feb',
'Mar'))
As soon as a new feature is introduced people start wondering if it can
accommodate more complex cases. For example, can we do two aggregations
at once? Given the Sales relation, can we output the sales total
amounts together with sales counts like this
Product JanCnt FebCnt MarCnt JanSum FebSum MarSum
Shorts 1 1 1 20 30 50
Jeans 1 1 1 25 32 37
T-shirt 1 1 10 15
We had to change column names in order to accommodate extra columns
and, if nothing else, the changed column names should hint the
solution. The other idea, which should be immediately obvious from the
way the table columns are arranged in the display, is that the result
is a join between the two primitive pivot queries
Product JanCnt FebCnt MarCnt
Shorts 1 1 1
Jeans 1 1 1
T-shirt 1 1
and
Product JanSum FebSum MarSum
Shorts 20 30 50
Jeans 25 32 37
T-shirt 10 15
Well, what about those fancy column names? There is nothing like JanCnt
in the original data. Indeed, there isn't, but transforming the month
column data into the new column with Cnt postfix is just a string
concatenation. Therefore, the answer to the problem is
select scount.*, ssum.* from (
select * from (
(select product, month || 'Cnt', amount from Sales)
pivot (count(*) for Month in ('JanCnt', 'FebCnt',
'MarCnt')
) scount, (
select * from (
(select product, month || 'Sum', amount from Sales)
pivot (sum(Amount) for Month in ('JanSum', 'FebSum',
'MarSum')
) ssum
where scount.product = ssum.product|||"Vadim Tropashko" <vadimtro_invalid@.yahoo.com> wrote in message
news:1131593567.019132.117700@.g14g2000cwa.googlegroups.com...
> Here is a solution, which I shamelessly plug in from my book
Are you the same Vadim Tropashko who works for Oracle Corp?
If so, why are you writing a SQL Server book? :)

> select scount.*, ssum.* from (
> select * from (
> (select product, month || 'Cnt', amount from Sales)
> pivot (count(*) for Month in ('JanCnt', 'FebCnt',
> 'MarCnt')
> ) scount, (
> select * from (
> (select product, month || 'Sum', amount from Sales)
> pivot (sum(Amount) for Month in ('JanSum', 'FebSum',
> 'MarSum')
> ) ssum
> where scount.product = ssum.product
That's grossly inefficient compared with using SUM(CASE) and COUNT(CASE)
and grouping on the product.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Adam Machanic wrote:
> "Vadim Tropashko" <vadimtro_invalid@.yahoo.com> wrote in message
> news:1131593567.019132.117700@.g14g2000cwa.googlegroups.com...
> Are you the same Vadim Tropashko who works for Oracle Corp?
> If so, why are you writing a SQL Server book? :)
SQL book, not Server:-)

> That's grossly inefficient compared with using SUM(CASE) and COUNT(CAS
E)
> and grouping on the product.
This is true. Although, I fail to see the point of langauge extensions
that can't work seamlessly with the existing features without being
forced to complicate syntax every time a new, slight variation of the
problem arrives. How about pivoting on composite columns, say Month x
Day. Does it require new extension...)

Dont understand this error

Hello, I could use some help with this error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This if statement is the problem

IF (select bill_freq from #header1) = '1'
update #header1 Set
[Monthend] = aa.pername
from sv00564 zz inner join sv00532 aa on zz.wennsoft_period_id = aa.wennsoft_period_id
Where zz.rmdnumwk like #header1.Invoice_Number

The error means that select bill_freq from #header1 returns more than 1row. If there is more than one row in #header1, SQL doesn't knowwhich row to use.

Don't understand the concept behinf RAND

I am trying to generate 15-digit creditcard value (varchar) for testing.
I thought I could simply use the RAND function and extract 15 characters
from the right of the decimal. However this doesn't work, I lose most of th
e
significant digits when I try to conver to a varchar.
So I thought I would generate 15 random numbers and concatenate them
together. However this doesn't work either. When I repeatedly run the
statement below, I get do not get any randomness at all, the same digit
repeats over and over again and will only occasionally change.
SELECT str(10*RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) ))
What don't I understand? Can anyone help me out with this?SELECT RIGHT(RTRIM(CONVERT(DECIMAL(18,17),
10.0*RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) ))),15)
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:1E08F862-FFFF-4DD0-8EFA-4F714DBD125F@.microsoft.com...
>I am trying to generate 15-digit creditcard value (varchar) for testing.
> I thought I could simply use the RAND function and extract 15 characters
> from the right of the decimal. However this doesn't work, I lose most of
> the
> significant digits when I try to conver to a varchar.
> So I thought I would generate 15 random numbers and concatenate them
> together. However this doesn't work either. When I repeatedly run the
> statement below, I get do not get any randomness at all, the same digit
> repeats over and over again and will only occasionally change.
> SELECT str(10*RAND( (DATEPART(mm, GETDATE()) * 100000 )
> + (DATEPART(ss, GETDATE()) * 1000 )
> + DATEPART(ms, GETDATE()) ))
> What don't I understand? Can anyone help me out with this?
>|||I thought that most credit card numbers were a fixed length of 16
digits, in grouping of 4 digits, with the first grouping being the
issuer and the rest of the number following some rules.
That means that your random numbers are going to fail (well, you might
hit a valid card number by chance every few billion rows). Look at
these websites for some help:
[url]http://www.omnipilot.com/Tip%20of%20the%20W.1768.8848.lasso[/url]
http://www.analysisandsolutions.com...e/ccvs/ccvs.htm|||Dave,
I find CHEKSUM(NEWID()) a good seed for the RAND() function.
Also, keep in mind that RAND() returns a float in the range 0 through 1
inclusive.
With the above in mind, I'd use something like:
select right(cast(rand(checksum(newid())) as decimal(15, 15)), 15)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:1E08F862-FFFF-4DD0-8EFA-4F714DBD125F@.microsoft.com...
>I am trying to generate 15-digit creditcard value (varchar) for testing.
> I thought I could simply use the RAND function and extract 15 characters
> from the right of the decimal. However this doesn't work, I lose most of
> the
> significant digits when I try to conver to a varchar.
> So I thought I would generate 15 random numbers and concatenate them
> together. However this doesn't work either. When I repeatedly run the
> statement below, I get do not get any randomness at all, the same digit
> repeats over and over again and will only occasionally change.
> SELECT str(10*RAND( (DATEPART(mm, GETDATE()) * 100000 )
> + (DATEPART(ss, GETDATE()) * 1000 )
> + DATEPART(ms, GETDATE()) ))
> What don't I understand? Can anyone help me out with this?
>|||>I thought that most credit card numbers were a fixed length of 16
> digits, in grouping of 4 digits
American Express is 15.|||--CELKO-- wrote:
> I thought that most credit card numbers were a fixed length of 16
> digits, in grouping of 4 digits, with the first grouping being the
> issuer and the rest of the number following some rules.
> That means that your random numbers are going to fail (well, you might
> hit a valid card number by chance every few billion rows). Look at
> these websites for some help:
> [url]http://www.omnipilot.com/Tip%20of%20the%20W.1768.8848.lasso[/url]
> http://www.analysisandsolutions.com...e/ccvs/ccvs.htm
Well, he might generate 15 digits and then calculate the last digit
using luhn, so it should at least pass that check.
On a curiosity side-not - anyone ever implemented luhn as a check
constraint? I'd love to see the beast.
Damien

Don't show point labels if value is zero

Hey, I have a stacked column chart. It shows headcount per month. The headcount is split up into employees and contractors.

I added point labels to the chart to display the exact headcount value per month. (=Sum(Fields!HeadCount.Value)) Now here is the problem: Some months don't have contractors so there is a 0 displayed at the bottom of the X-axis. I don't want this to happen!

I already tried to change the expression of the point labels to this:
=IIF(Sum(Fields!HeadCount.Value,"chart1_SeriesGroup1") > 0,Round(Sum(Fields!HeadCount.Value),1)," ")

But this keeps displaying the 0. Even if the last parameter in the previous expression is 'nothing' it is still displayed.

I hope it is possible to remove them ...

Some additional info:
series field is: Contractor: which is a boolean that determines if the employee is contractor or not
category field is: Month (a month representation like ... January 2006, February 2006 ...)
Data field is: headcount (the headcounts of the employees).

You can use the datavalue to compare with 0. You don't have to compare the total of the chart1_SeriesGroup1 to 0.

Is your case: =IIF(Sum(Fields!HeadCount.Value) > 0,Round(Sum(Fields!HeadCount.Value),1)," ")

Dont display the field if it contains a zero value

I'm working on a report for accounting. Usually debit and credit prob. I don't want to display the field if it contains zero. It creates a gap on my reports and want to avoid it.
Anyone who can help?
Thanks.Using CR 8.5 and RDC, each field has a setting called 'SuppressIfZero' and one called 'SuppressIfBlank'. As the name implies, setting the 'SuppressIfZero' to True will suppress the field if the value is Zero, setting the 'SuppressIfBlank' to True will suppress the field if it's blank.|||I did what you said but I still get a gap between my text. How can I eliminate it?

Thanks.|||Try to put the field in a separate section (If possible), and Suppress section if that value is blank.|||Hi

try suppressing the field or use a formula

if (fieldname) = "0" then "No Data"

I was working on the same thing in crystal reports 9 and got up to this finally.

c ya