Line chart threshold

I have numerous reports generating line charts. I would like to have
one more line added to the graphs set to a static value to show what
the threshold would be. I have tried drawing a line on the chart and
this works in BIDS preview, but it does not appear when I publish the
report. Any suggestions on a better way to do this? I have also
tried adding a field with a static value to the dataset, but it draws
the line multiple times and shows multiple instances of the value in
the legend.On Mar 4, 2:03 am, Victag <miketagg...@.gmail.com> wrote:
> I have numerous reports generating line charts. I would like to have
> one more line added to the graphs set to a static value to show what
> the threshold would be. I have tried drawing a line on the chart and
> this works in BIDS preview, but it does not appear when I publish the
> report. Any suggestions on a better way to do this? I have also
> tried adding a field with a static value to the dataset, but it draws
> the line multiple times and shows multiple instances of the value in
> the legend.
Add another data value. add at the place where you can see the
original data you are seeing now.
This should work
Thanks
Raj Deep.A|||On Mar 5, 9:45=A0am, RajDeep <rajalap...@.gmail.com> wrote:
> On Mar 4, 2:03 am, Victag <miketagg...@.gmail.com> wrote:
> > I have numerous reports generating line charts. =A0I would like to have
> > one more line added to the graphs set to a static value to show what
> > the threshold would be. =A0I have tried drawing a line on the chart and
> > this works in BIDS preview, but it does not appear when I publish the
> > report. =A0Any suggestions on a better way to do this? =A0I have also
> > tried adding a field with a static value to the dataset, but it draws
> > the line multiple times and shows multiple instances of the value in
> > the legend.
> Add another data value. add at the place where you can see the
> original data you are seeing now.
> This should work
> Thanks
> Raj Deep.A
I tried adding a value to the dataset and putting that value in the
graph, but now I see more than one line in the graph for the value and
it shows multiple times in the legend.

Line across Table

How do I get a line to go across the detail in a table?
If I set the underline for each cell, I get the value in each cell
underlined, I don't want that. I want an underline to go across the detail
section of my table. So, the user can see a line between each row in the
detail.Mike, you need to set the border style for the detail row cells. Select the
cells then set the bottom to solid.
=-Chris
"Mike L" <MikeL@.discussions.microsoft.com> wrote in message
news:E745059D-A68A-4BCD-B1C1-689956BA1351@.microsoft.com...
> How do I get a line to go across the detail in a table?
> If I set the underline for each cell, I get the value in each cell
> underlined, I don't want that. I want an underline to go across the
> detail
> section of my table. So, the user can see a line between each row in the
> detail.

Limiting the amount of rows

I want to list a particular attribute value and the number of times it occurs
in the database ie
Attribute value 1 6
Attribute value 2 5
Attribute value 3 5
Attribute value 4 4
Attribute value 5 4
Attribute value 6 3
Attribute value 7 3
Attribute value 8 3
Attribute value 9 3
Attribute value 10 2
Attribute value 11 2
Attribute value 12 1
Attribute value 13 1
However I want the result set to list the top 10 records. This could be
done using the top 10 function. However as you can see Attribute value
10 and 11 both have the total of 2 and are therefore equilvalent. So I
really want the top 11 returned in this case. If Attribute value 12
had been 2 then I would have wanted the top 12 etc
How can I do this?
MTKIRWAN wrote:
> I want to list a particular attribute value and the number of times
> it occurs in the database ie
> Attribute value 1 6
> Attribute value 2 5
> Attribute value 3 5
> Attribute value 4 4
> Attribute value 5 4
> Attribute value 6 3
> Attribute value 7 3
> Attribute value 8 3
> Attribute value 9 3
> Attribute value 10 2
> Attribute value 11 2
> Attribute value 12 1
> Attribute value 13 1
> However I want the result set to list the top 10 records. This could
> be done using the top 10 function. However as you can see Attribute
> value 10 and 11 both have the total of 2 and are therefore
> equilvalent. So I really want the top 11 returned in this case. If
> Attribute value 12
> had been 2 then I would have wanted the top 12 etc
> How can I do this?
Try something like this:
In this example, I'm looking for the TOP 4. There are 6 groups. If you
run a GROUP BY with COUNT in DESC order on the COUNT, you'll see we have
a matching count on 4 and 5, so what we really want is the first five
groups.
What I did was use an IN query to pull in just the raw totals for the
highest 4 rows and then use those results in the HAVING clause. The main
query does not use a TOP clause because we are limited by the HAVING.
create table #test (Attr varchar(10))
insert into #test values ('Attr1')
insert into #test values ('Attr1')
insert into #test values ('Attr1')
insert into #test values ('Attr1')
insert into #test values ('Attr1')
insert into #test values ('Attr2')
insert into #test values ('Attr2')
insert into #test values ('Attr2')
insert into #test values ('Attr3')
insert into #test values ('Attr3')
insert into #test values ('Attr3')
insert into #test values ('Attr4')
insert into #test values ('Attr4')
insert into #test values ('Attr5')
insert into #test values ('Attr5')
select Attr, COUNT(*) as 'Count'
from #test
Group By Attr
Having COUNT(*) IN (
select TOP 4 COUNT(*) as 'Count'
from #test
Group By Attr
Order By 1 Desc)
Order By 2 Desc
David Gugick
Imceda Software
www.imceda.com
|||On Wed, 22 Sep 2004 18:59:03 -0700, MTKIRWAN wrote:

