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.
I have two reports. I need to provide a service that when the user click one hyperlink (or button), then the program can export these two reports to two files as PDF and let the user save it one by one. Is it possible?
I was wondering if it would be possible to put report data in a hyperlink. For instance I have a report that pulls up a UPS tracking number and I want to be able to put it in the hyperlink to check the status from the UPS website.
I.E. http://www.upstracking.com/myhsk?<sql-data>jkhaskd
I need to be able to to extract the data that is returned by clicking a hyperlink. I click the link and it displays the data.
I know that SSIS has a web services task, and that appears to be a neat feature, but the link I am connecting to does not have a WSDL file or anything like that. So I think I cannot use this task.
Does anyone have a suggestion about how I might do this?
i am using a view grid to display data the viewgrid is tied to an sqldatasource control i want to give the user the ability of modifing data by colomn instead of by row how could i do ?
How can i confirm that the data entered within the standard controls and after the click event for the particular sql query the data is been stored in the database tables (depending insert,update,delete).I want to display the different messages on the click event and that after the changes made into the tables.How can i do it ?? I m using ASP.Net with C#,VB and the sql server to store data.I also want to know where are the database tables exactly get stored which are made in the Microsoft SQL Server Management Studio Express.I want to use as the existing item in .Net but could not find.. Thanxs...
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?
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)?
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.
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).
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?
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?
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
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?
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 = ' ')
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.
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)
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....
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.
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
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)?
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.
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
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')
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
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?
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.
I have a package that i am building right now and I need to filter out data from my employeeid field that is not an integer. How would i proceed with this? I currently have a conditional split filtering our employee id's that contain a dash.Â
Hi, I have some data coming through pipeline and I wanna add some component at some point to pass on only selected rows based on conditions to the objects onwards. My opinion is I should use conditional split object, but Please suggest me something if you know better.
