Hi everybody,
I would like to know how to create a hierarchy in a SSAS project using a SSRS datasource?
All values are displayed when I drag the field "parent" in the MDX generator...
Thanks in advance for any idea...
I am designing a table to represent data in hierarchy structure, I use id and parent id to represent the data in hierarchy form:
Id | parent_id
---+-----------
1 | 0
2 | 0
3 | 0
4 | 1
5 | 1
6 | 4
7 | 4
8 | 7
9 | 7
This structure requires complicated queries (recursive call) to find out all the child of a root node, so I have added another field for the root id.
Is this a good relational database design ? kindly suggest.
Id | parent_id | root_id
---+-----------+---------
1 | 0 |
2 | 0 |
3 | 0 |
4 | 1 | 1
5 | 1 | 1
6 | 4 | 1
7 | 4 | 1
8 | 7 | 1
9 | 7 | 1
10 | 2 | 2
11 | 2 | 2
12 | 10 | 2
13 | 10 | 2
Rgds
Vijay
Hi!
I have a report that uses a parent-child hierarchy in a table. It is hidden with the toggle set to itself, and that works perfectly.
However, instead of having it look like this(simple example):
+ Profit
After the + has been clicked:
-Profit
+Sales
+Cost
I would like it to look like this:
+Sales
+Cost
-Profit
Is this possible with when using a parent-child hierarchy?
http://ssasfreak.spaces.live.com/
Hi Expert,How do I flatten a Parent Child hierarchy to regular flat data: pleaseprovide some SQL code:I have now:Task_ID, Parent_Task_ID, Task_NameLevel11Project Management121Costing231Estimating242Task1352Task2363Task3373Task43I want to have:Level1Level2Level3Project ManagementCostingTask1Project ManagementCostingTask2Project ManagementEstimatingTask3Project ManagementEstimatingTask4Please help, I am stuck!Thanks in advance.Soumya
View 2 Replies View Relatedhow to find the orphaned value from the below parent/child hierarchy Table.
create table dbo.Hier(parent varchar(100), child varchar(100))
insert into Hier
select 'subramanium','Manickam' union all
select 'subramanium','Munuswamy' union all
select 'Munuswamy','senthil' union all
select 'Munuswamy','sasi' union all
select 'Munuswamy','uma' union all
select 'manickam','vijay' union all
select 'manickam','bhavani' union all
select 'manickam','dhanam' union all
select 'uma','varsha'
Delete from Hier where child='uma'
I tried:
select parent from Hier
where parent not in(select Child from Hier)
and parent <> 'subramanium'
Getting resultset as:
parent
======
uma
I need to know whether my select statement is correct or not,if its correct,how to write the same in CTE?
Hello,
I want to enable a drill down on a parent child hierarchy, just as it is possible in the Analysis Services.
An Example :
Hierarchy in a company : Jack (1) is the boss, Frank (2) and Andy (3) ar the Semi-Bosses , and Julia (4), Jane (5), Henry (6), Michael (7), Will (8) , Dave (9) are normal employees . Timmy(10) is a trainee. Furthermore Frank is responsible for Julia,Jane and Henry and Andy is responsible for Michael WIll and Dave. Dave is responsible for Timmy. So we get the following tree hierarchy structure.
1
2 3
4 5 6 7 8 9
10
My Employee Dimension Attributes : Name, Parent
I want to make a report, where I show the salary of each employee. But at first the user only sees 1, when he drills down he sees 2 and 3, too. When he drills down once more he sees 4 5 6 and 7 8 9, too (The user must be able to recognize which person is responsible for 456 and which for 789). And the last drill down shows 10, for which 9 is responsible ( exactly as it is possible in the analyis Service in the Cubebrowser)
I have read some posts where are MDX examples which are similar to my problem, but they did not solve my problem.
I hope somebody can help me
JF
I am working on a query to generate parent child hierarchy from a table.
Table has below records.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
[Code] ...
how to achieve this.l tried with temp tables it doesn't work.
Include children and exclude children in a single hierarchy in parent child dimension in mdx
*12-parent
 **20-parent
 - 9-parent
      --250-child1
      --210-child2
    --240-child3
