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.