How Do I Get My Table Filter To Work?
Mar 26, 2008
Some tables i have in a report. A couple of them work, but 2 others dont. I would do it in the query, but then i wouldnt be able to group them in a list.
So my question is, how come i can filter out my BillcustomerCode, but when i want to filter out my subbrand_id (which is an int) or subbran_ Descrition(which is a varchar). how would i filter out these types in the table filter.
Also, how would i do an "Or" instead of an "and" in my filter too.
I have one table where i need to have 3 things not in the table:
Bill_Cus_code !=RNPROF
Bill_Cus_code !=RNPROC
Sub_Brand_Id ! =65 (or Sub_Brand_Discription != "Pro Club")
how do i do this in the expression. It seems to take out "RNPROF and RNPROC" but not the third one. Why is this?
View 1 Replies
ADVERTISEMENT
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
Oct 22, 2015
We have two measures
[Measures].[NSPO]
is in FACT1 Â Measure Group
[Measures].[NetSignedPremium] is in FACT2 Â Measure Group
The requirement is Measure.[NetPremium] should be the greater of
[Measures].[NSPO] OR [Measures].[NetSignedPremium].
Â
All three measures are created in the cube and this logic is to be applied in the Calculations section of the cube.
Using an IIF statement like one mentioned below does the job, but data does not aggregate correctly for the
Measure.[NetPremium]
Measure.[NetPremium] = IIF ([Measures].[NetSignedPremium] > [Measures].[NSPO], [Measures].[NetSignedPremium] , [Measures].[NSPO])
To ensure that the columns are aggregated I have to use a SCOPE statement at the leaf level
[01 UWR Reference].[UWR Reference].Members Â
Â
SCOPE(
    FILTER( NONEMPTY( [01 UWR Reference].[UWR Reference].Members  ) ,
         ( ( ([Measures].[Net Signed Premium Rcvd])
         - ( [Measures].[NSPOTest] ) )
             < 0 ) )) ;
        This =  [Measures].[Net Signed Premium Rcvd] ;      Â
