Challenging Drilldown/visibility Problem
May 12, 2008
Hi,
I have a table with three group headers. I want the header row in the third section to be toggled by a textbox in the second header. This works as expected. However, there are times when there is no data in the third section. When this happens, the third section header row shows even though there is no data. I can also create an expression for the third section header's hidden property that looks for the existence of data, but then the toggle is deactivated. I need some way to say, "here's the toggle item. Show the header if there is data for it."
Any ideas are welcome.
View 2 Replies
ADVERTISEMENT
Dec 10, 2007
does anyone know why my initial visibility expression (=Level()>1) stays in effect after generating my report? The idea was to display only a couple of levels in the hierarchy at first, so that the user could more easily decide what he wants to expand. But expand doesnt do anything after initial rendering.
The only way I could get this kind of report to work (show hierarchy and expand/collapse) was to live with initial visibility = "visible", but unfortunately, that setting shows all levels right away, not a very user friendly thing.
I am running Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
View 1 Replies
View Related
Feb 13, 2008
Hello,
I'm using SSRS 2005
I'm trying to write an expression for the "Hidden" property of a column in a table. The column is only populated with data if the group on my table is open. If the group is collapsed, then the column is empty. I'd like to make the column hidden if the group is collapsed. So I'm thinking it would be something like this:
=Iif(Table_1_Group.Hidden = "True", "True", "False")
But I can't find anyone who has written this anywhere.
I'm basically trying to make this report do the same thing a matrix does, but the matrix doesn't let me label the columns. I put textboxes above the matrix in line with the columns, but when I deploy the report to ReportManager, the textboxes get thrown all over the place and don't line up with the columns anymore.
So I guess if you can't answer the first question, an alternate question I have is how are you supposed to label the columns (row groups) in your matrix? They don't have headers, if I try to line textboxes up with them they get all messed up during deployment. They have that textbox up there in the top left corner that runs across the top of all of the row group columns, but if I put labels separated spaces in that textbox, the spaces get removed in rendering so the text is pushed all to the left and doesn't line up anymore.
Thanks,
Andy
View 3 Replies
View Related
Aug 31, 2006
Hi,
I've been playing around with SSRS 2005 to see if it might be the solution for some reporting requirements I have been given, but I still haven't found a way to do exactly what I need. Basically I've been asked to provide a report which allows drilling down multiple levels of a client hierarchy to show some sales figures, sounds pretty simple right? The report will look like this (not showing sales figues here just client hierarchy)...
+ Client A
- Client B
- Client C
- Client D
+ Client E
- Client F
- Client G
- Client H
- Client I
- Client J
+ Client K
+ Client L
As you can see they want to be able to expand & collapse rows to drill down through their client hierarchy. The hierarchy will be 3 or 4 levels deep.
I have seen that RS2005 supports a simple way to drill-down via grouping, but I don't want to do it this way because it means that I will have to have a query which retrieves all of the data right away which would take several minutes.
What I'd really like to do is to lazily load the hierarchy so that the data is loaded as the tree is expanded so that it won't take too long to render the report initially. I.e. when they click on "Client B" I'll hot the DB and get the sales figures for all children of Client B, in the case above thats Clients C & D.
Can this be achieved using sub reports or report linking? If anyone knows of some tutorials or examples that might be able to help me with this I would really appreciate it.
Thanks.
Lachlan
View 3 Replies
View Related
Oct 16, 2007
Hello,
I am creating report which has group and I want to show the [+] in front of each grouped item and when user click this the group item will get displayed.
Please let me know any solution.
View 4 Replies
View Related
Mar 5, 2007
the field which has drilldown option is always showed minimum width after delopying to website
I attempted to set all properties of this fields as i can,such as "width","can grow" etc, but all do not work
any idea about this?
thanks
View 1 Replies
View Related
Apr 18, 2008
Hi,I need help to generate following query....------------------------------------------------------------------------------------------------------------- Tables and their columns:Table name: Employee Columns for Employee Table : (1) EmpID (2) EmpName (3) EcatID (4) ProjectIDSecond Table Name: EmpskillColumns for Empskill Table: (1) EmpID (2) SelfRating (3) EvalRatingInformation: (1) If EcatID = 1 means he is Project Manager [if not then he is a software Engineer] (2) Both Project Manager and his subordinates have the same ProjectID------------------------------------------------------------------------------------------------------------- Result Needed:-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PM Name Software Enggs Nme under that PM No of Self Rated Projects (for SEs) No of Evaluator rated Projects (for SEs) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Rajesh 10 8 Suguna Mahesh 5 5 Roopesh 3 3--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------In Short: List the Project Mangers name, in the next column list their subordinates (for one PM there are many sub ords), in the next column list the no of self rated projects of the sub ordinates, in the last column, list the No fo evaluator rated projects Can Some One Help me ??Thanks in AdvanceHave a nice day
View 20 Replies
View Related
Jun 14, 2005
I am a bit into this one and it is kind of bugging me as i have not figured it out yet after 1 hour. I have a web task in a Sql Server 2K that generates ASP code and ftps it to a remote location. This is nothing fancy: selects a few fields from a few tables and creates an HTML page into a file with a *.asp extension. It is much like a report that gets ship from the intranet to the Internet side of our business. You can do something similar by using the Tools>>Wizards>>Management>>Web Assistant Wizard... inside Enterprise Manager. The page looks fine but the problem (my) is that the rows all have the same color - white the default. I am using a template [*.tpl] and the alternating color for the rows <tr> has to come from the data in the query. Now what I would like to do is something like this to get alternating colors in my table rows: declare @bgcolor nvarchar(12) set @bgcolor = 'white' select @bgcolor = CASE @bgcolor WHEN 'white' THEN 'gray' WHEN 'gray' THEN 'white' ELSE 'white' END, , field1 , field2 , field3 from table1 inner join table2.... where .... And, maybe get results like these: 'white', 'field1res', 2, 3 'gray', 'field1res, 3, 3 'white', ....etc. The above DOES NOT WORK. Maybe because T-SQL is a set based language trying to do something like this is not appropriate - aka procedural. It does, however, still bugs the hell out of me. :) Maybe I should investigate using Reporting Services to handle this requirement. If anyone has any suggestions, please let me know.
View 6 Replies
View Related
Apr 15, 2005
Dear all, i have a challenging problem in writing a trigger here,,
here is the database script
-----------------------SCRIPT------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Categories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories_Properties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Categories_Properties]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Properties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Properties]
GO
CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentID] [int] NULL ,
[CategoryDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MainCategory] [bit] NOT NULL ,
[HavePhoto] [bit] NOT NULL ,
[MaxPhotos] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Categories_Properties] (
[Categories_PropertiesID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[PropertyID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Properties] (
[PropertyID] [int] IDENTITY (1, 1) NOT NULL ,
[PropertyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
------------------------END SCRIPT--------------------------
Categories and Categories_Properties are joined by CategoryID
Properties and Categories_Properties are joined by PropertyID
there is a relation between the table Categories and itself, the PrimaryKey is CategoryID, the ForiegnKey is ParentID
the problem is i need a trigger to fire when adding a record in Categories_Properties to add this property to all the children for this Category, for eexample if the categories tree
Autos >> Cars >> FIAT
when adding the Property BRAND to to Autos, this Property had to be inserted to Cars and FIAT too, i wrote some trigger but it adds this property to just the direct children, just to cars,
How to update it to include all the children in all levels till the leaf ones??
here is what i wrote
-----------------------Trigger-------------------------
CREATE TRIGGER [inherete_property] ON [dbo].[Categories_Properties]
FOR INSERT
AS
declare @CatID integer,@PropID integer
--set @CatID=select CategoryID from inserted
set @PropID=(select top 1(PropertyID) from inserted)
DECLARE cat_cursor CURSOR FOR
select CategoryID from Categories where ParentID in (select CategoryID from inserted)
open cat_cursor
fetch next from cat_cursor
into @CatID
WHILE @@FETCH_STATUS = 0
BEGIN
insert into categories_Properties(CategoryID,PropertyID) values (@CatID,@PropID)
FETCH NEXT FROM cat_cursor
INTO @CatID
END
CLOSE cat_cursor
DEALLOCATE cat_cursor
-----------------------End Trigger---------------------
thank you all
View 1 Replies
View Related
Mar 12, 1999
This problem occurs in a Visual Basic 5 application which accesses the SQL Server 6.5 database through Jet Engine(DAO).
If a user starts a transaction and then his application terminates abnormally (before rolling back or committing), say due to power failure or if the user kills the application with Ctrl+Alt+Delete, then the transaction started by him is not terminated (rolled back) by SQL Server. Even the process cannot be killed using the "Kill" command and the user keeps holding all the locks that he has already acquired. Also nobody can use Enterprise Manager. This happens when you have lock on more than 8 tables including tables in tempdb. The only way to solve this problem is to restart the server which is never expected in a multiuser system.
One can know the threadID of the process that needs to be killed using "Sysprocesses" system table. Is it possible to kill the thread in Windows NT?
Is there any setting due to which SQL SERVER will automatically kill a process or rollback a transaction started by a disconnected client ?
Is it possible that a process or transaction will have to release lock after the specified time ?
Is there any other way to tackle this problem?
-Gunvant Patil
gunvantp@mail.usa.com
View 2 Replies
View Related
Feb 19, 2006
How does one figure out, I mean compare the results after some changes have been done to the SQL Server, I mean the method in which the comparision is done, lets say I have a query I ran it it gave me the results in 40 seconds , I ran it again this time it took 30 seconds, then I again ran it it took 35 seconds, I then created an index , this time the query ran in 30 seconds ... how does one compare such things , I mean i need to give stats as too what kind of performace has takern place ... please helpo , I need to knw as a DBa how would you convince your maanger that becasue of some changes the performance has improved, cause when you ask the users they say its ok , we dont see the differnece & stuff , please help.
View 4 Replies
View Related
Jul 23, 2005
Table DDL:create table test(inId int primary key identity,inFK int not null,inSeq int not null,dtDate datetime)Data Insert:insert into testselect 1,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 1,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 1,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 2,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 2,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 3,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 4,1, getdate()If we select on this table:inId inFK inSeq dtDate----------- ----------- -----------------------------------------------------------------1 1 1 2005-02-01 12:54:40.9672 1 1 2005-02-01 12:54:41.9673 1 1 2005-02-01 12:54:42.9674 2 1 2005-02-01 12:54:43.9675 2 1 2005-02-01 12:54:44.9676 3 1 2005-02-01 12:54:45.9837 4 1 2005-02-01 12:54:47.077(7 row(s) affected)Problem:What I would like to do (using SQL and not a cursor) is to update thevalue of inSeq to its ordinal position, this will be based on the # ofoccurences of inFK. For Example, I would like to run a sql statementthat would transform the data to:update testset inSEQ = (some sql)select * from test - This would then produce:inId inFK inSeq dtDate----------- ----------- -----------------------------------------------------------------1 1 1 2005-02-01 12:54:40.9672 1 2 2005-02-01 12:54:41.9673 1 3 2005-02-01 12:54:42.9674 2 1 2005-02-01 12:54:43.9675 2 2 2005-02-01 12:54:44.9676 3 1 2005-02-01 12:54:45.9837 4 1 2005-02-01 12:54:47.077(7 row(s) affected)Any help would be greatly appreciated.TFD
View 17 Replies
View Related
Jul 20, 2005
here is a structure of tables in a databaseCUSTOMERS (CustID*, CompanyName, ContactName, ContactTitle, Address,City, Region, PostalCode, Country, Phone, Fax)EMPLOYEES (EmployeeID*, Lastname, Firstname, Title, TitleofCourtesy,Birthdate, Hiredate, Address, City, Region, Postalcode, Country,Homephone, Extension, Reportsto)ORDERS (OrderID*, CustID, EmployeeID, OrderDate, RequiredDate,ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity,Shipregion, ShipPostalCode, ShipCountry)ORDER_DETAILS (OrderID*, ProductID*, UnitPrice, Quantity, Discount)PRODUCTS (ProductID*, ProductName, SupplierID, PL_ID, QuantityPerUnit,UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)PRODUCT_LINES (PL_Id*, PL_Name, Description)SHIPPERS (ShipperID*, CompanyName, Phone)SUPPLIERS (Supplierid*, Companyname, Contactname, Contacttitle,Address, City, Region, Postalcode, Country,Phone,Fax)* - Primary keysqueries (these are problem questions given in a competition!!!)1.) In which month of 1997 was the most number of orders placed? Listthe month, number of orders placed during that month , number ofcustomers who placed orders in that month , gross (i.e. disregarddiscount) total value of orders placed .2.)Determine the products for which there has been at least oneorder during each month of 1997. Display for these products: theProduct ID, Product name, Product Line Name, number of orders placedduring 1997 , number of customers who have ordered the product in 1997, and gross (i.e. disregarding discount) total value of orders placedfor the product in 1997 .3.) Find the customers who have placed orders during 1996 & 1997 forproducts in every product line AND who have had their orders handledby either all employees or all but one employee. Display the customerID, company name, address, city, country and number of orders placed4.) For each product line, find the top three customers (based onmaximum total purchase value; ignore discounts). Display the ProductLine name, the Customer ID & Company name, and the total purchasevalue for that customer in the product line . Sort the output byProduct Line and within that, the line revenue figure (highest first).i think rank() shud b usedany clues/ideas ???
View 6 Replies
View Related
Sep 30, 2006
Hi,
I'm new to SQL Reporting Service. Now only I've started studying that. My question is,
I wanna list out my product sales for each month for each keywords. For example, I have 4 products, some 15 keywords (its an ecommerce site. Visitor coming from a particluar keyword and buying some products. Wanna list out sales for each keywords and product for Every month. To know the quarter sales wanna see the sales and product name for particular keyword). I'm having reports for each month sales. like,
Keywords
Product A
Product B
Product C
Product D
Keyword 1
125
24
275
12
Keyword 2
456
245
67
1223
For quarter sales, I want to see the sales and keywords list for each month and for each products in the same report. I don't want to create one another report for each and every product and month. If I want to see the sales and keywords for a particular product I want a report like,For Product 1
Keywords
Month1
Month2
Month3
Month4
Keyword 1
125
24
275
12
Keyword 2
456
245
67
1223
and For Month 1
Keywords
Product A
Product B
Product C
Product D
Keyword 1
125
24
275
12
Keyword 2
456
245
67
1223
Just by using drilldown I want this report. If any other method is possible, please tell me. It's very urgent. Please Help me. I'm very new to SQL Reporting Service. Started studying before 2 days. Thanks.............
View 6 Replies
View Related
Apr 8, 2008
When i am designing a report using BIDS how do i control the Drilldown on the Matrix Totals. At present when i created a drill down report it works fine for all non total Values but chooses a random Value when it drills on Totals (Usually the first but not always)
Eg if i have a Matix that looks like this
Name, Value1, Value2, Value3
John, 1,1,1 = 3
Mark 2,2,2 = 6
Fred 3,3,3 = 9
Total 6,6,6
If i want to Drill on either the totals at the bottom or the totals on the Right or the bottom, it will choose a random Value eg if i want ot see the totals for John the Column Parameter that it passes to my drill through report will be that for Value 1 which is incorrect.
Any help greatly appreciated.
View 6 Replies
View Related
May 26, 2008
Hi,
some of our Drill down has many values and we would like to display only top 10 ...
I know how to create CALCULATEDMEMBER for the cube ..but that becomes a measure and will NOT be in the drill down so,
how to create Top 10 for Raw Url
Drill Down
Groups
Top 10 Urls
( this has 10000 values¦¦.. can we just display top 10 here? Right now the report never comes back because of the count¦ or if there is a better way let me know
thanks
View 3 Replies
View Related
Oct 26, 2007
Hello,
I am writing a report that has two levels of groupings like this and the visibity is controlled by Title and Staff Grouping (Partner, Employee) for drilling down.
WTD Hours
*Staff
*Manager
Joe 8
Max 8
Totals 16
*Senior Manager
Bob 7
Totals 7
Totals 23
*Partner
*Partner
Ed 20
Total 20
Totals 43
* = Toggle Item
Now, all this is fine except that when everything is collapsed, I want to show the totals on the group header and hide the group footer, like this:
WTD Hours
*Staff 23
*Partners 20
Totals 43
NOT like this:
WTD Hours
*Staff
Totals 23
*Partners
Totals 20
Totals 43
I have the functionality working with the toggle states and visibility, but whenever the group is collapsed, all the formatting is lost and it is just whitespace not matching the rest of the row.
Is there any way to control this by some expression in the value, etc?
Thanks in advance!
View 2 Replies
View Related
Oct 31, 2007
I'm basically learning SSRS (and SQL for that matter) so bear with me. I'm building some reports and discovered that apparently one has to have the Enterprise edition to have the drilldown capability. We really don't need the Enterprise edition for anything else, being a small company. Is there absolutely no way to do drilldown in the Standard edition, is there any way to add just that capability, and are there any sort of workarounds? Thanks.
View 7 Replies
View Related
Feb 28, 2008
I have a report that drills down into 2 different levels like this:- Main Report -> Report -> Detail Report.
Everything works fine for the first 2 reports but when I try to drill down to the 3rd report (Detail Report in my example above) I keep gettting this error:
The path of the item '(null)' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath)
Has somebody got a solution or a workaround?
View 1 Replies
View Related
Apr 9, 2006
First, please reference the following document which shows the layout of the database and tables:
http://www.eastproject.org/cody/table_layout.pdf
Here's the deal--
I have two databases, SiteData and CommunityServer2. Both run on the same MS SQL 2000 Server. We're concerned about two tables on each database: SiteData.dbo.Users, SiteData.dbo.UM_User_Access_Type, CommunityServer2.dbo.aspnet_Users and CommunityServer2.dbo.aspnet_UsersInRoles.
Whever a user is inserted into the CommunityServer2.dbo.aspnet_Users table, I need to give the user appropriate rules (e.g., moderator, student, etc.) by inserting the correct RoleID in the SiteData.dbo.aspnet_UsersInRoles table.
Have I lost you yet? Good.
Please reference the following trigger:
ALTER TRIGGER trig_UpdateForumRoles ON CommunityServer2.dbo.aspnet_Users AFTER INSERTAS DECLARE @Username VARCHAR(100) --variable to hold the username from CommunityServer2.aspnet_UsersDECLARE @UserType VARCHAR(50) --variable to hold the username from SiteData.dbo.UM_User_Access_TypeDECLARE @UserID uniqueidentifier --variable to hold the username from CommunityServer2.aspnet_UsersBEGIN SELECT @Username=Username, @UserID=UserID FROM Inserted --should only return one record, meaning we'd only insert one record in aspnet_Users at a time.
SELECT @UserType=UserTypeFROM SiteData.dbo.UM_User_Access_Type UM, SiteData.dbo.Users U, aspnet_Users AU, aspnet_Roles AR, aspnet_UsersInRoles AUIRWHERE U.Username=@UserName and U.ID = UM.Student_ID and U.Username = AU.UserName and AU.UserID = AUIR.UserID and AUIR.RoleID = AR.RoleID -- if usertype is a SuperAdmin as determined from SiteData.dbo.UM_User_Access_Type, we insert the appropriate RoleID in aspnet_UsersInRoles IF (@UserType = 'SuperAdmin') BEGIN UPDATE aspnet_UsersInRole SET RoleID = 'EDA73944-1B67-4DAA-B6CB-792847B6C694' WHERE UserID = @UserID END
IF (@UserType = 'StaffAdmin' or @UserType='Partner') BEGIN UPDATE aspnet_UsersInRole SET RoleID = '7B317FF8-7C3C-4D95-AC44-E27E849D4520' WHERE UserID = @UserID END
IF (@UserType = 'Facilitator') BEGIN UPDATE aspnet_UsersInRole SET RoleID = 'D04FEA9F-35E9-41A5-B062-B9C1524D4266' WHERE UserID = @UserID END
IF (@UserType = 'Student') BEGIN UPDATE aspnet_UsersInRole SET RoleID = '4F94ABB9-1371-4834-95D6-E8364824F484' WHERE UserID = @UserID END ENDGO
However, it's not yet working. I can create the trigger fine (so no syntax problems); however, it's not updating the aspnet_UsersInRoles table.
Any ideas?Thank you very much!-Cody
View 5 Replies
View Related
Feb 7, 2000
I have about 20 remote databases that I need a query/scheduled task to run that generates a .rpt file. That part I can get.
However, I need to get this file to another company (via email or ftp, etc) and make it automated.
For example:
Scheduled task runs twice a month and generates a file that then needs to be emailed to an individual or ftp'd to a ftpsite. Is there a way to do this in SQL 7.0 (or 6.5)?
Thanks for all your help,
Laura
View 2 Replies
View Related
Nov 9, 2001
Challenging Query Problem
I am using SQL Server7 to store many thousands of distinct attributes for a product. Because SQL Server7 has a columns-per-table limitation of 1024, I had to decide between spreading the attributes among multiple tables or try a "link table" approach which would only use two tables. I chose the "link table" approach because I thought it would be a more elegant solution. I have had success using this approach up to the point of doing complex filtering of the records. This is where I need help.
Here is this "link table" approach database schema:
tblAttributes
=====================
attributeid
attributename
tblProductsAdditional
=====================
productsadditionalid
productid
attributeid
attributevalue
Instead of having tables with many columns, I use a single table to list the thousands of attributes. I then use another table to associate an attribute with a product and a attribute value.
The problem i am having is building a filter query that will get products that have multiple attribute values. Example, I want to find all products that have a attribute value of 'AAA' and an attribute value of 'BBB'. Because the attribute values are not in separate columns, a simple AND in a WHERE clause does not work- i get zero records returned. Here is sample SQL:
SELECT DISTINCT ProductItemID
FROM tblProductItemAdditional
WHERE ((AttributeValue = 'AAA') AND (AttributeValue = 'BBB'));
My question to you is- How do I filter the records to get the results I want?
I have started looking in cascading select statements. I am worried about cascading select statements because of performance problems that are associated with many nested subqueries. SQL Server7 also has a nested subquery limitation of 32 (which limits how large my search filter can be- I would like to make a filter using around 100 different values).
Is it possible to do what I want in practical way, or did I come to a "dead-end" in my database design? What should I do?
Thank you!
--Llyal
View 4 Replies
View Related
Feb 6, 1999
Hi,
I tried to create a view on following 4 tables with two different syntaxes. One with ANSI syntax and other with conventional(using WHERE clause). Both SELECT work fine and return same result sets. Views are created in both cases. SELECT from Conventional_View works fine But SELECT from the ANSI_View returns error message without Error Number as :
'DB-Library Process Dead - Connection Broken'
CREATE TABLE dbo.SILT01 (
SILF01A varchar (8) NOT NULL ,
SILF01B varchar (25) NULL ,
SILF01C smallint NULL ,
CONSTRAINT SILT01_PK PRIMARY KEY CLUSTERED
(SILF01A)
)
GO
CREATE TABLE dbo.SILT19 (
SILF23A varchar (8) NOT NULL ,
SILF01A varchar (8) NOT NULL ,
CONSTRAINT aaaaaSILT19_PK PRIMARY KEY CLUSTERED
(
SILF23A,
SILF01A
)
)
GO
CREATE TABLE dbo.SILT20 (
SILF23A varchar (8) NOT NULL ,
SILF15A varchar (8) NOT NULL ,
SILF01A varchar (8) NOT NULL ,
CONSTRAINT aaaaaSILT20_PK PRIMARY KEY CLUSTERED
(
SILF23A,
SILF15A,
SILF01A
)
)
GO
CREATE TABLE dbo.SILT23 (
SILF23A varchar (8) NOT NULL ,
SILF23B varchar (30) NULL ,
SILF23C varchar (40) NULL ,
SILF23D varchar (8) NULL ,
SILF23E varchar (1) NULL ,
SILF23F text NULL ,
SILF23G varchar (25) NULL ,
SILF23H varchar (8) NULL ,
SILF23I varchar (25) NULL ,
SILF23J varchar (25) NULL ,
SILF23K text NULL ,
SILF23L varchar (25) NULL ,
SILF23M varchar (8) NULL ,
SILF23N varchar (25) NULL ,
SILF23O varchar (25) NULL ,
SILF23P text NULL ,
SILF23Q varchar (25) NULL ,
SILF23R varchar (8) NULL ,
SILF23S varchar (25) NULL ,
SILF23T varchar (25) NULL ,
SILF23U varchar (15) NULL ,
SILF23V varchar (15) NULL ,
SILF23W varchar (15) NULL ,
SILF23X varchar (15) NULL ,
SILF23Y varchar (15) NULL ,
SILF23Z varchar (15) NULL ,
SILF23A1 varchar (15) NULL ,
SILF23A2 varchar (15) NULL ,
SILF23A3 varchar (15) NULL ,
SILF23A4 varchar (15) NULL ,
SILF23A5 varchar (15) NULL ,
SILF23A6 varchar (3) NULL ,
CONSTRAINT aaaaaSILT23_PK PRIMARY KEY CLUSTERED
(
SILF23A
)
)
GO
create view ANSI_View as
SELECT DISTINCT SILT01.SILF01A, SILT23.SILF23A,
SILT23.SILF23B, SILT20.SILF15A
FROM ((SILT01 INNER JOIN SILT19 ON SILT01.SILF01A = SILT19.SILF01A)
INNER JOIN SILT23 ON SILT19.SILF23A = SILT23.SILF23A)
LEFT JOIN SILT20 ON (SILT19.SILF01A = SILT20.SILF01A) AND
(SILT19.SILF23A = SILT20.SILF23A)
create view Conventional_View as
SELECT DISTINCT SILT01.SILF01A, SILT23.SILF23A,
SILT23.SILF23B, SILT20.SILF15A
FROM SILT01, SILT19,SILT23, SILT20
WHERE ( SILT01.SILF01A = SILT19.SILF01A AND
SILT19.SILF23A = SILT23.SILF23A ) AND
( ( SILT19.SILF01A *= SILT20.SILF01A) AND
(SILT19.SILF23A *= SILT20.SILF23A) )
SELECT * FROM ANSI_VIEW
'DB-Library Process Dead - Connection Broken'
SELECT * FROM Conventional_View (Works fine)
Expecting Help or Suggestions from experts.
Thanx,
Gunvant Patil( gunvantp@mail.usa.com )
View 1 Replies
View Related
May 10, 2007
I am new to Reporting Services. I built a tabular report using report build wizard, comprise 10 columns, including a year column from year1 to year10.
Now my client requests 3 of the columns need to display different value accordingly:
From year 1- 5, display the field value from data source,
From year 6 onward, display zero value, which there is value exist in data source.
The subtotal calculated column 10 applies different formula as well:
From year 1-5, sum (column 3, 4, 6)
From year 6 onward column 10 less sum (column 3, 4)
Could anyone help to shed some light for how to overcome this challenge?
Many thanks in advance,
Liwei
from Australia
View 1 Replies
View Related
Jul 19, 2006
Hi all,
I need to search the field containing the word I enter, but this word is bracketed by "{{" and "}}". For example, I would like to search the keyword apple, I need to search by the following sql statement.
select name from table where name likes '%{{apple}}%'
But the case is that that column may contain some space between the bracket and the keyword. i.e. {{ apple }}. How can I write a sql statement to search name from the keyword?
Thanks
Spencer
View 6 Replies
View Related
Jan 10, 2008
Here is the table A:
Week Year Name
1 2007 x
2 2007 b
3 2007 d
4 2007 d
5 2007 d
6 2007 d
7 2007 d
1 2008 h
This table goes all the way to week 52. I have only 2007 and 2008. so when the user pass start week :1 end week:4 start year 2007 end year 2007
The report will come out something like this
Week Year Name
1 07
2 07
3 07
4 07
Here is the select statement for that
Select week,year,name from dbo.A
Where Week between @startWeek and @endWeek in this case 1 and 4
The real challenge is if the user enters the following input
start week :49 end week:1 start year 2007 end year 2008
in the case the report should look something like this
Week Year Name
49 07
50 07
51 07
52 07
1 08
How can I accomplish such thing in my select statement? I did create a function to handle such scenario but it did not work out.
Any thoughts!
Thanks
View 9 Replies
View Related
May 22, 2007
I have two servers running the same report server project with the same webapplication front end.
Both instances of SQL are on SP2.
When I drilldown in the report viewer on server A, and click next page it returns to the parent report. (which obviously it should go to the next page of the current report) Sometimes this odd behavior happens and sometimes it doesn't.
However on server B, everything works fine.
Note: the installation on server A went a littly crazy. Am I missing a service pack that would have fixed this?
View 1 Replies
View Related
May 9, 2007
When runing a report in Report Manager I can click on an item and SQL Server provides the logic to drill down into the underlying tables. I can find no button to go back. The only way I've found to go back is to click a different tab at the top of the screen (History or something) then click View again and you get the original report. In designer there's a back button. Is there not such a button when running a saved report????
Thanks.
View 1 Replies
View Related
Jun 14, 2007
Hello,
I have a drilldown matrix report similar to the one below...state - drilldowns to sales person which lists the sales for a particular product...in each state there are usually more than 10 names, however i would only like to see the TOP 10 name appear for the sale off that product
My SQL is as follows
SELECT Sales,Consultant, ProductNo, State, Sum(Value) As SumOfValue,
From tblSales
where productNo = 2000
ORDER BY SumOfValue Desc;
If I put in the select TOP 10 it returns only the top 10 for all the states however i need the top 10 off EACH state, is there a way to filter this..or better yet a sort button which will show the top 10 only.
Sales for product
NSW charlie brown 25
bob snow 20
william tell 10
....50 sales reps..etc
WA Charles manson 34
fiona apple 20
peter 1 ....more sales reps etc
View 2 Replies
View Related
Jan 28, 2008
Hi
Im having trouble again with a matrix report.
I wish to make a matrix report with 5 columns. The right part of course will be filled depending on how many moths the user has chosen. The left part will be field with country, city and then the person names and some other info.
What I want to do is to have the first 2 columns, country and city, with drilldown. That is pushing the plus sign to open the corresponding group. How do I do this?
Thanks in advanced
Kind Regards
View 4 Replies
View Related
Oct 22, 2007
Im having a silly question, but im not able to find a solution for this. Im having a simple tabular report in which i have 3 groups defined and a detailed row. It is a drill down report wherein we need click on each group to get the next group and so on. When i went thro the wizard i was able to get that, so i tried myself without the wizard by adding the table and groups manually. However i found for the details grouping i was not able to set the "Visibility can be toggled by another report Item" because it was disabled, but in the report which i generated thro. the wizard it was selected and the last group item was given in that. Im not able to find why the checkbox is disabled when i create the groups manually. Please Help.
View 6 Replies
View Related
Oct 4, 2001
Challenging Date Spans Problem:
A health insurance plan stores information about covered members in the following table:
CREATE TABLE enrollment
(
ssn CHAR(9) NOT NULL,
startdate DATETIME NOT NULL,
enddate DATETIME NOT NULL,
policy INTEGER NOT NULL,
CONSTRAINT pk_enrollment PRIMARY KEY CLUSTERED (ssn, startdate) ,
CONSTRAINT ck_noreversedate CHECK (enddate>=startdate))
INSERT enrollment
VALUES (111111111, 2000-01-01, 2000-04-30, 1)
INSERT enrollment
VALUES (111111111, 2000-06-01, 2001-12-31, 1)
INSERT enrollment
VALUES (222222222, 2000-01-01, 2000-06-30, 1)
INSERT enrollment
VALUES (222222222, 2000-07-01, 2001-12-31, 2)
INSERT enrollment
VALUES (333333333, 2000-01-01, 2000-06-30, 1)
INSERT enrollment
VALUES (333333333, 2000-07-01, 2001-12-31, 1)
INSERT enrollment
VALUES (444444444, 2000-01-01, 2000-06-30, 1)
INSERT enrollment
VALUES (444444444, 2000-07-01, 2000-11-30, 1)
INSERT enrollment
VALUES (444444444, 2001-03-01, 2001-06-30, 1)
INSERT enrollment
VALUES (444444444, 2001-07-01, 2001-12-31, 1)
Assume that there are no overlapping enrollments. We are able to enforce this at the time the records are inserted (e.g., through the front-end application).
Members can have multiple enrollments. These enrollments can be adjacent (e.g, member 222-22-2222 has one enrollment which terminates on 06/30/2000 and another that begins on 07/01/2000) or there can be gaps (e.g., member 111-11-1111 has one enrollment which terminates on 04/30/2000, the subsequent enrollment begins 06/01/2000).
Our task is to write a SELECT statement that will coalesce all adjacent enrollments where the policy is the same. E.g., member 333-33-3333 has two records in the source table, these records would be combined into a single enrollment with start date = 01/01/2000 and end date = 12/31/2001.
SELECT e.ssn, e.policy, MIN(e.startdate) AS startdate, MAX(e.enddate) AS enddate FROM enrollment e GROUP BY e.ssn, e.policy WHERE EXISTS (SELECT * FROM enrollment e2 WHERE e.ssn = e2.ssn AND e.policy = e2.policy AND e.startdate = DATEADD(y,1,e2.enddate)) OR EXISTS (SELECT * FROM enrollment e3 WHERE e.ssn = e3.ssn AND e.policy = e3.policy AND e.enddate = DATEADD(y,-1,e3.startdate))
UNION
SELECT e4.ssn, e4.policy, e4.startdate AS startdate, e4.enddate FROM enrollment e4 WHERE NOT EXISTS (SELECT * FROM enrollment e5 WHERE e4.ssn = e5.ssn AND e4.policy = e5.policy AND e4.startdate = DATEADD(y,1,e5.enddate)) AND NOT EXISTS (SELECT * FROM enrollment e6 WHERE e4.ssn = e6.ssn AND e4.policy = e6.policy AND e4.enddate = DATEADD(y,-1,e6.startdate))
This works for member 333-33-3333 who has only one set of adjacent enrollments.
However it fails for member 444-44-4444 who has two sets of adjacent enrollments. Can anyone suggest an approach that would return two rows for 444-44-4444, one with start date 01/01/2000 and end date 11/30/2000 and another with start date 03/01/2001 and end date 12/31/2001?
View 2 Replies
View Related
Dec 8, 2004
Hi,
I have data coming in from our OLTP's view which has the following sales related information:
1) Date of Sale
2) Product Sold
3) Customer to which the Product was sold
4) QTy. sold
5) Total Sale Amount
I am bringing in this information into my "staging area" and from there I am building my Customer, Product, and Time Dimension and obviously my FACT table. The measures in my fact table were TotalSalesand Total QtySold and everything was working fine and I was able to analyze, slice, dice my cube in many ways...
Now the problem:
I also have planning related data available from another OLTP based view which contains the following information:
1) Plan Year
2) Plan Month (using Plan Year and Plan month,I "derived" a date which for any month was the 1st of that month)
3) Planned Product
4) Planned Sale (Amount)
5) Planned Qty (for sale)
You would have noticed that planning is NOT done at "Customer Level" and the planning data is only for product and time level (and that too only upto month level).
After populating my FAct table from the Sales data, I appended the planning related data into my fact table hoping that as long as I do not select any thing in the Customer dimesion and only go as low as the month level on my Time dimension, I will be able to see all of the following (measures) for any/all products:
Planned Sale, Actual Sale, Planned Qty., Actual Qty,
However I am unable to get anything displayed in the Planned Sale and Planned Qty despite applying all of the little knowledge that Ihave so far in the world of BI and making data marts
....
I really hope that my questions and requirement is clear.Can someone please help me get to
the solution.
Many many TIA.
View 1 Replies
View Related