> However I want the result set to list the top 10 records. This could be
> done using the top 10 function. However as you can see Attribute value
>10 and 11 both have the total of 2 and are therefore equilvalent. So I
> really want the top 11 returned in this case. If Attribute value 12
>had been 2 then I would have wanted the top 12 etc
> How can I do this?
TOP 10 WITH TIES
?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo Kornelis wrote:
> On Wed, 22 Sep 2004 18:59:03 -0700, MTKIRWAN wrote:
>
> TOP 10 WITH TIES
> ?
> Best, Hugo
I guess you learn something every day. Never knew.
David Gugick
Imceda Software
www.imceda.com

Limiting the amount of rows

I want to list a particular attribute value and the number of times it occurs
in the database ie
Attribute value 1 6
Attribute value 2 5
Attribute value 3 5
Attribute value 4 4
Attribute value 5 4
Attribute value 6 3
Attribute value 7 3
Attribute value 8 3
Attribute value 9 3
Attribute value 10 2
Attribute value 11 2
Attribute value 12 1
Attribute value 13 1
However I want the result set to list the top 10 records. This could be
done using the top 10 function. However as you can see Attribute value
10 and 11 both have the total of 2 and are therefore equilvalent. So I
really want the top 11 returned in this case. If Attribute value 12
had been 2 then I would have wanted the top 12 etc
How can I do this?MTKIRWAN wrote:
> I want to list a particular attribute value and the number of times
> it occurs in the database ie
> Attribute value 1 6
> Attribute value 2 5
> Attribute value 3 5
> Attribute value 4 4
> Attribute value 5 4
> Attribute value 6 3
> Attribute value 7 3
> Attribute value 8 3
> Attribute value 9 3
> Attribute value 10 2
> Attribute value 11 2
> Attribute value 12 1
> Attribute value 13 1
> However I want the result set to list the top 10 records. This could
> be done using the top 10 function. However as you can see Attribute
> value 10 and 11 both have the total of 2 and are therefore
> equilvalent. So I really want the top 11 returned in this case. If
> Attribute value 12
> had been 2 then I would have wanted the top 12 etc
> How can I do this?
Try something like this:
In this example, I'm looking for the TOP 4. There are 6 groups. If you
run a GROUP BY with COUNT in DESC order on the COUNT, you'll see we have
a matching count on 4 and 5, so what we really want is the first five
groups.
What I did was use an IN query to pull in just the raw totals for the
highest 4 rows and then use those results in the HAVING clause. The main
query does not use a TOP clause because we are limited by the HAVING.
create table #test (Attr varchar(10))
insert into #test values ('Attr1')
insert into #test values ('Attr1')
insert into #test values ('Attr1')
insert into #test values ('Attr1')
insert into #test values ('Attr1')
insert into #test values ('Attr2')
insert into #test values ('Attr2')
insert into #test values ('Attr2')
insert into #test values ('Attr3')
insert into #test values ('Attr3')
insert into #test values ('Attr3')
insert into #test values ('Attr4')
insert into #test values ('Attr4')
insert into #test values ('Attr5')
insert into #test values ('Attr5')
select Attr, COUNT(*) as 'Count'
from #test
Group By Attr
Having COUNT(*) IN (
select TOP 4 COUNT(*) as 'Count'
from #test
Group By Attr
Order By 1 Desc)
Order By 2 Desc
David Gugick
Imceda Software
www.imceda.com|||On Wed, 22 Sep 2004 18:59:03 -0700, MTKIRWAN wrote:
> However I want the result set to list the top 10 records. This could be
> done using the top 10 function. However as you can see Attribute value
>10 and 11 both have the total of 2 and are therefore equilvalent. So I
> really want the top 11 returned in this case. If Attribute value 12
>had been 2 then I would have wanted the top 12 etc
> How can I do this?
TOP 10 WITH TIES
?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On Wed, 22 Sep 2004 18:59:03 -0700, MTKIRWAN wrote:
>> However I want the result set to list the top 10 records. This could
>> be done using the top 10 function. However as you can see Attribute
>> value 10 and 11 both have the total of 2 and are therefore
>> equilvalent. So I really want the top 11 returned in this case. If
>> Attribute value 12 had been 2 then I would have wanted the top 12 etc
>> How can I do this?
> TOP 10 WITH TIES
> ?
> Best, Hugo
I guess you learn something every day. Never knew.
--
David Gugick
Imceda Software
www.imceda.com