ENDÂ SCOPE; Â
My problem is the FILTER within the SCOPE statement does not work. How to modify the filter in SCOPE? OR How to make IIF Aggregate correctly?
View 4 Replies
View Related
Nov 12, 2007
Good morning all,
I have searched everywhere for this and I can't find any information on it. When I use the Query Builder, in the top pane, the Filter pane, there are five columnsimension, Hierarchy, Operator, Filter Expession and Parameters.
In the dropdown for Operator, one of the choices is MDX. I cannot find any documentation on how to use this operator. I want to limit my filter to SELF_AND_AFTER, and I am hoping that this is possible using this operator. I know how to hand code it, but I have a ton of other tweaks to make to the query, so I want to use the Query Designer as much as I can.
Does anyone have any links to documentation on how to use this operator?
Thanks,
Kathryn
View 2 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 ABC
as
begin
/*
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 ABC
as
begin
/*
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 3 Replies
View Related
Sep 21, 2006
I have several reports where I am trying to use a single sp, and filter some of the paramater selections in reporting services.
I can get a single item to work in the filters
ie: state.value = SC
but i have not been able to get a multivalue scenario to work.
ie: State.value in SC,GA,TN
or State.value in ('SC','GA','TN')
or State.value in ("SC","GA","TN")
or State.value in 'SC','GA','TN'
or State.value in "SC","GA","TN"
I am guessing it is a simple syntax thing, but well obviously I havent found it.
Any help would be appreciated
Rick
View 5 Replies
View Related
Nov 3, 2006
Hi All,
How do I filter an EXEC table as to put the returned value into a variable?
I have to filter an EXEC table because I am using a table variable to define which table tto select.
Help appreciated.
View 6 Replies
View Related
Jun 7, 2007
I'd like to set the Filters in the Filters tab of the Table Properties dialog to say:
=Fields!WT_TO.Value > 0 OR
=Fields!WT_TO_PREV.Value > 0
but teh And/Or column is permanently disabled, and its sticking in a default value of AND
what's up with that?
View 6 Replies
View Related
Oct 26, 2005
I am using RS 2005. I am setting up a Model for use within Report Builder so our clients can write their own reports.
View 12 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
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
Aug 27, 2007
Table filter operation where I want to filter against any combination from any of the available fields:
table a(f1 int, f2 int)
2 indexes - first by field f1, second by field f2
data - many records like (f1=1, f2=2), and many records like (f1=3, f2=4)
sql: select * from a where f1=1 and f2=4
result will be - no records, but full table scan is required, indexes are not used
question - how to optimize above structure, but do not create compound index by (f1, f2)?
if we will have many fields (lets say 10), which can be used in filter in any combination we will need to create 10! Indexes. Any way to avoid or optimize it for a high volume searches?
View 1 Replies
View Related
Feb 11, 2008
Hai guys,
In my report I want to use OR condition instead of AND in Table Filter Expression. By default this option is disabled in VS2005 Pro. How do I enable this? or is there any other way to do this? Thanks in advance.
View 7 Replies
View Related
May 20, 2008
We have a fact table where one of the measures in that fact table is invoice balance. Some of our reports requirements list that they only want to see the sum of those invoices where the balance is greater than X dollars. How do I go about filtering calculations based on a measures value?
View 3 Replies
View Related
Oct 6, 2004
I have a big table with several types of transsactions: PO (Puchase Orders, SO,( Sales orders), INV (Invoices) ....
I want to create cubes with only one type of transactions (1 cube for PO,...)
Where and how can I filter the rows I want to use in my cube ?
Thanks
View 2 Replies
View Related
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
Apr 21, 2008
H, need help please!
I have two tables on a reports, each having it's own dataset.
I want to set the visibility of the table depending on the filter selection.
So if the user selects: show open then show only the table with the open dataset.
I don't now how to this using the expression in the visibility expression.
Please Assist!
Regards
View 2 Replies
View Related
Mar 15, 2007
I am using VS 2005/SQL 2005 and must use a stored procedure as provided. The stored procedure returns parent rows and child rows (same as parent with additional columns populated, like ChildID). I cannot change the stored procedure.
I have been unable to filter the data (either at the Dataset level or the Table Properties level) to only see the "parent" rows. On my report, I added one column to print the value for my test and a second to use it:
=Trim(CStr(Len(Fields!ChiIdID.Value)))
=IIf(Trim(CStr(Len(Fields!ChildId.Value)) = "0"),"PARENT","CHILD")
Both columns produce the values I expect. However, if I try a filter using the first expression, it fails! I.E. if I test = 0, it filters out ALL rows and if I test != 0, all rows are displayed?????
I am new to SSRS, so I would not be surprised if this is cockpit error. It seemed like a very simple requirement that I have used in othe report tools for years.
Any help would be appreciated.
View 3 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
Jul 20, 2005
I have a set of udf's dealing that return a one column table of valuesparsed from a comma delimeted string.For example:CREATE FUNCTION [dbo].[udf_filter_patient](@patient_list varchar(2000))RETURNS @patient TABLE(patient_id int)ASBEGINinsert into @patientselect patient_id from patient-- parse @patient_list stuff excludedRETURNENDI have come up with the following two schemes to use these udfs. Theseexamples are obviously simplified, and I have a handful of stored proceduresthat will use between 10 or more of these filters. If the two areequivalent, I prefer Method 2 because it makes for much neater SQL whenusing many filter criteria.So my question is, will one noticebly outperform the other? Or is there abetter way in which to filter on a list of criteria?Method 1 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASCREATE TABLE #patient(patient_id int)INSERT INTO #patientSELECTpatient_idFROMdbo.udf_filter_patient( @patient_list )SELECT*FROMopen_billsINNER JOIN #patient on #patient.patient_id = open_bills.patient_idGOMethod 2 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASSELECT*FROMopen_billsWHEREopen_bills.patient_id IN ( SELECT patient_id FROMdbo.udf_filter_patient( @patient_list ) )GOThanks for the help!Chris G
View 4 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
Feb 9, 2006
Hello,
Probabaly a silly question yet as a DOTNET developer, I'm trying to simulate DTS when for example, I don't have permission to perform DTS on a production server.
In particular and interested regards caching of rows before the service decides to flush the buffer and write to the target table. Safe to assume DTS is cursor based?
View 1 Replies
View Related
Feb 10, 2015
I have a table:
declare tableName table
(
uniqueid int identity(1,1),
id int,
starttime datetime2(0),
endtime datetime2(0),
parameter int
)
A stored procedure has new set of values for a given id. Sometimes the startime and endtime are the same, in which case I update the value of parameter. Sometimes I add a new time range (insert statement), and sometimes I delete a time range (delete statement).
I had a question on merge, with insert, delete and update and I got that resolved. However I have a different question regarding performance of the merge statement.
If my target table has hundreds of millions of records and I want to delete/update/insert a handful of records, will SQL server scan the entire target table? I can't have:
merge ( select * from tableName where id = 10 ) as target
using ...
and I can't have:
merge tableName as target
using [my query] as source on
source.id = target.id and
source.starttime = target.startime and
source.endtime = target.endtime
where target.id = 10
...
This means I cannot filter the set of rows in the target table to a handful of records where id = 10.
View 1 Replies
View Related
Mar 26, 2008
Hi All,
I am working on SQL server 2005 Reports.
I have one report, one dataset is assigned to it, and one table which displays it.
Now I come accros requirement that, the column value in the filter condition for the table is present in one textbox.
I can not use textbox i.e. reportItems in filter condition. Can someone suggest me how to use textbox value in filters?
I want to display parent/child records on report. I am not getting the proper solution.
The data is like this:
Sequence ItemCode IsParent
1 XYZ 0 'do not have child record
2 PQR 1 'have child records with sequence no 3
3 ASD 0
3 AFDGE 0
3 VDC 0
4 ASR 1 'have child records with sequence no 5
5 ASR 0
If IsParent = 1, that record has child records with sequence = parent sequenece + 1
I think u can understand the data I need to bind, and it is like:
XYZ
+ PQR
ASD
AFDGE
VDC
ASR
On + click we can do show/hide of child records.
I m not getting how to achive this in SQL server report. Can u give some hint?
Thanks in advance
Pravin
View 1 Replies
View Related
Feb 28, 2000
alter table amy_table add(Category varchar(64));
says that my syntax is wrong ... can anyone help?
View 3 Replies
View Related
Jan 29, 2008
I have a table with a group (i.e. 3 lines in the group).
KeepTogether on the table is set to true.
Despite this, the group is split on the page break. (i.e. 2 lines on 1 page, 1 on the next)
What is the workaround for this behaviour?
View 15 Replies
View Related
Feb 7, 2008
Hi All,
I have few stored procedures which I need to pull reports from. They all work with Temporary tables (#Name1). I am writing a wrapper kind of a SP which will call these SP's based on some inputs. When first I created a dataset with this SP, I got errors that the # tables were not found. I thought using temporary tables is not supported with SRS 2000. But now again I tried and it worked! The only difference now is that the wrapper SP now is on the same machine as the SRS and in the earlier case, it was not. Does that make any difference? Ultimately, will the combination of temporary tables and SRS 2000 work or not?
Please let me know. Thanks a lot...
Manoj.
View 5 Replies
View Related
Mar 28, 2000
Since I have to go across the network, I'm trying to use the UNC. However, this won't even work when I'm using the UNC to point to the server on which this is run. I'm trying to restore a single table on 6.5. What is the obvious piece that I'm missing?
This works.
LOAD TABLE address
FROM DISK = 'd:MSSQLackupDBBackup.DAT'
WITH source='address'
This doesn't.
LOAD TABLE address
FROM DISK = 'server1d$MSSQLackupDBBackup.DAT'
WITH source='address'
View 2 Replies
View Related
Feb 18, 2006
I've never really setup or used MS SQL Server (just a couple hours, oneday, several months ago). I think MS SQL Server has the ability to use"linked tables", like MS Access does. Is this correct? What I want todo is have an MS SQL Server setup, which compatible applications cantalk with, but I want the data to come from a Paradox database. I canuse any MS SQL version, whatever would work best. I'm not sure aboutthe Paradox version, I know it is an old DOS version. I can't justconvert the data to another format, because Paradox still needs to useit.I tried using MS Access before, with ODBC drivers it *should* be ableto work with the Paradox data. However, I ended up with lots of datacorruption. I'm hoping MS SQL Server may work better, and not corruptthe Paradox data everytime it is updated.
View 4 Replies
View Related
Mar 9, 2007
Hello all,
I had editted this post to ask a better question.
I had noticed that certain expressions won't work in details. Is this true ?
when I tried using an IIF expression, I always get a blank or an empty field even if it was a text.
could someone clarify this ? I don't know whether it is just me or is it a limitation.
Sincerely,
Bernard Ong
View 5 Replies
View Related
Jul 11, 2006
Hi
I have 2 columns the first column is a counter thats always counting up like a meter, the second column is a formula that calculate the difference between the counter(column one). like
Counter
Difference
1
1
3
2
6
3
9
3
11
2
13
2
17
4
25
8
The first row is taken as it is, but the second row will be difference = 3-1 the third one will be 3-6 and so on.
How can I do this in a SQL table or any other away?
Your help will be highly appreciated.
View 9 Replies
View Related
Aug 2, 2000
hi,
I have a stored procedure to calculate the balance for a group of people. Inside the sp,
I created a temp table at the beginning of the procedure, then use it to calculate the opening
balance first, then I truncated the table in the middle, reuse it to calculate the closing balance.
The sp will take the from date and the through date to do the calculation. Somehow,
when I passed a different from date and the same through date to the sp, it returned a different
closing balance. It took me a while to figure out that the temp table I was using didn't get
truncated in the middle. I did a 'select count(*) ' directly after the truncate statement.
Sometimes it returned a non-zero number, and sometimes it returned 0.
We are using SQL 7.0 Service pack 1. Does anyone know the reason?
Heidi Zhang
hZhangcti@use.net
View 3 Replies
View Related
Dec 15, 2003
I tryed to update tables part of my MSDE database, using the SqlDataAdapter.Update() method. It worked fine untill I tryed to update a table that has a Column with the Text SQL DataType. It didn't work. The error was :
"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."
Is there a way to do it ?
Thanks,
Jeff
View 4 Replies
View Related
