How Do I Filter All The Data Not In The List?

Apr 28, 2008

Hi

I am using below code to fetch the data hit our webserver from different website or engine.

ex: If I pass the parameter Ref as google and the dates, will fetch the data (hits) from google.

I have created a table (PoList) to have names like google,amazon,aol upto 14 names.

My Question : How do I filter all (hits) the data which the name (Ref) is NOT in the PoList table.

Thanks in advance

CREATE PROCEDURE sp_Portals

@Ref as nvarchar(255),
@SDate as Decimal(18,0),
@EDate as Decimal(18,0)


AS

SELECT COUNT(b.Pcode) AS totclicks,
case when b.Pcode = 'NPF' then b.Pcode + '-- No Products Found'

else a.prodmodel
end as prodmodel,
SUM(c.orderTot) AS [Sales Total],
COUNT(c.cusID) AS NumSales,

b.Pcode as Code

FROM CusDetails c RIGHT OUTER JOIN
View1 b LEFT OUTER JOIN
Products a ON COALESCE (b.ProdID, 0) = a.ID ON c.sessID = b.SessID
WHERE (b.Referer LIKE '%' + @Ref+ '%') AND (b.theDate >= @SDate) AND (b.theDate <= @EDate)
GROUP BY b.Pcode, a.prodmodel
ORDER BY totclicks DESC
GO

View 2 Replies


ADVERTISEMENT

Power Pivot :: Filter Sharepoint List Data In It?

Mar 20, 2015

I am trying to import the data from SharePoint into my PowerPivot window as a Datafeed. I am able to successfully import the whole data from SharePoint list. But, now i would like to apply a filter (Where Clause) before importing the data.

View 2 Replies View Related

Where Condition Filter List Of Values

Mar 24, 2015

SELECT
c.CustomerId,
c.CustomerName,
co.OrderKey,
co.OrderNumber,
co.CustomerPO,
co.DueDate,

[code]....

sb.CarrierId data is Sec.

Carriers.ExcludeCarriers I have value as 'Sec,QB' list of values.

I am trying to display sb.Carrierid that is not equal to Carriers.excludeCarriers list of values. It fails now and displays sec values in resultset.

View 1 Replies View Related

SQL Server 2008 :: Filter List Of Employees Where Sum Of VALIDATED Hours Is Less Than 80?

Mar 12, 2015

How do I filter a list of Employees where the Sum of "VALIDATED" hours is less than 80? For example.

Here is the flat table

SELECT EMP_NO, hours, IsValidated, rate_type
FROM Pay_Records
WHERE pay_period_id = 2
Order by EMP_NO

Output will be something like this

12345 | 2 |true |REG
12345 | 15 |false |OVR
12345 | 30 |true |OVER
33334 |2| true |REG

Total Validated hours for the Employee 12345 will be 32 NOT 47. How do I list employees who worked less than 80 validated hours. The hours are validated only when it is true.

View 2 Replies View Related

Report Model Not Allowing Users To Filter Using A Pick List

Jan 9, 2008

I have a MSSQL Report Services Report Model set up to allow users to create their own ad hoc reports. The data source for my model is a Named Query that queries a MS SQL view that actually pulls data from a series of tables & other views. When you create a report from this model and attempt to filter the data some of the fileds will provide you with a pick list to select which values you would like to filter on and other fields do not provide you with a pick list but require you to enter the data directly that you wish to filter on. Most of the data fields that I am trying to filter on are varchar fields and like I said some will create a pick list for you to selet from and others will not. This all seemed to start after I changed my data source to a Named Query rather than having my data source as the MS SQL View directly. I did this becuase it seemed that anytime I had to make a change to the views that the data source pulled from it would mess up any existing reports that had been created and this does not happen if I use a named query. I have gone crazy trying to figure this one out so any ideas would be greatly appreciated!

View 1 Replies View Related

FREETEXT FILTER ON FREETEXTTABLE USING COLUMN LIST FOR REFINED SEARCH

Sep 12, 2007

