Rewrite to Sargable

Is there a way to rewrite either of the following where clause items to allow
them to be SARGABLE?
1. ISDATE(Team.TextValue) = 1 -- Ensure that the value in the TextValue
field is a date. TextValue datatype is varchar(25).
2. LEFT(Team.Codes, 1) <> '-' -- Exclude the Codes that begin with a '-'.
Codes datatype is varchar(10).
Message posted via http://www.droptable.com
For #2, try:
Team.Codes not like '-%'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a089625d4de6@.uwe...
Is there a way to rewrite either of the following where clause items to
allow
them to be SARGABLE?
1. ISDATE(Team.TextValue) = 1 -- Ensure that the value in the TextValue
field is a date. TextValue datatype is varchar(25).
2. LEFT(Team.Codes, 1) <> '-' -- Exclude the Codes that begin with a '-'.
Codes datatype is varchar(10).
Message posted via http://www.droptable.com
|||Hi
For #1
If Team.TextValue should only be dates, you may want to validate it on the
UI. If it can be dates and other entries you may want to hold a datetype flag
instead.
John
"cbrichards via droptable.com" wrote:

> Is there a way to rewrite either of the following where clause items to allow
> them to be SARGABLE?
> 1. ISDATE(Team.TextValue) = 1 -- Ensure that the value in the TextValue
> field is a date. TextValue datatype is varchar(25).
> 2. LEFT(Team.Codes, 1) <> '-' -- Exclude the Codes that begin with a '-'.
> Codes datatype is varchar(10).
> --
> Message posted via http://www.droptable.com
>

Rewrite to Sargable

Is there a way to rewrite either of the following where clause items to allo
w
them to be SARGABLE?
1. ISDATE(Team.TextValue) = 1 -- Ensure that the value in the TextValue
field is a date. TextValue datatype is varchar(25).
2. LEFT(Team.Codes, 1) <> '-' -- Exclude the Codes that begin with a '-'.
Codes datatype is varchar(10).
Message posted via http://www.droptable.comFor #2, try:
Team.Codes not like '-%'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a089625d4de6@.uwe...
Is there a way to rewrite either of the following where clause items to
allow
them to be SARGABLE?
1. ISDATE(Team.TextValue) = 1 -- Ensure that the value in the TextValue
field is a date. TextValue datatype is varchar(25).
2. LEFT(Team.Codes, 1) <> '-' -- Exclude the Codes that begin with a '-'.
Codes datatype is varchar(10).
Message posted via http://www.droptable.com|||Hi
For #1
If Team.TextValue should only be dates, you may want to validate it on the
UI. If it can be dates and other entries you may want to hold a datetype fla
g
instead.
John
"cbrichards via droptable.com" wrote:

> Is there a way to rewrite either of the following where clause items to al
low
> them to be SARGABLE?
> 1. ISDATE(Team.TextValue) = 1 -- Ensure that the value in the TextValue
> field is a date. TextValue datatype is varchar(25).
> 2. LEFT(Team.Codes, 1) <> '-' -- Exclude the Codes that begin with a '-'
.
> Codes datatype is varchar(10).
> --
> Message posted via http://www.droptable.com
>sql

Rewrite to Sargable

Is there a way to rewrite either of the following where clause items to allow
them to be SARGABLE?
1. ISDATE(Team.TextValue) = 1 -- Ensure that the value in the TextValue
field is a date. TextValue datatype is varchar(25).
2. LEFT(Team.Codes, 1) <> '-' -- Exclude the Codes that begin with a '-'.
Codes datatype is varchar(10).
--
Message posted via http://www.sqlmonster.comFor #2, try:
Team.Codes not like '-%'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6a089625d4de6@.uwe...
Is there a way to rewrite either of the following where clause items to
allow
them to be SARGABLE?
1. ISDATE(Team.TextValue) = 1 -- Ensure that the value in the TextValue
field is a date. TextValue datatype is varchar(25).
2. LEFT(Team.Codes, 1) <> '-' -- Exclude the Codes that begin with a '-'.
Codes datatype is varchar(10).
--
Message posted via http://www.sqlmonster.com

Reuse of report elements

I am working on a project developing a fairly large number of reports with a team of developers. Many of these reports have common elements and code, such as common headers with user-selectable colors. Additionally, many of the common parts of the reports are at mockup stage currently, and many features will have to be added to the reports as time goes on.

We're attempting to create a generalized framework that will minimize the duplication of effort as we develop these reports, and as we go back and modify or fix them later.

What is the best way to approach this?

My first attempt was to create a report template and base all the reports off of the same template. That was fine for the first pass, but as we need to make changes later, they will not be propagated to the already existing reports.

My second attempt was to have each componant of that template reference a subreport, so that changes to the actual report template will be minimized as we go forward. This works great for minimizeing work, but it appears that you lose many features with the use of subreports, and there seems to be a pretty serious performance impact as well. I have posted about one such issue here: Pagination

If anyone has pointers about how to go about this, and where I should start, they would be greatly apreciated!

You can't link common bits into a report unfortunately. IMO your best option is to keep using a template report as your base. Because reports are deployed to a RS server, you can't just update a file and expect all the reports to pick it up, the change has to be added to the reports and then they should be redeployed. I do believe there are ways to change the *styles* of already deployed reports, there are some css files you can tweak under the Reports and ReportServer virtual dirs in IIS.

You just have to make sure that whoever orders the changes knows the true cost of implementing it.

|||

Well, the subreport method got us most of the way there, but we had to do things that way.

The only part that I couldn't figure out was the ability to specify the report source for the subreport as a parameter or field. That way we could share the base report across our projects (using sourcesafe) and changes to it would propagate to all the reports (yes, we'd still have to redeploy, but the issue we're trying to avoid is the need to manually edit things, where a change could be forgotten on one report and introduce a bug.)