Is It Possible To Make A Filter Dropdown Only Show Items Satisfying Any Existing Filters?
May 22, 2008
Hi all,
In my cube there is a customer dimension and a product dimension.
If I filter on a single customer, and then click on the filter dropdown for product, all of the products for every customer are shown.
Is it possible to make it so it only show the members for the customer selected?
I suspect the answer is no, as that would mean you can't untick any previously ticked members, but our account management team have got the right hump about them showing so I need to at least attempt it.
Regards,
Phil
View 2 Replies
ADVERTISEMENT
Oct 12, 2015
I'm trying to create a report in SSRS using a Matrix.
The data in my dataset looks like this one:
Part     Action   SortID  Count
---------------------------------------------------
Login     LA1     1       12
Login     LA2     2        25
Login     LA3     3       548
Register  RA1     1        12
Register  RA2     2          56
I have one rowgroup by the column Part and one columngroup by SortID and my Data should look like this:
Login         LA1      LA2       LA3
                 12        25        548
Register     RA1      RA2
                 12        56
But it looks like
Login         LA1      LA2       LA3
                 12        25        548
Register     RA1      RA2      LA3
                 12        56
how I could solve my Problem, that in row number 2 the LA3 isn't shown?
View 4 Replies
View Related
May 20, 2008
I'm running SQL Server 2008 Feb08 CTP and I've got a tablix with column groupings and row groupings which works nicely, I'm a becomming a big fan of the tablix.
However sometimes the filter I have on a column group returns no records and the whole column group disappears.
Is there any way to make this column appear with but with empty cells as I have another tablix down the page with the same columns and I want the columns to all line up on the multiple tablix.
In Version 2005 I used to achieve the equivalent of a column group within a table by adding a list with filter to the cell, this meant the column always was shown, the tablix allows does this much more elegantly but I'll have to go back to the old method if I can't make the column remain.
Am I just missing a simple option?
Nathan
View 1 Replies
View Related
Jan 10, 2008
Guys,
In my report builder when I drag the SKU in the filter area, its doesnt give me the dropdown and show me the distinct values, it just gives me a text box and expects me to type in the SKU.
I have other attributes whcih have the same property as SKU but they all work fine when I drag them into Filter area.
Is there a limitation in reporting svcs where if the Field has more than "n" number of distinct records it will not populate the dropdown, instead just present you with a text box?
My DataModel Table has around 110K records and roughly 7K distinct SKU's
I compared the SKU field to other fields in the Report Model Designer and also DataSource View and nothing seems to be out of normal to not show the dropdown with distinct values except for the # of distinct records.
Appreciate your help in advance
View 4 Replies
View Related
Dec 13, 2006
I have a report that has 2 dropdowns, selecting from the first dropdown populates the second one. This works fine in the BI Studio.
When I deploy this report to the 'Report Manager' and make a selection from the first dropdown, the second dropdown loads (as expected). I tried to select from the second dropdown (which has only 1 item - which is correct), the dropdown does not appear correctly - as in, I can't see that item.
Since we can't attach anything here, below is the link to a screenshot of my issue:
http://docs.google.com/View?docid=ddd6j2xn_52c5qd5
If you look closely at the screenshot from the link above, you'll see that there is a value in the second dropdown - it just won't show completely - as if the dropdown is not rendering correctly. I can view source on the page and see that the dropdown has a value.
What appears to be happening is the if only 1 item is in the second dropdown and that item is longer than the size of the dropdown, the dropdown won't render.
Here is my value for the second dropdown '012 Candy Gadler David Thapero'. This is the only value in the second dropdown. - You can try 35 chars or more in the dropdown to confirm.
Notes:
+ No special chars are in either dropdown
+ I am using IE 7 (Also had someone test this on IE 6 - same problem)
+ Using Visual Studio 2005 to build report - where this works fine
Work around:
+ If I add another item to the dropdown via a UNION query, I see the original value + the new item in the dropdown #2
Please share your thoughts
Thanks,
h.
View 9 Replies
View Related
Mar 7, 2007
Hi All,
Hope someone can help here. Since installing Service Pack 2 onto our SQL 2005 instances our report models are not working correctly. When selecting fields within the models as filters, and getting them to prompt when the report is run, the ordering of the data within the filters does not match the selected ordering in the model definition. The ordering is random each time the report is run.
This issue does not happen when setting the filter up, the data appears in the correct order, but when running the report the filter data is incorrect.
I have spent the last 4 hours building a new system and testing this with each stage of service packing. The RTM version of SQL 2005 is ok, SP1 is also OK, but when SP2 is applied the ordering fails.
Any ideas?
Many thanks in advance.
Regards
Nick
View 4 Replies
View Related
Dec 26, 2006
Hi,
I have dropdown parameter with multi-values allowed.
In my report headed I want to show all the dropdown values that were checked by the user to run the report. But since there could be a couple of hundred values I want to show ALL when all the values are selected instead of listing them one by one.
How can I do that?
Thanks,
Igor
View 4 Replies
View Related
Jan 22, 2007
Hi,
We have an issue with our replication configuration when viewed through replication monitor. Parameterised Filters and joined filters don't appear in the gui. However, when we script the publication all the filters are present.
This issue only seems to occur when we have a remote distributor.
I should also point out that we have a merge push topology that uses a custom RMO synchronisation component on a separate server to either the publisher or the distributor. Also all the databases in the topology are called the same name. This has caused us other issues relating to this topology in particular so I raise it here as well although I don't expect it to be the case in this instance.
Any help would be greatly appreciated in clarifying this matter.
View 1 Replies
View Related
May 4, 2004
Hello,
Inside a column I have this result:
Record 1: Sales;Admins
Record 2: ;Sales;Admins
Record 3: Sales;
Record 4: Admins;
You can see the delimiter ";", it can be everywhere.
Now I want to delete "Sales".
Therefor I have to search where the "Sales" is. (records)
After that I want to delete the "Sales".
If I delete it the record may not have 2 or more delimiters after each other, like here:
Record 1: ;Admins (good, better is to remove the delimiter also)
Record 2: ;;Admins (bad)
Record 3: ;(good, better is to remove the delimiter also)
Record 4: Admins;
Can somebody help me how to build this query?
Thanks!
View 7 Replies
View Related
Nov 27, 2007
In a multi-parameters report I want to show the parameter(s) user selected in a text box. How to set this up. For example user selected A, B, C in the parameter dropdown etc in the text box I want to show A, B, C.
Thanks
View 1 Replies
View Related
Jun 6, 2007
Hi,
I'm totally new to SSRS2005 and I was wondering if I could extend the existing report items. For instance, I'd like to add a few extra properties to the textbox report item. I tried wrting a custom Textbox control that inherits from System.Windows.Forms.Textbox. When I imported the dll in VS, the new textbox showed up in the toolbox, but it was grayed out.
Any idea what I'm missing here?
Thanks,
Phil
View 3 Replies
View Related
Mar 19, 2007
Is it possible to make certain items in a parameter selection list appear bold?
View 3 Replies
View Related
Oct 1, 2014
I am looking to filter the query using the free text predicate Contains for one of the column that have dimensions of the items. The query is something like
Select * from sampletable
where contains(filtercolumn, 'Near("*cm , *cm , *cm , *Kg.",10,True)')
Data in the filtercolumn is something like 64cm x 25cm x 35cm = 10Kg.
The query do not return any data.I also tried running the below query to see if cm* can be searcher for, but with no result again
Select * from sampletable
where contains(filtercolumn,, '"cm*"')
If I do something like below, it does return all columns that have 35cm within the text.
Select * from sampletable
where contains(filtercolumn,, '"35cm"')
View 1 Replies
View Related
Mar 6, 2015
I have a problem with report built in SSRS and deployed with Dashboard Designer to Sharepoint. There are few filters connected to report, 2 of them are multivalue. Regardless of data returned, when I select too many items in filter, the report is getting super small. It doesn't matter what you select, size changes when you select exact number of items or more. I replaced report with single line (filters where still conected) - result was the same.

