Analysis :: Calculate Max Value Over A Hierarchy?

Jun 19, 2015

I have a fact table that captures the captures oldest date between a Task date and todays date per Person (so there is only fact per person) like this - a measure is created from the Task Days

PersonID, Dateduekey, Task Days
130          20130809     679

I have a person dimension which has a hierarchy of Department_Name->Team_Name->Person I have created some MDX that gives the MAX Task Day for the hierarchy but when you Person attribute of the hierarchy the code runs really slow.with set a as nonempty(([v Dim Fee Earners].[People Structure].[Person],[Dim Calendar].[Primary Date].[All]),[Measures].[Days KPI]) member measures.a as max(existing(a),[Measures].[Days KPI])

How can I get this running for more efficiently when [v Dim Fee Earners].[People Structure].[Person] is selected? To be fair the true measure [Days KPI] is already calculated at person level so if there is way to use the [Days KPI] when [v Dim Fee Earners].[People Structure].[Person] is selected that would be good

View 6 Replies


ADVERTISEMENT

Analysis :: Calculate Members At Certain Level In One Hierarchy As Sum Of Corresponding Members At A Certain Level

Jun 4, 2015

Problem setting is a geography dimension with multiple user defined hierarchies in SSAS 2008. 

Ex.: 
Hierarchy 1 (political territory): level 6 --> level 5 --> level 4 --> level 3 (state) --> level 2 --> level 1
Hierarchy 2 (sales territory): level 4 --> level 3 --> level 2 (sales region) --> level 1
...
Hierarchy 9

The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states.  Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state. 

So far what I've got is:

CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL;
SCOPE (Measures.[weighted measure state and above],
Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE),
Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE),
... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;

This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.

View 5 Replies View Related

Analysis :: What Collation Is Hierarchy Using

Jul 27, 2015

Is there a way - through a DMV or other query - that I can retrieve the collation for a hierarchy using code?I know that I can find out the server, cube, and hierarchy collation using SSMS.  But I'm looking to do this in code, in order to compare collations between two cubes' dimensions, and compare member values.

View 7 Replies View Related

Analysis :: DAX - Use Of Alternate Attribute Hierarchy

Jul 31, 2015

I have a Tabular model with a situation where I want to have three alternate attributehierachies in one dimension.

Dimension FruitAndVegetables (with 4 columns: Id, Name, Fruit and Vegetable)
Id Name     Fruit      Vegetable
1 Apple      Apple   
2 Onion                   Onion
3 Banana   Banana
4 etc

Now I would like to put Vegetable on rows in a report without getting a blank row (with the sales of all Fruits)..I would like to supress all those Fruit records without adding  a separate filter to the report, just let the user pick this Attribute should do the move.

View 2 Replies View Related

Analysis :: Displaying Two Levels From Same Hierarchy In Two Columns

Oct 7, 2015

I want to display Month and year from same time hierarchy in two separate columns.My hierarchy is as below:

Ship Date > Ship Date Hierarchy
Ship Date year
Ship Date Quarter
Ship Date month
The display I am looking for is as below
Ship Date year Ship Date Month
measures
2015 Jan
200
2015 Feb
300

I am using Tail function as I need only the last 13 months, below is my query:

with set [Month] as
{
tail([SHIP DATE].[Ship Date Hierarchy].[SHIP CAL MTH].members, 13)
}
select
{[Measures].[TAG BUID Distinct Count]} on 0,
{[Month]* [SHIP DATE].[Ship Date Hierarchy].[SHIP CAL YEAR]} on 1
from
[GSDR_P4]

This gives me error that The Ship Date Hierarchy hierarchy is used more than once in the Crossjoin function.

View 2 Replies View Related

Analysis :: Getting (NULL) String In User Hierarchy?

Apr 17, 2015

I am working to get 'NULL' as a string instead of blank in below shown Hierarchy

Level1: ASIA
Level2: (BLANK)
Level3: Indonesia
Malaysia

NOTE: In Level2  '(BLANK)' means it is  actually blank.

I tried changing 'UnknownMemberName' to 'NULL' at the Dimension, but this did not work. 

View 3 Replies View Related

Analysis :: Establish Relations Between Different Levels In Hierarchy

Sep 24, 2015

I am having a requirement as below. I am having a Emp_Dim dimension table which is having a Manager_Key which is dependent on again on the Emp_Key. Based on the designation I have to create a Hierarchy. I have to create a Hierarchy with 10 levels according to the Designation.

While extracting data I am giving a particular Area manager id in Emp_Key I should get the accumulated data (His and the employees working under him as well). So it's like my MDX query should be like this if I am giving any emp_key then I need all the business done by under him.

My doubt is how I can establish the relations between the different levels in my Hierarchy. I am pretty new to SSAS.

View 3 Replies View Related

