Filter in stored procedure

I am trying to edit this query i already have that is fully functional for another report in reporting services. The report has visits sales reps made to stores, broken down by period, week and then date. Now, i need to filter it out by Visits. They want the Sales Reps who had less then 6 visits a day. How would i code that out in my stored procedure? When i have Stores and Account Status( which makes it pretty broken down). Any suggestions. Thanks..heres the code.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

AS

BEGIN

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

END

SET NOCOUNT OFF

If dbo.Qry_Visits.Visits stores the cumulative count of the visits made then you can add 'Qry_Visits.Visits < 6' to the where clause. To make it more generic instead of hard coding values, you may want to have to edit the signature of the proc to have an input parameter that represents the number of visits also.

|||

try:

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

AS

BEGIN

declare @.visit_req int

set @.visit_req=6

--only return rows that have at least 6 visits

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

and dbo.Qry_Visits.Visits >= @.visit_req

END

SET NOCOUNT OFF

|||it does not show the cumulative. It shows one visit per sales person, per date. So its really broken down. So if i put <6 it will show everything, because each record has one visit. I only want the ones with less then 6 visits by a Sales Rep. Which means the sum of visits per Sales Rep, per day. How would i get that?|||

I don't really understand the data structure, but is possible to just do an aggregate similar to:

Code Snippet

Pseudo Code:

SELECT YourColumns, SUM(Visits) AS Visits
FROM AllThoseTables
GROUP BY YourColumns
HAVING SUM(Visits) > 5

|||

Then do:

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

AS

BEGIN

declare @.visit_req int

set @.visit_req=6

--only return rows that have at least 6 visits

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

and dbo.Qry_Visits.[SR Code] NOT IN(select [SR Code] from dbo.Qry_Visits

group by [SR Code]

having count(Visits) < @.visit_req)

END

SET NOCOUNT OFF

filter in Reporting Services: Best way?

Hi there,
I'm a newbe to reportig services and try to make up a financial report:
store currentYear pastYear Delta DeltaIn%PastYear
I tried for several hours with the table control:
- Gain the date with a query from ssas
- set a parameter for the year
- put several tables on the report and set a filter to the year dimension
The problem I have with this approach is:
- Formatting: I would prefer one table
- I don't know how to calculate the deltas (currentYear-pastYear)
Regards
TomPressed the post button to fast...
My questions are:
- Can I set a filter on a column in a table control?
- Can I calculate in a table control a value, that derives from other
columns in the same or different tables?
- Or is it better to use different controls (like the matrix or textboxes)?
Please help me - I have a deadline and running out of time!
Thanks in advance,
Tom
"Tomilee" wrote:
> Hi there,
> I'm a newbe to reportig services and try to make up a financial report:
> store currentYear pastYear Delta DeltaIn%PastYear
> I tried for several hours with the table control:
> - Gain the date with a query from ssas
> - set a parameter for the year
> - put several tables on the report and set a filter to the year dimension
> The problem I have with this approach is:
> - Formatting: I would prefer one table
> - I don't know how to calculate the deltas (currentYear-pastYear)
> Regards
> Tom|||Hi,
Just answering to your questions.
1. Can I set a filter on a column in a table control?
No for the full row you can set filter.
2. Can I calculate in a table control a value, that derives from other
columns in the same or different tables?
Yes very much. using <Expressions> option.
Regards
Amarnath
"Tomilee" wrote:
> Pressed the post button to fast...
> My questions are:
> - Can I set a filter on a column in a table control?
> - Can I calculate in a table control a value, that derives from other
> columns in the same or different tables?
> - Or is it better to use different controls (like the matrix or textboxes)?
> Please help me - I have a deadline and running out of time!
> Thanks in advance,
> Tom
> "Tomilee" wrote:
> > Hi there,
> >
> > I'm a newbe to reportig services and try to make up a financial report:
> >
> > store currentYear pastYear Delta DeltaIn%PastYear
> >
> > I tried for several hours with the table control:
> > - Gain the date with a query from ssas
> > - set a parameter for the year
> > - put several tables on the report and set a filter to the year dimension
> >
> > The problem I have with this approach is:
> > - Formatting: I would prefer one table
> > - I don't know how to calculate the deltas (currentYear-pastYear)
> >
> > Regards
> >
> > Tom|||Tomilee,
to get store currentYear pastYear Delta DeltaIn%PastYear
you should do all of this in the MDX query ( you mentioned using SSAS)...
To get the PastYear info, look into the ParallelPeriod function in MDX. the
Delta is current - ParallelPeriod, etc
in answer to your other questions... you can NOT put a filter on a textbox,
but you can filter on a group, table, etc... But what you can do is use a
conditional if on the textbox expression... ie
IIF (Fields!fieldname.Value > 5,truepart, falsepart)
Using this you might can get the effect of a filter..
--
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.
"Tomilee" wrote:
> Hi there,
> I'm a newbe to reportig services and try to make up a financial report:
> store currentYear pastYear Delta DeltaIn%PastYear
> I tried for several hours with the table control:
> - Gain the date with a query from ssas
> - set a parameter for the year
> - put several tables on the report and set a filter to the year dimension
> The problem I have with this approach is:
> - Formatting: I would prefer one table
> - I don't know how to calculate the deltas (currentYear-pastYear)
> Regards
> Tom|||Thanks for your Help! Tom
"Wayne Snyder" wrote:
> Tomilee,
> to get store currentYear pastYear Delta DeltaIn%PastYear
> you should do all of this in the MDX query ( you mentioned using SSAS)...
> To get the PastYear info, look into the ParallelPeriod function in MDX. the
> Delta is current - ParallelPeriod, etc
> in answer to your other questions... you can NOT put a filter on a textbox,
> but you can filter on a group, table, etc... But what you can do is use a
> conditional if on the textbox expression... ie
> IIF (Fields!fieldname.Value > 5,truepart, falsepart)
> Using this you might can get the effect of a filter..
> --
> 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.
>
> "Tomilee" wrote:
> > Hi there,
> >
> > I'm a newbe to reportig services and try to make up a financial report:
> >
> > store currentYear pastYear Delta DeltaIn%PastYear
> >
> > I tried for several hours with the table control:
> > - Gain the date with a query from ssas
> > - set a parameter for the year
> > - put several tables on the report and set a filter to the year dimension
> >
> > The problem I have with this approach is:
> > - Formatting: I would prefer one table
> > - I don't know how to calculate the deltas (currentYear-pastYear)
> >
> > Regards
> >
> > Tom

Filter in Replication

Hello,
I have a table JobDescription with the columns:
JobId
Email
StartTime
EndTime
Priority
etc.
I want the Filter in that way that only the rows of the JobDescription of a
certain Email Adress will be synchronized.
Can anybody tell me how this work?
thx
mathon
But which rows from which email would be displayed is only clear when the
user logs in the application with his email-adress. So I think the static
filter is not the right. Should I use dynamic filter? - How do I have to
quote it in that way in the Publication Process and then in my code? -
Unfortunately I haven't found a good sample for that.
mathon
|||Yes - dynamic filters is the way to go. If this is per
user, you can have a column for the loginname (the data
returned from select suser_sname()) and use that in the
filter. The dynamic filter option is a subset of the
horizontal filters part of the wizard and will appear if
you have selected the advanced options (first checkbox in
the wizard). Your dynamic filter will look like this:
SELECT <published_columns> FROM [dbo].[JobDescription]
WHERE JobDescription.SuserSName = SUSer_SName().
If you don't want another column in the table, and want
to map logins to email addresses, you can publish the
mapping table and apply the dynamic filter to that table,
or alternatively encapsulate the whole thing in a UDF to
be used in the where clause.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Dear Mr. Ibison!
Let me reflect once to know If I'll be right:
The method in the Login-mask selects the the Email of the User (Engineer) ->
SELECT Email FROM Engineer WHERE UserId=? AND password=?.
So in the Publication Wizard:
1) First I check "Horizontally, by filtering the rows published data"
2) Then I check "Yes, enable dynamic filters"
3) Then I select the Table to filter - [dbo].[JobDescription]
4) Then the Select Statement looks as follows:
SELECT <published_columns> FROM [dbo].[JobDescription] WHERE
[dbo].[JobDescription].EmailEngineer = EngineerLogin()
But when I want to go further the following error occurs:
"The WHERE clause 'JobDescription.EmailEngineer = EngineerLogin()' could not
be executed on table '[dbo].[JobDescription]'.
Error 195. 'EngineerLogin() is not a recognized function name.
Do you know what went wrong? (The function exists in my class-file.)
Regards
mathon
"Paul Ibison" wrote:

> Yes - dynamic filters is the way to go. If this is per
> user, you can have a column for the loginname (the data
> returned from select suser_sname()) and use that in the
> filter. The dynamic filter option is a subset of the
> horizontal filters part of the wizard and will appear if
> you have selected the advanced options (first checkbox in
> the wizard). Your dynamic filter will look like this:
> SELECT <published_columns> FROM [dbo].[JobDescription]
> WHERE JobDescription.SuserSName = SUSer_SName().
> If you don't want another column in the table, and want
> to map logins to email addresses, you can publish the
> mapping table and apply the dynamic filter to that table,
> or alternatively encapsulate the whole thing in a UDF to
> be used in the where clause.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>
|||Mathon,
firstly, I must clarify that the ability to filter data
on a per subscriber basis is part of merge replication
only (out of the box), so that is the scenario we are
looking at.
Your login mask may or may not be an issue. Is the
username and password used to log into sql server - ie is
this a SQL Server login? If it is, then filtering will be
simpler. In the wizard, ensure the advanced options are
enabled, and after selecting horizontal filters, select
the dynamic option. Your EngineerLogin() function as far
as I can see is a function you've created in your VB
code, so is not accessible to SQL Server (CLR integration
is a posibility in SQL 2005 but not SQL 2000). So, to map
the login name to the email address, you'll need a user-
defined function to be created in SQL Server. This
function will take suser_sname() as a parameter and will
return the email address as a return value.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
The password and userId is the log-in information for my mobile application
and not the login for the sql server. Is this more complicated?
regards
mat
"Paul Ibison" wrote:

> Mathon,
> firstly, I must clarify that the ability to filter data
> on a per subscriber basis is part of merge replication
> only (out of the box), so that is the scenario we are
> looking at.
> Your login mask may or may not be an issue. Is the
> username and password used to log into sql server - ie is
> this a SQL Server login? If it is, then filtering will be
> simpler. In the wizard, ensure the advanced options are
> enabled, and after selecting horizontal filters, select
> the dynamic option. Your EngineerLogin() function as far
> as I can see is a function you've created in your VB
> code, so is not accessible to SQL Server (CLR integration
> is a posibility in SQL 2005 but not SQL 2000). So, to map
> the login name to the email address, you'll need a user-
> defined function to be created in SQL Server. This
> function will take suser_sname() as a parameter and will
> return the email address as a return value.
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||OK - in that case there might be issues. Do you use the
same login to SQL Server for everyone? Is the mobile
device used by an individual user or multiple users?
In the easiest scenario, the device is used by the same
user each time. Typically you'll be using a sql server
login which doesn't differentiate between users, so we
can't rely on this in the filter. If this is true, you
could set the filter to use HOST_NAME(). In the merge
agent's job step for this subscriber, add -HOSTNAME
[emailaddress] to filter the data (replace emailaddress
with the individuals email address). The filter will be
as follows:
SELECT <published_columns> FROM [dbo].[JobDescription]
WHERE JobDescription.email = HOST_NAME().
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
Yes the device is always used by the same user and everybody has the same
login and password (sa,sa) to the SQL Server database. But i do not exactly
know what you mean with merge agents job step? Where should I add
-HOSTNAME[emailaddress]? And is it then still dynamically when I am not able
to filter according to the login a user makes in my mobile application?
regards
mat
"Paul Ibison" wrote:

> OK - in that case there might be issues. Do you use the
> same login to SQL Server for everyone? Is the mobile
> device used by an individual user or multiple users?
> In the easiest scenario, the device is used by the same
> user each time. Typically you'll be using a sql server
> login which doesn't differentiate between users, so we
> can't rely on this in the filter. If this is true, you
> could set the filter to use HOST_NAME(). In the merge
> agent's job step for this subscriber, add -HOSTNAME
> [emailaddress] to filter the data (replace emailaddress
> with the individuals email address). The filter will be
> as follows:
> SELECT <published_columns> FROM [dbo].[JobDescription]
> WHERE JobDescription.email = HOST_NAME().
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Mathon,
the hostname will be dynamic in the sense of each
subscription will be able to get different data. This
means that each mobile device can have different data. It
doesn't mean that if a different user logs onto the
device he'll see different data - the data is partitioned
according to device. If there is a 1:1 relationship
between users and devices then this is fine.
The merge agent is a job. The middle step runs the merge
executable. In this step there will be a number of
parameters , and at the end you can add -HOSTNAME with a
value. If you are doing this programatically, the
HOSTNAME is a property of the merge object.
I recommend that you set it up and have a play around
with the dynamic filtering in a test environment to see
if it meets your requirements.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
Unfortunately I am not so familiar with the Replication Mechanism. In my
application I realized the replication only in that way that all my tables
would be replicated and no filters were set. I do this in the Publication
Wizard in the SQL Server Enterprise Manager. In the code I only quote then
the properties for the Replication like login, publisher, etc. (The
Replication works, but as said I want to filter the one table
JobDescription.)
So I am not sure how I should execute your quoted steps to reach my aim. Can
you probably give me a simpler and/or more detailed explanation?:-/ sorry..
mathon

filter in merge publication

Hi There,
I would like to filter first or latest 100 row while performing the
publication for merge replication. Is there any information how i can do
this in the case when table does not exist date fiels.
Thanks for help.
Indra.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Indra,
Provided you can relate 100 rows to a particular key value, you could use
static filters:
In SQL Server Enterprise Manager, expand the Replication and Publications
folders, right-click the publication for which you want to add a row filter,
and then click Properties. On the Filter Rows tab, click the Filter Clause
(...) button next to the article you want to filter, and then in the Specify
Filter dialog box, complete the WHERE clause with a condition for the filter
(BOL). - further details are in BOL: "merge replication, row filters".
HTH,
Paul Ibison
sql

Filter Html tags on Full text Search

I have a Text field in my database that has data along with the HTML tags.
I dont want to search these HTML tags on my FUll text Search.
Example Data
< Font color='red'> Blah</Font>
I want to ignore the font tages on my search.
How do i do it?
save the content in the text data type columns into columns of the image
data type, and use the document type column with a value of htm so that only
the content of these files will be indexed.
"Bruce" <Bruce@.discussions.microsoft.com> wrote in message
news:3BF3659B-E3EB-495F-8D17-75DDD996E323@.microsoft.com...
>I have a Text field in my database that has data along with the HTML tags.
> I dont want to search these HTML tags on my FUll text Search.
> Example Data
> < Font color='red'> Blah</Font>
> I want to ignore the font tages on my search.
> How do i do it?
>
|||Can you please be more elaborate.I am indexing almost 9 columns of Text data
type.How do I save all these columns into columns of the image data type?
How do I use document type columns?
"Hilary Cotter" wrote:

> save the content in the text data type columns into columns of the image
> data type, and use the document type column with a value of htm so that only
> the content of these files will be indexed.
> "Bruce" <Bruce@.discussions.microsoft.com> wrote in message
> news:3BF3659B-E3EB-495F-8D17-75DDD996E323@.microsoft.com...
>
>
|||for the document type column you must
1) ensure you have a column which is char(3) or char(4) and contains the
value htm or .htm
2) store your html content in an image data type column
3) use sp_fulltext_column to specify that the document type is specified in
the document type column you created above in 1)
here is an example
sp_fulltext_column 'MyTable','ImageColumn', 'add', 1033,
'DocumentTypeColumn'
where MyTable is the table you are full text indexing, ImageColumn is a
column of the image datatype, and DocumentTypeColumn is the char(3) or
char(4) column which tells what the native type of the document you are
storing is.
Now, if you also might want to convert your docs to pure text. Using
FiltDump -b myhtmldoc.htm > myhtmldoc.txt is one way of doing it.
To convert your columns from html to text or from the text datatype to image
you should spit them out to the file system, and then convert them and push
them back.
Let me know if you need code samples to do this.
"Bruce" <Bruce@.discussions.microsoft.com> wrote in message
news:604B02B0-2741-402F-AE62-318452B9BDF0@.microsoft.com...
> Can you please be more elaborate.I am indexing almost 9 columns of Text
data[vbcol=seagreen]
> type.How do I save all these columns into columns of the image data type?
> How do I use document type columns?
>
> "Hilary Cotter" wrote:
only[vbcol=seagreen]
tags.[vbcol=seagreen]
|||Hilary,
"Now, if you also might want to convert your docs to pure text. Using
FiltDump -b myhtmldoc.htm > myhtmldoc.txt is one way of doing it."
Is the use of FiltDump in the above scenario a violation of Microsoft's
licensing agreement?
Thanks,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIB1eqrtEHA.2596@.TK2MSFTNGP10.phx.gbl...
> for the document type column you must
> 1) ensure you have a column which is char(3) or char(4) and contains the
> value htm or .htm
> 2) store your html content in an image data type column
> 3) use sp_fulltext_column to specify that the document type is specified
in
> the document type column you created above in 1)
> here is an example
> sp_fulltext_column 'MyTable','ImageColumn', 'add', 1033,
> 'DocumentTypeColumn'
>
> where MyTable is the table you are full text indexing, ImageColumn is a
> column of the image datatype, and DocumentTypeColumn is the char(3) or
> char(4) column which tells what the native type of the document you are
> storing is.
> Now, if you also might want to convert your docs to pure text. Using
> FiltDump -b myhtmldoc.htm > myhtmldoc.txt is one way of doing it.
> To convert your columns from html to text or from the text datatype to
image
> you should spit them out to the file system, and then convert them and
push[vbcol=seagreen]
> them back.
> Let me know if you need code samples to do this.
>
> "Bruce" <Bruce@.discussions.microsoft.com> wrote in message
> news:604B02B0-2741-402F-AE62-318452B9BDF0@.microsoft.com...
> data
type?[vbcol=seagreen]
image[vbcol=seagreen]
that
> only
> tags.
>
|||That's in interesting question. I'll have to check into it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OEpwKoFuEHA.2624@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Hilary,
> "Now, if you also might want to convert your docs to pure text. Using
> FiltDump -b myhtmldoc.htm > myhtmldoc.txt is one way of doing it."
> Is the use of FiltDump in the above scenario a violation of Microsoft's
> licensing agreement?
> Thanks,
> John
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIB1eqrtEHA.2596@.TK2MSFTNGP10.phx.gbl...
> in
> image
> push
Text[vbcol=seagreen]
> type?
> image
> that
HTML
>
|||Yes, as you had indicated in the past that any such use was not allowed by a
non-publicly available license policy for these files in another thread. If
you or Microsoft would make this licensing policy public, then there would
be less confusion on this issue.
Best Regards,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#SNgrgWuEHA.3320@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> That's in interesting question. I'll have to check into it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OEpwKoFuEHA.2624@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
specified[vbcol=seagreen]
a[vbcol=seagreen]
are
> Text
> HTML
>
|||Specifically what I said in the past was that I had been advised by
Microsoft that you could not use the word breakers for your own purposes, ie
to roll your own hit highlighting solution.
Filtdump may be another matter, as it is a diagnostic tool.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%2344qClXuEHA.2828@.TK2MSFTNGP12.phx.gbl...
> Yes, as you had indicated in the past that any such use was not allowed by
> a
> non-publicly available license policy for these files in another thread.
> If
> you or Microsoft would make this licensing policy public, then there would
> be less confusion on this issue.
> Best Regards,
> John
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#SNgrgWuEHA.3320@.TK2MSFTNGP15.phx.gbl...
> the
> specified
> a
> are
>
|||You also "generalized" response to include ALL .dll and .exe files in
addition to the wordbreaker dll files, if memory servers me correctly. I
also asked you (or Microsoft) at that time to make public (and now again)
the specific licensing policy from Microsoft that you are referring to. If
it is a secret or under NDA, then how can anyone judge whether or not he or
she is violating a non-public licensing agreement.
Best Regards,
John
PS: Feel free to contact me off-line.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eyNhvefuEHA.3456@.TK2MSFTNGP10.phx.gbl...
> Specifically what I said in the past was that I had been advised by
> Microsoft that you could not use the word breakers for your own purposes,
ie[vbcol=seagreen]
> to roll your own hit highlighting solution.
> Filtdump may be another matter, as it is a diagnostic tool.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%2344qClXuEHA.2828@.TK2MSFTNGP12.phx.gbl...
by[vbcol=seagreen]
would[vbcol=seagreen]
Microsoft's[vbcol=seagreen]
contains[vbcol=seagreen]
is[vbcol=seagreen]
Using[vbcol=seagreen]
of[vbcol=seagreen]
data[vbcol=seagreen]
[vbcol=seagreen]
the
>
|||Please review the pertinent posts:
http://groups.google.com/groups?hl=e...ver .fulltext
http://groups.google.com/groups?hl=e...ver .fulltext
I am not trying to hide anything and AFAIK the communication was not under
NDA. I do not have the communication I had with the Microsoft developer, nor
do I have the response I received from the link I posted as I posted above.
The link I posted is regarding distributing dlls and exes, as you correctly
point out. When I asked another question about tapping into services exposed
by another Microsoft product for my commercial use, I was directed to this
link by a PSS engineer who explained this was the forum to ask these
questions to.
I'll follow up on filtdump and post back here with the response I get. I
will ask that it can be made public.
Please stop mischaracterizing what I say, or check the original posts before
commenting on them.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:#dD0fKhuEHA.2632@.TK2MSFTNGP10.phx.gbl...
> You also "generalized" response to include ALL .dll and .exe files in
> addition to the wordbreaker dll files, if memory servers me correctly. I
> also asked you (or Microsoft) at that time to make public (and now again)
> the specific licensing policy from Microsoft that you are referring to. If
> it is a secret or under NDA, then how can anyone judge whether or not he
or[vbcol=seagreen]
> she is violating a non-public licensing agreement.
> Best Regards,
> John
> PS: Feel free to contact me off-line.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eyNhvefuEHA.3456@.TK2MSFTNGP10.phx.gbl...
purposes,[vbcol=seagreen]
> ie
allowed[vbcol=seagreen]
> by
thread.[vbcol=seagreen]
> would
Using[vbcol=seagreen]
> Microsoft's
> contains
ImageColumn[vbcol=seagreen]
> is
char(3)[vbcol=seagreen]
you[vbcol=seagreen]
> Using
datatype[vbcol=seagreen]
them[vbcol=seagreen]
> of
> data
of[vbcol=seagreen]
htm[vbcol=seagreen]
> the
Search.
>

Filter Help! - Datatypes

Hello,
I am having trouble using a filter due to incorrect data types. I am trying to eliminate items with a dollar value of 0 from the report. The dollar field that I am using comes from a SQL server database and the data type of the field is decimal(17,5). What value do I need to put in the value field?
I have tried = 0, = 0.0, and = 0.00000
Help!Check this thread:
http://groups.google.com/groups?threadm=OuLkPagWEHA.3012%40tk2msftngp13.phx.gbl
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1665CEF7-A11A-47FD-AB27-BE3A7AB35FFF@.microsoft.com...
> Hello,
> I am having trouble using a filter due to incorrect data types. I am
trying to eliminate items with a dollar value of 0 from the report. The
dollar field that I am using comes from a SQL server database and the data
type of the field is decimal(17,5). What value do I need to put in the
value field?
> I have tried = 0, = 0.0, and = 0.00000
> Help!|||The data provider will return the field as System.Decimal. System.Decimal is
completely different than System.Double (i.e. =0.0) and therefore the
comparison fails.
Please try one of the following:
* change the filter expression to convert the decimal to a double:
=CDbl(...)
* or change the filter value to convert the constant into a decimal:
=CDec(0.0)
More details on the conversion functions are available at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1665CEF7-A11A-47FD-AB27-BE3A7AB35FFF@.microsoft.com...
> Hello,
> I am having trouble using a filter due to incorrect data types. I am
trying to eliminate items with a dollar value of 0 from the report. The
dollar field that I am using comes from a SQL server database and the data
type of the field is decimal(17,5). What value do I need to put in the
value field?
> I have tried = 0, = 0.0, and = 0.00000
> Help!|||Thank you! Both of the options work great.
"Robert Bruckner [MSFT]" wrote:
> The data provider will return the field as System.Decimal. System.Decimal is
> completely different than System.Double (i.e. =0.0) and therefore the
> comparison fails.
> Please try one of the following:
> * change the filter expression to convert the decimal to a double:
> =CDbl(...)
> * or change the filter value to convert the constant into a decimal:
> =CDec(0.0)
> More details on the conversion functions are available at:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:1665CEF7-A11A-47FD-AB27-BE3A7AB35FFF@.microsoft.com...
> > Hello,
> >
> > I am having trouble using a filter due to incorrect data types. I am
> trying to eliminate items with a dollar value of 0 from the report. The
> dollar field that I am using comes from a SQL server database and the data
> type of the field is decimal(17,5). What value do I need to put in the
> value field?
> >
> > I have tried = 0, = 0.0, and = 0.00000
> >
> > Help!
>
>

Filter Help Needed

Good Day to All,
Need some help with Filters in SQL Reporting 2000.
Have a table report setup with a DS going to a Store Proc. need to add a
filter that is a bit complex. Don't know which filter section I should be
putting this in but in all cases I've run into errors.
Basically when the data is brought back from my Store Proc, I need to filter
out the data according to the following:
IF CBalance < 0
THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
ELSE (TTLBalance > (CBalance*0.25))
I keep getting this error that the filter comparison fails and I should
check the data Types returned by the filter expression.
Please help...you might want to cast your fields as varchar in your query. This has
been the answer to many filter comparison problems I have had in the
past.
Eric wrote:
> Good Day to All,
> Need some help with Filters in SQL Reporting 2000.
> Have a table report setup with a DS going to a Store Proc. need to add a
> filter that is a bit complex. Don't know which filter section I should be
> putting this in but in all cases I've run into errors.
> Basically when the data is brought back from my Store Proc, I need to filter
> out the data according to the following:
> IF CBalance < 0
> THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
> ELSE (TTLBalance > (CBalance*0.25))
> I keep getting this error that the filter comparison fails and I should
> check the data Types returned by the filter expression.
> Please help...|||Thanks Topher that was it. Odd the filter section can't handle comparisons
other then varchar / string like values. I wonder if this carried over into
the newer version.
Thanks again!!!
"Topher" wrote:
> you might want to cast your fields as varchar in your query. This has
> been the answer to many filter comparison problems I have had in the
> past.
>
> Eric wrote:
> > Good Day to All,
> > Need some help with Filters in SQL Reporting 2000.
> > Have a table report setup with a DS going to a Store Proc. need to add a
> > filter that is a bit complex. Don't know which filter section I should be
> > putting this in but in all cases I've run into errors.
> >
> > Basically when the data is brought back from my Store Proc, I need to filter
> > out the data according to the following:
> > IF CBalance < 0
> > THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
> > ELSE (TTLBalance > (CBalance*0.25))
> >
> > I keep getting this error that the filter comparison fails and I should
> > check the data Types returned by the filter expression.
> >
> > Please help...
>