Small amount of items selected:
More items selected:
Size of the raport in Dashboard Designer is set to "Percentage of dashboard page", when I selected autosize, result was the same.
View 3 Replies
View Related
May 6, 2005
Hi,
I've got a question...
sample record x
id field1 field2
1 a q
2 b x
3 a y
4 b z
I want to set a "y" filter on field2 but want to have all records with the same field1 value as result.
so, in the field2 = "y" filter, I want record 1 and 3 as result because field1 is in both records "a"
(Show me all records with the same Field1 value when field2 is y)
Is this possible and if so, how?
Kind regards,
Emiel Romein
Netherlands, the
View 3 Replies
View Related
Jun 3, 2014
I am looking for a way to show how I can have a result set that shows a record with one item and the number of records where it was purchased or sold. Below is my sample data.
Use tempdb
go
create table #ItemLedgerEntry
(
ENTRYNO INT NOT NULL
, ITEMNO VARCHAR (50) NOT NULL
, POSTINGDATE DATETIME NOT NULL
, ENTRYTYPE INT NOT NULL
[code]....
I know something like this will give me results but I'd like to run one query.
select itemno, count(*) from #ItemLedgerEntry where entrytype = 0
group by itemno
order by count(*) desc
View 5 Replies
View Related
Jun 23, 2014
I have the following table records structure:
Code:
drop table ##t1
create table ##t1(col1 int, col2 int, col3 int, col4 int)
insert into ##t1(col1 , col2, col3, col4)
values (1,2,3,4),
(5,6,7,8)
[Code] .....
I can't figure out how to put all nulls at the bottom and have all of my records on the top.
View 11 Replies
View Related
Aug 9, 2005
How do we sync up dtsx files with Visual Source Safe? best practices?
View 10 Replies
View Related
Jul 18, 2007
I am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?
John Smith
Title: MSTP Location: Main Campus IP:127.0.0.1
Softwares:
Adobe Standard 7.0 Access 5.0
Internet Explore 6.0 Office XP
Any suggestion is appreciated.
View 1 Replies
View Related
Jun 10, 2015
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
My script is here, and the sample result is attached. How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
a.Customer_Status_Txt,
a.Legal_Entity_Type_Txt,
a.Business_Unit_Txt
FROM dw_mart.dbo.DimCustomer a
[code]....
View 1 Replies
View Related
Jun 10, 2015
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
[code]....
View 6 Replies
View Related
Apr 14, 2015
I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.
What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.
View 3 Replies
View Related
Feb 12, 2008
Hi,
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)
TIA
Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)
--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO
View 5 Replies
View Related
Mar 9, 2007
Hello!
What is the best way to make a copy of an existing sql server database to another (physical) server?
Plan to make a full backup of another sql server database to another server. I've read about detach and attach
and copying the datafiles and log files but some say it is prone to data loss? Is this true?
And another thing, what if the existing sql server can't affor downtime for me to copy db files etc.?
Anybody here who have done the same?
Any help is very much appreciated.
Thanks!
View 3 Replies
View Related
Apr 17, 2007
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
View 3 Replies
View Related
Jul 20, 2005
Hi(SQL Server 2000)I have an existing table (t) with a column that is NOT an identity column(t.ID), but it has manually inserted "row numbers". I want to make thiscolumn become an identity column. This column is a key field to othertables, so I want to keep the row numbers that are allready inserted.From the Query Analyzer, how do I do this?Thanks in advance!Regards,Gunnar VøyenliEDB-konsulent asNORWAY
View 3 Replies
View Related
Feb 25, 2015
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode
1A
1B
24
28
26
310
312
410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10
- 2 and 11
- 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
View 8 Replies
View Related
Apr 10, 2015
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). Â In some cases, items can be components of another item (called "Kits"). Â In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". Â In our Items table, we have a field called "Parent_Item_Id". Â Records for Child Items contain the Item Id of the parent. Â So a sample of my database would be the following:
ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. Â So an example of the report would be the following:
Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100
How I can setup my report to group properly?
View 6 Replies
View Related
May 12, 2006
I cannot find an easy way to DELETE items which are > 1 time in my table (i am working with MS SQL 2000)
idserialisOk
-------------------
2AAA1
3BBB0
5dfds0
6CCC1
7fdfd 0
8AAA0
9CCC0
I want to DELETE each Row IN
SELECT doublons.serial, Count(doublons.serial) AS 2Times
FROM doublons
GROUP BY doublons.serial
HAVING Count(doublons.serial)>1
and WHERE isOK = 0
in my exemple , after deleting, my table must look like
idserialisOk
-------------------
8AAA1
9CCC1
3BBB0
5dfds0
7fdfd0
thank you for helping
View 10 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
Nov 14, 2007
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
View 1 Replies
View Related
Aug 13, 2007
Hi All,
I would like to restore database using RESTORE DATABASE ... REPLACE command.
If database exists already and has any open connections this command will fail.
I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command.
I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
Please advice me how to do it.
Thanks in advance,
Roman
View 3 Replies
View Related
