How to display all Labels in X-axis

To display all labels of x-axis, go to Interval property of X-Axis label and give value as '1'.

Named Set for Months in Current Financial Year

ORDER(
StrToMember("[Date].[Financial Period].[Year].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1]")
:
StrToMember("[Date].[Financial Period].[Year].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN  CSTR(CINT(DATEPART("q", Now())) - 1)
ELSE  CSTR(CINT(DATEPART("q", Now())) + 3) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN CSTR(CINT(Format(Now(),"MM")) - 3)
ELSE CSTR(CINT(Format(Now(),"MM")) + 9) END + "]"
)
,[Date].[Financial Period].CURRENTMEMBER.PROPERTIES("ID", TYPED), DESC)

MDX named Set for Financial Year

Current Financial Year

STRTOMEMBER("[Date].[Financial Period].[Year].&["+
  CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
  ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]")

Last Financial Year

STRTOMEMBER("[Date].[Financial Period].[Year].&["+
  CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy") -1
  ELSE CSTR(CINT(Format(Now(), "yyyy")) - 2) END + "]")

Named Set for Current Month calculation

WITH SET [Calendar Months]
AS EXTRACT(STRTOMEMBER("[Date].[Month].&["+cstr(month(NOW()))+"]",CONSTRAINED)
*{[Date].[Calendar Period].[Month]},[Date].[Calendar Period])
SELECT NON Empty[Calendar Months]  on Rows, [Measures].[Actual Amount] on Columns FROM [Sales]

Cross Apply and Outer Apply in SQL

DECLARE @Year INT

SET @Year = 2008

SELECT  ISNULL(ROUND(a.SalesAmount,0),0) AS CurrentSales

                     , ISNULL(ROUND(b.SalesAmount,0),0) As PreviousSales

                     ,a.MONTH As 'Month'

            FROM

                      (SELECT    SUM(Fs.SalesAmt) AS SalesAmount

                          ,DD.MonthName AS 'Month'

                           ,DD.MonthNumber

                              FROM    FactSales FS

                              JOIN     DimDate DD on DD.DateKey = FS.DateKey

                              WHERE    DD.Year = @Year

                              GROUP BY DD.MonthName

                           ,DD.MonthNumber

                          ) AS a

             OUTER APPLY

             --CROSS APPLY

                         (SELECT    Sum(Fs.SalesAmt) AS SalesAmount

                                      ,DD.MONTHNAME AS 'Month'

                                      ,DD.MonthNumber

                        FROM    FactSales FS

                        JOIN     DimDate DD ON DD.DateKey = FS.DateKey

                        WHERE     DD.Year = @Year - 1

                                    and   DD.MonthNumber = a.MonthNumber

                        GROUP BY DD.MONTHNAME

                           ,DD.MonthNumber

                         ) AS b

Consider ther is no data for year 2007, then on replacing ‘Outer Apply’ with ‘Cross Apply’ no data will be displayed. But using Outer Apply will display data for the year 2008 (current Sales) and 0 for 2007 (Previous Sales)

XMLA Script to Process SSAS 2005 and 2008 Cubes

To Process SSAS 2008 Database

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Parallel>

    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">

      <Object>

        <DatabaseID>Mercury_HelpDesk</DatabaseID>

      </Object>

      <Type>ProcessFull</Type>

      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

    </Process>

  </Parallel>

</Batch>

To Process SSAS 2005 Database

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Parallel>

    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

      <Object>

        <DatabaseID>CIT Global WareHouse</DatabaseID>

      </Object>

      <Type>ProcessFull</Type>

      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

    </Process>

  </Parallel>

</Batch>


XMLA Script to Process cube through variable supplied

"<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine/">
  <Parallel>
    <Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema/" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance/" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2/" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100/">
<Object>
<DatabaseID>"+ @[User::CubeDatabase] +"</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>"

SQL Function to Display Text with Sentence Caps

Below Query creates a function to display the text in sentence caps, e.g.., displays 'ANANDH KUMAR' as 'Anandh Kumar'

CREATE FUNCTION [dbo].[ProperCase](@Input AS VARCHAR(8000))

RETURNS VARCHAR(8000)

AS

BEGIN

   DECLARE @Reset BIT;

   DECLARE @Ret VARCHAR(8000);

   DECLARE @i INT;

   DECLARE @c CHAR(1);

   SELECT @Reset = 1, @i=1, @Ret = '';

  

   WHILE (@i <= LEN(@Input))

      SELECT @c= SUBSTRING(@Input,@i,1),

               @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END,

               @Reset = CASE WHEN @c LIKE '[a-zA-Z]' THEN 0 ELSE 1 END,

               @i = @i +1

              

   RETURN @Ret

END