Thursday, March 29, 2012

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...
>