Limitation for UDAs

When user-defined aggregates are discussed, there are two usage scenarios that are often mentioned. One is calculating the median value of a set of numbers, and the other is concatenating a set of strings into a comma-separated list.
However, I can not see how it would be possible to create an aggregate function for either of these problems. Because MaxSizeBytes can never be more than 8000 bytes these functions would only be able to operate on a limited number of values, since all values need to be kept for the entire execution. I have added this as a suggestion at the Product Feedback centre but have not yet received any feedback.
Or am I just missing something here, something that I have misunderstood?You aren't missing anything. For the reason you point out, neither of these examples are particularly good. (Of course, string concatenation won't work for more reasons than the size limit: we also can't guarantee the order of the concatenation right now.) This may change in future versions of SQL Server, but for now it's a limitation we're stuck with.

There are cases where UDAs could be used, but you're right that they cannot be aggregates which need to store all of the inputs until they're done. One example would be to find the bitwise 'or' or 'and' of a set of bitfields.

Incidently, the median of a set of items can be computed using a single T-SQL query. Assume you have a table 'sales' with the schema (city int, number int), you can find the median value of the 'number's for each 'city' by using the rank() function as follows:

select middles.city, avg(convert(float, number)) as median
from (
select city, floor((count(*) + 1) / 2.0) as lowmiddle, ceiling((count(*) + 1) / 2.0) as highmiddle
from sales
group by city
) as middles
join (
select city, number, rank() over (partition by city order by number) as rank
from sales
) as ranks
on (middles.city = ranks.city) and
((middles.lowmiddle = ranks.rank) or (middles.highmiddle = ranks.rank))
group by middles.city
Cheers,
- Isaac

|||Isaac, thanks for the answer and the nice query for median. It is definitely a lot better than the alternatives that are available in earlier versions of SQL Server. It would still not be able to match an aggregate in performance though.
Regarding string concatenation I generally agree it is not a particularly useful operation. But if it is to be done I strongly feel that it should be implemented just the way it would be today, apart from the size limitation. Guaranteeing any specific order of the values would be undesirable and confusing behaviour, unless the specific order is specified by the query executing the aggregate. In fact, if size was unlimited it would of course be possible for the UDA implementor to sort the rows before returning, if they wanted a specific order.|||

Regarding order, what would be desirable is for the query writer to be able to specify the order in which items were input to an order-sensitive UDA. One can imagine cases in which order would be significant, but in which the state does not have to be maintained in the UDA if the input is guaranteed to be ordered. This could be a win.

Cheers,
-Isaac

Limit to one value in fld b for given value in field a

Okay, i have a problem, which would be easy in a well-normalized db,
but im working in a legacy app here, and can't normalize the db because
it would break the existing apps around it.
I have a manifest table which essentially acts as both a header & line-items
table.
There is a MANIFESTID field which serves to link the lineitems together onto
one
manifest. There is also an ACTION field, which serves to indicate whether
the
manifest is for delivery or for pickup.
I dont know if it really is this hard, or if im just brain-freezing here,
but here is what
i'm trying to do... i want to put in some sort of constraint so that for all
the lines which
have any given MANIFESTID, the ACTION field is the same on all of them.
Thanks in advance,
- Arthur Dent.Not sure if I fully understood your problem, but in SQL Server, you cannot
create a constraint declaratively which can enforce some logic based on
multiple rows.
A general alternative is to use a after trigger with a rollback statement.
This is somewhat prohibitive in high performance systems since the logic can
turn out to be complex depending on your schema. In rare cases, you might be
able to work it out with a scalar udf used in a check constraint as well.
Anith|||As example for clarification,
ACTION is declared with a check as either D or P or Delivery or Pickup.
I might have 5 rows where the ManifestID is <10>. What i want is to be able
to say
For All Rows Where MANIFESTID = 10,
ACTION on ALL 5 Rows can be 'D' or 'P', but they ALL
must be 'D' or ALL must be 'P'
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23Y4JaqG7FHA.3544@.TK2MSFTNGP09.phx.gbl...
> Not sure if I fully understood your problem, but in SQL Server, you cannot
> create a constraint declaratively which can enforce some logic based on
> multiple rows.
> A general alternative is to use a after trigger with a rollback statement.
> This is somewhat prohibitive in high performance systems since the logic
> can turn out to be complex depending on your schema. In rare cases, you
> might be able to work it out with a scalar udf used in a check constraint
> as well.
> --
> Anith
>|||On Fri, 18 Nov 2005 13:53:12 -0500, Arthur Dent wrote:

>As example for clarification,
>ACTION is declared with a check as either D or P or Delivery or Pickup.
>I might have 5 rows where the ManifestID is <10>. What i want is to be able
>to say
>For All Rows Where MANIFESTID = 10,
>ACTION on ALL 5 Rows can be 'D' or 'P', but they ALL
>must be 'D' or ALL must be 'P'
Hi Arthur,
This might be one of those cases where a UDF + CHECK constraint will
work:
CREATE FUNCTION dbo.CheckNoOthers
(@.ManifestID int, @.Action char(1))
RETURNS char(3)
AS
BEGIN
DECLARE @.Result char(3)
IF EXISTS (SELECT *
FROM YourTable
WHERE ManifestID = @.ManifestID
AND Action <> @.Action)
BEGIN
SET @.Result = 'No'
END
ELSE
BEGIN
SET @.Result = 'Yes'
END
RETURN @.Result
go
ALTER TABLE YourTable
ADD CONSTRAINT NoOthers
CHECK (dbo.CheckNoOthers(ManifestID, Action) = 'Yes')
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yah, that looks like it would probably work. Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:qgq1o1pc00til4dvnk7h2jk7nssscm6vk2@.
4ax.com...
> On Fri, 18 Nov 2005 13:53:12 -0500, Arthur Dent wrote:
>
> Hi Arthur,
> This might be one of those cases where a UDF + CHECK constraint will
> work:
> CREATE FUNCTION dbo.CheckNoOthers
> (@.ManifestID int, @.Action char(1))
> RETURNS char(3)
> AS
> BEGIN
> DECLARE @.Result char(3)
> IF EXISTS (SELECT *
> FROM YourTable
> WHERE ManifestID = @.ManifestID
> AND Action <> @.Action)
> BEGIN
> SET @.Result = 'No'
> END
> ELSE
> BEGIN
> SET @.Result = 'Yes'
> END
> RETURN @.Result
> go
> ALTER TABLE YourTable
> ADD CONSTRAINT NoOthers
> CHECK (dbo.CheckNoOthers(ManifestID, Action) = 'Yes')
> go
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

limit the number of digits after decimal point in flaot data type

Hi..

I have a column in the data base with the type Float,

I want to limit the number of digits after decimal point to 2 when I display the value in ASP.NET but I don't know how!?

the number that appear after calculation llike "93.333333"

I use decimal(2,2) as data type but an error accour and this is the message

"- Unable to modify table.
Arithmetic overflow error converting float to data type numeric.
The statement has been terminated."

Can you help me..

thanks

Hi..

Before saving to database you can use String.Format() method to modify your float value.

First you need to convert float into string using

String myStr = Convert.ToString(yourFloatHere);

And then use String.Format("{0:N}", myStr);

and then again convert it into float using COnvert.ToDouble(MyStr);

Cheers n Hope it would help

|||

Hi..

I try what you suggest but the following error produced:

Cannot implicitly convert type 'double' to 'float'. An explicit conversion exists (are you missing a cast?)

Do you konw its solution?!

This is my code:

//------------------

float temp;

String value;

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

con.Open();

SqlCommand cmd =newSqlCommand("select SUM(FinalGrid)/COUNT(FinalGrid)from studentSections Where studentSection_UserID='" + Session["ID"].ToString() +"'", con);

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())

{

value =Convert.ToString(reader[0]);

}

String.Format("{0:2}", value);temp =Convert.ToDouble(value); //Here is the error

reader.Close();

//------------------------

Is it has any wrong?!

|||

Do not use the decimal(2,2). Just have the way it is now and just modify the SQL statment that you use to retrive the data and include the function

STR(columnname, lenght, number of decimal places)

This function will automatically round the data in this column to number of decimal places u want when the data is returned to ur ASP.NET application.

|||

Hi..

Can you tell me where I should use this function? and what do you mean by "length" ?

|||

Suppose you are using the query "select column1 from table1" to get the data to your applicaiton, where column1 is the one where you want only 2 decimals

u will have to use it as "select str(column1,10,2) as column1 from table1"

this will make sure you have only 2 decimals in the data that is returned, if there are more than 2 , it will round it to 2 decimals.

|||

Thanks so much its work very nice