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

SSRS : Displaying Parameter values as a Label in the report

We can just show the Parameter value in the text box by writing simple expression as

=Parameters!ParamName.value

But If the Parameter is a Multi value parameter, we have to write Join expression to display multiple selected values. Ex:  

                    = Join(Parameters!ParamName.Value, “,”)

 This will give you comma separated list of parameter values

In this method, if the number selected values are more, the text box will grow up the report might not look nice. If we want to show “ALL”, or <Some message> when ‘Select All’ is selected in the report parameter, we can use the following expression(Assuming that the values for the parmaters are supplied from the data set “DataSet_ParamName”)

=IIF(Sum(Fields!ID.Value, “DataSet_ParamName”)=Parameters!ParamName.Count, ”Al””,Join(Parameters!ParamName.Value, “,”))

We can also change the same expression to display some message when the selected Parameters count is greater than some values.

=IIF(Parameters!ParamName.Count >=2, ”Selected More than 2”,Join(Parameters!ParamName.Value, “,”))

If the selected parameter count is greater than 2 then it will show the message as as “Selected More than 2”.

SSRS - Serial number for the group column

                We were developing a report which contain a group (grouped by Product) and the group contain a detail row in it. We wanted to show Serial Number for the Products, we trying to use RowNumber function in SSRS but it didn’t come up as expected.  Finally we found the solution from Web which was written by Bilal Hani- a Microsoft Community Contributor as how to “add serial number to grouping column in SSRS Tablix”.

1. Open the report properties (right click on blank area outside the layout and click properties) or (Menu-->Report-->Report Properties)

2. Click on the code pane on the left

3. In the code window enter below script

Dim Counter as integer=0

public function getGroupCounter() as Integer

 Counter=Counter+1

 return Counter

end function

4. Go to the properties of the group (based on which group you need the group serial number)

5. Click the variables tab on the left

6. Click on Add and set Name as GroupCountValue and set the expression (in the expression window) as =Code.getGroupCounter, click ok and close the window.

7. Now in the textbox where you want to display the group number, set the expression as =Variables!GroupCountValue.Value

                Now the report will return the Serial Number for each Product as Expected. !!

Repeat Header / Keep Header Visible in Tables in RS 2008

               When I tried to set the properties like ‘Repeat header rows on each page’ or ‘Keep header rows visible while scrolling’ -  it doesn’t work in SSRS 2008. Through the searches, I found the following solution for it.

            In the grouping pane of the report page, click on the small triangle and select "Advanced Mode"

It will give you a list of static members in the selected Tablix.
Select the corresponding static and press F4 to see its properties

In the properties window :
- set KeepWithGroup to After
- set RepeatOnNewPage to True for repeating headers
- set FixedData to True for keeping headers visible 

Now click to preview the report...the selected static (Header Row) will be displayed in each page of the report. !!