Draw Horizontal Line at Threshold

I have a table in reporting services. I have a field that calcuates a
percentage and the report is sorted in ascending order by this
percentage. I want to draw a single red horizontal line at a
threshold, let's say 95% so that any record lower than 95% would be
above the line and any record higher would be below the line. How can
I do this?On Apr 4, 6:54 am, robertpet...@.hotmail.com wrote:
> I have a table in reporting services. I have a field that calcuates a
> percentage and the report is sorted in ascending order by this
> percentage. I want to draw a single red horizontal line at a
> threshold, let's say 95% so that any record lower than 95% would be
> above the line and any record higher would be below the line. How can
> I do this?
I would suggest adding an extra query/stored procedure return field/
column to include in the report that is used as a flag for the
percentages. Have the flag set for the minimum row at or above 95%.
Then in the report, set the table properties (F4) (BorderColor->Top
and BorderStyle->Top) according to that flag. Something like these
expressions should work:
For BorderColor:Top:
=iif(Fields!Flag.Value = 1, "Red", "White")
For BorderStyle:Top:
=iif(Fields!Flag.Value = 1, "Solid", "None")
Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Download a report from Reporting Services.

Hi,

I have a report that′s already in the reporting service (2000 and 2005 - both systems), that I need edit in order to make some changes, but the person who did the report already deleted the source. I only have the one that had been uploaded to the reporting services. In there i only see an option to upload the report, but not to download.

Is there any way that i can download the report from the reporting services?

Thanks.

Yes, you can download the .RDL file using the "Edit" link on the Report Manager report properties page, the SOAP API method GetReportDefinition, and in Management Studio by right-clicking on the report and selecting "Edit Report".

Keep in mind that the RDL you get out of the server will be exactly the RDL you last published, but it will NOT include changes made to data sources and parameters after the RDL was published.

domain account vs local account for SQLServerAgent

Hi there,

