Syntax For Report - Hierarchy
Feb 21, 2008
hi all
I originally posted this in November last year and thought we had it sorted but it appears still to give an incorrect result.
I have been working on other work and have not really had the time to look into properly. So now here I am again
I will not use my original post as I think I also didn't explain properly what the issue was so I will try again.
Web application
1 field = application
1 field = type
each ticket must have 1 application and 1 type
BUT may have 2 application or 3 application (linked to that one field)
BUT may have 2 type or 3 type (linked to that one field)
so could show like this:
application.....office issues application2.......empty application3 .....empty
type .....general type2...empty type3........empty
OR
application.....hardware application2.......drive application3.....empty
type.....fault type2.....servername
OR
application ....software ...........application 2 .......server application 3 ........fault
type .........alert ..............type 2 ..........heartbeat ............type 3 ......servername
in the datatabase
application table - 1 to many to the ticket table
office issues
hardware
drive
software
server
fault
type table - 1 to many to the ticket table
general
fault
servername
alert
heartbeat
servername
ticket table
ticket no
applicationid
typeid
when I do a report using ssrs 2005
I want to do a report on ticket by application (with it showing the app in the right hierachy order)
I tried with a function doing the sort first then with a view
but it seems it is still out of order (happy to post if this is the way people think I should go)
or is there a better way to do this?
thanks
jewel
View 1 Replies
ADVERTISEMENT
Jan 28, 2008
Hi SSRS Experts
I'm trying to create a report using a SAP BI InfoCube as my datasource.
One of the fields in the InfoCube (GL Account) consist of a 3 level hierarchy, which need to be reported on.
The query that I built using QueryBuilder executes fine and displays the hierarchies correctly, but when I preview the report, i get the following error:
An error occurred during local report processing
An error has occurred during report processing
Query execution failed for data set "SAPBWD"
The "0GL_ACCOUNT 1000IE" Object was not found.
0GL_ACCOUNT is the GL account field to report on
1000IE is the selected GL Account Hierarchy to be displayed
Here's the query that was generated using query builder:
SELECT NON EMPTY { [Measures].[D2A3Y7ANS9YOF2FONXMUS9RAI] } ON COLUMNS, NON EMPTY {[0GL_ACCOUNT 1000IE].[LEVEL03].ALLMEMBERS * [ZF_MC].[LEVEL01].ALLMEMBERS } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [ZFGL_M03/ZREP_BOR001] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING SAP VARIABLES [ZFV_FY] INCLUDING [0FISCYEAR].[Z12007] [ZFV_PC] INCLUDING [0PROFIT_CTR 1000FS].[10000001000033] [ZFV_RM] INCLUDING [0FISCPER3].[005] [ZFV_PV] INCLUDING [0VERSION].[003]
I hope you would be able to assist.
Kind Regards
Utian Goliath
View 1 Replies
View Related
Apr 30, 2006
Hi,
I restored a wrong copy of a db, since then I that restored the correct
version. during the execution of the reports in report services I get an
error that read *** 'reportServerTempdb.dbo.persitedstream' ** this message
is sparatic, at time the reports are displayed other time I get that error,
the db backup was done an 2005, after I restored the latest version of the db
I reapplied RP services sp2 and I still get the error. Please if this is a
setting that I am missing or what I screwed up.
Thanks in advance.
View 4 Replies
View Related
Sep 12, 2007
Hi,
I would like to know how I can indicate a NULL value in a report expression in SSRS / Report Designer.
I am trying to code :
IIF(Value_A = 0, <NULL>, Value_A)
It may look weird but I am trying to return NULL values when Value_A is 0 (zero), in the sample scenario above.
I have tried using the keyword "NULL", but it is highlighted as a syntax error, and suggested to use System.DBNull. I tried it and then it says that components of the System collection cannot be used in an expression, so I am left drawing blanks.
Thanks.
regards,
Kenny
View 4 Replies
View Related
Apr 27, 2008
Hi,
I am having trouble getting the following query to execute in the report builder:
OPEN Symmetric KEY DemoKey Decryption BY Certificate DemoCertificate;
SELECT
PIN,
EffDate,
Cast(DecryptByKey(Address1) AS varchar(200)) AS Address1,
Cast(DecryptByKey(Address2) AS varchar(200)) AS Address2,
Cast(DecryptByKey(Address3) AS varchar(200)) AS Address3,
Cast(DecryptByKey(City) AS varchar(125)) AS City,
Cast(DecryptByKey(State) AS varchar(125)) AS State,
Cast(DecryptByKey(ZipCode) AS varchar(90)) AS ZipCode,
Cast(DecryptByKey(Country) AS varchar(125)) AS Country
FROM Address
CLOSE Symmetric KEY DemoKey;
I am getting the following error when I try to generate a report in the report builder:
Semantic query execution failed. Incorrect syntax near the keyword 'Open'.
Incorrect syntax near ')'.
----------------------------
Query execution failed for data set 'dataSet'.
----------------------------
An error has occurred during report processing.
I was getting a similar error when I tried to enter the query using the Named Query window of the dsv file. However, I was able to overcome this obstacle by entering the query directly into the code of the dsv file and it works perfectly. That is, when I click on "Explore Data" in the dsv design view the data is retrieved from the database and is decrypted properly.
It appears that the report builder's semantic query engine does not accept transact-sql statements that involve data decryption. Does anybody know if this is true? Or is there some workaround for this situation? Any help would be greatly appreciated.
Thanks,
Mark
View 5 Replies
View Related
Sep 3, 2007
Hi
I get the following error when running a report in report builder using the Adventureworks sample model:
Semantic query execution failed. Incorrect syntax near 'NULLAND'.----------------------------Query execution failed for data set 'dataSet'.----------------------------An error has occurred during report processing.
Here is how to replicate the error:
Create a table report using the Adventure Works model.
Select Product entity, add Product Category and #Products to the table.
Edit formula for #Products to "COUNT(Products)" and add a filter on Products for "Discontinued Date is empty". (I want a count of products that have not been discontinued).
If you run the report now, it willl work as expected.
Add a report filter for "Product.Color is empty" (I only want to see products that don't have a color)
Run the report to get the above error.
While the above is a contrived example, I am getting the same error on a data model that I am developing for a customer.
Am I missing something, or is this a bug in Report Builder?
Thanks
View 2 Replies
View Related
May 20, 2008
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
View 7 Replies
View Related
Dec 14, 2003
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
View 3 Replies
View Related
Mar 31, 2008
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
View 4 Replies
View Related
Sep 23, 2007
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
View 5 Replies
View Related
May 8, 2006
HI!
I am new in DB so I need some advices for finding the right solution.
I need to be able to make automatically any join between tables which the user choose and deliver the result.
is like making an hierarchy between all tables in DB (parent-child) and then making the select statement for the right join.
if u have any idea about how can I manage this, pls help me.
thanks a lot!
View 2 Replies
View Related
Feb 26, 2008
I have an Interesting situation that I'm hoping some of you experts can help me with basically I have the following hierarchy:
Net Profit
.....Gross Profit
..........Revenue
..........Direct Costs
.....Indirect Expense
Now, I have another hierarchy - separate from the above - that I need to assimilate to the hierarchy above - Example:
Expenses
.....Node a
..........Child a (Indirect Expense Type)
.....Child b (Direct Cost type)
Net Sales Adjustments
.....Child a (Revenue type)
.....Child b (Revenue type)
Depending on the type, the top most node needs to be assigned to the appropriate node in the initial hierarcy, like this:
Net Profit
....Gross Profit
........Revenue
................Net Sales Adjustments
....................Child a (Revenue type)
....................Child b (Revenue type)
........Direct Costs
...........Expenses
.................Child b (Direct Cost type)
........Indirect Expense
............Expenses
.................Node a
.....................Child a (Indirect Expense Type)
Now, I've been able to figure out how to assign the top most node and leafs if all the children have the same type (using the expan stored proc listed in the books online), but my question is on this portion:
Expenses
.....Node a
...........Child a (Indirect Expense Type)
.....Child b (Direct Cost type)
Basically, the answer is to work backwards - if a child has a type that is different than the other children, a copy of the hierarch (up to the child) needs to be made and assigned to the appropriate initial node.
I've tried modifying the expand stored proc to give me the lineage of the child and see if there's a way I can copy the node and place it appropriately - I'm brainfried at this point, and I'm hoping that someone outthere can point me in the right direction.
Thanks in advance for your time
View 6 Replies
View Related
Mar 7, 2008
Hai everyone.,
i need to get solution for tree hierarchy in sql is there any solution or any keyword like 'connect by prior' in oracle ..
plz help me on this..
for example:
id | FName |parentid |
1 | sandy |
2 | robert| 1
if i give the parentid 1 in where condition of a query i need the details of 'sandy'
Thanks in Advance.
B.Arul.
View 2 Replies
View Related
Jul 10, 2006
Hello!I have a table that looks like this:Col1; Col2; Col3; Col4; Col538; 75; 233; 916; 277038; 75; 233; 916; 277138; 75; 233; 916; 277238; 75; 233; 923; 265438; 75; 233; 923; 265538; 75; 245; 913; 245438; 75; 245; 913; 2456....And I need a query (not a procedure) that shows me this:38; NULL; NULL; NULL; NULLNULL; 75; NULL; NULL; NULLNULL; NULL; 233; NULL; NULLNULL; NULL; NULL; 916; NULLNULL; NULL; NULL; NULL; 2770NULL; NULL; NULL; NULL; 2771NULL; NULL; NULL; NULL; 2772NULL; NULL; NULL; 923; NULLNULL; NULL; NULL; NULL; 2654NULL; NULL; NULL; NULL; 2655NULL; NULL; 245; NULL; NULLNULL; NULL; NULL; 913; NULLNULL; NULL; NULL; NULL; 2454NULL; NULL; NULL; NULL; 2456....Does anybody know how i can get this result? How?Help! Thank you!SQLNullps: SQL-Server 2000
View 3 Replies
View Related
May 24, 2006
Hi to All!
Is there a perfect method to implement a hierarchy structure with different types as a table in Sql server 2005? Currently I am thinking of this way:
[Node | ParentId | ParentType | ChildId | ChildType]
But there is this nagging little voice saying it can be better
Cheers!
Nele
View 5 Replies
View Related
Mar 9, 2004
Hi, I'm trying to figure out how to write I SP on the following table that will produce the results show at the bottom. My main problem is the sorting, is there a way I loop through the records in TSQL and ORDER BY SortSort where the ParentID's are the same?
Thank you.
Table example
IDParentIDSortOrderCaption
1null1Main
211Files
313Folders
422File2
512Holders
621File1
752Holder2
851Holder1
Required result example
Main
-Files
--File1
--File2
-Holders
--Holder1
--Holder2
-Folders
View 3 Replies
View Related
Apr 28, 2008
Hi
i tried on CTE but iam not gettng correct result plz help me
Declare @TMaster Table (MasterId int, Type varchar(100), ParentMasterId int)
Insert into @Tmaster
Select 1, 'Fixed', 14 Union All
Select 2, 'Flexible',14 union All
Select 3, 'Others',14 union All
Select 13, 'Retirement benefits',1 union All
Select 14, 'PBHeads', 0
Select * From @Tmaster
Declare @TDetails Table ( MasterId Int, Description varchar(100))
Insert into @TDetails
Select 1, 'Basic' union all
Select 1,'Conveyance' union all
Select 1,'HRA' union all
Select 1,'Special Allowance' union all
Select 2, 'Children Education' union all
Select 2,'Travel Allowance' union all
Select 2,'Medical Expenses' union all
Select 2,'Variable Spl Pay' union all
Select 3, 'Project Allowance' union all
Select 3,'Quarterly Incentive' union all
Select 3,'Shift Allowance' union all
Select 3,'Annual Component' union all
Select 13,'Provident Fund' union all
Select 13,'Gratuity' union all
Select 13,'Super annuation'
Select * From @TDetails
in group list we have to show fixed, flexible, other
in subgroups(L1) list under fixed we have to show
Basic salary, HRA, Conveyance, Special Allowance,Retirement benefits
in subgroup(L2) we have to show Gratuity, providedfund, Super annuation they are subgroups of retirementbenefits
output :
Group Subgroup(L1) Subgroup(L2)
Fixed
Basic salary
HRA
Conveyance
Special Allowance
Retirement benefits
Provident Fund
Gratuity
Super annuation
Flexible reimbursements
LTA/Group Travel
Medical expenses
Children education
variable special pay
Others
Project allowance
Performance incentive
Medical insurance
View 12 Replies
View Related
Aug 7, 2007
Hi all,
Ive got a bit of a problem,
I have two tables:
CATEGORY
id
name
parent_CATEGORY_id
RECORD
id
CATEGORY_id
Stock_Held_Number
ps. Ive stripped out any non relevant fields
I also have the following query (again ive stripped out the non-relevant fields)
SELECT TOP (100) PERCENT SUM(dbo.RECORD.Stock_Held_Number) AS TotalStock, CATEGORY.Name AS FundName
FROM CATEGORY
LEFT OUTER JOIN dbo.RECORD ON CATEGORY.ID = dbo.RECORD.CATEGORY_id
GROUP BY CATEGORY.Name, RECORD.Stock_Held_Number
At the moment its grouping all the CATEGORIES and giving me a sum total for each which is great.
The problem I have is the CATEGORY table, there is an optional join to parent CATEGORY records on the table.
What Im trying to do is to provide a fully ordered result within the CATEGORIES and I don't have a clue.
For example:
The CATEGORY table has the following values
ID Name Parent_Category_id
1 Pens
2 Animals
3 Rocks
4 Horses 2
5 Dogs 2
When I currently run the query I get:
Name TotalStock
Pens 20
Animals 30
Rocks 40
Horses 50
Dogs 60
It's technically correct because I don't want the parent to calculate the total value of the children
What Im really trying to do is order them within the hierarchy though and indent (if possible) the result so I would get
Name TotalStock
Animals 30
---Dogs 60
---Horses 50
Pens 20
Rocks 40
Does anybody have any pointers as to how I can achieve this.
Many thanks
Mike
View 3 Replies
View Related
Feb 21, 2008
how do i create a parameter in hierarchy?
SAMPLE
billing period:
YEAR 2007
1st quarter
jan
feb
mar
2nd quarter
april
may
june
something like that.... if the user chose the 1st quarter the report will generate a sum-up report for the 1st quarter of 2007...
kindly reply asap.. i really need to get this report working..
thanks!
View 5 Replies
View Related
Nov 2, 2007
I have structure:FolderId, FolderName, ParentFolderId, FullPathe.g.1, First, null, First2, Sec, 1, First/Sec.....When update happens to FolderName, i need to update all FullPaths thatare below Folder which is changing.Any ideas?Thank you.
View 3 Replies
View Related
Jul 20, 2005
Greetings!I could really use some suggestions on how to improve on thefollowing, it at all possible:Table 'Customer'---------------------ID GUID PK....Table 'Facility'-----------------ID GUID PKCustomerID GUID FK (FK to Customer GUID)....Table 'Rate'----------------ID PKOwnerID GUID Nullable FK (FK to Customer, Facility GUID PK)OwnerLevel INT Contraint 1-3<Rate Data>Table 'Rate' is a sparse hierarchy of data. There are 3 possiblelevels in the hierarchy as follows:OwnerID <NULL>OwnerLevel 1This indicates Global rate data.OwnerID <Customer.ID>OwnerLevel 2This indicates Customer-specific rate data.OwnerID <Facility.ID>OwnerLevel 3This indicates Facility-specific rate data.Now, a given Customer need not have an entry in the Rate table. If aCustomer does not have an entry, it is supposed to 'inherit' Globalrate data. A given Facility need not have an entry in the Rate table.If a Facility does not have an entry, it is supposed to inheritCustomer-specific rate data, and in the absence of an entry for theFacility's parent Customer, it is supposed to inherit Global ratedata.The challenge is that I want to write a view to give me back theappropriate rate record for Customer and Facility. Here's what I'vedone so far.View _Rate--------------SELECTRate.*,NULL AS TargetIDFROMRateWHERERate.OwnerID IS NULLUNIONSELECTRate.*,Customer.ID AS TargetIDFROMRateCROSS JOINCustomerWHERERate.OwnerID IS NULLOR Rate.OwnerID = Customer.IDUNIONSELECTRate.*,Facility.ID AS TargetIDFROMRateCROSS JOINFacilityWHERERate.OwnerID IS NULLOR Rate.OwnerID IN (Facility.CustomerID, Facility.ID)View view_Rate--------------------SELECT_Rate.*FROM_RateINNER JOIN(SELECTTargetID,MAX(OwnerLevel) AS OwnerLevelFROM_RateGROUP BYTargetID) AS Filtered_RateON_Rate.TargetID = Filtered_Rate.TargetIDAND _Rate.OwnerLevel = Filtered_Rate.OwnerLevelThe combination of these two views gives a resultset that contains 1record for every Target ID as follows:TargetID <NULL>OwnerID <NULL>OwnerLevel 1This indicates Global rate data established at the Global level.TargetID <Customer.ID>OwnerID <NULL>OwnerLevel 1This indicates Customer rate data for the specific Customer identifiedby Customer.ID is inherited from the Global rate data.TargetID <Customer.ID>OwnerID <Customer.ID>OwnerLevel 2This indicates Customer-specific rate data for the specific Customeridentified by Customer.ID (not inherited).TargetID <Facility.ID>OwnerID <NULL>OwnerLevel 3This indicates Facility rate data is inherited from the Global ratedata.TargetID <Facility.ID>OwnerID <Customer.ID>OwnerLevel 2This indicates Facility rate data for the specific Facility identifiedby Facility.ID is inherited from the Facility's parent Customer'sCustomer-specific rate data.TargetID <Facility.ID>OwnerID <Facility.ID>OwnerLevel 3This indicates Facility-specific rate data for the specific Facilityidentified by Facility.ID (not inherited).I know this is a lengthy post, and a complicted query scenario, butI'm not willing to accept that my solution is the best solution justyet. Please consider that I really need this functionality in a VIEWas much as possible.Thank you for your learned consideration.I eagerly await your replies.Darryll
View 2 Replies
View Related
Dec 21, 2007
Hi everybody,
I've been reading the book MCITP Self-Paced Training Kit (Exam 70-441): Designing Database Solutions by Using Microsoft SQL Server(TM) 2005 (Self-Paced Training Kits) as a part of my preparation for exam 70-441
The book is really clear, and I could understand everything easily because I've some experience as a database developer.
Until now, there's only one thing that I didn't fully understand:
On pages 53 and 54, it mentions a type of hierarchy called: a graph hierarchy, and it gives a small example made the things even more complicated
I've many books about database design theories, but they don't use the term (graph hierarchy) any where...
So, I appreciate if you can point me to an online resource or a book that discusses this term thorougly.
Thank you
View 3 Replies
View Related
Jul 13, 2006
What are some strategies in categorizing items?
View 4 Replies
View Related
Sep 6, 2007
Hello experts,
Last time I worked with SSAS and build a Cube. Because I€™m now very happy with the front-end excel 2003 or excel 2007 I thought I build my own Report with SSRS.
Now there is something I don€™t understand:
I build a Hierarchy in SSAS that a want to use in SSRS. Is there a chance to use it without any features? Have I to use parameters or something another?
In my opinion it makes any sense to build a hierarchy new, because it€™s already exists in SSAS.
Have a nice day
Alex
View 5 Replies
View Related
Dec 5, 2007
Hello ,
I'm facing a complicated problem and I don't think that the solution will be an easy one.
I have an SQL statement in Oracle which I need to translate it in Sql Server 2005.
select lpad(' ',5*(orderid)) || to_char(descr) as menui
from <table _name>
where MENU_ITEM not in ('test1','test2','test3') and item_parent not in ('test4,'test5,'test6')
start with <item_parent='item_parent' >
connect by prior <menu_item = item_parent and menu_name='something'; > ?(condition)
Somewhere I have read that SQL server does not support Hierarchical Sql statements. Is this true ? How am I going to do that ?
Any help will be appreciated.
Thank you
View 7 Replies
View Related
Dec 12, 2007
How can I get the data from a table that stores hierarchical information keeping the right order and hierarchy levels?
I also need to specify a first level....let me explain.
Table: Sites
Id (PK)
Title
Url
ParentId
If I have something like this:
1 | Test | http://localhost | NULL
2 | Sub-Test | http://localhost/subtest | 1
3 | SecondItem | http://anotherServer | NULL
4 | ChildOfSub-Test | http://localhost/subtest/child | 2
And I specify that I want all child items below the Id 2, it must return:
Id | Title | Url | ParentId | Depth
2 | Sub-Test | http://localhost/subtest | 1 | 0
4 | ChildOfSub-Test | http://localhost/subtest/child | 2 | 1
How can I do this? I've saw some examples with CTEs, but they always return all of the items.
View 7 Replies
View Related
Nov 23, 2007
Hi people
In have a query over two tables, and the ouptut is:
RowID | SubMarcaID | MarcaID | ProductoID
1 | 1 | 1 | 1
2 | 1 | 1 | 2
3 | 1 | 1 | NULL
4 | 3 | 1 | 3
5 | 3 | 1 | NULL
(SubMarcaID and MarcaID are the KEYS from SUBMARCA, and ProductoID is the key from PRODUCTO. Also SubMarcaID and MarcaID are foreing keys on PRODUCTO)
Now, I need a ParentRowID based on SubMarcaID and MarcaID, like a hierarchy
RowID | SubMarcaID | MarcaID | ProductoID | ParentRowID
1 | 1 | 1 | 1 | 3
2 | 1 | 1 | 2 | 3
3 | 1 | 1 | NULL | 0
4 | 3 | 1 | 3 | 5
5 | 3 | 1 | NULL | 0
How can I do this?
Code Block
SELECT ROW_NUMBER() OVER (ORDER BY PRODUCTO.MarcaID, PRODUCTO.SubMarcaID) AS RowID,
PRODUCTO.SubMarcaID,
PRODUCTO.MarcaID,
PRODUCTO.ProductoID
FROM PRODUCTO, SGP_SUBMARCA
WHERE PRODUCTO.SubMarcaID = SUBMARCA.SubMarcaID
AND PRODUCTO.MarcaID = SUBMARCA.MarcaID
GROUP
BY PRODUCTO.SubMarcaID,
PRODUCTO.MarcaID,
PRODUCTO.ProductoID
WITH
ROLLUP
HAVING (
GROUPING(PRODUCTO.ProductoID) = 1
AND GROUPING(PRODUCTO.SubMarcaID) = 0
AND GROUPING(PRODUCTO.MarcaID) = 0
)
OR (
GROUPING(PRODUCTO.SubMarcaID) = 0
AND GROUPING(PRODUCTO.MarcaID) = 0
AND GROUPING(PRODUCTO.ProductoID) = 0
)
View 1 Replies
View Related
Mar 8, 2007
I have a table with a parent, child, and grandchild relationship. Can anyone help me with a query that will return the child and grandchild of a parent?
Heres my table:id pid name--------------------------------1 0 UntID2 0 Vin Number3 0 Make4 3 Model5 4 Model Number6 0 Model Year7 0 Vehicle Type8 0 Odometer MilesWhen I select 3 as the id I need these results:id pid name--------------------------------3 0 Make4 3 Model5 4 Model Number
Thanks for any help!
Ryan
View 15 Replies
View Related
Sep 17, 2007
For a short time I have considered how to get the hierarchy in a self-linked table, incidentally I have found an article about ‘Recursive CTEs’ in ‘SQL Server Books Online’, where an example is very impressive and arouses me to find a way to get the ancestors or descendants of a given node in a self-linked (self-referenced) table.
And now I want to share this method to anyone who has the same problem to resolve or someone like me who has addiction in SQL.
First of all we have to create a table for the following functions and build some test data. The statemens look like:
create table ST_CATEGORY(
CATEGORYID uniqueidentifier not null default NEWID(),
PARENTID uniqueidentifier,
[NAME] varchar(128),
COMMENT varchar(4096),
CONSTRAINT PK_ST_CATEGORY primary key (CATEGORYID)
)
go
insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(@rootoid, NULL, 'ROOT', 'ROOT NODE')
insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(NEWID(), @rootoid, 'Business Application', 'group for all business applications')
declare @techoid uniqueidentifier
set @techoid = NEWID()
insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(@techoid, @rootoid, 'Tech101', 'technical tips')
insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(NEWID(), @techoid, 'C#', 'C# tips')
go
Now that the test table and data are prepared, we can get ancestors or descendants through the following four stored procedures of a given category.
CREATE PROCEDURE spGetAncestor
@categoryID uniqueidentifier
AS
BEGIN
-- find parents/ancestor
WITH Ancestor( CATEGORYID ) AS
(
SELECT PARENTID
FROM ST_CATEGORY
WHERE CATEGORYID = @categoryID
UNION ALL
SELECT PARENTID
FROM Ancestor, ST_CATEGORY
WHERE Ancestor.CATEGORYID = ST_CATEGORY.CATEGORYID
)
SELECT * FROM Ancestor
END
GO
CREATE PROCEDURE spGetSelfAndAncestor
@categoryID uniqueidentifier
AS
BEGIN
-- find self and parents/ancestor
WITH SelfAndAncestor( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE CATEGORYID = @categoryID
UNION ALL
SELECT PARENTID
FROM SelfAndAncestor, ST_CATEGORY
WHERE SelfAndAncestor.CATEGORYID = ST_CATEGORY.CATEGORYID
)
SELECT * FROM SelfAndAncestor
END
GO
CREATE PROCEDURE spGetDescendant
@categoryID uniqueidentifier
AS
BEGIN
-- find children/descendant
WITH Descendant( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE PARENTID = @categoryID
UNION ALL
SELECT ST_CATEGORY.CATEGORYID
FROM Descendant, ST_CATEGORY
WHERE Descendant.CATEGORYID = ST_CATEGORY.PARENTID
)
SELECT * FROM Descendant
END
GO
CREATE PROCEDURE spGetSelfAndDescendant
@categoryID uniqueidentifier
AS
BEGIN
-- find self and children/descendant
WITH SelfAndDescendant( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE CATEGORYID = @categoryID
UNION ALL
SELECT ST_CATEGORY.CATEGORYID
FROM SelfAndDescendant, ST_CATEGORY
WHERE SelfAndDescendant.CATEGORYID = ST_CATEGORY.PARENTID
)
SELECT * FROM SelfAndDescendant
END
GO
View 1 Replies
View Related
Jun 13, 2008
Ok first I am still very new to SQL so I really do not understand quite a bit (SP's, Sub Query's,CTE, etc..). I also went through the SQLTeam article on trees and hierachies but only confused myself even more.
I will try my best to put all the information that I know of (in the shortest way that I know).....sorry I know this is long :(
SQLExpress2005
SQL Server Management Studio Express
Two Tables
PartMaster
PRTNUM_01.......varchar(30) ////Part Number
PMDES1_01.........varchar(50) ////Primary Description
PMDES2_01.........varchar(50) ////Secondary Description
PRTNUM_01........PMDES1_01............PMDES2_01
100700..............Slide. .................6-inch
102121..............Video Card...........128MB
IPC4235-01........Chassis..............Parts
IPC4235-03........Chassis..............Parts
102128..............Riser................Card
102385..............Mother...............Board
801549..............Bracket,.............Power
801709..............Bracket..............Fan
100773..............Hardware.............Hinge
ProductStructure
PARPRT_02........varchar(30) ////Top Level Part Number
COMPRT_02......varchar(50) ////Component Part Number
EFFDTE_02.........smalldatetime //// Effective Date
QTYPER_02.........float ////// Quantity
PARPRT_02.............COMPRT_02..........EFFDTE_02....QTYPER_02
4235........................100700............2008-08-30.......1
4235........................102121............2007-08-30.......1
4235........................IPC4235-01......2008-05-20.......1
4235........................IPC4235-03......2007-08-30.......1
4235........................IPC4235-03......2008-05-20.......0
IPC4235-01...............102128............2007-11-19.......1
IPC4235-01...............102385............2007-11-19.......1
IPC4235-01...............801549............2007-11-19.......1
IPC4235-01...............801709............2008-04-01........1
IPC4235-01...............801709............2007-05-20........0
IPC4235-03...............100773............2007-09-20........1
--------What I am trying to accomplish is the retrieve the following:
PARPRT_02|COMPRT_02|Des|SubComp|Sub#|SubDes|EFFDTE_02|QTYPER_02
4235|100700|Slide 6-inch|n.a|n.a|n.a|2008-08-30|1
4235|102121|Video Card 128MB|n.a|n.a|n.a|2007-08-30|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|102128|Riser Card|2007-11-19|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|102385|Mother Board|2007-11-19|1
4235|IPC4235-01|ChassisParts|IPC4235-01|801549|Bracket,Power|2007-11-19|1
I have gotten Close but no cigar with the following:
SELECT ps.PARPRT_02,
ps.COMPRT_02,
pm.PMDES1_01 + ' ' + pm.PMDES2_01 as Description,
ISNULL(ps2.PARPRT_02, 'n.a.') AS [Sub Component],
ISNULL(ps2.COMPRT_02, 'n.a.') AS [Part Number of Sub],
ps.EFFDTE_02, ps.QTYPER_02
FROM ProductStructure ps
INNER JOIN
(SELECT COMPRT_02, MAX(EFFDTE_02) AS EFFDTE_02
FROM ProductStructure
WHERE (PARPRT_02 = '4235')
GROUP BY PARPRT_02, COMPRT_02) AS p3
ON ps.COMPRT_02 = p3.COMPRT_02 AND ps.EFFDTE_02 = p3.EFFDTE_02
Inner Join PartMaster AS pm On pm.PRTNUM_01 = ps.COMPRT_02
LEFT JOIN ProductStructure ps2
ON ps2.PARPRT_02 = ps.COMPRT_02
WHERE ps.PARPRT_02 = '4235'
ORDER BY
ps.PARPRT_02, ps.COMPRT_02
Which is returning:
PARPRT_02|COMPRT_02|Description|Sub Component|Part Number of Sub|EFFDTE_02|QTYPER_02
4235|100700|Slide 6-inch|n.a|n.a|2008-08-30|1
4235|102121|Video Card 128MB|n.a|n.a|2007-08-30|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|102128|2008-05-20|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|102385|2008-05-20|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|801549|2008-05-20|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|801709|2008-05-20|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|801709|2008-05-20|1
4235|IPC4235-03|Chassis Parts|IPC4235-03|100773|2008-05-20|0
Line 1 - fine / Line 2 - fine / Line three (need to add description Part number of Sub)(need to keep original) / Line 4 and 5 same as line 3 / Line 6 and Line 7 should not show because the later date has a QTY of zero / Line 9 should not show because date is zero.
I once again appologize for the length and details on this , but this is has been driving me nutts. ANY help will be appreciated.
Thanks
View 8 Replies
View Related
Jan 9, 2014
I need to Return values with hierachy sum.
I have provided the tables with data and result expected below
============
CREATE TABLE
============
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON [PRIMARY]
[Code] ....
The parent data will have either QA or QC
Child will have data in QY.
========================
EXPECTED RESULT
========================
Mode_Info |Mode_Detail| QA| QC|QY
Air||4 | |
Sea|||5|
SEA|SHIP|||15
SEA|BOAT|||25
ROAD|||2 |
ROAD|BUS|||20
ROAD|BIKE|||30
ROAD|CYCLE|||4
ROAD|TRAM|||10
ROAD|CAR|3||
ROAD|BMW|||36
ROAD|AUDI|||84
View 3 Replies
View Related
Feb 5, 2014
I have one table which consists of data in the following manner
Child Parent
ZN1 NS
ZN2 NS
A1 ZN1
A3 Zn1
A2 Zn2
A4 Zn2
IT1 A4
It2 A3
It3 A2
It5 A1
Now i want to display the information in the follwing manner.
Zn1 NS
A1 Zn1
It5 A1
A3 Zn1
It2 A3
Zn2 NS
A2 Zn2
It3 A2
A4 Zn2
IT1 A4
View 2 Replies
View Related
Jul 20, 2014
how to create a hierarchy using SQL. I have a database with ID numbers that are duplicated based on customer preferences. For example, an ID number can fall into multiple categories
ID# 001 in section A
ID# 001 in section B
ID# 001 in section C
The same ID Number is in section A, B and C.The hierarchy is that A is at the top and C is at the bottom. So, if my ID number is in all 3 sections (A, B & C) I only want to count it as falling into section A as that is at the top of the rating scale.
View 1 Replies
View Related
