Pivot table Conditional calculation field

Hello All

I am using owc pivot table as a control to display the data in cubes.

Now i have a requirement to apply the formula (calculated field) for all records except few.

How to solve this scenario.

Please Help

Thanks

I have written this query and this query returns me the same results as i expected except the Grand Total at bottom which show the same value as of old grand total.

"WITH MEMBER [Measures].[Q1] AS '[Measures].[BY] * IIf([Center].CurrentMember.Name = ""Apple Research Center"", 1.2, 1) ' SELECT {[Measures].[Q1],[Measures].[Prior],[Measures].[CY]} ON COLUMNS, [Center].[Center].Members ON ROWS FROM Demo"

Please Help

Anil

|||

I am still in need of a solution for this question

please help!!!

|||Does any body come accross this situation?|||

Hi Anil,

Do you mean, "all records", or "all members"? If it's the latter, then an MDX formula is best, using the IF function. If it's the former, then use a function in the fact table.

Could you tell me the structure of your cube, in terms of measures and dimensions, and then maybe I can help you with a MDX formula.

Cheers,

Jerome

|||

Jerome,

Thanks for your response.

I meant all records.

I am new to this analysis service.

Could you please help me out.

Thanks

|||

Hi Anil,

Sorry I took so long. If you need to apply a function to some of the records of the fact table, you can create a function on the SQL Server which applies the formula according to a condition:

CREATE FUNCTION <Function Name>

RETURNS <Data Type> AS

IF <Condition>

RETURN <X>

ELSE

RETURN <Y>

Then use this function in a view based on the fact table, and either use the view to populate a new version of the fact table, or else base the cube on the view.

I hope this helps. Now, I'll be able to help you better if you give me more specific information:

1. Structure of the Fact Table

2. Measures of the Cube

3. Dimensions

4. Condition for which you want to display rows from the fact table.

I look forward to hearing from you.

Cheers,

Jerome

|||

Jerome,

Thank you for your response.

I implemented this but the total is displaying the old value only.

Pivot table Conditional calculation field

Hello All

I am using owc pivot table as a control to display the data in cubes.

Now i have a requirement to apply the formula (calculated field) for all records except few.

How to solve this scenario.

Please Help

Thanks

I have written this query and this query returns me the same results as i expected except the Grand Total at bottom which show the same value as of old grand total.

"WITH MEMBER [Measures].[Q1] AS '[Measures].[BY] * IIf([Center].CurrentMember.Name = ""Apple Research Center"", 1.2, 1) ' SELECT {[Measures].[Q1],[Measures].[Prior],[Measures].[CY]} ON COLUMNS, [Center].[Center].Members ON ROWS FROM Demo"

Please Help

Anil

|||

I am still in need of a solution for this question

please help!!!

|||Does any body come accross this situation?|||

Hi Anil,

Do you mean, "all records", or "all members"? If it's the latter, then an MDX formula is best, using the IF function. If it's the former, then use a function in the fact table.

Could you tell me the structure of your cube, in terms of measures and dimensions, and then maybe I can help you with a MDX formula.

Cheers,

Jerome

|||

Jerome,

Thanks for your response.

I meant all records.

I am new to this analysis service.

Could you please help me out.

Thanks

|||

Hi Anil,

Sorry I took so long. If you need to apply a function to some of the records of the fact table, you can create a function on the SQL Server which applies the formula according to a condition:

CREATE FUNCTION <Function Name>

RETURNS <Data Type> AS

IF <Condition>

RETURN <X>

ELSE

RETURN <Y>

Then use this function in a view based on the fact table, and either use the view to populate a new version of the fact table, or else base the cube on the view.

I hope this helps. Now, I'll be able to help you better if you give me more specific information:

1. Structure of the Fact Table

2. Measures of the Cube

3. Dimensions

4. Condition for which you want to display rows from the fact table.

I look forward to hearing from you.

Cheers,

Jerome

|||

Jerome,

Thank you for your response.

I implemented this but the total is displaying the old value only.

PIVOT Query

Hi,
I have a requirement for a query which returns as many columns as many
rows are present in the table.
Table looks like this
Parent_Assembly_id Child_Assembly_id
1 2
2 3
3 4
3 5
I need to transform the data to look like
Level1 Level2 Level3 Level4 .....
1 2 3 4
1 2 3 5
.Hi
I'd prefer to do such kind of things on the client side
CREATE TABLE #Test
(
Parent_Assembly_id INT NOT NULL,
Child_Assembly_id INT NOT NULL
)
INSERT INTO #Test VALUES (1,2)
INSERT INTO #Test VALUES (2,3)
INSERT INTO #Test VALUES (3,4)
INSERT INTO #Test VALUES (3,5)
SELECT T2.Parent_Assembly_id AS Level1,
T2.Child_Assembly_id AS Level2,
T.Parent_Assembly_id AS Level3,
T.Child_Assembly_id AS Level4
FROM #Test T JOIN #Test T1
ON T.Parent_Assembly_id=T1.Child_Assembly_id JOIN #Test T2 ON
T1.Parent_Assembly_id=T2.Child_Assembly_id
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:OANptPUUFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a requirement for a query which returns as many columns as many
> rows are present in the table.
> Table looks like this
> Parent_Assembly_id Child_Assembly_id
> 1 2
> 2 3
> 3 4
> 3 5
>
> I need to transform the data to look like
> Level1 Level2 Level3 Level4 .....
> 1 2 3 4
> 1 2 3 5
> .
>|||Hi,
Thanks for the quick reply.
The problem I am facing is that I would not know before hnd the number
of levels that are present in the database.
The number of levels can be N levels. Is there any way of achieving
this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OtLIzWUUFHA.4092@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'd prefer to do such kind of things on the client side
> CREATE TABLE #Test
> (
> Parent_Assembly_id INT NOT NULL,
> Child_Assembly_id INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (2,3)
> INSERT INTO #Test VALUES (3,4)
> INSERT INTO #Test VALUES (3,5)
> SELECT T2.Parent_Assembly_id AS Level1,
> T2.Child_Assembly_id AS Level2,
> T.Parent_Assembly_id AS Level3,
> T.Child_Assembly_id AS Level4
> FROM #Test T JOIN #Test T1
> ON T.Parent_Assembly_id=T1.Child_Assembly_id JOIN #Test T2 ON
> T1.Parent_Assembly_id=T2.Child_Assembly_id
>
>
> "Nishanth" <cvnishanth@.hotmail.com> wrote in message
> news:OANptPUUFHA.3152@.TK2MSFTNGP12.phx.gbl...
many
>|||Hi
Well , thus I said that it will done better on the client side
You will have to calculate how many leveles you have and according to the
number to build a query.
I'd suggest you to do some searching on internet for examples of Itzik
Ben-Gan to deal with hierarchy data
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:eGNFbEVUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Thanks for the quick reply.
> The problem I am facing is that I would not know before hnd the number
> of levels that are present in the database.
> The number of levels can be N levels. Is there any way of achieving
> this?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OtLIzWUUFHA.4092@.TK2MSFTNGP12.phx.gbl...
> many
>