Filtering without a tuple-set ?

a normal Filter with a tuple-set:

Filter(
[Brand].members,
(([Measures].[Sale],
[Posting Period].[Year].&[2005]) > 0)
)

The brands where we sold something in the year 2005. This works fine but....

a Filter within a range of days ? :

Filter(
[Brand].members,
(([Measures].[Sale],
ClosingPeriod([Posting Period].[Day]).lag(365):ClosingPeriod([Posting Period].[Day]))) > 0)

The brands where we sold something in a range between currentday -365 days and currentday.
This won't work with the filter-function ! Is there an other way to do this ?

Kind regards

If your Sales measure does not contain negatives you could simply aggregate the measure for the range of dates.

Filter(
[Brand].members,
Aggregate(ClosingPeriod([Posting Period].[Day]).lag(365)
:ClosingPeriod([Posting Period].[Day]
,[Measures].[Sale] ) > 0)

If your measure might contain negative figures and there is a chance that a date range could sum to 0 even though there are sales for the specified period, you might be better off getting a count of the non empty cells.

Filter(
[Brand].members,
NonEmpty(ClosingPeriod([Posting Period].[Day]).lag(365)
:ClosingPeriod([Posting Period].[Day]
,[Measures].[Sale] ).Count > 0)

Filtering with StartTime and Endtime problem

Hello,

I have a problem with the following task:

The user is prompted to specify a starttime like 02 and a endtime 04.

The user for example wants to search all events between 2 am and 10 am regardless of the date.

I use the following SQL ti filter:

Where (DATEPART(hour, Event.EventDateTime) BETWEEN @.StartHour AND @.EndHour)

This works fine as long starhour is smaller than endhour.

When i want to filter Events beteween 23 (11 pm.) and 02 (2 am) i get no results. I have to make a search possible for a time span that is over midnight.

Anyone who has some ideas?

Thank you i advance!

Hello Luskan,

Try this:

select *

from Table1

where 1 =

case

when @.StartHour > @.EndHour and (datepart(hour, Event.EventDateTime) >= @.StartHour or datepart(hour, Event.EventDateTime) <= @.EndHour) then 1

when @.StartHour <= @.EndHour and datepart(hour, Event.EventDateTime) between @.StartHour and @.EndHour then 1

end

Hope this helps.

Jarret

filtering with set?

i have two dimensions named dimLocation and dimTargetSet. What is needed is to count the members of dimLocation that is also a member of dimTargetSet. I am thinking of using a filter but have no idea what to do.

Count(FILTER(Descendants([dimLocation].currentmember,9,Leaves), ?))

Any ideas?... thank you so much....

Are you saying that both dimensions have the same key values? From your example code, I am guessing that you may be using AS2k (as there are only dimension and no hierarchy references) so I have coded my suggestion below to work on AS2k.

count(filter(descendants([dimLocation].currentmember,,leaves), linkMember([dimLocation].currentmember,[dimTargetSet]) <> NULL))

LinkMember will look for a member with the same key in the other hierarchy.

|||

thanks for your reply... i tried it but its not what i need. uhhmmm... what i need to do is to count the members of the first dimension.

count(Descendants([dimLocation].&[2583],9,LEAVES))

But the problem is when we select a member of the second dimension ([dimTargetset])... the second dimension is about location grouping... so if we select a group for west it should only count the members of [dimLocation] that belongs to that group....

Thank you....

|||

If targetset is a group of locations, why haven't you just modelled it as an attribute (or level in AS2000) of the location hierarch? Unless there is a many-to-many relationship between targetsets and locations, this would probably be the best way to go.

If you model two items as separated dimensions, then they are, by definition, treated as independent entities.

The only other way to do this would be to count a non-empty measure value

count(filter(descendants(dimLocation.Currentmember,,LEAVES),NOT IsEmpty(Measures.[....])))

If you are using AS2k5 you could use the NonEmpty() function which would be faster in most cases than using filtering.

|||

thank you so much for your reply....

filtering with set?

i have two dimensions named dimLocation and dimTargetSet. What is needed is to count the members of dimLocation that is also a member of dimTargetSet. I am thinking of using a filter but have no idea what to do.

Count(FILTER(Descendants([dimLocation].currentmember,9,Leaves), ?))

Any ideas?... thank you so much....

Are you saying that both dimensions have the same key values? From your example code, I am guessing that you may be using AS2k (as there are only dimension and no hierarchy references) so I have coded my suggestion below to work on AS2k.

count(filter(descendants([dimLocation].currentmember,,leaves), linkMember([dimLocation].currentmember,[dimTargetSet]) <> NULL))

LinkMember will look for a member with the same key in the other hierarchy.

|||

thanks for your reply... i tried it but its not what i need. uhhmmm... what i need to do is to count the members of the first dimension.

count(Descendants([dimLocation].&[2583],9,LEAVES))

But the problem is when we select a member of the second dimension ([dimTargetset])... the second dimension is about location grouping... so if we select a group for west it should only count the members of [dimLocation] that belongs to that group....

Thank you....

|||

If targetset is a group of locations, why haven't you just modelled it as an attribute (or level in AS2000) of the location hierarch? Unless there is a many-to-many relationship between targetsets and locations, this would probably be the best way to go.

If you model two items as separated dimensions, then they are, by definition, treated as independent entities.

The only other way to do this would be to count a non-empty measure value

count(filter(descendants(dimLocation.Currentmember,,LEAVES),NOT IsEmpty(Measures.[....])))

If you are using AS2k5 you could use the NonEmpty() function which would be faster in most cases than using filtering.

|||

thank you so much for your reply....

filtering with set?

i have two dimensions named dimLocation and dimTargetSet. What is needed is to count the members of dimLocation that is also a member of dimTargetSet. I am thinking of using a filter but have no idea what to do.

Count(FILTER(Descendants([dimLocation].currentmember,9,Leaves), ?))

Any ideas?... thank you so much....

Are you saying that both dimensions have the same key values? From your example code, I am guessing that you may be using AS2k (as there are only dimension and no hierarchy references) so I have coded my suggestion below to work on AS2k.

count(filter(descendants([dimLocation].currentmember,,leaves), linkMember([dimLocation].currentmember,[dimTargetSet]) <> NULL))

LinkMember will look for a member with the same key in the other hierarchy.

|||

thanks for your reply... i tried it but its not what i need. uhhmmm... what i need to do is to count the members of the first dimension.

count(Descendants([dimLocation].&[2583],9,LEAVES))

But the problem is when we select a member of the second dimension ([dimTargetset])... the second dimension is about location grouping... so if we select a group for west it should only count the members of [dimLocation] that belongs to that group....

Thank you....

|||

If targetset is a group of locations, why haven't you just modelled it as an attribute (or level in AS2000) of the location hierarch? Unless there is a many-to-many relationship between targetsets and locations, this would probably be the best way to go.

If you model two items as separated dimensions, then they are, by definition, treated as independent entities.

The only other way to do this would be to count a non-empty measure value

count(filter(descendants(dimLocation.Currentmember,,LEAVES),NOT IsEmpty(Measures.[....])))

If you are using AS2k5 you could use the NonEmpty() function which would be faster in most cases than using filtering.

|||

thank you so much for your reply....

Filtering with Replication

Hi,
I have to replication a database with a merge replication type
In that database there are 4 tables that I dont need on the subscribers
I use a pull subscriptions
I try to do a publication with out those tables but i received a message
saying that those tables have dependencies on other tables.
What the best way to manage this situation
Those tables are very large and there are long to copy from the Publisher to
the Subscribers.
I try using the row filtering but I think I dont understand what rows
filtering works because after doing few test the replication was reacting the
same way as with out row filtering.
Thanks in advance !
ignore this message and move on. Let me try to explain how this option
works. Suppose you are filtering on the orders table, orders detail table
and the customers table. You filter on the customers table by StateID. The
CustomerID column is also in the orders table, but not in the orders detail
table.
A CA customer moves to a different state (NY) and so the record for this
customer leaves the Customers table. But as there is no filter by state on
the orders table or the orders detail table, his orders remain in CA. Merge
replication is able to walk the DRI and drag the customers order, and order
details rows with him when he moves to NY from CA in something called
partition realignment.
If you are only replicating a single table, merge walks the DRI and tells
you that the related records are not going to move with this customer which
probably is not what you want.
This is a very complex topic so if you need more info, please post back.
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
"GC" <GC@.discussions.microsoft.com> wrote in message
news:FA0E0960-370C-41B6-B5E4-533DACA4FC06@.microsoft.com...
> Hi,
> I have to replication a database with a merge replication type
> In that database there are 4 tables that I dont need on the subscribers
> I use a pull subscriptions
> I try to do a publication with out those tables but i received a message
> saying that those tables have dependencies on other tables.
> What the best way to manage this situation
> Those tables are very large and there are long to copy from the Publisher
> to
> the Subscribers.
> I try using the row filtering but I think I dont understand what rows
> filtering works because after doing few test the replication was reacting
> the
> same way as with out row filtering.
> Thanks in advance !
>
>
>

Filtering with an "In" operator

Is it possible to filter with an "In" operator? "In" is not an option when
building a filter expression so I was hoping someone found a workaround. I
plan to pass a comma-seperated list of values within quotes. The report I'm
using runs each morning and I filter that report without having to re-query
the database.
Thanks, ChrisTo do an "In" just put two of the same Filter Expression next to each other.
The And/Or column will change to Or.
For example:
=Fields!State.Value Equals WA Or
=Fields!State.Value Equals CA Or
...etc...
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Chris" <chrisf@.unr.edu> wrote in message
news:u0lGTFTaEHA.972@.TK2MSFTNGP12.phx.gbl...
> Is it possible to filter with an "In" operator? "In" is not an option
when
> building a filter expression so I was hoping someone found a workaround.
I
> plan to pass a comma-seperated list of values within quotes. The report
I'm
> using runs each morning and I filter that report without having to
re-query
> the database.
> Thanks, Chris
>|||Multi-valued parameters are not supported RS 2000. Here's a related post
with a solution that might work for you:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=ONnNRtsBEHA.3064%40tk2msftngp13.phx.gbl&rnum=2&prev=/groups%3Fq%3D%2522in%2Bclause%2522%2Bgroup:microsoft.public.sqlserver.reportingsvcs%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3DONnNRtsBEHA.3064%2540tk2msftngp13.phx.gbl%26rnum%3D2
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <chrisf@.unr.edu> wrote in message
news:u0lGTFTaEHA.972@.TK2MSFTNGP12.phx.gbl...
> Is it possible to filter with an "In" operator? "In" is not an option
when
> building a filter expression so I was hoping someone found a workaround.
I
> plan to pass a comma-seperated list of values within quotes. The report
I'm
> using runs each morning and I filter that report without having to
re-query
> the database.
> Thanks, Chris
>

Filtering values in ResultSet by setting range on complex index

Hi!
I have table with complex index on 5 fields. One of them is string filed. I want to implement some sort of filtering, by setting SetRange() in my SQLCeCommand. But i need to fileter only by one string field and to get the values starting with the input string value.
I tried to use such code:
...
command.SetRange(DbRangeOptions.Prefix, new object[] {null, null, null, "Com", null}, null);
resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
....
But it doesn't work. As a result i've got an empty result set.
Usage of simple index on one field and setting the correspondent range will solve problem, but i can't have such index due to project restrictions.
Is there any way to set prefix range only by one value of complex index? If not, please, explain me how does Prefix Range works.

Thanx

If filtering has the same behavior as seeking (which I am assuming - read: not tested) then you might want to consider putting the column with the non-null value as the first in the index. This will require that you recreate the index with the new column order.

Filtering Top n on Group or Table

Hopefully someone can help.

I am trying to filter either my group or the whole table to return the top 5 lines of my data.

If I use Topn (Fieldname) = 5 in my filter for the group, it returns merely the total of the table and hides all the detail.

If I use the same filter in the table filter, only the table header is returned.

I have rather a complex formula based on parameters to calculate the field I want to show the top 5 of. e.g. If parameter equals 2 then add balance_period_1 to balance_period_2. This formula works perfectly if I remove the top5 filter. See detailed expression below

The expression is

Topn(iif(Parameters!Period.Value = "02", Fields!Year_Opening_Balance. Value+Fields!Balance_Period_01.Value +Fields!Balance_Period_02.Value,0))

The Operator is set to =

The Value is set to 5

Any ideas would be greatly appreciated.

Set the filter expression to:
=iif(Parameters!Period.Value = "02", Fields!Year_Opening_Balance. Value+Fields!Balance_Period_01.Value +Fields!Balance_Period_02.Value,0))

Set the filter operator to:
TopN

Set the filter value to:
=5

Note: if you set the filter value to just 5, it will be interpreted as string constant - which won't work. You have to set it to =5, which will evaluate to the integer value 5.

-- Robert

|||

Thanks!!

So simple but blind to my eyes, now all is revealed!

Filtering through Matrix or groups in matrix

I have a report thats fully functional. I just want to add a filter so that my "Visits" field only displays the Visits per day that are less then 6. When i try to filter out the matrix or the group, it tells me the datatypes are different . Something about int32. Its in a matrix, but i have seen this happen in a table too, so i guessing thats not the problem. I just want to be able to display the information for Sales Reps with less then 6 Visits. Any help, will be greatlly appreciated.

Filter work fine with "=" sign so what u need to do is

fileter condition < =6 (In expression use =6 instead of plain 6)

or you may try using =cint(6)

Filtering the parameter content

Hello,

I have built a report using a Cube (and not a relational database).

I have a date as a parameter and I would like to filter its content: the parameter goes from 1900 to 2090 and I would like the user to see only from 2006 to 2090.

Can you help me by giving me tricks to do it ? There may have several ways of doing it but I can't find them.

Thank you in advance !

Alexis

If you always want it to start from 2006( not change to 2007 to 2090 when it is 2008) it′s easy in MDX:

SELECT {[Time].[Year].&[2006] : NULL } on rows, bla bla bla.

This will make it take every member from the time dimension from 2006 up til the end, in your case 2090.|||

Thank you, it works perfectly !

It would be interesting to make dynamic date range according to the actual year but.... next time !

Filtering the dataset

Hi All,
I have reports which return employee information. I want the report to have
an optional 3 parameters representing Entity, Business Unit and Cost Centre.
If any of these parameters(or a combination is used) I want to filter the
returned dataset to display only relevant rows.
Before anyone suggests using the parameters in the stored procedure, forget
it! The stored procedure is complex enough that it has to figure out the
users access level...so I'd rather filter after the fact.
I've hit a brick wall with this one, so if anyone has any fantastic ideas,
Id love to hear them.
Kind Regards,
Terry PinoFiltering the dataset can be done with the filter tab in the dataset.
U can try with the filters - Expression & Value. using custom expressions.
To help U out, I need more details abt the parameters and returned columns
with which u filter the dataset.
"Terry" wrote:
> Hi All,
> I have reports which return employee information. I want the report to have
> an optional 3 parameters representing Entity, Business Unit and Cost Centre.
> If any of these parameters(or a combination is used) I want to filter the
> returned dataset to display only relevant rows.
> Before anyone suggests using the parameters in the stored procedure, forget
> it! The stored procedure is complex enough that it has to figure out the
> users access level...so I'd rather filter after the fact.
> I've hit a brick wall with this one, so if anyone has any fantastic ideas,
> Id love to hear them.
> Kind Regards,
> Terry Pino|||Thanks Chans,
I worked it out after posting this article.Basically I ended up with 3
filter values each one something like this:
iif(Parameter.value1 is null,"",Fields.value1) = iif(Parameter.value1 is
null,"",Parameter.value1)
Getting around my problem of the filter parameter being null.
Always helps to talk about things;-)
Cheers,
Terry
"Chans" wrote:
> Filtering the dataset can be done with the filter tab in the dataset.
> U can try with the filters - Expression & Value. using custom expressions.
> To help U out, I need more details abt the parameters and returned columns
> with which u filter the dataset.
>
> "Terry" wrote:
> > Hi All,
> >
> > I have reports which return employee information. I want the report to have
> > an optional 3 parameters representing Entity, Business Unit and Cost Centre.
> > If any of these parameters(or a combination is used) I want to filter the
> > returned dataset to display only relevant rows.
> > Before anyone suggests using the parameters in the stored procedure, forget
> > it! The stored procedure is complex enough that it has to figure out the
> > users access level...so I'd rather filter after the fact.
> > I've hit a brick wall with this one, so if anyone has any fantastic ideas,
> > Id love to hear them.
> >
> > Kind Regards,
> > Terry Pino|||Did you filter the report or the dataset?
"Terry" wrote:
> Thanks Chans,
> I worked it out after posting this article.Basically I ended up with 3
> filter values each one something like this:
> iif(Parameter.value1 is null,"",Fields.value1) = iif(Parameter.value1 is
> null,"",Parameter.value1)
> Getting around my problem of the filter parameter being null.
> Always helps to talk about things;-)
> Cheers,
> Terry
> "Chans" wrote:
> > Filtering the dataset can be done with the filter tab in the dataset.
> > U can try with the filters - Expression & Value. using custom expressions.
> >
> > To help U out, I need more details abt the parameters and returned columns
> > with which u filter the dataset.
> >
> >
> > "Terry" wrote:
> >
> > > Hi All,
> > >
> > > I have reports which return employee information. I want the report to have
> > > an optional 3 parameters representing Entity, Business Unit and Cost Centre.
> > > If any of these parameters(or a combination is used) I want to filter the
> > > returned dataset to display only relevant rows.
> > > Before anyone suggests using the parameters in the stored procedure, forget
> > > it! The stored procedure is complex enough that it has to figure out the
> > > users access level...so I'd rather filter after the fact.
> > > I've hit a brick wall with this one, so if anyone has any fantastic ideas,
> > > Id love to hear them.
> > >
> > > Kind Regards,
> > > Terry Pino