Analysis :: How To Give Permission To Read Dimensions Members In Hierarchy

Jun 9, 2015

I am using a "Client" dimension that includes a "Holding - Client" hierarchy. I have to make sure, that only the appropriate roles may access appropriate members from this dimension, but I only have the information which role may access which ClientID - I do not have the information which HoldingID should be accessible. Also, "Client" is used as the key column of the dimension with "ClientID" and "HoldingID" as key columns. The hierarchy is strict, no client may belong to multiple holdings.

I cannot seem to find the right MDX for the allowed member set. My MDX expression would need to look like this:

[Client].[Holding - Client].[Client].&[*]&[123]

In this example I want to give access for client &123, no matter the holding, so &1&123 and &2&123 would be allowed.

Is this doable?

View 4 Replies View Related

Analysis :: Ragged Hierarchy - Unary And Custom Rollup Calculations

Aug 13, 2015

Any working example of what a ragged hierarchy should look like for the unary and custom rollup calcs to work?

I have a parent-child hierarchy that works as expected but can't manage to get the same with a flattened ragged hierarchy. Parents disappear if any of the children aren't in the fact table (even though the children are set to '~' and the parent has its own custom rollup calc).

Do I need to replicate the unary and measure formula all the way to the end in the same way i do the member name/value (so i can set ignore if same as parent)? Setting unary to null seems to work when the key doesnt exist in the fact table but the default behaviour for null is '+' so i end up getting wrong results if the key does exist!

Repro:

In words

I have an EAV style fact table. The measure name is defined in a 'dim measure' table. Some measures exist in the fact, some are manufactured using Unary ops or custom rollup formulas.

Here's a diagram of the hierarchy

I want to recreate the functionality as a flattened hierarchy (the number of levels is fixed), but can't seem to do it... Here's what I've gotten for the same hierarchy created using the two different approaches

Below are queries to recreate the tables from adventureworksdw2014. The parent child hierarchy is set up the standard way... measureparentkey is parent. set name, unary and formula as expected. Set NonLeafDataHidden for the parent attribute. I've tried various combinations for the ragged hierarchy but none work 100% of the time. I want to have a user defined hierarchy for the performance benefits.

--Fact view
CREATE View dbo.FactSales
AS
select fis.ProductKey
, fis.OrderDateKey
, 1 AS MeasureKey -- salesAmount
, fis.SalesAmount AS MeasureValue

[Code] .....

View 3 Replies View Related

Analysis :: SSAS LastnonEmpty Not Showing Data For Entire Time Hierarchy?

Jun 8, 2015

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.

View 8 Replies View Related

Analysis :: Waterfall Hierarchy - Unary Operator Not Working In Ragged Utility Dimension

Jul 15, 2015

I have a disconnected utility dimension with the following data and a waterfall->subwaterfall hierarchy. I have ignore if same as parent set on sub water fall. The unary operator is assigned to the relevant level

WaterFall WaterFallUnary SubWaterFall SubWaterFallUnary

Opening ~ Opening ~
Price ~ Price ~
Total Composition ~ Composition L1 +
Total Composition ~ Composition L2 +
Total Composition ~ Composition L3 +
Total Composition ~ Composition L4 +
Total Composition ~ Composition L5 +
Closing ~ Closing ~

I'm using MDX to set values to the members

SCOPE(Measures.Waterfall,[Dim Waterfall].[Water Fall].&[Price]);
THIS=Measures.LowestPrice;
END SCOPE;
SCOPE(Measures.Waterfall,[Dim Waterfall].[Sub Water Fall].&[Composition L1]);
THIS=Measures.[Composition L1];
END SCOPE;

[Code] ....

But when i run a simple select query

SELECT
{[Measures].[Waterfall]} ON COLUMNS,
[Dim Waterfall].[Water Fall].[Water Fall] ON ROWS
FROM [GPA]

I only get a value for price. Total Composition is NULL. If I change my query to look at the subwaterfall level then I see values for composition L1->L5, so I know there are values there I was expecting the unary operator to aggregate my composition measures into Total Composition.

I should point out that every measure that's being assigned to my utility dim is calculated with in excess of a dozen or so steps and intermediate calcs, is this causing some kind of solve order issue?

I know that as a workaround i could probably do something like

SCOPE(Measures.Waterfall,[Dim Waterfall].[Water Fall].&[Total Composition]);     
    THIS=SUM([Dim Waterfall].[Hierarchy].[Water Fall].&[Total Composition].Children,Measures.Waterfall);     
END SCOPE;

But that defeats the purpose of the unary operators

This is sql server 2014 multidimensional

View 5 Replies View Related

Analysis :: Include And Exclude Children In A Single Hierarchy In Parent Child Dimension In MDX

Apr 8, 2015

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

View 3 Replies View Related

Analysis :: Using A Filter In CALCULATE

Sep 8, 2015

