Hello experts, I have two SSRS report columns. The first one consists =Sum(Fields!Sales_Value.Value) But I want to create a condition for the second column such as: SUM(SWITCH(Fields!PS.Value=Fields!RPP.Value,(Fields!Sales_Value.Value)) But it still displays the same values with the first column. Any idea?

=Sum(IIF(Fields!PS.value=Fields!RPP.value,Fields!Sales.value,0) Can u try above expression . Thanks.Rajkumar Yelugu

Thank for your reply. I have tried your code snippet. But unfortunately all values are #ERROR now.

I have tried the following code: Sum(IIF(Fields!PS.value=Fields!RPP.value,Fields!Sales_Value.Value,0))

OOps I see i did missed a '(' . Sum(IIF(Fields!PS.value=Fields!RPP.value,Fields!Sales_Value.Value,0)) If this did'nt helped you can u please give some more info of what you are trying to achieve ? Thanks .Rajkumar Yelugu

Here is my complete query: WITH MEMBER Measures.[DC Stock Level] AS SUM([Dim Date].[WeeklyCalendar].[Week Of Year].members(0):ClosingPeriod([Dim Date].[WeeklyCalendar].[Week Of Year]), Measures.[DcInventory] ) MEMBER Measures.[Store Stock Level] AS SUM([Dim Date].[WeeklyCalendar].[Week Of Year].members(0):ClosingPeriod([Dim Date].[WeeklyCalendar].[Week Of Year]), Measures.[StoreInventory] ) MEMBER [Measures].[W1] AS ( PARALLELPERIOD( [Dim Date].[WeeklyCalendar].[Week Of Year],1,[Dim Date].[WeeklyCalendar].CurrentMember) ,[Measures].[Sales Value]) SELECT NON EMPTY { [Measures].[Sale Unit], [Measures].[Sales Value],[Measures].[SalesBGP],Measures.[DC Stock Level],Measures.[Store Stock Level],[Measures].[W1] } ON COLUMNS, NON EMPTY { ([Dim Product MAX].[Group Desc].[Group Desc].ALLMEMBERS * [Dim Product MAX].[Department Desc].[Department Desc].ALLMEMBERS * [Dim Product MAX].[Style Code].[Style Code].ALLMEMBERS * [Dim Product MAX].[VAT Rate].[VAT Rate].ALLMEMBERS * [Dim Product MAX].[ITPP].[ITPP].ALLMEMBERS * [Dim Product MAX].[RPP].[RPP].ALLMEMBERS * [Dim Product MAX].[PS].[PS].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DimWarehousesMAXStoreName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimClassMAXClassDesc, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimDepartmentMAXDepartmentDesc, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimGroupMAXGroupDesc, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimSeasonMAXSeasonDesc, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimDateWeeklyCalendar, CONSTRAINED) ) ON COLUMNS FROM [HC_WEEKLYSALES])))))) WHERE ( IIF( STRTOSET(@DimDateWeeklyCalendar, CONSTRAINED).Count = 1, STRTOSET(@DimDateWeeklyCalendar, CONSTRAINED), [Dim Date].[WeeklyCalendar].currentmember ), IIF( STRTOSET(@DimSeasonMAXSeasonDesc, CONSTRAINED).Count = 1, STRTOSET(@DimSeasonMAXSeasonDesc, CONSTRAINED), [Dim Season MAX].[Season Desc].currentmember ), IIF( STRTOSET(@DimGroupMAXGroupDesc, CONSTRAINED).Count = 1, STRTOSET(@DimGroupMAXGroupDesc, CONSTRAINED), [Dim Group MAX].[Group Desc].currentmember ), IIF( STRTOSET(@DimDepartmentMAXDepartmentDesc, CONSTRAINED).Count = 1, STRTOSET(@DimDepartmentMAXDepartmentDesc, CONSTRAINED), [Dim Department MAX].[Department Desc].currentmember ), IIF( STRTOSET(@DimClassMAXClassDesc, CONSTRAINED).Count = 1, STRTOSET(@DimClassMAXClassDesc, CONSTRAINED), [Dim Class MAX].[Class Desc].currentmember ), IIF( STRTOSET(@DimWarehousesMAXStoreName, CONSTRAINED).Count = 1, STRTOSET(@DimWarehousesMAXStoreName, CONSTRAINED), [Dim Warehouses MAX].[Store Name].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS My purpose is to get the same Measure values by using the : Fields!PS.value=Fields!RPP.value condition. I will display these new measure values as different columns in my report

In other words, I should get the [Measures].[Sale Unit], [Measures].[Sales Value], [Measures].[SalesBGP], Measures.[DC Stock Level], Measures.[Store Stock Level], [Measures].[W1] measures by using this condition: Fields!PS.value=Fields!RPP.value

Hi Innocent1973, Based on your information, I get that you might want another column to display the SUM(Fields!Sales_Value.Value) at the condition of Fields!PS.Value=Fields!RPP.Value in your dataset, you could add another column and then type in the expression =SUM(IIF(Fields!PS.Value=Fields!RPP.Value,Fields!RPP.Value,nothing )) If this is not what you want, could you please give some draft to demonstrate your requirement. Thanks, Challen Fu

Thank you for your reply. My Dataset contains following fields: I have calculated and displayed all the columns within the first part of report,named TOTAL SALES. I would like to get the same columns based on a criteria for the second part,named FULL PRICE. For example, BGP Column expression for the TOTAL SALE part is: =IIF(SUM(Fields!Sales_Value.Value)=0,0,SUM(Fields!SalesBGP.Value)/SUM(Fields!Sales_Value.Value)) and My criteria is : Fields!.PS.Value= Fields!RPP.Value But it really becomes a headache for me now. I also have created some calculated measures on SSAS side. But could not filter nulls. Any idea?

Hi, We could not see your picture, If possible, could you please send it to sqltnsp AT microsoft DOT com, With the beginning “hi Challen”, please attach current report layout and desired report layout, which is a big help for me to supply further assistance. Once I receive your email, I will give you a feedback as soon as possible. Regards, Challen Fu