Filtering Table name with Parameter

<br><br>I obtain table names from a database and pass them to a dropdownlist. Based on user selection, I want to pass each table name to a query.Here is an extract from my code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="select * from @.dDTable"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="dDTable" PropertyName="SelectedValue" DefaultValue="product" Direction="InputOutput" Size="15" Type="String" /> </SelectParameters> </asp:SqlDataSource>I keep getting this error: Must declare the table variable "@.dDTable".Please does anyone knows the best way to go about this?

Hi,

thats not working. Dynamic table name are allowed in here. You could use a stored procedure instead which then uses dynamic sql to execute the query that was first concatenated (with the table name).

But you should be aware of the curse of dynamic SQL :-)

http://www.sommarskog.se/dynamic_sql.html

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks.

Filtering structure across 2 dimensions

I would appreciate any help with the following problem:

Other than cube redesign, which I know we can accomplish, if theres an actual way through MDX to filter structure between 2 dimensions. As of right now I can only accomplish filtering measures by 2 dimensions. Example:

SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )

What occurs is all members from Dimension1 is returned and the Measures are filtered accordingly. What I want is it to only bring back the rows which have a relationship with Dimension2. Is this possible?

But if I try to filter structure across 2 different heirarchies in the same dimension it works fine.

SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[Heirarchy2].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension1].[Heirarchy1].[AnyHeirarchyLevel].[AnyMember] )