BOL notes that in order for replication agents to run properly, the
SQLServerAgent must run as a domain account which has privledges to log
into the other machines involved in replication (under "Security
Considerations" and elsewhere). This makes sense; however, I was
wondering if there were any repercussions to using duplicate local
accounts to establish replication where a domain was not available.
Anotherwords, create a local windows account "johndoe" on both machines
(with the same password), grant that account access to SQL Server on
both machines, and then have SQL Server Agent run as "johndoe" on both
machines. I do not feel this is an ideal solution but I have
circumstances under which I may not have a domain available; my
preliminary tests seem to work.

Also, are there any similar considerations regarding the MSSQLSERVER
service, or can I always leave that as local system?

Dave"Dave C." <metal@.rules.spam> wrote in message news:<Pine.LNX.4.44.0311061247410.17583-100000@.ccrma-gate.stanford.edu>...
> Hi there,
> BOL notes that in order for replication agents to run properly, the
> SQLServerAgent must run as a domain account which has privledges to log
> into the other machines involved in replication (under "Security
> Considerations" and elsewhere). This makes sense; however, I was
> wondering if there were any repercussions to using duplicate local
> accounts to establish replication where a domain was not available.
> Anotherwords, create a local windows account "johndoe" on both machines
> (with the same password), grant that account access to SQL Server on
> both machines, and then have SQL Server Agent run as "johndoe" on both
> machines. I do not feel this is an ideal solution but I have
> circumstances under which I may not have a domain available; my
> preliminary tests seem to work.
> Also, are there any similar considerations regarding the MSSQLSERVER
> service, or can I always leave that as local system?
> Dave

See "Setting up Windows Services Accounts" in BOL. There are a number
of things that can only be done with a domain account; in addition,
LocalSystem is a highly privileged account, so for both functionality
and security reasons, using a domain account is usually a good idea.

I'm not sure about using multiple local accounts if no domain is
available. I would guess that it would work, but password management
and synchronization could be awkward.

Simon

Does TOP trump a WHERE clause?

I have this query:

SELECT TOP 3 headline, news_id, body FROM ml_news WHERE date_added > cutoff_date ORDER by date_added DESC;

The problem is that where there only 3 valid rows that are within the acceptable date range, it still pulls back three records. If I remove the 'TOP 3' from the query, it then correctly only pulls the two valid records.

I'd like to use the TOP 3 to only pull three records when there are more than three but I want it to pull less than three when there are less than three valid records. So, does the TOP keyword cause a query to always pull that many records regardless of the where clause if there are at least that many records available?

If so, will setting the ROWCOUNT work the way I desire?

If not, any other ideas?

Thanks!
-DAGTANo, TOP does not trump a where clause. You WHERE clause and ORDER BY clause will provide you preliminary resultset, and your TOP will further filer those results.

For example, from Northwind:

SELECT TOP 5 * CustomerID FROM Customers --Returns 5 values,

SELECT TOP 5 * CustomerID FROM Customer WHERE CustomerID LIKE 'A%' --Returns 4 values|||Thanks for the reply.

If TOP does not trump the WHERE, can you see anything wrong with this?

SELECT TOP 3 headline, news_id, body FROM ml_news WHERE date_added > cutoff_date ORDER by date_added DESC;

When I run:
SELECT headline, news_id, body FROM ml_news WHERE date_added > cutoff_date ORDER by date_added DESC;
I get two results, but when I run the first query I get three results.

Thanks,
-DAGTA|||If those are the exact queries, and the second query (without the TOP) is only returning 2 rows, then the first query will only return 2 rows.

Are you absolutely certain that those are the EXACT 2 queries that are running?

Is cutoff_date truly a column in your table, or is there a parameter value being substituted in there when the query runs?

Terri

Does the order matter

Hi,
I've been always wondering if the join order in select statement matter, for
example, I expected these two statements have different execution time but
they seem to have the same execution plan.
select ...
from ( SmallTableA A join SmallTableB B on A.id = b.id ) join BigTableX X on
x.id = a.id
comparing with
select ...
from ( BigTableX X join SmallTableA A on x.id = a.id ) join SmallTableB B
on A.id = b.id
I have the same question about the order of AND and OR operand.
select ...
from MyTable
where City = @.City AND StreetAddress = @.StreetAddress
comparing with
select ...
from MyTable
where StreetAddress = @.StreetAddress AND City = @.City
Thank you very muchOn Tue, 27 Sep 2005 15:54:16 -0700, Zeng wrote:

>I've been always wondering if the join order in select statement matter
(snip)
>I have the same question about the order of AND and OR operand.
(snip)
Hi Zeng,
The query optimizer is free to reorder all elements in your query, as
long as the results are unaffected. So you'll probably get the same
execution plan for your different queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Zeng,
I think the order does matter when performing OUTER JOINS and when combining
ANDs and ORs.
HTH
Jerry
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:ug8swc7wFHA.1168@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've been always wondering if the join order in select statement matter,
> for
> example, I expected these two statements have different execution time but
> they seem to have the same execution plan.
> select ...
> from ( SmallTableA A join SmallTableB B on A.id = b.id ) join BigTableX X
> on
> x.id = a.id
> comparing with
> select ...
> from ( BigTableX X join SmallTableA A on x.id = a.id ) join SmallTableB B
> on A.id = b.id
>
> I have the same question about the order of AND and OR operand.
> select ...
> from MyTable
> where City = @.City AND StreetAddress = @.StreetAddress
> comparing with
> select ...
> from MyTable
> where StreetAddress = @.StreetAddress AND City = @.City
>
> Thank you very much
>|||I'm not sure what you mean by combining ANDs and ORs, would you mind
explaining? thanks!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eBeo0k7wFHA.3152@.TK2MSFTNGP10.phx.gbl...
> Zeng,
> I think the order does matter when performing OUTER JOINS and when
combining
> ANDs and ORs.
> HTH
> Jerry
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:ug8swc7wFHA.1168@.TK2MSFTNGP15.phx.gbl...
but
X
B
>|||You missed the most basic idea of declarative languages. You tell the
compiler WHAT you want and the compiler figures out HOW to do it. Have
you had a course on progamming languages yet?
Join order does not matter. The optimizer can change it based on
current stats and indexing.|||Zeng,
R=1 AND S=1 AND T=2 OR U=3 AND V=4 etc... I use parens for something like
this.
HTH
Jerry
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:OBzlmC9wFHA.2792@.tk2msftngp13.phx.gbl...
> I'm not sure what you mean by combining ANDs and ORs, would you mind
> explaining? thanks!
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eBeo0k7wFHA.3152@.TK2MSFTNGP10.phx.gbl...
> combining
> but
> X
> B
>|||Joe,
So the order of the join clauses doesn't matter when you're using multiple
joins and join types in the same statment i.e., LEFT, RIGHT, INNER etc...?
But the order of the tables listed for a LEFT or RIGHT join does matter
right? You know T-SQL 100x better than me so...
Thanks
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127874943.221690.200070@.f14g2000cwb.googlegroups.com...
> You missed the most basic idea of declarative languages. You tell the
> compiler WHAT you want and the compiler figures out HOW to do it. Have
> you had a course on progamming languages yet?
> Join order does not matter. The optimizer can change it based on
> current stats and indexing.
>|||Order doesn't matter, unless you force it with optimizer hints.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:ug8swc7wFHA.1168@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've been always wondering if the join order in select statement matter,
> for
> example, I expected these two statements have different execution time but
> they seem to have the same execution plan.
> select ...
> from ( SmallTableA A join SmallTableB B on A.id = b.id ) join BigTableX X
> on
> x.id = a.id
> comparing with
> select ...
> from ( BigTableX X join SmallTableA A on x.id = a.id ) join SmallTableB B
> on A.id = b.id
>
> I have the same question about the order of AND and OR operand.
> select ...
> from MyTable
> where City = @.City AND StreetAddress = @.StreetAddress
> comparing with
> select ...
> from MyTable
> where StreetAddress = @.StreetAddress AND City = @.City
>
> Thank you very much
>|||As long as you join no more than 4 or 5 tables, in that case order
usually should not matter.
Only in a perfect world order does not matter at all.
In reality, if you join too many tables, it is impossible for the
optimizer to consider all the permutations, so the optimizer will
consider only some permutations, then quit.
Just think: even with just 5 tables there are 5*4*3*2=120 possible join
orders, and I wasn't considereing different indexes yet.|||You are correct--sort of, but there's no way to know which permutations will
be considered. There's no documentation that the optimizer applies any
deferrence to the order specified in the query. If order matters, then you
should specify optimizer hints like FORCE ORDER or use SET FORCEPLAN ON.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1127923668.694625.281030@.f14g2000cwb.googlegroups.com...
> As long as you join no more than 4 or 5 tables, in that case order
> usually should not matter.
> Only in a perfect world order does not matter at all.
> In reality, if you join too many tables, it is impossible for the
> optimizer to consider all the permutations, so the optimizer will
> consider only some permutations, then quit.
> Just think: even with just 5 tables there are 5*4*3*2=120 possible join
> orders, and I wasn't considereing different indexes yet.
>