aggregation of 12-parent only
aggregation of 20-parent only
aggregation of 9 with children
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. Â So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
1 2015-01-01Â 2015-12-31 AAÂ 2015-01-01Â 2015-03-31Â BBÂ 2016-01-01Â 2016-03-31Â CCÂ 2017-01-01Â 2017-03-31Â DDÂ 2017-01-01Â 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date)
declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date)
insert @parent select 1, '1/1/2015', '12/31/2015'
insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
[Code] .....
Basically i have three Tables
Request ID Parent ID Account Name Addresss
1452 1254789 Wendy's Atlanta Georgia
1453 1254789 Wendy's Norcross Georgia
1456 1254789 Waffle House Atlanta Georgia
Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID
45897 Incentive 10 N/A N/A N/A 1452 1254789
45898 Incentive 10 N/A N/A N/A 1453 1254789
45899 Incentive 10 N/A N/A N/A 1456 1254789
Bid_Number Bid_Name Request_ID Parent ID
Q789456 Wendy'Off 1452 1254789
Q789457 Wendy'Reba 1452 1254789
Q789456 Wendy'Off 1453 1254789
Q789457 Wendy'Reba 1453 1254789
Q789456 Wendy'Off 1456 1254789
I want the Result
Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol
1254789 Incentive 10 N/A N/A N/A 1
1254789 Incentive 10 N/A N/A N/A 2
Bid Number AutoGeneratedCol_Link
Q789456 1
Q789457 1
Q789456 2
Request ID AutoGeneratedCol_Link
1452 1
1453 1
1456 2
I have a table with below kind of data,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL
Here I want,
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
Looking for below output ...
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
I have hundreds of rows in Table A, each with an Organization ID, Organization Name, and Parent ID.
There's also an Organization table which contains the Organization mappings (this also has an Organization ID, Organization Name, Parent ID). - The parent ID is basically an Organization ID, referring to the Parent Organization (and this is recursive, so each Parent can have another Parent etc.)
The problem I have is in Table A. I need to create new columns showing the parent hierarchy starting from the Organization Name in the table.
This step is quite easy, simply using a JOIN with the Organization table ON Parent ID works pretty well.
The complication comes here:
-Every Organization in Table A is the lowest level in its hierarchy (i.e. it doesn't have any children).
- Every Organization in Table A has a varying amount of parents (i.e. some might have 1 parent who then has another parent, or some might have 1 parent as the BOSS (top most level))
I need to organise the Table so that each organization can have its parents listed beside (one in each column), and should show NULL if it doesn't have a parent at a certain level. - This bit is difficult to explain, so I've attached an image
So essentially, regardless of how many parents an organization has, the top most parent (BOSS) should always appear under the same column. (image explains this)...
I have a have a relatively simple SSAS cube that I'm trying to display in a matrix but the order of the data is not correct. The rows of the matrix are a time hierarchy and the query designer produces the correct MDX and when I run the MDX in the data tab, the order is correct. However in the report preview tab, the order is incorrect. Specifically, the time hierarchy looks like this Year-Month-FullDate. The report is ordering the Months like this 1,10,11,12,2,3,4,5,6,7,8,9. I ran the MDX query in Sql Studio and the order is correct, and I used Excel to consume the same cube and it produces the correct order.
The report is somehow treating the Month like character data instead of numeric, how do I fix this?
I have a time dimension which has Date, Week, Month and Year. However, the hierarchy will have only Week, Month and Year. It works great for any Sales measure with AggregateFunction as SUM.
I have created a new measure with AggregateFunction = LastNonEmpty. Also in the backend, I have pushed all the inventory data to last date in every month as inventory is always looked on a monthly basis not on a weekly basis. This measure shows correct data for every last week of the month in the hierarchy. However, Months and Years are displayed as zeros.
what I am doing wrong.
Huh?
I've got good RI data...BUT..a developer loaded the tables in alpha table order....
Such that the child loaded BEFORE the parent....
Huh?
Got a test being set up now to mess with the child file to add a key that doesn't exist in the parent...
But Why is this allowed?
In DB2 you can specify
LOAD DATA REPLACE NO CHECK....
On the load card...you then need to run a check after to verify the data...
Is that what's going on? Is there such a utility in SQL Server to run a check post load?
I'm confused....
Any comments appreciated.
Thanks
Brett
8-)
I have two projects inside my solution - a windows project and a reporting services project. I am using my business logic class as my datasource which includes the objects I want to wire up to my reports. Is there anyway to specify that I want to use these objects from my windows project rather than creating a new sharing datasource inside the RS project?
Thanks
hi,
i have two tables i want the identity value of the parent table to be inserted into the chile table
here is my code,but i don't know why it isn't working !
protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string pcontent = TextBox1.Text; string data = TextBox2.Text; addtopic(pcontent,connectionString); addfile(data, connectionString); } public void addtopic(string subject,string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) { SqlCommand command = new SqlCommand("INSERT INTO parent" + "(content)" + "Values(@content)", connection); command.Parameters.Add("@content", SqlDbType.Text).Value = subject; connection.Open(); command.ExecuteNonQuery(); } } public void addchild(string name, string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) {Guid id = Guid.NewGuid(); SqlCommand commandd = new SqlCommand("INSERT INTO child" + "(parentid,data,uniqueid)" + "Values(@@IDENTITY,@data,@uid)", connection); commandd.Parameters.Add("@data", SqlDbType.NVarChar, 50).Value = name; commandd.Parameters.Add("@uid", SqlDbType.UniqueIdentifier).Value = id;
thanks in advance :)
connection.Open(); commandd.ExecuteNonQuery(); }
}
I have a parent/child relationship in a relational database broken out like this:
Table Name: categories[category_id] int (primary_key NOT NULL),[category_name] varchar(50),[parent_fk] int
The parent references the category_id in the same table to create the parent/child relationships. I can get all the bottom level categories by doing this:
select category_id, category, parent_fk from categories where category_id not in ( select parent_fk from categories)
Each bottom-level category has a count attached to it.
The problem I have is getting the counts rolled up for each parent of the bottom level. A parent could/will have multiple bottom-level categories (and counts).
My sql is a little weak, could you help me out? I can utilize everying in SQL 2000 (stored proc, UDF, anything).
Thanks!
Here is the table - Company with fields:
CompanyID, ParentCompanyID (both integers)
Given a CompanyID - I want to get all the children for the Company.
I did similar procedures but somehow, could not get this to work.
I want to find all the child of a node in a tree . A child can have multiple parent i.e 2 can be place under multiple parent . The folling is the data:
ParentID ChildID
-------------------------
10 8
8 6
8 7
7 2
7 5
5 1
5 2
6 2
child of parent if input 10
8
7
6
2
2
5
1
(2) can be neglected
input 7
2
5
1
(2) can be neglected
input 8
7
2
5
(2) can be neglected
Plz help we to write the sql statements
Thanks
In our database we have a list of devices in a "Device" Table, eachhaving one or more IP's located in the "IP" Table linked through aforein key on the DeviceID Column.I would like to retrieve this information as SuchDeviceID IpAddress1 10.0.0.1, 10.0.0.2, 10.0.0.32 ...345etc.Is it possible to do that without using cursors? Through a query?
View 1 Replies View RelatedHello all, I'm having a real hard time trying to figure this one out. I'm trying to create a sql query that selects both the parent name and it's children, but it's got to loop through all the record sets to populate a drop down as an end result.
I think I thought this out correctly:
I have 2 tables
category
relationship
tbl category
cat_id //auto int
cat_name // varchar
relationship
r_id // auto int
parent_id // int
child_id // int
both the parent_id and child_id are associated with the cat_id
in my category table I could have
1cars // this is parent
2 audi
3 bmw
4 chevy
Table data example
r_id parent_id child_id
****************************
1 1 15
2 1 16
3 1 17
4 2 55
5 2 56
etc...
I want to select both the parent cat_name from category and also select the child cat_name where the parent_id = #
I can do it manaully like this
select cat_name, cat_id, parent_id , child_id from category, relationships where child_id = cat_id and parent_id = 1
what is the best way to loop through all the parent ids to find child category?
Could this be done in a stored procedure?
thanks in advance.
I am having problems creating a trigger in SQL Server? I have 2 tables (parent and child) with one to many relationship. When I save a record, one row gets inserted in the parent and one to many gets inserted in the child. The trigger is on the parent table and it is trying to select the number of new records just inserted in the child table that meets a certain criteria. Since the transaction hasn't been committed I can not select the number of records from the child. Does anyone know how to handle this? My manager insists this be done in a trigger.
Thanks, James
Any one have any ideas or links to point me to ???
View 2 Replies View RelatedI would like to create a View (we'll call it FamilyView) using two tables, that I can then query against.
For example:
Parent
{
ID_PK,
Name,
PhoneNum,
Address
}
Child
{
ID_PK,
ParentID_FK,
Name
}
The view would return a dataset like this:
Parent.Name, Parent.PhoneNum, Parent.Address, Child.Name1, Child.Name2, Child.Name3... Child.NameN
William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred
Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven
Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward
with an unknown number of children for each parent.
Then I would like to be able to query against this view with something like this:
SELECT * FROM FamilyView Where Child2 = 'Peter'
I have no idea how to write the SQL for this View. Is it possible?
Is this possible without using a cursor?
Thanks for any advice you all can give me.
Brian
Below is my sample data of my table named "Groups"
Code:
with Groups as (
select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all
select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all
select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all
select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all
select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all
select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup )
select * from groups;
Expected result:
Code:
with ExpectedResult as (
select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all
select '' as GroupName,'peoplesoft' as SubGroup union all
select '' as GroupName,'mysql' as SubGroup union all
select 'Microsoft' as GroupName,'Nokia' as SubGroup union all
select '' as GroupName,'EShop' as SubGroup union all
select 'IBM' as GroupName,'Meiosys' as SubGroup union all
select '' as GroupName,'UrbanCode' as SubGroup )
select * from ExpectedResult;
some sample query to how to achieve this parent-child has the same table.
Hello all gurus out there.
I'm an entre level junior programmer. My question is kind of confusing but I'll try to put it as simple as I can.
First we have a main table called "job1". This table consists the order information. The file_id is the unique id and the primary key for this table. This table also pertains other information such as customer data (max limit 5), job data etc. This table is actively (non-stop) used throughout the day.
We have a non-interactive process which will take customers information from the main table and insert into the child table table "jobcust". Jobcust would have file_id, cust, cust_type. For example, if Job1 table had fiel_id=100 and cust1="Tom" and Cust2="David", now Jobcust will have two records file_id, cust1 and file_id,cust2. The main problem is the child table needs to be updated right away and our non-interactive process is good at doing that.. but it is causing a major DATA LATENCY. I would like to ask you all, if you know any better way of doing this without any process.. like in the back end with a trigger/procedure or something like that.
All you help is appreciated.
Thanks much.
I am importing data from a paradox table and trying to clean it up. I have this query that finds all the child records that are not in the parent table.
Select MemberID
FROM memtype AS a
WHERE NOT EXISTS
(SELECT *
FROM members AS b
WHERE a.MemberID IN (b.MemberID));
Now I'm trying to delete all those child records instead of just selecting them so I tried...
Delete MemberID
FROM memtype AS a
WHERE NOT EXISTS
(SELECT *
FROM members AS b
WHERE a.MemberID IN (b.MemberID));
Sql clearly doesn't like this
I have a parts table which has partid (GUID) column and parentpartId (GUID) column. Need to copy the records to the same table with new GUIDs for partids. How to do that? cursor or temp tables?
View 5 Replies View RelatedI have an application that has an existing query which returns org units (parent and child) from organization table with orderby on createddate + orgid combination.
Also I added another log table Organization_log with exact columns as Organization table and additional 'IS_DELETED' bool column.
WITH Org_TREE AS (
SELECT *, null as 'IS_DELETED', convert (varchar(4000), convert(varchar(30),CREATED_DT,126) + Org_Id) theorderby
FROM Organization WHERE PARENT_Org_ID IS NULL and case_ID='43333'
[code]...
I need to modify the query:
1. To display the records both from the Organization table and Organization_Log table.
2. The orderby should be sorted on 'Organization Name' asc and it should follow the child order in alpha sort as well.
E.g.:
aaa
==>fff
==>ggg
bbb
==> aaa
==> hhh
eee
==> ccc
==> ddd
==> fff
Hello,
I am wondering if there is a way to insert one parent record with multi child records in one transaction? I am using dataset to update my database. I want to use transaction so if one record insert fails all the transctions rollback.
Thanks
Your Input would be greatly appricated.
hello there,
I think I might know the answer to this, but I wanted to see if any one has come up with a slick idea for enforcing this relationship. If I have two tables and one is dependent on the other (parent-child relationship), how can I enforce that every parent record has a corresponding child record? Here is a code example
USE tempdb
GO
CREATE TABLE dbo.Parent
(
ParentId int NOT NULL
)
ALTER TABLE dbo.Parent ADD CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (ParentId)
CREATE TABLE dbo.Child
(
ParentId int NOT NULL
,ChildId int NOT NULL
)
ALTER TABLE dbo.Child ADD CONSTRAINT PK_Child PRIMARY KEY CLUSTERED (ParentId, ChildId)
ALTER TABLE dbo.Child ADD CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentId)
REFERENCES dbo.Parent(ParentId)
INSERT INTO dbo.Parent VALUES (1)
INSERT INTO dbo.Child VALUES (1,1)
INSERT INTO dbo.Parent VALUES (2)
INSERT INTO dbo.Child VALUES (2,2)
INSERT INTO dbo.Child VALUES (2,1)
INSERT INTO dbo.Parent VALUES (3)
SELECT
p.ParentId, 'I SHOULD HAVE A DEPENDENT RECORD'
FROM
dbo.Parent p
LEFT JOIN
dbo.Child c
ON
p.ParentId = c.ParentId
WHERE c.ParentId IS NULL
ParentId 3 should have a child record associated with it. I am assuming that these are my choices:
1) code all inserts to the parent table along with a insert to the child table and wrap those in a transaction
2) place a trigger for insert on the parent table that ensures that the child table is populated after data for the parent.
Here is the gotcha, we will be using a middle-tier data access layer (nhibernate or dlink) so .NET application developers will be creating the data modifications at the transactional level. Also there might be several ongoing ETLs that populate this schema as well, so multiple points of entry and seperate code blocks. I don't want to hide business logic within triggers.
I assume that all our coders are competent and could enforce this properly via code, but I know that mistakes happen. Has any one come across this situation and have a solution for enforcing the integrity of the schema with constraints?