This only returns rows in which the two heirarchy members are related.

So obvisouly If I moved my 2nd Dimension under the 1st Dimesion and make it a heirachy it would work, but any other way around this?

Thanks a lot.

When you say: "only bring back the rows which have a relationship with Dimension2", are they only related via the fact table, or in some other way? In the former case, try Non Empty, like:

SELECT { [Measures].Members } ON COLUMNS,
Non Empty [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )

|||

Thanks for the reply.

Yes, they are only related via the fact table. If I try non empty, and please correct me if im wrong, that will filter by measures (sales, units, etc), and only show members that have sales/units. In essence exluding the real relationships (members that have no sales won't be shown)

I would like to bring back every member that is related to Dimension 2's member, regardless of no sales or not.

Thanks.

|||

The fact of sales is a relation between dimension1 and dimension2. There isn't another relation between dimension1 and dimension2.

If you have another one the it should exists as another fact table or anything else.

Filtering SqlDataSource to show all vs. non-null records

Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (?) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance.

Bill

Construct a better SELECT that uses a parameter.

SELECT ...

FROM ...

WHERE (@.ShowAll=1)

OR (@.ShowAll=0 AND (col1 IS NOT NULL OR col2 IS NOT NULL OR col3 IS NOT NULL))

|||Sweet. Thank you much.

Filtering SQL statement with datepart

Hi!

Im making a page where I want to display this weeks information & news as a default setting.

What do I have to do to make it work (Im new to .Net but used to ASP 3 & MySql)?

My idea is to store the week number in a variable which is updated att Page_load and then
to use this variable in the following sql statement that gets news and info from the MsSql
database where weeknumber is = the defined variable.

So how do I store weeknumber in a variable?
AND
How do I use this variable in the SQL statement?

Weeknumber can be store in a Session variable.

There is a function in SqlServer called DateDiff() with many options, you can get the number of week between 2 date. than compare this nubmer with the Session variable.

Hope this help

Filtering SQL statement by server variable

Hi

Hope somebody can help me with a problem that i am having. I have the following piece of code

<%

Dim sqlConnAs SqlConnectionDim sqlCmdAs SqlCommandDim sqlReaderAs SqlDataReader

sqlConn =

New SqlConnection("server=myserver;database=aspnet;uid=username;pwd=password")

sqlCmd =

New SqlCommand("SELECT SamAccount, FirstName, Surname, FirstName + N' ' + Surname+' Logged In ' AS UserLoggedIn FROM tblUsers WHERE (SamAccount = " + Request.ServerVariables("logon_user") +")", sqlConn)

sqlConn.Open()

sqlReader = sqlCmd.ExecuteReader()

%>

<%

Dim strPCNAmeAsString

sqlReader.Read()

strPCNAme = sqlReader(

"UserLoggedIn")

Response.Write(strPCNAme)

%>

The bit i have i have underlined and made bold throws up the error. Basically what i am trying to do is to filter the the SQL statement by the server variable logon user. Everthing works fine until the point that i put the where clase in.

Can anybody help please?

Thanks

Marcus

put your loginname in single quotes for query maybe it will help :

New SqlCommand("SELECT SamAccount, FirstName, Surname, FirstName + N' ' + Surname+' Logged In ' AS UserLoggedIn FROM tblUsers WHERE (SamAccount ='" + Request.ServerVariables("logon_user") +"')", sqlConn)

|||Thanks for that managed to get it working now

Filtering Single Recordset

Hello, I am a noob, so here goes. I would like to filter a resultset
returned from a stored procedure into 2 tables (e.g., summary section and
detail section). I would like to avoid 2 hits to the dB, so am returning a
summary and detailed resultset in 1 recordset. What I would like to do is
filter recordset 1 (say RectType = 1) into a summary table and filter
recordset 2 (say RectType = 2) into a detailed table. This has to be a
simple solution, but I must be missing something. Here's what I have tried
so far:
1) Adding filter on Details Grouping in the table - Nothing happens.
2) Adding filter in table Properties - Receive error: "An error has occurred
druing report processing. The processing of filter expression for table
'Summary' cannot be performed. The comparison failed. Please check the data
type returned by filter expression."
TIAI would think that you would need to do the filter on the table...but
that you are comparing two different data types. Try a convert to char
or a convert to int so that your data from the recordset and the data
in the filter are of the same type.|||OMG - I didn't even think of converting to char and that worked!!! Thanks a
bunch dude!
"Luke" wrote:
> I would think that you would need to do the filter on the table...but
> that you are comparing two different data types. Try a convert to char
> or a convert to int so that your data from the recordset and the data
> in the filter are of the same type.
>

filtering rows that already exist in the destination table

Hi All,

What is the most straighforward way of not importing rows that already exist in the destination table (as determined by a primary key value)? Thanks.

Regards,

Daniel

Method 2 here: http://www.sqlis.com/default.aspx?311

-Jamie

|||Excellent, thanks.

Filtering results after execution and multivalue parameter questions

Hi. 2 questions:

1.
I'm trying to execute a report, then allow the user filter the results using a value list from the report dataset in a dropdown list. When I create a filtering parameter, i can't figure out how to make it work on results after the report is executed.

2.
How can I make a multivalue parameter default to (Select All)?

thanks!
#1: You can simulate the "user-driven filtering" by using drillthrough actions to the report itself and setting the filter parameter accordingly. In any case, it will require the report to be reprocessed because filtering data may impact many other things (such as pagination).

#2: Make the valid values list and the default value both query-based and use
the same dataset column.

-- Robert