SQL server : Usage of "in" clause in "If" condition

                    I had a situation to pass multi-value parameter into SSRS data set and execute a query if the multi value parameter contains a value -'ABC' else execute another one query  (to put it in simple form) ..So i tried using the following syntax:


If @Param1 in ('ABC')  
Select  a,b from Table1


else 
select b,c from Table1
End



 we got surprised when the above syntax didn't work as expected..Then we identified the trick over the SQL syntax :

The IN clause should be in reverse order when it has been used in IF Conditions:


If @Param1 'ABC' in (@Param1)


Select  a,b from Table1


else 
select b,c from Table1
End

Then it started working fine..!

SSAS - Simple Date Expression

              In general the Time dimension table will contain the full date (ie, Date with Time format) which is not preferable while browsing the data from the cube. If you want to display the Date field in the Format like “June 25,2008” –follow the given below steps:

è In Data Source View ,Right Click on Time Dimension

è  Choose “New Named Calculation”

è  Enter the Column name for the new field (Ex :SimpleDate)

è  Type following Expression.

(In the following sample Full_Date is the Date Field.)

 

Syntax:

DATENAME(mm, Full_Date) + ' ' +

DATENAME(dd, Full_Date) + ', ' +

DATENAME(yy, Full_Date)

Displaying Minutes into HH:mm:SS Format in SSAS

     To display the data in time format(HH:mm:SS)  then we you can use the following expression in calculation field :

            In the following sample, RestoreTime is the measure field which is Total minutes taken to restore the service. To display the minutes into HH:mm:SS format ,

Expression to create calculated field in SSAS:

Case
       When [Measures].[RestoreTime] <> 0   Then
              Format(Int([Measures].[ RestoreTime]/86400), "0#::")
               + Format (TimeSerial(0, 0,  [Measures].[ RestoreTime]-
            (Int([Measures].[RestoreTime]/86400) * 86400)), "HH:mm:ss")
       Else
             null
End

SSAS Calculation- Some Sample MDX expressions

              SSAS Calculation is a Multidimensional Expressions (MDX) expression or script that is used to define a calculated member, a named set, etc., in a cube in Microsoft SQL Server Analysis Services. Here, I am going to give you some sample MDX expressions to do some typical calculations which are often required in providing business solutions using SSAS.

1)       To calculate Cumulative Values (Order by Measure) :

If you want to calculate the cumulative value of a measure based on certain dimensional attribute (ordered by the Measure), you can use the following expression:

iif

(

[Measures].[Amount]> 0,

Sum (Head(Order ([DimRegion].[Area].Siblings,[Measures].[SaleAmount],BDESC) ,

Rank ([DimRegion].[Area].CurrentMember,Order ([DimRegion].[Area].Siblings,

            [Measures].[SaleAmount],BDESC ))),

[Measures].[SaleAmount])

, null

)

The above expression will give us the Regional Area wise cumulative Sales Amount.

2)      Similar to the above sample,  If you want to calculate the Cumulative sum of a measure based on the Time dimension members, we can use the following expression

Sum(

{[DimTime].[Quarter].FIRSTCHILD:[Dim Time].[Quarter].CURRENTMEMBER},

[Measures].[SaleAmount]

    )

The above expression will return you the Quarter wise cumulative Sale Amount.

3)      To calculate Total (Sum ) value of a measure and return the same to all members of the attribute

iif

(

[Measures].[Expense Amount]> 0,

sum([DimRegion].[Area].PARENT,  [Measures].[SaleAmount]),

null

)

4)      To Calculate the Percentage to Total (Contribution to Total Sales by each reach region)

            [Measures].[SaleAmount]/

MAX([DimRegion].[Area].Allmembers,[Measures].[ SaleAmount])*100

5)      To Calculate Periodic Growth of Sales in SSAS calculations

SUM(

[DimTime].[Time_Hierarchy].CURRENTMEMBER,[Measures].[SaleAmount])-SUM(ParallelPeriod([DimTime].[Time_Hierarchy].[Year],1,[DimTime].[Time_Hierarchy].CURRENTMEMBER),[Measures].[SaleAmount]

)

6)      Consider the above Calculated Member Name as [Growth], If you want to calculate What is the Periodic Growth Percentage of Sales, then we can use the following expressions:

(

[Measures].[Growth]/SUM(ParallelPeriod([Dim Time].[Time Hierarchy].[Year],1,

[Dim Time].[Time Hierarchy].CURRENTMEMBER),[Measures].[Sales Amount])

)

7)      SSAS Calculation to calculate Last Year Values:

SUM

(

ParallelPeriod([Dim Time].[Time Hierarchy].[Year],1,

[Dim Time].[Time Hierarchy].CURRENTMEMBER),

[Measures].[Sales Amount]

)

8)      SSAS Calculation for Year To Date (YTD) values

SUM

(

YTD([Dim Time].[Time Hierarchy]),

[Measures].[Sales Amount]

)

9)      SSAS Calculation for Quarter To Date (QTD) values

SUM

(

QTD([Dim Time].[Time Hierarchy]),

[Measures].[Sales Amount]

)

10)   SSAS Calculation for Month To Date (MTD) values

SUM

(

MTD([Dim Time].[Time Hierarchy]),

[Measures].[Sales Amount]

)

11)   Previous Member value

To get value of the Previous Member:

Syntax:

      Dimesion.Member.PREVMEMBER

Ex:

IIF([Dim Sales Stage].[Sales Stage].PREVMEMBER>0,

[Measures].[FactSalesCount]/Sum(([Dim SalesStep].[SalesStep].PREVMEMBER),[Measures].[FactSalesCount]),1)*100

(ie, the above example gives us the Percentage of current SalesStep’s count against previous sales step. The IIF is used to check as the previous member has any value- to avoid divide by zero issue)

12)   Last Year Values

SUM(ParallelPeriod([Date].[Time_Hierarchy].[Year],1,[Date].[Time_Hierarchy].CURRENTMEMBER),[Measures].[SalesCount])

            The above example will give us the last year sales count