I'm having what i think it's a strange error in CALCULATE function:

Calculation:=CALCULATE( DISTINCTCOUNT([SK_CUSTOMER]); 'fact x'[_sales net]<>0)

where _sales net is a calculated column, sum of 3 existing columns lets call columnA , columnB, columnC

This calculation doesn't work well, the results seem to be like as if _sales net was only equal to columnA.

Naturally I've already done build deploy and full process.

View 2 Replies View Related

Calculate Average Analysis Servives

Aug 6, 2004

Hi this might be pretty simple to you guys out there but i am having issues doing it.

Please help!!!!!!!!!!

Calculate Average of a measure called DIST irrespective of any dimension the page field.

Ie. to say it should calculate the average for any doension i bring on the rwo field.

Looking forward to your help.


you can mail me at hem_k01@yahoo.com

View 6 Replies View Related

Analysis :: MDX To Calculate Maximum Gap In Sales For A Product?

Sep 30, 2015

I have a fact table which lists the last 12 month historical sales of a product,country,month combination so the table structure is like:

<month, productID, countryID, (month-3 sales), (month-4 sales).........,(month-14 sales)>

I need to write an MDX query which will return a flag that indicates if a product has a gap of more than 2 consecutive months in terms of positive sales. So I calculate if there is a gap of 3 months. If yes, then return 1 else null. The calculation below is scoped at product level.

IIF(([month-3 sales]<=0 AND [month-4 sales]<=0 AND [month-5 sales]<=0)
OR  ([month-4 sales]<=0 AND [month-5 sales]<=0 AND [month-6 sales]<=0)
OR ([month-5 sales]<=0 AND [month-6 sales]<=0 AND [month-7 sales]<=0)
...............
OR ([month-12 sales]<=0 AND [month-13 sales]<=0 AND [month-14 sales]<=0)),1,NULL)

Unfortunately I cannot do this calculation at relational level as a product may have a gap of more than 2 months at country level but may not have this at a higher level in geography.

optimizing this as the performance is bad and other calculated measures are dependent on this. Based on this calculation, I can write a scope which sums up all products so that I get the count of products with a gap of more than 2 months in sales.

I have also tried CASE statement and NESTED IIFs but the performance is worse than the above.

View 5 Replies View Related

Analysis :: Tabular DAX / Calculate SUM Against Unrelated Table?

Nov 4, 2015

I tried using the page below but wasn't able to quite properly adapt it to my scenario

[URL]