I am trying to do a freetext filter with mutiple columns using a column list, but I can't get the syntax down for multiple column list. First, am I am going about this the right way...Do I need to be doing both? Second why doesn't mutiple columns work. I can't find any good samples online. What I am trying to accomplish is a refined search stored procedure that uses the freetext to do the search refinement. Any help would be appreciated.

select

b.rank,

a.ProductID,

a.ProductName,

a.Sequence,

a.ProductImage,

a.ItemID,

a.ItemName,

a.ManufacturerItemCode,

a.ItemImage,

a.ItemSourceID,

a.PackageID,

a.BrandID,

a.BrandName,

a.ManufacturerID,

a.ManufacturerName,

a.ProductCategoryID,

a.CategoryID,

a.CategoryName,

d.CustomerGroupName,

isnull(h.PackageDescription,a.ItemPKG) as PKG,

case g.StockStatus

when 1 then 'Yes'

when 0 then 'No'

else ''

end as StockStatus,

isnull(g.StandardUnitPrice,a.ListPrice) as Price,

isnull(j.SupplierAbbreviation,a.ManufacturerAbbreviation) as ItemSource

from

dbo.vw_mcProductItem a

inner join freetexttable(dbo.vw_mcProductItem, (ProductName,ItemName,ManufacturerItemCode,ItemPKG,BrandName,ManufacturerName,ManufacturerAbbreviation,CategoryName), @SearchWord) as b ON a.ItemID = b.[KEY]

inner join [dbo].[mcCustomerGroupItem] c on c.ItemID = a.ItemID

inner join [dbo].[mcCustomerGroup] d on d.CustomerGroupID = c.CustomerGroupID

inner join [dbo].[mcCustomerGroupCustomer] e on e.CustomerGroupID = d.CustomerGroupID

inner join [dbo].[mcCustomerUser] f on f.CustomerID = e.CustomerID

left outer join [dbo].[mcSupplierItem] g on g.ItemID = a.ItemID

left outer join [dbo].[mcPackage] h on h.PackageID = g.SellingPackageID

left outer join [dbo].[mcItemSource] i on i.ItemSourceId = a.ItemSourceId

left outer join [dbo].[mcSupplier] j on j.SupplierID = g.SupplierID

where

d.CustomerGroupID = @CustomerGroupID

and f.UserID = @UserID

and FREETEXT(BrandName,ManufacturerName,CategoryName, @SearchWord)

View 1 Replies View Related

SQL Server 2012 :: List Of Order Numbers Based On Stock Availability - Filter Results?

Dec 23, 2014

Trying to build a list of order numbers based on stock availability.

The data looks something like this:

OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10

In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.

View 1 Replies View Related

Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.

Feb 12, 2008

Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.

View 1 Replies View Related

Pre-filter Data In SQL

Nov 14, 2006

I need to query the Employees table in the ABC database to find all employees who report to EmployeeID 5 (indicated by a value of 5 in the €˜ReportsTo€™ column) and who have the word €˜Sales€™ in their job title (i.e the Title column). In my database the Employees table is very large so you want to pre-filter the data before doing more complicated processing on it. How would I use the following statement to to pre-filter the data?

SELECT * FROM EMPLOYEES WHERE ReportsTo = 5

View 1 Replies View Related

Automatic Select Filter (something Like Global Table Filter)

Apr 15, 2008

Hello,

Here is my problem:


I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)

Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;

All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.

However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.

Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.

However, I don't know what is the easiest and fastest way to filter the tables. Example:

I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;

Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".

I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.

I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?


Thanks in advance.

Best regards,
Beroetz

View 5 Replies View Related

Best To Filter Data For A Report?

Jan 22, 2008

Hi All,

I have an SQL2005 db where I have some data I want to retrieve for a report. There are a couple of related tables holding some secondary info linked by foreign keys and one large table holding the bulk of the data. On my site I want to have a page where people can select filter options such as to display items that were entered before a date, after a date, between two dates, objects that are marked as complete in the table, etc.... Essentially various optional filters.