Filter for WordML vs Filter for XML vs Filter for Text

We currently are saving WordML (Word 2003's XML format rather than binary
format) docs into a column of our SQL Server 2005 database and full text
indexing on it. Works okay, but not great. For example, all the XML tags
are indexed... so you find many words that are not in the document (from the
user persepctive).
The fix for that is clear... use an XML filter. (Though how to do that is
not so clear... we tried moving the WordML from a Text column to an XML
column, but it gives errors on illegal characters in the Word doc.)
However, it would seem that even an XML filter will not do nearly as well as
a filter designed for WordML.
Can anyone point me to a WordML full-text-search filter for SQL Server
2005?
Thanks,
Brian
Have you tried storing them as doc's in varbinary (max) or image columns and
indexing them with the Word iFilter.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian" <TargetedConvergence@.newsgroup.nospam> wrote in message
news:%23bHGZ5idHHA.4616@.TK2MSFTNGP03.phx.gbl...
> We currently are saving WordML (Word 2003's XML format rather than binary
> format) docs into a column of our SQL Server 2005 database and full text
> indexing on it. Works okay, but not great. For example, all the XML tags
> are indexed... so you find many words that are not in the document (from
> the user persepctive).
> The fix for that is clear... use an XML filter. (Though how to do that is
> not so clear... we tried moving the WordML from a Text column to an XML
> column, but it gives errors on illegal characters in the Word doc.)
> However, it would seem that even an XML filter will not do nearly as well
> as a filter designed for WordML.
> Can anyone point me to a WordML full-text-search filter for SQL Server
> 2005?
> Thanks,
> Brian
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u2u%23lFjdHHA.4656@.TK2MSFTNGP06.phx.gbl...
> Have you tried storing them as doc's in varbinary (max) or image columns
> and indexing them with the Word iFilter.
No, for our app we need to keep them as .xml files, not .doc files.
But we could put the Word .xml file in a varbinary column if there is a
WordML iFilter out there that would act on the .xml file.
Thanks,
Brian
|||Hi Brian,
have you tried storying your WordML files in an a column of type XML? That
should invoke the XML filter.
Best regards,
-Denis.
"Brian" wrote:

> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u2u%23lFjdHHA.4656@.TK2MSFTNGP06.phx.gbl...
>
> No, for our app we need to keep them as .xml files, not .doc files.
> But we could put the Word .xml file in a varbinary column if there is a
> WordML iFilter out there that would act on the .xml file.
> Thanks,
> Brian
>
>
|||"denistc" <denistc@.discussions.microsoft.com> wrote in message
news:64C9346C-881C-4FC5-928C-1C7751ACECA6@.microsoft.com...
> have you tried storying your WordML files in an a column of type XML? That
> should invoke the XML filter.
Yes, though we've had trouble with some WordML files being rejected.
We've narrowed the issue on that front... any WordML file that has "UTF-8"
in the docheader gets rejected... not sure why though. Also not sure why
Word is creating some files with that encoding. Simply removing the tag
from the docheader (without any other re-encoding) seems to work fine, oddly
enough.
We're a bit nervous to move to the XML column until we understand what
conditions might cause XML to reject a WordML doc coming out of Word...
because without that understanding, we can't be sure it won't happen to our
customers.
Thanks for the suggestion,
Brian
|||"Brian" <TargetedConvergence@.newsgroup.nospam> wrote in message
news:%23OqdVzFgHHA.2396@.TK2MSFTNGP04.phx.gbl...
> Yes, though we've had trouble with some WordML files being rejected.
> We've narrowed the issue on that front... any WordML file that has "UTF-8"
> in the docheader gets rejected... not sure why though. Also not sure why
> Word is creating some files with that encoding. Simply removing the tag
> from the docheader (without any other re-encoding) seems to work fine,
> oddly
> enough.
Haven't done much with WordML myself, but assuming the WordML file has
"UTF-8" in the docheader, I'd be interested to know if it actually is a
UTF-8 file? Or is it possible it is being saved with an incorrect BOM or
invalid (non-UTF-8-encoded) characters in the doc? One of those two would
be my first guess. If so, that might be a bug that needs to be reported to
MS. You might try opening that WordML file in a hex editor to verify what
is actually being stored.

filter for SQL commad ?

I dont know to write SQL command filter.
m_strQueryDelete.Format("DELETE FROM tab WHERE (Col1 = abc AND Col2
= abc AND ??)"
example: ODBC found 100 records.
I need to delete first 90 records and to leave last 10 records.
Thank you
Joseph
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Client-filte...ict259609.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=894697
Is there a unique field in the table, where you could write a query
like:
DELETE FROM TAB WHERE UNIQUE_FIELD IN
(SELECT TOP 90 UNIQUE_FIELD FROM TAB WHERE (COL1= 'ABC' AND COL2='ABC'
AND ??))
Good luck,
Tony Sebion
"Joseph" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_894697_c2b2ed0878532c267d72ee72a1e9aff5@.dbf orumz.com:

> I don't know to write SQL command filter.
> m_strQueryDelete.Format("DELETE FROM tab WHERE (Col1 = 'abc' AND Col2
> = 'abc' AND ??)"
> example: ODBC found 100 records.
> I need to delete first 90 records and to leave last 10 records.
> Thank you
> Joseph
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL: http://www.dbforumz.com/Client-filte...ict259609.html
> Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=894697
sql

filter for profiler

Hi ,
How can i actually use the filter for profiler
i have set up the profiler with a filter using the object_name ( as like
"tb_transaction" w/o the quotes) and i have also bring out the object_name
column and make it into a group
however, it seems not to be work. i have tested with statements like "select
* from tb_transaction" and "select * from tb_customer" and both statements
were shown in the profiler and the column object_name is always empty
appreciate ur kind advice
tks & rdgs
Message posted via http://www.droptable.comHi
Object_name is not always recorded, using the object_id and database_id will
give you better results.
John
"maxzsim via droptable.com" wrote:

> Hi ,
> How can i actually use the filter for profiler
> i have set up the profiler with a filter using the object_name ( as like
> "tb_transaction" w/o the quotes) and i have also bring out the object_name
> column and make it into a group
> however, it seems not to be work. i have tested with statements like "sele
ct
> * from tb_transaction" and "select * from tb_customer" and both statements
> were shown in the profiler and the column object_name is always empty
> appreciate ur kind advice
> tks & rdgs
> --
> Message posted via http://www.droptable.com
>|||Hi ,
I have just tried using the object id method
1. the object id column is still blank
2. the profiler shows whatever SQL statement that i have executed on all the
tables including the object that i specially wanted it to be filtered
kindly advise
tks & rdgs
John Bell wrote:[vbcol=seagreen]
>Hi
>Object_name is not always recorded, using the object_id and database_id wil
l
>give you better results.
>John
>
>[quoted text clipped - 11 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1|||Hi
Object_name and object_id are not recorded for this type of event/statement.
If you had a stored procedure then SQL:StmtStarting and SQL:StmtCompleted
record the object_id of the stored procedure (not the table being accessed)
See the topic "TSQL Data Columns" in Books Online for information of the
columns captured and what they mean.
You will need to search your source code to find references to a given table
.
John
"maxzsim via droptable.com" wrote:

> Hi ,
> I have just tried using the object id method
> 1. the object id column is still blank
> 2. the profiler shows whatever SQL statement that i have executed on all t
he
> tables including the object that i specially wanted it to be filtered
> kindly advise
> tks & rdgs
> John Bell wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1
>|||> You will need to search your source code to find references to a given tab
le.
... or capture the execution plan. It might include the SQL statement (I do
n't recall right now...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A8427BCF-906D-46E3-857E-8CE37AC1B666@.microsoft.com...[vbcol=seagreen]
> Hi
> Object_name and object_id are not recorded for this type of event/statemen
t.
> If you had a stored procedure then SQL:StmtStarting and SQL:StmtCompleted
> record the object_id of the stored procedure (not the table being accessed
)
> See the topic "TSQL Data Columns" in Books Online for information of the
> columns captured and what they mean.
> You will need to search your source code to find references to a given tab
le.
> John
> "maxzsim via droptable.com" wrote:
>|||Hi ,
Below is from BOL on the object_name & object_id :
Object ID 22 System-assigned ID of the object.
ObjectName 34 Name of the object being referenced.
Is there a way to monitor all the activities performed on a particular table
?
i find it actually quite diffcult to know the object_id of a SP as there
could be many many SPs which would require me to first know what are the
activities being performed on a table in order to zoom in on a particular SP
else i might be trying on all SPs and the worst case is the one that i am
looking for is the last SP .
would appreciate if you could further advise
tks & rdgs
John Bell wrote:[vbcol=seagreen]
>Hi
>Object_name and object_id are not recorded for this type of event/statement
.
>If you had a stored procedure then SQL:StmtStarting and SQL:StmtCompleted
>record the object_id of the stored procedure (not the table being accessed)
>See the topic "TSQL Data Columns" in Books Online for information of the
>columns captured and what they mean.
>You will need to search your source code to find references to a given tabl
e.
>John
>
>[quoted text clipped - 19 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1|||Hi
There is no easy way to do that, if you do use stored procedures then it
does not necessarily guarantee that a table is being accessed as it could
have branched code. For instance you could use
SP:StmtStarting/SP:StmtCompleted and search the textdata column (which is
easier if it is loaded into a table where you can use patindex) but this
would not be a definitive answer if you have not called every stored
procedure with every combination of parameters/data that will execute all
branches. There is also the possibly of the textdata being truncated.
John
"maxzsim via droptable.com" wrote:

> Hi ,
> Below is from BOL on the object_name & object_id :
> Object ID 22 System-assigned ID of the object.
> ObjectName 34 Name of the object being referenced.
> Is there a way to monitor all the activities performed on a particular tab
le ?
>
> i find it actually quite diffcult to know the object_id of a SP as there
> could be many many SPs which would require me to first know what are the
> activities being performed on a table in order to zoom in on a particular
SP
> else i might be trying on all SPs and the worst case is the one that i am
> looking for is the last SP .
> would appreciate if you could further advise
> tks & rdgs
> John Bell wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1
>|||tks for your advice
appreciate it ..
John Bell wrote:[vbcol=seagreen]
>Hi
>There is no easy way to do that, if you do use stored procedures then it
>does not necessarily guarantee that a table is being accessed as it could
>have branched code. For instance you could use
>SP:StmtStarting/SP:StmtCompleted and search the textdata column (which is
>easier if it is loaded into a table where you can use patindex) but this
>would not be a definitive answer if you have not called every stored
>procedure with every combination of parameters/data that will execute all
>branches. There is also the possibly of the textdata being truncated.
>John
>
>[quoted text clipped - 32 lines]
Message posted via http://www.droptable.com

filter for profiler

Hi ,
How can i actually use the filter for profiler
i have set up the profiler with a filter using the object_name ( as like
"tb_transaction" w/o the quotes) and i have also bring out the object_name
column and make it into a group
however, it seems not to be work. i have tested with statements like "select
* from tb_transaction" and "select * from tb_customer" and both statements
were shown in the profiler and the column object_name is always empty
appreciate ur kind advice
tks & rdgs
--
Message posted via http://www.sqlmonster.comHi
Object_name is not always recorded, using the object_id and database_id will
give you better results.
John
"maxzsim via SQLMonster.com" wrote:
> Hi ,
> How can i actually use the filter for profiler
> i have set up the profiler with a filter using the object_name ( as like
> "tb_transaction" w/o the quotes) and i have also bring out the object_name
> column and make it into a group
> however, it seems not to be work. i have tested with statements like "select
> * from tb_transaction" and "select * from tb_customer" and both statements
> were shown in the profiler and the column object_name is always empty
> appreciate ur kind advice
> tks & rdgs
> --
> Message posted via http://www.sqlmonster.com
>|||Hi ,
I have just tried using the object id method
1. the object id column is still blank
2. the profiler shows whatever SQL statement that i have executed on all the
tables including the object that i specially wanted it to be filtered
kindly advise
tks & rdgs
John Bell wrote:
>Hi
>Object_name is not always recorded, using the object_id and database_id will
>give you better results.
>John
>> Hi ,
>[quoted text clipped - 11 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Hi
Object_name and object_id are not recorded for this type of event/statement.
If you had a stored procedure then SQL:StmtStarting and SQL:StmtCompleted
record the object_id of the stored procedure (not the table being accessed)
See the topic "TSQL Data Columns" in Books Online for information of the
columns captured and what they mean.
You will need to search your source code to find references to a given table.
John
"maxzsim via SQLMonster.com" wrote:
> Hi ,
> I have just tried using the object id method
> 1. the object id column is still blank
> 2. the profiler shows whatever SQL statement that i have executed on all the
> tables including the object that i specially wanted it to be filtered
> kindly advise
> tks & rdgs
> John Bell wrote:
> >Hi
> >
> >Object_name is not always recorded, using the object_id and database_id will
> >give you better results.
> >
> >John
> >
> >> Hi ,
> >>
> >[quoted text clipped - 11 lines]
> >>
> >> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1
>|||> You will need to search your source code to find references to a given table.
... or capture the execution plan. It might include the SQL statement (I don't recall right now...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A8427BCF-906D-46E3-857E-8CE37AC1B666@.microsoft.com...
> Hi
> Object_name and object_id are not recorded for this type of event/statement.
> If you had a stored procedure then SQL:StmtStarting and SQL:StmtCompleted
> record the object_id of the stored procedure (not the table being accessed)
> See the topic "TSQL Data Columns" in Books Online for information of the
> columns captured and what they mean.
> You will need to search your source code to find references to a given table.
> John
> "maxzsim via SQLMonster.com" wrote:
>> Hi ,
>> I have just tried using the object id method
>> 1. the object id column is still blank
>> 2. the profiler shows whatever SQL statement that i have executed on all the
>> tables including the object that i specially wanted it to be filtered
>> kindly advise
>> tks & rdgs
>> John Bell wrote:
>> >Hi
>> >
>> >Object_name is not always recorded, using the object_id and database_id will
>> >give you better results.
>> >
>> >John
>> >
>> >> Hi ,
>> >>
>> >[quoted text clipped - 11 lines]
>> >>
>> >> tks & rdgs
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Hi ,
Below is from BOL on the object_name & object_id :
Object ID 22 System-assigned ID of the object.
ObjectName 34 Name of the object being referenced.
Is there a way to monitor all the activities performed on a particular table ?
i find it actually quite diffcult to know the object_id of a SP as there
could be many many SPs which would require me to first know what are the
activities being performed on a table in order to zoom in on a particular SP
else i might be trying on all SPs and the worst case is the one that i am
looking for is the last SP .
would appreciate if you could further advise
tks & rdgs
John Bell wrote:
>Hi
>Object_name and object_id are not recorded for this type of event/statement.
>If you had a stored procedure then SQL:StmtStarting and SQL:StmtCompleted
>record the object_id of the stored procedure (not the table being accessed)
>See the topic "TSQL Data Columns" in Books Online for information of the
>columns captured and what they mean.
>You will need to search your source code to find references to a given table.
>John
>> Hi ,
>[quoted text clipped - 19 lines]
>> >>
>> >> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Hi
There is no easy way to do that, if you do use stored procedures then it
does not necessarily guarantee that a table is being accessed as it could
have branched code. For instance you could use
SP:StmtStarting/SP:StmtCompleted and search the textdata column (which is
easier if it is loaded into a table where you can use patindex) but this
would not be a definitive answer if you have not called every stored
procedure with every combination of parameters/data that will execute all
branches. There is also the possibly of the textdata being truncated.
John
"maxzsim via SQLMonster.com" wrote:
> Hi ,
> Below is from BOL on the object_name & object_id :
> Object ID 22 System-assigned ID of the object.
> ObjectName 34 Name of the object being referenced.
> Is there a way to monitor all the activities performed on a particular table ?
>
> i find it actually quite diffcult to know the object_id of a SP as there
> could be many many SPs which would require me to first know what are the
> activities being performed on a table in order to zoom in on a particular SP
> else i might be trying on all SPs and the worst case is the one that i am
> looking for is the last SP .
> would appreciate if you could further advise
> tks & rdgs
> John Bell wrote:
> >Hi
> >
> >Object_name and object_id are not recorded for this type of event/statement.
> >If you had a stored procedure then SQL:StmtStarting and SQL:StmtCompleted
> >record the object_id of the stored procedure (not the table being accessed)
> >See the topic "TSQL Data Columns" in Books Online for information of the
> >columns captured and what they mean.
> >
> >You will need to search your source code to find references to a given table.
> >
> >John
> >
> >> Hi ,
> >>
> >[quoted text clipped - 19 lines]
> >> >>
> >> >> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1
>|||tks for your advice
appreciate it ..
John Bell wrote:
>Hi
>There is no easy way to do that, if you do use stored procedures then it
>does not necessarily guarantee that a table is being accessed as it could
>have branched code. For instance you could use
>SP:StmtStarting/SP:StmtCompleted and search the textdata column (which is
>easier if it is loaded into a table where you can use patindex) but this
>would not be a definitive answer if you have not called every stored
>procedure with every combination of parameters/data that will execute all
>branches. There is also the possibly of the textdata being truncated.
>John
>> Hi ,
>[quoted text clipped - 32 lines]
>> >> >>
>> >> >> tks & rdgs
--
Message posted via http://www.sqlmonster.com

filter expressions combined with OR instead of AND

I need to combine filter expressions for my report item with the OR-logic -
but the Reporting Services dialog window is set on the AND-logic. How can I
get past this?
Example
======
What I need is the following:
Fields!Name1 = Parameters!Name
OR
Fields!Name2 = Parameters!Name
What I get from Reporting Services is the following:
Fields!Name1 = Parameters!Name
AND
Fields!Name2 = Parameters!Name
Thank you for your help.I think you can set that in the Expression builder, there is a column called
condition... But if for some reason it doesn't work properly go to the
generic query builder and simply type your expression
Sorry - you are talking about the filter... You could make a function in
the code section, pass both of the field values and parameter values into
the function and return 1 to include the row and 0 to exclude it...Then your
filter expression would be
GetFilter(Fields!Name1.Value,Fields!Name2.Value, Parameters!Name) = 1
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"j schuetz" <j schuetz@.discussions.microsoft.com> wrote in message
news:E5CA33E2-B55D-496D-A48D-C94892135151@.microsoft.com...
> I need to combine filter expressions for my report item with the
OR-logic -
> but the Reporting Services dialog window is set on the AND-logic. How can
I
> get past this?
> Example
> ======> What I need is the following:
> Fields!Name1 = Parameters!Name
> OR
> Fields!Name2 = Parameters!Name
> What I get from Reporting Services is the following:
> Fields!Name1 = Parameters!Name
> AND
> Fields!Name2 = Parameters!Name
> Thank you for your help.
>
>|||Dear Wayne
Thank you for your advice. However, I am a controller, not a programmer.
When I am working with RS, I only use the graphic interface - never the code
section... (-;
Is there not a more user-friendly (controller-friendly...) way around this
problem?
Thanks again.
"Wayne Snyder" wrote:
> I think you can set that in the Expression builder, there is a column called
> condition... But if for some reason it doesn't work properly go to the
> generic query builder and simply type your expression
> Sorry - you are talking about the filter... You could make a function in
> the code section, pass both of the field values and parameter values into
> the function and return 1 to include the row and 0 to exclude it...Then your
> filter expression would be
> GetFilter(Fields!Name1.Value,Fields!Name2.Value, Parameters!Name) = 1
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "j schuetz" <j schuetz@.discussions.microsoft.com> wrote in message
> news:E5CA33E2-B55D-496D-A48D-C94892135151@.microsoft.com...
> > I need to combine filter expressions for my report item with the
> OR-logic -
> > but the Reporting Services dialog window is set on the AND-logic. How can
> I
> > get past this?
> >
> > Example
> > ======> >
> > What I need is the following:
> >
> > Fields!Name1 = Parameters!Name
> > OR
> > Fields!Name2 = Parameters!Name
> >
> > What I get from Reporting Services is the following:
> >
> > Fields!Name1 = Parameters!Name
> > AND
> > Fields!Name2 = Parameters!Name
> >
> > Thank you for your help.
> >
> >
> >
>
>|||Hi,
I also had this problem and discovered that if you put the common filed
first Reporting Services will give you an OR.
So if youy enter your parameters as follows:
Parameters!Name = Fields!Name1
then
Parameters!Name = Fields!Name2
Reporting Services will generate:
Parameters!Name = Fields!Name1
OR
Parameters!Name = Fields!Name2
Steve
"j schuetz" wrote:
> I need to combine filter expressions for my report item with the OR-logic -
> but the Reporting Services dialog window is set on the AND-logic. How can I
> get past this?
> Example
> ======> What I need is the following:
> Fields!Name1 = Parameters!Name
> OR
> Fields!Name2 = Parameters!Name
> What I get from Reporting Services is the following:
> Fields!Name1 = Parameters!Name
> AND
> Fields!Name2 = Parameters!Name
> Thank you for your help.
>
>|||Too bad, but at least you know the differences between AND and OR :))
In any case, you'd be in a much better shape if you do filtering in
your SQL query, not in the report itself. RS filtering is evil,
because it makes SQL server pull ALL the data from the query, just to
throw away some of it later. It would be much more efficient (and
easier) to apply your filtering in your query (dataset):
SELECT ...
FROM ...
WHERE Name1 = @.paramName OR Name2 = @.paramName
On Mon, 24 Jan 2005 07:13:06 -0800, j schuetz
<jschuetz@.discussions.microsoft.com> wrote:
>Dear Wayne
>Thank you for your advice. However, I am a controller, not a programmer.
>When I am working with RS, I only use the graphic interface - never the code
>section... (-;
>Is there not a more user-friendly (controller-friendly...) way around this
>problem?
>Thanks again.
>
>"Wayne Snyder" wrote:
>> I think you can set that in the Expression builder, there is a column called
>> condition... But if for some reason it doesn't work properly go to the
>> generic query builder and simply type your expression
>> Sorry - you are talking about the filter... You could make a function in
>> the code section, pass both of the field values and parameter values into
>> the function and return 1 to include the row and 0 to exclude it...Then your
>> filter expression would be
>> GetFilter(Fields!Name1.Value,Fields!Name2.Value, Parameters!Name) = 1
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "j schuetz" <j schuetz@.discussions.microsoft.com> wrote in message
>> news:E5CA33E2-B55D-496D-A48D-C94892135151@.microsoft.com...
>> > I need to combine filter expressions for my report item with the
>> OR-logic -
>> > but the Reporting Services dialog window is set on the AND-logic. How can
>> I
>> > get past this?
>> >
>> > Example
>> > ======>> >
>> > What I need is the following:
>> >
>> > Fields!Name1 = Parameters!Name
>> > OR
>> > Fields!Name2 = Parameters!Name
>> >
>> > What I get from Reporting Services is the following:
>> >
>> > Fields!Name1 = Parameters!Name
>> > AND
>> > Fields!Name2 = Parameters!Name
>> >
>> > Thank you for your help.
>> >
>> >
>> >
>>|||Thanks to Steve and Usenet User for their helpful piece of advice. Now I am
getting along!

filter expression using measures

Hi guys,

I want to filter an MDX query based on a specific measure values, however, the MDX query designer allows you to filter only based on a dimension.

Is there any soulution to resolve this problem.

Sincerely,

--Amde

In the data tab for reporting services, select your dataset you want to filter by in the drop down list. Now, click the "..." to the right. In the filters tab, you can filter the dataset by an expression you provide.

For example, if you wanted to filter the results so that only customers with over 100 purchases were displayed, you could do this:

Expression: =Fields!NumberOfPurchases.value
Operator: >
Value: 100

You should be good to go. Good luck!!

|||thx!

Filter expression for searching "with all the words"

Using VWD I have created a search feature using the LIKE clause. The filter expression on my SQLDataSource allows the user to search the Description field of a database and yield a result that contains the exact word or phrase entered into a textbox. Assuming that the user enters more than one word, my understanding is that the search result is limited to database rows that contain the EXACT phrase (such as found in an advanced Google search using the “with the exact phrase” option). The current filter expression is:

Description LIKE '%{0}%'

For example, if “John Smith” is typed into the search textbox, the results will include a row with:

1. “John Smith is my neighbor”

but NOT a row with

2. “John is my neighbor. His last name is Smith”.

How does one modify the filter expression so that the search result is like the Google “with all the words” search option, where the search results are limited to records in which all the words typed into the textbox are present but not necessarily in the EXACT continuous order? In the example above, BOTH Descriptions would be returned in the search results when “John Smith” in typed into the search textbox.

Thanks for any help you can provide in helping me refine my search options.

Sounds like you're looking for a "full-text search".

read this:http://msdn2.microsoft.com/en-us/library/ms142571.aspx

|||

To search several words,like "John Smith" ,you have to split that into "john" and "smith " then use

Description LIKE '%John%' andDescription LIKE '%Smith%'

but that operation is quite expensive,I recommend you to use fulltext search. You can see the following like for details:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_fulltextsearch.asp

|||

Thanks for the suggestions. I will explore these options.

I do have a couple of general questions about doing searches:

1. Am I correct in assuming that when you use LIKE as I have done (Description LIKE '%{0}%') that the search results that are returned are for the EXACT word or phrase that was searched (i.e., comparable in accuracy - if not speed - to the Google exact phrase search)?

2. Since full-text searches rely on an index they do not necessarily return EXACT results because "noise" words are eliminated? Thus the best that can be hoped for is an exact word order, but not an exact phrase?

3. Can the combined filter expression cited above (Description LIKE '%John%' and Description LIKE '%Smith%) contain parameters instead of the hardcoded search terms (Description LIKE '%{0}%' andDescription LIKE '%{0}%) that allow any combination of terms to be entered into the search textbox?

I look forward to researching the full-text approach and getting it implemented into my search page. Thanks again.

sql

filter expression "LIKE" together with report paramter in RS

Hi there
I have a text field that I would like to filter with a LIKE expression, i.e.:
WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%BSK%')
When I use this filter expression in the data query of Reporting Services
everything goes well.
But when I try to use this filter expression in the the report item (table)
I don't get this filter expression working. How do I write this filter
expression in the filter definition in the properties of my table? How
exactly is the right way to write the filter value (BSK) so that the filter
gets me every text field that has BSK somewhere in its contents?
Thanks a lot for your help!
Judith.If you are using a parameter in the Like expression with a wild card try it
like this:
WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%' + @.BSK + '%')
"j schuetz" <jschuetz@.discussions.microsoft.com> wrote in message
news:52BC887E-C77F-4FC7-9DDE-ED5B20826EAF@.microsoft.com...
> Hi there
> I have a text field that I would like to filter with a LIKE expression,
> i.e.:
> WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%BSK%')
> When I use this filter expression in the data query of Reporting Services
> everything goes well.
> But when I try to use this filter expression in the the report item
> (table)
> I don't get this filter expression working. How do I write this filter
> expression in the filter definition in the properties of my table? How
> exactly is the right way to write the filter value (BSK) so that the
> filter
> gets me every text field that has BSK somewhere in its contents?
> Thanks a lot for your help!
> Judith.|||Thank you for your input. I know now how to solve the problem. The thing is
that the syntax of the filter expression in the table properties window is
different from the syntax in the query.
The value field in the filter expression of the table properties has to have
the following syntax: [= "*" & Parameters!Stichwort.Value & "*"], i.e. the
wild card symbol is * instead of %.
With this syntax the LIKE operator in the table properties window works just
fine!
"Steve Dearman" wrote:
> If you are using a parameter in the Like expression with a wild card try it
> like this:
> WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%' + @.BSK + '%')
>
> "j schuetz" <jschuetz@.discussions.microsoft.com> wrote in message
> news:52BC887E-C77F-4FC7-9DDE-ED5B20826EAF@.microsoft.com...
> > Hi there
> >
> > I have a text field that I would like to filter with a LIKE expression,
> > i.e.:
> > WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%BSK%')
> >
> > When I use this filter expression in the data query of Reporting Services
> > everything goes well.
> >
> > But when I try to use this filter expression in the the report item
> > (table)
> > I don't get this filter expression working. How do I write this filter
> > expression in the filter definition in the properties of my table? How
> > exactly is the right way to write the filter value (BSK) so that the
> > filter
> > gets me every text field that has BSK somewhere in its contents?
> >
> > Thanks a lot for your help!
> >
> > Judith.
>
>

Filter Expression

Hi,

I'm trying to create a query where the results are restricted by the date selected. When the user selects two different dates, the query works. However, if the user wanted to see receivals for only one day, the query returns nulls even though there are receivals for that day. Below is the code:

SELECT {{[Measures].[Tonnes]}} on 0,
NonEmptyCrossJoin({{Filter([Date].[Day].members, [Date].currentmember.name = "2006-10-16")}}, {{[Transaction].[Closing CarryOver].[IN].[Receival tonnes]}}, {{[Measures].[Tonnes]}}, 2) DIMENSION PROPERTIES MEMBER_CAPTION on 1
FROM GrainMovements

The "2006-10-16" is the date selected by the user. Any ideas why the filter doesn't like just returned one date, would be extremely helpful! Thanks.

First, you should lose NonEmptyCrossJoin from the query, which is hurting here, and also may produce wrong results if you have more than one measure group.

The simplest query to do what you want is

SELECT {[Measures].[Tonnes]} on 0,
NON EMPTY {([Date].[Day].[2006-10-16], [Transaction].[Closing CarryOver].[IN].[Receival tonnes])} DIMENSION PROPERTIES MEMBER_CAPTION on 1
FROM GrainMovements

Filter Expression

Hi

I have some data that need to be filtered based on a SET of Id's.
If it's about a single ID, then i would pass it as a parameter in a
stored procedure and use it within the the WHERE Clause, but here those
ID's are determined in run time and I can't simply create a a stored
procedure for an unknown amount of ID's.
I looked into the SQL Server 8.0 Manual but had no examples how to use
the Function Filter.
Generaly, how can filter some records based on a set of ID's?

Best regardscoosa (coosa76@.gmail.com) writes:
> I have some data that need to be filtered based on a SET of Id's.
> If it's about a single ID, then i would pass it as a parameter in a
> stored procedure and use it within the the WHERE Clause, but here those
> ID's are determined in run time and I can't simply create a a stored
> procedure for an unknown amount of ID's.
> I looked into the SQL Server 8.0 Manual but had no examples how to use
> the Function Filter.
> Generaly, how can filter some records based on a set of ID's?

FROM tbl t
JOIN iter_intlist_to_tbl(@.list_of_ids, DEFAULT) f ON t.id = f.number

iter_intlist_to_tbl is table-valued function that unpacks a space-
separared list of integers into table which you then join with.

Code at http://www.sommarskog.se/arrays-in-...ist-of-integers.
The rest of the article provides more discussion about this kind of problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Filter Error: "..processing of filter expression..cannot be perfor

My results return a tinyint column which is either 0,1,2.
When adding a filter to the matrix it seems ok
blah.value = 2
but when running the report i get the following error:
--error--
An error has occured during report processing.
The processing of filter expression for the matrix 'matrix1' cannot be
performed. The comparison failed. Please check the data type returned by the
filter expression.
--enderror--
i have also tried
blah.value = "2"
with no success
anyone know why?a-ha!
this works
Expression
=CInt(Fields!salesGroup.Value)
Operator
=
Value
=2
frankly this is crap
i am returning a number and comparing to a number
- why should i have to convert a number to er a er number?
- why do i have to put an equals in front of the number?
"adolf garlic" wrote:
> My results return a tinyint column which is either 0,1,2.
> When adding a filter to the matrix it seems ok
> blah.value = 2
> but when running the report i get the following error:
> --error--
> An error has occured during report processing.
> The processing of filter expression for the matrix 'matrix1' cannot be
> performed. The comparison failed. Please check the data type returned by the
> filter expression.
> --enderror--
>
> i have also tried
> blah.value = "2"
> with no success
> anyone know why?
>

Filter Error

I have No Filters in Place but I Keep getting this error on a couple reports.

An error has occurred during report processing.

The processing of SortExpression for the table ‘table2’ cannot be performed. The comparison failed. Please check the data type returned by the SortExpression.

What is table2

Have you configured interactive sorting to text field

sql

Filter duplicate records

Hi

Have been given the task of trying to write reports in Crystal after someone found out I new one of Access from the other.

The report is taking data from SAGE database with custom written tables. The problem I have is that one of the custom table has not been well written allowing multiple identical entries.

The table in question holds data for each delivery note posted on SAGE. In this table are field I need relating to Product Number, Quantity and Nett Weight. Due to the way data has been stored I can have up to 16 records (effectively duplicates - same part number, quantity, nett weight) when I only want one record displayed.

Is it possible to write a filter that basically says that if for a given delivery number there is more than 1 record with the same Product Number, Quantity then filter the number of records down to one? If this is not possible, would it be possible to do the same but specifying to pick the record with the highest nett weight? assuming that if there were two identical records for all of the aforementioned fields that it could still bring this down to one?You can do it in your Query:

SELECT ProductNumber, Quantity, 'NettWeight' = MAX(NettWeight)
FROM TableName
GROUP BY ProductNumber, Quantity

(I use SQL Server 7, so you may need to adjust the syntax a bit to work in your database.)

- or -

You can do it in Crystal Reports. There should be a property called SuppressDuplicates (or something similar, I don't have CR in front of me). If you set it to True, Crystal should display only unique records. I think that property is available for fields as well as sections, but I'm not sure. Also, I use CR 8.5, so if you're using a different version, SuppressDuplicates may not exist at all or it may be called something different.

Filter double results from query

We are working on an application which aggregates news articles from various sources. The problem is, when querying the database, is that there are a lot of double results because some of the sources get their news from the same press agency. To clarify things a bit here's a link to an example:

http://84.244.146.27/Zoekresultaten.aspx?SearchQuery=Amsterdam

As you can see, there are a lot of the same results. What we want is to filter out the double results irrespective of the source. In other words; when the query encounters the first result, display that and leave out the other same results.

I've tried filtering them out by using DISTINCT on the 'news header' / results, but that does not seem to work. Any ideas on how to get this done?

Any help is greatly appreciated!

Providing the aggregator select is an infrequent action, you can use a CURSOR within it to select just the first record to a buffer table which the page will read.

|||

Thanks Tatworth for your answer! I'm afraid that it sounds a bit like Chinese to me though...

The aggregator is run every couple of minutes and of course it would be best to filter the results in that step, then filtering on querying results. There's a slight problem though with the aggregator script. It's in PHP :( and PHP and MSSQL don't play along that nicely with eachother as far as I have experienced.

Is there a way to filter within the select query?

|||

Yes, as an example:

14-11-2007

10:52

Marokkaanse vaders in actie in Amsterdam-West

BNdestem

|||

If your answer is 2005 or Express, and you want the earliest record, this syntax will do it for you:

SELECT *

FROM (

SELECT {the fields from your original query},row_number() OVER (PARTITION BY {the list of key fields} ORDER BY {Your datetime field} ASC) AS TheRank

{the rest of your original query}

) t1

WHERE TheRank=1

Example:

SELECT *FROM (SELECT NewsDate,NewsTitle,NewsLink,row_number()OVER (PARTITIONBY NewsTitleORDER BY NewsDateASC)AS TheRankFROM NewsTable) t1WHERE TheRank=1

|||

Thanks very much Motley! It works like a charm!

Filter does not work on a group?

I must be missing something. I have a table with two groups. One groups on
the user who created the record the other on the date the record was created.
I sort on the username group by name and the createdate group with a
category type.
My dataset pulls information for the current year however in this table I
want to only display data for the last week. If I apply the filter to the
table I receive expected results however if I apply the filter to the group
nothing comes back.
For either group if I put "= #2/22/2005#" for the expression "<=" as the
operator and "= Fields!CreateDate" as the Value I return 0 records despite
having some. I know my syntax is correct becaus this works at the table
level.
Can anyone tell me what I am missing?And you have date values in there more recent than 2/22/2005?
2/22/2005 <= Fields!CreateDate
I would try "= Fields!CreateDate" in the expression and "= #2/22/2005#" in
the value.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ben Holcombe" <BenHolcombe@.discussions.microsoft.com> wrote in message
news:41E911DB-58AE-4F74-9A6B-B6144C454DCA@.microsoft.com...
>I must be missing something. I have a table with two groups. One groups
>on
> the user who created the record the other on the date the record was
> created.
> I sort on the username group by name and the createdate group with a
> category type.
> My dataset pulls information for the current year however in this table I
> want to only display data for the last week. If I apply the filter to the
> table I receive expected results however if I apply the filter to the
> group
> nothing comes back.
> For either group if I put "= #2/22/2005#" for the expression "<=" as the
> operator and "= Fields!CreateDate" as the Value I return 0 records despite
> having some. I know my syntax is correct becaus this works at the table
> level.
> Can anyone tell me what I am missing?|||Same result. I know there are values past 2/22/2005 because I see them
without the filter applied. The filter works at the table level just not the
group level. This is strange.
"Jeff A. Stucker" wrote:
> And you have date values in there more recent than 2/22/2005?
> 2/22/2005 <= Fields!CreateDate
> I would try "= Fields!CreateDate" in the expression and "= #2/22/2005#" in
> the value.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Ben Holcombe" <BenHolcombe@.discussions.microsoft.com> wrote in message
> news:41E911DB-58AE-4F74-9A6B-B6144C454DCA@.microsoft.com...
> >I must be missing something. I have a table with two groups. One groups
> >on
> > the user who created the record the other on the date the record was
> > created.
> > I sort on the username group by name and the createdate group with a
> > category type.
> >
> > My dataset pulls information for the current year however in this table I
> > want to only display data for the last week. If I apply the filter to the
> > table I receive expected results however if I apply the filter to the
> > group
> > nothing comes back.
> >
> > For either group if I put "= #2/22/2005#" for the expression "<=" as the
> > operator and "= Fields!CreateDate" as the Value I return 0 records despite
> > having some. I know my syntax is correct becaus this works at the table
> > level.
> >
> > Can anyone tell me what I am missing?
>
>

filter detail section of matrix report

Hi,
is there a way to filter details section of the report created using Matrix report type?
I just want to see rows that contain positive numbers.

Thanks,
Igor

Have you tried using the filter tab in the matrix properties dialog box.

I'd be careful though as each matrix cell is an aggregation of 1 or more detail rows. Depending on the nature of the source data and whether any aggregation is actually occuring you'll want to watch out for filtering underlying negative values vs a negative result of an aggregate.

Can you not do this in the source query?

Filter delete rows on subscriber

How does SQL server replication delete rows on the client that no longer
matches the filter? I need this to decide if I want to add a new table into
replication.
TIA.
Rajesh
There is a table with a large number of rows (5-6 million), with only a few
rows (2000-3000) replicated to each client. Each client needs to get a
different set of rows.
I want to know whether:
1. The filter is applied on the subscriber to delete the rows?
2. Or the IDs to be deleted is sent to the subscriber by the publisher?
3. Or the IDs on the subscriber is sent to the publisher, for which the
publisher responds with the IDs that should no longer be available in the
subscriber.
Due to the large number of rows, I'm just worried that #2 and #3 will lead
to a lot of inefficeincy. And if that is the case, I would rather sync this
data outside of replication.
Rajesh
"Rajesh" wrote:

> How does SQL server replication delete rows on the client that no longer
> matches the filter? I need this to decide if I want to add a new table into
> replication.
> TIA.
> Rajesh
sql

Filter date for ToDay, some problem with time.

I all.

In a talbe I've a datatime field. for example it contain '16-4-2007 10:45'.

I like to write a SQL that return all record with the date field equals '16-4-2007' (it's not important the time). how to?

thank you.

Try this query to retrive data

select

*from clientswhere clientAddressbetween'2007-04-12 00:00:00.000'and'2007-04-12 23:59:59.999'

The alternative query is

select

*from clientswhere clientAddress>='2007-04-12'and clientAddress<'2007-04-13'

The time format is yyyy-MM-dd and time. Since you are saving time in DB the query should be framed as above

Hope this will help you

|||

try this syntax

print

convert(varchar(20),convert(datetime,'16-04-2006 16:45',105),105)

how it works: convert string to date time with correct format for date string and next convert date time back to string with format you need.

See help for CONVERT in T-SQL help for more format info.

Filter Data within a graph

I have a dilemma where I have a dataset that is composed of three columns:

Key, TimeStamp, Value

My chart needs to show different lines based on what the Key in the dataset is through a certain timeframe. Take for example my dataset has 4 keys. On the first key, I need to show the average of the values. On the second key, I need to show the max values. My third key, I need to show the min values. My 4th key I need to show the Median.

I need to be able to put 4 different entries in the Values list of my chart and have them filtered based on the criteria based on the same column within the dataset. I guess my question is: Can I put an IIF statement inside of the Value entry to get my desired result and if so, what would the syntax be?

Yes, you have to put 4 entries into the values list. For example:

1. =iif(Fields!Key.Value = "A", Fields!Value.Value, Nothing)
2. =iif(Fields!Key.Value = "B", Fields!Value.Value, Nothing)
3. =iif(Fields!Key.Value = "C", Fields!Value.Value, Nothing)
4. =iif(Fields!Key.Value = "D", Fields!Value.Value, Nothing)

Note: in a line chart, the chart control will only connect data points that have a value different than null (Nothing).

-- Robert

Filter Data using parameters (in vb.net)

I am trying to use reporting services with vb.net. Since there is no EASY way
to pass dataset to the reporting services (i understand until Vs.net2005). As
an alternative below is what I am thinking of.
I am using vb.net and trying to dynamically generate the SQL to control the
filter. In the initial report definition I have the base SQL get a large set
of data and filter the data within the report format.
How do i pass filter the report data by using parameters. Has anybody got
any sample code?
I have checked the available paramters and what they are supposed to be used
for. They are:
rs prefix (which uses the Command, Format, Snapshot paramters)
rc prefix - i.e type of format
Not sure how I can use the existing parameters so that I am able to filter.
Please help,
Rgs,
MehulI just had a similar issue, I was returning a large set of data for a graph,
and wanted to use the same data to show only a single row, so I put a table
and filtered the data based on the parameter passed into the report.
1.) add the table
2.) set upt he rows and headers to show
3.) click on table and select properties
4.) click filters
5.) in left column, select the field name that you want to filter on
6.) in second column choose relationship
7.) in third columns, select expression, then choose parameters from the
tree and select the actual parameter to filter based on
8.) the filter looks something like Fields!filedName.Value =Parameters!parameterName.Value
you may have to explicitly cast these both to ints
CINT(Fields!filedName.Value) = CINT(Parameters!parameterName.Value)
And at this point, you have your form element showing only data that matches
the parameter that was passed to the report.
Hope this helps
"melu" <melu@.discussions.microsoft.com> wrote in message
news:5C51CC73-3424-47E1-95E7-9E9EE73684A3@.microsoft.com...
> I am trying to use reporting services with vb.net. Since there is no EASY
way
> to pass dataset to the reporting services (i understand until Vs.net2005).
As
> an alternative below is what I am thinking of.
>
> I am using vb.net and trying to dynamically generate the SQL to control
the
> filter. In the initial report definition I have the base SQL get a large
set
> of data and filter the data within the report format.
>
> How do i pass filter the report data by using parameters. Has anybody got
> any sample code?
>
> I have checked the available paramters and what they are supposed to be
used
> for. They are:
> rs prefix (which uses the Command, Format, Snapshot paramters)
> rc prefix - i.e type of format
> Not sure how I can use the existing parameters so that I am able to
filter.
>
> Please help,
> Rgs,
> Mehul