I have two UNRELATED (can't be related for reasons outside the scope of this, other relationships already exist) tables.

Each table does, however, contain a WorkerID:

WorkerTimesheets:

WorkerID, HoursLogged, Date
3,10,2015-05-05
3,6,2015-05-10
4,8,2015-05-01

Then the unrelated table "ConstructionSites"

SiteID, SiteManagerID, SiteOpen,SiteClosed
A5, 3,2015-01-01,BLANK

What I want to do is be able to show a measure that SUMS the number of hours logged by anyone who is a SiteManager from the ConstructionSites table.

I wanted to do a SUMX of WorkerTimesheets against HoursLogged, but FILTER against WorkerTimesheets[WorkerID] = ConstructionSites[SiteManagerID] so only workers who are also SiteManager would be counted.However, I can't seem to get that to resolve it always throws an error along the lines that it can't determine context.

View 3 Replies View Related

Power Pivot :: How To Calculate Discount In Analysis

Jun 16, 2015

I have a problem with calculating discount in PowerPivot analysis. I have

dCalendar , dDiscount, fSalesBook .

dDiscount columns:
DiscKey, StartDate, EndDate, Product, DiscountName, DiscountPercent

fSalebook columns:
OrderDate, Product, Qty, Sales, Discount

dCalendar columns:
Date, Holiday, MonthNo, MonthName, QuarterNo, QrtName, Year.

The problem (at least I feel) is I defined some dates in the Startdate and Enddate for a particular year. But these discounts should apply for all of the years in my fSalesBook, not just for that year in the dDiscount table.

So in essence, when you calculate discount for a product in fSalesBook you should consider only the month and day of the transaction, if the sale is in between the discount dates.

I have the following relationships.

Table [Column] ------->Table [Column]
fSalesBook[OrderDate] -----> dCalendar[Date]

I am not sure how to relate the dDiscount table with the dCalendar table and not sure even there is a need to relate them to calculate Row level discount in fSalesBook. You can change the relations/model if there is need be.

PS: One more clause, Sometimes I offer discount only for a particular product in that Discount season, that's why there is a column for Product in the dDiscount table.

What should be my DAX formula to calculate Row level discount in fSalesBook?

Here is the link for the sample workbook.

[URL]

View 6 Replies View Related

Analysis :: Disable Drill Down On Calculate Member

Jun 3, 2015

In one of the business requirement, we need to disable a particular drill down on a calculated member, Is it possible?

View 3 Replies View Related

Analysis :: Calculate Percentage Difference Of Two Values From Selected Years

Aug 31, 2015

Developing a measure which displays the difference of two values from the selected years.

An example : Show the difference of the sales amount from 2013 and 2015.

Since i am not really into mdx or calculated members.

View 6 Replies View Related

Analysis :: How To Calculate Full Year Using Built In Time Intelligence

May 13, 2015

I am struggling to calculate Full year in my SSAS Cube. Meaning, regardless of what fiscal year hierarchy level I am in; i need a measure aggregating from 01/01/year of current member to 12/31/year of current member.

I want to replicate it using the Year To Date below:

FY-FQ-FM is the fiscal year quarter hieararchy

I am using for built in time intelligence.

Create Member 
  CurrentCube.[DimTime].[FY-FQ-FM DimTime Calculations].[Year to Date] 
  As "NA";   
  /*Year to Date*/
  (
    [DimTime].[FY-FQ-FM DimTime Calculations].[Year to Date],
 
[Code] ....

View 3 Replies View Related

Analysis :: Hierarchy Based On Dimension Table Joined Multiple Times Against A Fact Table?

Aug 11, 2015

I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above).  When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer. 

Is there a way to build hierarchies within my Customer dimension based on the same Customer table?  What I want is to view the data in Excel and see the Customer dimension.  Within Customer, I want four hierarchies. 

View 2 Replies View Related

Analysis :: Measures Showing No Rows In SSAS Browser After Cube Processed Ok And Calculate Command Is There

Jun 10, 2015

I've created a cube and it processed fine. The calculate command is there. The measure that I'm attempting to run in the SSAS/Visual Studios browser is simply a count rows measure.  When I drag the measure to the window, it says no rows available.  If I click on the filter that allows nulls, the only change it makes is that it goes from no rows available to "NULL". 

View 4 Replies View Related

Analysis :: Power BI Analysis Services Connector - Remote Server Returned Error

Mar 5, 2015

I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.

We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.

View 10 Replies View Related

Analysis :: Cube Needs To Be Deployed From VS After SSIS Analysis Services Processing Task Completes?

May 13, 2014

I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS.  In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task.  The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube.  This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.

I would expect that once development is done, and it is processed and deployed, that is it.  My thinking is that the SSIS task should just update the already deployed cube,

View 2 Replies View Related

Analysis :: How To Right Choose Key Column In Mining Structure For Microsoft Analysis Services

Jun 12, 2015

How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
 
I have table:

"Incoming goods"

Create table Income (         
ID int not null identity(1, 1)            
[Date] datetime not null,             
GoodID int not null,               
PriceDeliver decimal(18, 2) not null,               
PriceSalse decimal(18, 2) not null,               
CONSTRAINT PK_ Income PRIMARY KEY CLUSTERED (ID),             
CONSTRAINT FK_IncomeGood foreign key (GoodID)  references dbo.Goods ( ID )            
)

I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?

View 2 Replies View Related

Analysis :: Create Analysis Services Project In Visual Studio 2012 Data Tools?

Feb 18, 2013

It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?

View 5 Replies View Related

Analysis :: Excel 2013 Pivot-table / Grouping On Date That Comes From Analysis Service 2008?

Nov 24, 2015

I am using

SSAS: version 2008
Excel: version 2013

I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:

DateKey:
    - Type: System.Integer
    - Value: yyyyMMdd
Date:
    - Type: System.DateTime
DateStr0:
    - Type: System.String
    - Value: dd/MM/yyyy (note: I am not using US culture)
    - Example: 01/11/2015  
DateStr1:
    - Type: System.String
    - Value: %d/%M/yyyy (note: I am not using US culture) 
    - Example: 1/11/2015  

Filtering on date is working fine:

Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting  DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:

I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:

But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.

For date values of my data coming from cube, excel consider them as 'General'

    2.1. But value here is same as it was in simple test.

    2.2. 'Date Filter' works just fine.

    2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell. 

    2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.

    2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.

So, I don't know, what is the puzzle piece that I am missing.

1. Date filter works, group does not work

2. Excel consider it as 'General' string.

3. SSAS does not allow to change 'NameColumn' to Date.

View 2 Replies View Related

Analysis :: Running Analysis Service In Terms Of Port Usage?

May 30, 2015

I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.

View 2 Replies View Related

Analysis :: Creating Roles In Analysis Service Without AD Account

Aug 6, 2015

I want to find out if it is possible to create a role in a cube without an AD account, e.g. using a GMail email address.

View 3 Replies View Related

Db Hierarchy

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

HIERARCHY HELP! PLEASE?

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

Hierarchy

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

Hierarchy

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







Copyrights 2005-15 www.BigResource.com, All rights reserved