What I'm wondering is what's the best way to handle building the query to retrieve this data for the next page to display the report? I can think of a couple of ways but would essentially result in a lot of messy combining of strings to create the SQL query either in the C# code or in the stored procedure itself. I'm guessing, and hopping, there might be a cleaner method for this as it's a rather common thing I would think.

Thanks :)

View 1 Replies View Related

Filter Data By Row Number

Jan 30, 2007

SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] <= 89) AS a
WHERE RowNum <= 4
UNION
SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] >= 90) AS a
WHERE RowNum <= 2

This query returns values above 90 (I need 2 of them) or values between 80 and 89 (data is already filtered for only greater >=80) and I need 4 values above 80. I only need either 2 above 90 or 4 above 80, not both, and this query returns 2 above 90, but also the values between 80 and 89. If there are already 2 above 90, I do not want any values between 80 and 89. If there are 4 above 80, I do not need any additional values. If the are two above 80 and 1 above 90, I will take all of them (max I will ever take is 4).

View 1 Replies View Related

Using A Loop To Filter Data?

Jan 31, 2007

Current Code

SELECT [Patient Identifier], Date, [Operator Index], Time
FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
ISNULL(t9.Date, t8.Date)
ORDER BY c.Time) AS RowNum
FROM (SELECT [Patient Identifier], Date, 2 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 90
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 2) AS t9 FULL JOIN
(SELECT [Patient Identifier], Date, 4 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 80
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
Complete AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)) AS d
WHERE d .RowNum <= d .[Rows]

Current Input
Patient IDDATE Time Operator Index
5170000318OCT2006 11:48 91
5170000318OCT200611:50 100
5170000417OCT200611:41 89
5170000417OCT200611:50 93
5170000417OCT200611:52 91
5170000417OCT200612:00 93

Current Output

Patient IDDATE Time Operator Index
0517_0000318OCT200611:48 91
0517_0000318OCT200611:50 100
0517_0000417OCT200611:41 89
0517_0000417OCT200611:50 93

It should be
Patient IDDATE Time Operator Index
5170000318OCT2006 11:48 91
5170000318OCT200611:50 100
5170000417OCT200611:50 93
5170000417OCT200611:52 91

