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

filter data from multiple tables crashes app!

i didnt think my sql qeury was that complicated that it would crash my web
app. all im trying to do is filter data between two tables. heres my query

<cfquery name="GetResults" datasource="#datasource#">
SELECT *
FROM Content, Content_Sites
WHERE Content.ContentID <> Content_Sites.ContentID
ORDER BY Content.ContentID DESC
</cfquery
equals works, but when i try not equals, it all goes haywire. any ideas?

TIAjonezy (jonezy@.donotmailmejonezy.com) writes:
> i didnt think my sql qeury was that complicated that it would crash my
> web app. all im trying to do is filter data between two tables. heres
> my query
><cfquery name="GetResults" datasource="#datasource#">
> SELECT *
> FROM Content, Content_Sites
> WHERE Content.ContentID <> Content_Sites.ContentID
> ORDER BY Content.ContentID DESC
></cfquery>
> equals works, but when i try not equals, it all goes haywire. any ideas?

Yes and no. Since I don't know your tables, and neither know what you
are trying to achieve, how could I really have any ideas?

But, OK, having seen people using <> in the wrong place before, I can
make a guess. Say that both table have a thousand rows. You are now
asking for all million combinations of these two thousand rows - save
those that have the same ID.

I guess what you are looking for is really something like:

SELECT *
FROM Content c ,
WHERE NOT EXISTS (SELECT *
FROM Content_Sites cs
WHERE c.ContentID = cs.ContentID)
ORDER BY c.ContentID DESC

That is, list all Content that does not have any content site.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <vl786sjaijmr91@.corp.supernews.com>,
jonezy@.donotmailmejonezy.com says...
> i didnt think my sql qeury was that complicated that it would crash my web
> app. all im trying to do is filter data between two tables. heres my query
> <cfquery name="GetResults" datasource="#datasource#">
> SELECT *
> FROM Content, Content_Sites
> WHERE Content.ContentID <> Content_Sites.ContentID
> ORDER BY Content.ContentID DESC
> </cfquery>
> equals works, but when i try not equals, it all goes haywire. any ideas?

How big are the tables? You DO realize you've asked for a
cross product? That means the database is returning the
ENTIRE contents (minus one row) of the Content_Sites table
for each ROW of the Content table.

Assuming 500 rows in Content_Sites and 1000 rows in
Content, you are getting back 500,000 rows in your query.

--
Cam|||thanks.., dunno how i overlookd NOT EXISTS.

i also realized i forgot to include the table connection between content and
content_sites. guess i was in a hurry.

thanks again!

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93E9E8B075BFAYazorman@.127.0.0.1...
> jonezy (jonezy@.donotmailmejonezy.com) writes:
> > i didnt think my sql qeury was that complicated that it would crash my
> > web app. all im trying to do is filter data between two tables. heres
> > my query
> ><cfquery name="GetResults" datasource="#datasource#">
> > SELECT *
> > FROM Content, Content_Sites
> > WHERE Content.ContentID <> Content_Sites.ContentID
> > ORDER BY Content.ContentID DESC
> ></cfquery>
> > equals works, but when i try not equals, it all goes haywire. any
ideas?
> Yes and no. Since I don't know your tables, and neither know what you
> are trying to achieve, how could I really have any ideas?
> But, OK, having seen people using <> in the wrong place before, I can
> make a guess. Say that both table have a thousand rows. You are now
> asking for all million combinations of these two thousand rows - save
> those that have the same ID.
> I guess what you are looking for is really something like:
> SELECT *
> FROM Content c ,
> WHERE NOT EXISTS (SELECT *
> FROM Content_Sites cs
> WHERE c.ContentID = cs.ContentID)
> ORDER BY c.ContentID DESC
> That is, list all Content that does not have any content site.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql