Default Value For All Records In A Parametered Filter
Feb 28, 2007
I have a parametered filter in my sql query: Warehouse_Class_Code like '%' + @Filter + '%'
In the parameter I have several value to set the right filter. When I run the report I always have to select one of the values. I added a label "All records" with a value "&". I tried "%" as a default value at the parameter settings.
In the preview the label "All records" is shown, but when I run the report in the HTML browser, I see <Select a value>. How to change the default value to "All records"?
View 2 Replies
ADVERTISEMENT
Jan 9, 2007
I've made a parameter for sorting data in a report.
The expression for the table sorting is: =Fields(Parameters!Parametername.Value).Value
The parameter has two labels (and values):
1. Item (Description)
2. Item number (Item_No)
The description is a string, but the item number is a integer. In de parameter properties I can set one value for string or integer.
When I use a "locked" sorting I can use the CInt function: =CInt(Fields!Item_No.Value) but when I use a parameter this doesn't work.
Please help!
View 1 Replies
View Related
Nov 12, 2015
how can I have a default filters in my Excel PowerPivot Table, without the user need to select them in Excel creating with DAX Language.
View 8 Replies
View Related
Oct 25, 2012
I created a tabular model, and used it as a source for the power view. Is there any possibility to set a dynamic default value for the filter. My model has a field named 'CalendarMonth', by default - filter should take current month. I would like to know is there any possibility with out changing the data in the source.
View 4 Replies
View Related
Jan 13, 2006
Dear all,
My question about SQL statement:
Table1 - Fields: T1PK, T1Field
Records:
1, ABC
2, DEF
3, GHI
Table2 - Fields: T2PK, FK (join T1PK), T2Field
Records:
101, 1, XXX
102, 1, YYY
103, 1, XXX
104, 2, XXX
I want to write a SQL statement that join Table1 and Table2 together, T2Field equal to XXX, and list all records from Table1.
That's the result columns: T1PK, T1Field, T2PK, T2Field
Result records:
1, ABC, 101, XXX
1, ABC, 103, XXX
2, DEF, 104, XXX
3, GHI, NULL, NULL
Is it possible? Please help!!
Joey
View 2 Replies
View Related
Jan 16, 2006
I have an SQLDataSource that I would like to filter out some records that are stored in an ObjectDataSource. Is this possible? The data that is filling the ObjectDataSource is being populated by a WebService.
SQL in SQLDataSource----------------------------
SELECT id, accountFROM contactWHERE id NOT IN (SELECT id FROM ObjectDataSource.Records...)
Thanks.
View 1 Replies
View Related
Mar 6, 2014
I have a table that has multiple records as illustrated in the simple list below. The real list is much longer.
MachineA 1/1/2008
MachineA 1/3/2008
MachineB 1/7/2008
MachineB 1/8/2009
MachineB 5/5/2010
MachineA 5/7/2011
MachineA 4/2/2013
I need to query to return a result for each unique machine with the latest date. The example result below would be returned because they have the latest date.
MachineA 5/7/2011
MachineB 5/5/2010
Select Distinct would almost do it, but I need each unique machine that has the latest date.
View 9 Replies
View Related
Apr 13, 2007
Hi All,I have a table with a column DeletedDate which stores a logical deleteof a record.I need to set up transactional replication for reporting purposes thatthis deleted records should not be replicated to the subscriber. Thatis, if i see a value on the DeletedDate, I don't want that record tobe picked up for replication.At the same time, when someone marks the record for deletion (byputting a date on the DeleteDate column), I want that record to bedeleted on the subscriber database. (I can also set up a job to do thedeletes on the subscriber but i'd rather let the replication take careof it).Can this scenario be implemented in Microsoft SQL 2000? I wouldappreciate any ideas / thoughts in this matter.Thanks in advance,Aravin Rajendra.
View 3 Replies
View Related
Mar 11, 2014
I would like to filter records with in effective date and expiration date; If there is no record within that range, then check for grace period records ( effective date -30 days and expiration date + 90 days)
Below is the detailed script for sample data...
declare @tab table ( sno int identity, name varchar(100), EFFECTIVE_DATE date, EXPIRATION_DATE date)
insert into @tab (name, EFFECTIVE_DATE , EXPIRATION_DATE )
SELECT 'chandu', GETDATE(), NULL union all
SELECT 'chandu', '2014-02-11 00:00:00' , '2014-03-20 00:00:00' union all
SELECT 'AAA', '2014-01-11 00:00:00' , '2014-05-11 00:00:00' union all
[Code] ...
Output:
/*
snonameEFFECTIVE_DATEEXPIRATION_DATE
1chandu2014-03-11NULL
2chandu2014-02-112014-03-20
3AAA2014-01-112014-05-11
4CCC2014-04-09NULL
8DDD2014-03-102014-04-11
*/
Expected output:
snonameEFFECTIVE_DATEEXPIRATION_DATE
1chandu2014-03-11NULL
3AAA2014-01-112014-05-11
4CCC2014-04-09NULL
8DDD2014-03-102014-04-11
Looking for query WITH OUT using GROUP BY clause
View 10 Replies
View Related
Jan 25, 2007
Hi,
I have a merge (SQL 2005 Standard -> Express) topolgoy which is having problems
The main problem is that the join filters don't seem to work for one area and I am hoping someone can help me with some troubleshooting advice
There are 140+ tables in the topology but the ones causing particular pain are a parent child relationship where the child is actually a bridge/linking table to another table.
Therefore although it is a parent child in the replication filters it is the reverse. i.e. the child has the paramterised filter on it and the parent is one level down joined by it's id. There are other tables joined to this parent table but it stays at three levels deep. The @join_unique_key therefore is set to 0 as is the partition options for the parent /child relationship.
However, when we synchronise we have a problem. The rows get inserted in to the database in RI order but only the child records are replicated down to the subscriber.
The child table with the parameterised filter has 13 articles joined to it in total and one of the other branches of join filters go down as deep as four levels. Most though do not.
Does anyone have any suggestions as to why this might be happening? Any help would be greatly appreciated.
Cheers, James
P.S. I should add this problem only occurs when the edits are made at the publisher. If new records are added at the subscriber everything is fine.
View 9 Replies
View Related
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
Mar 28, 2007
I'm using an objectDataSource connected to a strongly typed dataset to populate a GridView. I want to be able to show all the records, or let the user to select only those records that expire in a certain month. The expire field is of type date I'm used to all records being returned when a parameter is missing. If I have Select * from table where last=@last, only the records where the last name is 'Smith' will be returned if @last = 'Smith', but all records are returned is @last = "". But that's not how it's working with the date. I'm passing an integer from 1 to 12 in a querystring. I have the equivalent of select * from table where (MONTH([AD ENDS]) = @month)MONTH(datefield) always returns an integer from 1 to 12. If @month is empty, I want all the records to be displayed, but nothing is. If @month is an int form 1 to 12, it works fine. How can I get all the records if no month is selected? Can I have two objectdatasources and programmatically select which one populates the gridview depending on if I want to filter the data or not? Diane
View 5 Replies
View Related
Mar 6, 2008
I am trying to filter the records in an Access2003 project form using data from a control in a different form. I define the variable in form1 as RegKeyWord. The record source for form2 ends with WHERE CustomerName Like RegKeyWord. I receive the error Invalid column 'RegKeyWord'
Can anyone help?
View 1 Replies
View Related
Jan 6, 2006
Hi everyone,
I have created a stored procedure in sql server with parameters for my c# application. Wanted to know is there anyway to set the default value for @searchpostcode to select all the records?
Right now it brings the records based on the postcode specified .(I have dropdownlist in my c# application that passes the parameters for postcode)
My stored procedure:
CREATE PROCEDURE sp_accepting_practice (@searchpostcode as nvarchar(100)) AS
SELECT dbo.tbdentists.Title, dbo.tbdentists.FirstName, dbo.tbdentists.Surname, dbo.tbpractices.PracticeName, dbo.tbpractices.PracticeAddress1, dbo.tbpractices.PracticeAddress2, dbo.tbpractices.Town, dbo.tbpractices.Postcode, dbo.tbpractices.Phone, dbo.tbdentistspractices.ListNo, dbo.tbtreatment.treatmentNatureFROM dbo.tbdentists INNER JOIN dbo.tbdentistspractices ON dbo.tbdentists.DentistId = dbo.tbdentistspractices.DentistId INNER JOIN dbo.tbpractices ON dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId AND dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId INNER JOIN dbo.tbtreatment ON dbo.tbdentistspractices.TreatmentId = dbo.tbtreatment.treatmentIdWHERE dbo.tbpractices.Postcode LIKE '%' + @searchpostcode + '%'ORDER BY dbo.tbpractices.PracticeId
EXECUTE sp_accepting_practice G4GO
I greatly appreciate your help. Thanks in Advance
Regards
Shini
View 9 Replies
View Related
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
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
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Aug 11, 2015
@pvColumnName  VARCHAR(100) = Default,  
However, I am unable to determine what is the value for Default. Is it '' ?
Default is not permitted as a constant - below fails to parse:
WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)
View 4 Replies
View Related
Apr 3, 2008
Hi,
Does anyone have a workaround or know of a fix to this problem:
Default value set to 'date pick' from date currently within field by setting value equal to that field . ie if date is 01/01/2010 date picker opens in Jan 2010 - works ok.
However, once published to Sharepoint and run through browser the Date Picker ignores the default value and the date picker opens for today. ie April 2008.
Any words of wisdom gratefully recieved,
Howard Stiles
View 1 Replies
View Related
Mar 18, 2014
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
View 3 Replies
View Related
Sep 7, 2015
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
--Final output should be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4
View 2 Replies
View Related
Aug 6, 2007
HI,
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
Thanks ,
Sunil
View 7 Replies
View Related
Nov 2, 2006
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
View 3 Replies
View Related
Dec 3, 2014
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
View 5 Replies
View Related
Jul 23, 2005
I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.
View 2 Replies
View Related
Jan 18, 2008
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.
;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
Thanks in advance.
View 4 Replies
View Related
Nov 28, 2005
I tried to port 10000 records using DTS. After porting of 9900 records I got an error and comes out without any result. But I want to keep the records which has been ported till the error occured. Plz help me.
View 1 Replies
View Related
Oct 31, 2007
Hi, I have had this problem for a while and have not been able solve it.
What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.
then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.
The table name is Adm_disc_Match_tbl
Basically what i have 4 fields.
Index_key = which is the patient common link (text)
ur_episode = this wil change for each Hospital (text)
Admission_datetime = patient admission date and time (datetime)
Discharge_datetime = patient discharge date and time (datetime)
example of data
Code: ( text )
Index_key,ur_episode,Admission_datetime,discharge_ datetime
HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
FREDA-11/1925,G147858-2052082,9/05/2007 12:00,25/05/2007 11:17
If anyone could help it would be much appreciated.
View 6 Replies
View Related
Feb 3, 2007
Hello I am working in an advertising company and I have to find all records who have advertised 7 days back but if one person has advertised more than once means for different products, his name should come only once. How can I filter this. I am totally new to this language I can write simple query but don’t know how to filter it.
Name IndividualID Product Date
A 1 x 2007-01-27.23.59.59.777
A 1 y 2007-01-28.00.00.00.000
b 2 xx 2007-01-28.00.00.00.000
A 1 z 2007-01-28.00.00.00.000
b 2 yy 2007-01-28.00.00.00.000
A 1 p 2007-01-28.00.00.00.000
c 3 m 2007-01-28.00.00.00.000
d 1 n 2007-01-28.00.00.00.000
A 1 s 2007-01-28.00.00.00.000
View 1 Replies
View Related
May 17, 2007
Hi,
I have a supllier named K O'Neill and i want to filter on his name in my supplier table . Is this possible ?
When I try Vend Name = 'K O%Neill' it does not work.
Can anyone help?
Thanks.
View 6 Replies
View Related
Aug 21, 2006
I need a query that returns every n record from the database and the last record.
For every n-th is easy, but I need a
WHERE Index % n = 0
but I need a last one too.
Any Help.
View 3 Replies
View Related
May 24, 2007
Hi I am wandering if it is possible to achieve the following:
I am using Reporting Services and Reporting services I got a report with a text parameter.
I would like the user to introduce AA and find the product AA and when he introduces AA* it finds AA, AAD, AAC...
I am doing the following:
Filter([DIM Product].[Product].ALLMEMBERS,
,IIF(INSTR(@Product, "*") > 0
,[DIM Product].[Product].currentmember.name = Replace(@Product, "%", "")
,INSTR([DIM Product].[Product].currentmember.name, @Product)
)
)
The problem ist that the function Replace does not work!
Does someone know how to do it?
View 1 Replies
View Related
May 22, 2007
What is the syntax for using IN as a filter? in SQL it would be IN( 'A', 'B', 'C') but this does not work in Reporting services.
View 4 Replies
View Related