The data is organized by patient id, date, time (ascending)
For a given patient id, on a certain data, testing was performed. A value between 80 and 100 is acceptable data. I need either the first 2 tests with a score above 90 or the first 4 tests above 80. (The tests are further sorted by time because the testing is time dependant. On some occassions, there is just too much data. What is wrong with my current query?

View 1 Replies View Related

[RESOLVED] Trying To Filter Some Data On SQL

Apr 14, 2008

Here is a simple grid view page I made.

http://visualboxscore.com/boxscores/cfb_box_scores.aspx

Ultimately I want to be able to filter every field that is listed. However, for now I am just learning this stuff and would like to just filter the by the name in the "offense" field.

However, it is not binding the selection thus not filtering it out. Do you see any problems in my code?

<%@ Page Language="VB" %>
<html>
<head id="Head1" runat="server">
<title>GridView DetailsView Master-Details (2 Page)</title>
</head>
<body>
<form id="form1" runat="server">
<b>Choose a team:</b>
<asp:DropDownList ID="DropDownList1" DataSourceID="SqlDataSource2" AutoPostBack="true"
DataTextField="offense" Runat="server" />
<asp:SqlDataSource ID="SqlDataSource2" Runat="server" SelectCommand="SELECT DISTINCT [offense] FROM [cfb_boxscores]"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" />
<br />
<br />
<asp:GridView ID="GridView1" AllowSorting="True" AllowPaging="false" Runat="server"
DataSourceID="SqlDataSource1" DataKeyNames="offense"
AutoGenerateColumns="False">
<Columns>
<asp:BoundField HeaderText="Date" DataField="date" DataFormatString="{0:MM/dd/yy}" ItemStyle-HorizontalAlign="Center" SortExpression="date" />
<asp:BoundField HeaderText="Offense" DataField="offense" ItemStyle-HorizontalAlign="Center" SortExpression="offense" />
<asp:BoundField HeaderText="Defense" DataField="defense" ItemStyle-HorizontalAlign="Center" SortExpression="defense" />
<asp:BoundField HeaderText="Rush Yards" DataField="rush_net" ItemStyle-HorizontalAlign="Center" SortExpression="rush_net" />
<asp:BoundField HeaderText="YPC" DataField="yards_per_carry" DataFormatString="{0:#.##}" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_carry" />
<asp:BoundField HeaderText="Pass Yards" DataField="pass_yards" ItemStyle-HorizontalAlign="Center" SortExpression="pass_yards" />
<asp:BoundField HeaderText="YPA" DataField="yards_per_att" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_att" />
<asp:BoundField HeaderText="Total Yards" DataField="total_yards" ItemStyle-HorizontalAlign="Center" SortExpression="total_yards" />
<asp:BoundField HeaderText="YPP" DataField="yards_per_play" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_play" />
<asp:BoundField HeaderText="Points" DataField="points" ItemStyle-HorizontalAlign="Center" SortExpression="points" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server" SelectCommand="SELECT [date], [offense], [defense], [rush_net], [yards_per_carry], [pass_yards], [yards_per_att], [total_yards], [yards_per_play], [points] FROM [cfb_boxscores]"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>">
<SelectParameters>
<asp:ControlParameter Name="Offense" ControlID="DropDownList1" />
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>

overall I want to format the table look and and background but for now I just want to see how this stuff functions.

Thanks!!

View 3 Replies View Related

How To Filter Data From Publisher

Jun 13, 2008

I have a master database at headquarters which will be the publisher. I have 5 databases at client sites that have the same database schemas as the master, which will be the subscribers. Each table in the database contains a Customer_No column, which is set as a default to 1 at client 1, 2 at client 2, 3 at client 3, etc. The master database contains data for all customers.

How do I get Merge Replication to only replicate the data from the publisher based on the customer_no column in each table?

View 1 Replies View Related

Filter Data On Specified Dates

Apr 28, 2015

i want to filter the data on specified dates. (between dates).

SELECT a.UserID, b.Name, b.EmployeeNum,b.Department, MIN(a.TransactionTime) AS TransactionTime,a.TransactionTime as TR_Date,
min(CONVERT(CHAR(20), TransactionTime, 113)) As TransactionDateTime
FROM NitgenAccessManager.dbo.NGAC_AUTHLOG AS a INNER JOIN NitgenAccessManager.dbo.NGAC_USERINFO AS b
ON a.UserID = b.ID
WHERE (a.FunctionKey = ' ')

[code]...

View 3 Replies View Related

Filter Data By Hours

May 22, 2008



Hello!

I need to create a report that shows daily events per hours, like this:

15.5. Example
00:00 8
01:00 9
02:00 34

Is this possible? The data should be filtered by the datetime. If the datetime is for example 15.5.2008 00:45, it goes to the first row of that example table I created.

Edit. I am using Sql server 2005.

Thanks,

Aleksi Rytkönen

View 4 Replies View Related

Filter Cube Data

Dec 28, 2006

Hi

i am using pivot table 11 to display the data from SSAS Cube. the problem is

1- pivot table fetches all the data and display it according to the design. but what i need is

a- the user could only see the data which was posted by himself.

b. He must be unaware of the data posted by other users.



in database table there is a field named userId which is also which is part of dimenssion in myCube.

i dont want to use filteraxes for this purpose actually i want the user to be unaware of other users of system also

any single helping word will be appriciated



Regards

Salman Bashir

View 5 Replies View Related

And/or Filter Field Not Enabled In The Group Filter Tab

Jan 26, 2006

Howdy,

I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.

PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?

The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)

View 14 Replies View Related

How To Query Data By Various Amount Of Filter Value ????

Jul 2, 2007

Generally, on any screen, we design filter screen by allowing user to identify range or one value to search.
But sometime in some screen, It will be more convenient for user if user can identify No matter how value to search.
 
For example
On screen which have information of people in any province.
So user would like to search it by identify no matter how value to search.
There are check box of any province on filter part which enable users choose it.
 
Hence, if sometime user choose (by clicking on checkbox) 3 provinces : LA, Michigan, WachingtonDC  to see description only 3 chosen province.
and sometime user choose (by clicking on checkbox) 2 provinces : LA, Michigan to see description only 2 chosen provinces.
 
Please give me any idea for create Stored Procedure or any tecnique to complete my idea....
 
 
Help me Pleaseeeee

 
 
 

View 1 Replies View Related

Filter Data And Update GridView

May 11, 2008

I have a GridView that gets data from an SqlDataSource. It works fine, but now when I want to filter the results and add more parameters to my sql query, nothing happens. My initial select statement in the SqlDataSource contains only 2 parameters, so now when a user clicks a Button, onclick I call a function that is supposed to change the select statement of the SqlDataSource, add 2 more parameters taken from 2 controls, and then update the GridView. This is what I use: dataSource.SelectCommand = "new select command here";  dataSource.SelectParameters.Add("State", txtState.Text); dataSource.SelectParameters.Add("City", txtCity.Text);  grid.DataBind(); It compiles, but when I click the button to filter the results, the GridView always looks the same as if nothing happened. I do not know if this is the right way to do it, or am I on the wrong track? Help is appreciated. Thank you in advance.

View 2 Replies View Related

Query To Filter Alphanumeric Data?

Dec 4, 2011

I have alphanumeric data in a Table Assets. The column name is Milepost.

Column Values are like below

MilePost

1.24
1.61
4.56
4.78
5.45
6.91
7.19

[code]....

Now I want to select records between 1 to LR 4.41 which should return all records between 1 to LR 4.41

My below query is not returning proper values. So i need a correct sql query.

Select Milepost from Assets where Milepost between '1' and 'LR 4.41'

View 4 Replies View Related

Filter Month End From Daily Data

Jul 8, 2013

I have a series of daily data and I am looking to filter for month end business days and create a sum.

This piece of code gets what I need, arbitrarily, for the 7th day of every month. How can I amend the code so that rather than 7, I have the last business day of every month?

select FROM_DATE, PORTFOLIO, SUM(VALUES) from MyTable
where DATEPART(day,FROM_DATE) = 7
group by FROM_DATE, PORTFOLIO

I have the following code for last business day of month (from a previous question raised on these forums), but when I set DATEPART(day,FROM_DATE) equal to the following piece of code, I get no results.

CASE DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1))%7
WHEN 5 THEN DATEADD(dd, -1, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Saturday subtract one day
WHEN 6 THEN DATEADD(dd, -2, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Sunday subtract two days
ELSE DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)
END

View 15 Replies View Related

Filter Data Back To Publisher

Mar 1, 2007

Hi all,

We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?

Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?

Thanks,

Iain



View 3 Replies View Related

Data Filter With Report Model

Sep 24, 2007



I am trying to applying an data filter that will filter out user based on userid

Three tables:

tblPerson (fact table): contains over 1 million records with about 20 fields. One of the fields is called BureauID (int). BureauID indicates what Bureau the person works in for an given record.

tblBureau (attribute table) which has a FK relationship to tblPerson) contains the following fields:

BureauID int - this is the linked field to tbl person
Bureau Code varchar
Bureau ShortName varchar

The reason we use tblBureau as in attribute table instead of place the bureau code and short name directly into table Person is so that we index tbl person quickly with "ints".

tblDataAccess, which has two fields loginID with bureau Code. The bureau code in this table tells me what bureau that userid has access to.

How do i get the report model to filter based on this userid????. I know i have to add some data filters but i am not sure where.

I put an filter on DataAccess so that login = getuserid().

But what do i do to table person or table bureau, so that a person can only see the people in there bureau.

Thanks for you help

Ryan Swann

View 5 Replies View Related

Analysis :: Filter Data For Two Hierarchies

Sep 23, 2015

I have an existing MDX query returning the correct resultset. However, I want to add a filter so that for a combination of value in Hierarchy1 and Hierarchy2, the data is filtered out.

For example I have data like

H1    H2   Amount
1      1      100
2      1      50
3      1     45

I am getting a value of 100+50+45=195.

I want to filter the data for the combination H1=3 and H2=1. Expected result would be 100...

H1    H2   Amount
1      1      100
2      1      50

View 2 Replies View Related

On Click Of Hyperlink Filter Data

Apr 24, 2008



Hello,

I have a report which has 2 tables.
The first table shows the current summary data as below.


ServerName Date ProjectStatus

Srv1 01/10 Green
Srv2 01/10 Red
Srv 3 01/10 Green

and the second shows the detailed history per server,

eg for Srv1

Table 2
ServerName Date ProjectStatus

Srv1 01/10 Green
Srv1 01/09 Green
Srv1 01/08 Red
Srv1 01/07 Red
Srv1 01/16 Red

I have a link on Srv1 column, on click of which i want to filter and show the data only for Srv1.
If user clicks Srv2 data in table 2 should be refreshed with values only for Srv2.

How to do this ?

Note: I have done this using subreports but I dont want to use sub reports for when it comes to using them in web aspx page it has its own issues.

I want a single report to have this functionality.

I am not able to set the Report Parameter on click of link.

Thanks
Prasad








View 11 Replies View Related

Filter Data By Date Period

Jan 25, 2008

is there a way to retrieve datetime values?

I want to create a query with to parameters:
@DateFrom and @DateTo.

and want sql to retrieve for me all the rows that it's date value is between these dates.

View 1 Replies View Related

Query To Filter Duplicate Data From Table.

Apr 8, 2004

i am having 3000 records in table. now i want take out the duplicate from that table.

for example: i want to find out the duplicates of 'CompanyNames'.

help needed to write query for this operation.

View 2 Replies View Related

SQL 2012 :: Query To Filter Out Specific Data?

Aug 21, 2015

I need to return 1 ip address for each machine. I can easily achieve this with using group by and return max(ipaddress) however I would like to filter out specific range '192.168.%' but only want to do that if machine has more then 1 ip address. If the machine has only 1 ip address then it can return it even if its '192.168.%'

below is test table to show sample data

CREATE TABLE dbo.[testip](
[machineid] INT NULL,
[ipaddress] [varchar](20) NULL
)
GO
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (1,'155.119.1.22')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (1,'192.168.1.5')

[code]....

View 4 Replies View Related

Filter Data From Multiple Tables Crashes App!

Jul 20, 2005

i didnt think my sql qeury was that complicated that it would crash my webapp. all im trying to do is filter data between two tables. heres my query<cfquery name="GetResults" datasource="#datasource#">SELECT *FROM Content, Content_SitesWHERE Content.ContentID <> Content_Sites.ContentIDORDER BY Content.ContentID DESC</cfquery>equals works, but when i try not equals, it all goes haywire. any ideas?TIA

View 3 Replies View Related

Report Builder - Data Model Filter

Oct 18, 2007

I have built a data model in visual studio that I am using with report builder in reporting services. The data model is using data from a view in SQL Server 2005. I am trying to apply a filter to the data model to restrict the data available in report builder. I know that I could change the code for the view and filter that data that way, but I would rather creates two models each one producing differnt sets of filtered data. I have tried adding a filter to the data model, but it does not seem to work. Do anyone have any suggestions?

View 3 Replies View Related

How To Filter Out Records With A Condition In The Data Flow?

Apr 3, 2008



Hi, all experts here,

I am desperate to need you help.

Could you please give me any advices on how to filter out the records through out the data flow by any particular condition? E.g. In my case, I want to filter out rows with null id (will get rid of those rows with null id which are not matched in the look up component)? Hope it is clear for your help and I am looking forward to hearing from you for your help and thank you very much.

With kindest regards,
Yours sincerely,

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved