MDX error – the definition of the set contains a circular reference

My MDX code for the report is using a WHERE clause using a named set, which throws the above error complaining about circular reference. I had no clue what it referred to. Google returned with a solution, i.e. you cannot put a named set after WHERE clause. Put the definition of the named SET directly in the WHERE-clause it should work.

Example:

WITH SET [HS Events] AS {

{ [Event Types].[Hierarchy].[Event Type].&[Health & Safety]&[Health & Safety Hazard]

, [Event Types].[Hierarchy].[Event Type].&[Health & Safety]&[Health & Safety Injury/Illness]

, [Event Types].[Hierarchy].[Event Type].&[Health & Safety]&[Health & Safety Near Miss]

, [Event Types].[Hierarchy].[Event Type].&[Health & Safety]&[Health & Safety Plant or Property Damage/Loss] }

SELECT [Measures].[Events Count] ON COLUMNS

FROM Cube

WHERE [HS Events]

 

Solution:

SELECT [Measures].[Events Count] ON COLUMNS

FROM Cube

WHERE

{ [Event Types].[Hierarchy].[Event Type].&[Health & Safety]&[Health & Safety Hazard]

, [Event Types].[Hierarchy].[Event Type].&[Health & Safety]&[Health & Safety Injury/Illness]

, [Event Types].[Hierarchy].[Event Type].&[Health & Safety]&[Health & Safety Near Miss]

, [Event Types].[Hierarchy].[Event Type].&[Health & Safety]&[Health & Safety Plant or Property Damage/Loss] }

 

Advertisements

Read Full Post »

How to backup SSAS databases using SSIS?

In SSIS, we could use the Analysis Services Execute DDL Task Control Flow Item to backup SSAS databases using XMLA. The XMLA script to do the backup is obtained from SQL Server Management Studio (SSMS) Scripting function. To generate such script, connect to the SSAS database in SSMS, backup the cube then  click “Script”. The location of the backup file defaults to C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Backup. 

In the Analysis Services Execute DDL Task Control Flow Item, configure it as follows. Copy the XMLA script into the Source Direct field.

The above solution is to create a backup of one instance of SSAS database (this is hardcoded in the XMLA script as you can see)  and the name of the backup file (.abf) remains the same hence the Allow Overwrite needs to be turned on every time. It backups then overwrites to the exisiting abf file.

In order to backup all SSAS databases dynamically with timestamp appending to the backup files (.abf), we need to use Script Task.

Configure the ForEachLoop to below:

The SSAS source connection – leave the Intial Catalog to blank as the script will loop through each cube.

Create 2 variables – one to store the backup XMLA script and another one to store the DatabaseName. Set the ReadOnyVariables to DatabaseName and the ReadWriteVariables to BackupScript User defined variables in the Script Task Editor.

 Leave everything else.

Cick Edit Script. Ensure that the Script Language is set to Microsoft Visual Basic 2008.

 Public Sub Main()
  ‘
  ‘ Add your code here
        ‘
        Dts.Variables.Item(“BackupScript”).Value = “<Backup xmlns=””http://schemas.microsoft.com/analysisservices/2003/engine“”>” + “” + “” + Dts.Variables.Item(“DatabaseName”).Value.ToString + “” + “” + “<File>” + Dts.Variables.Item(“DatabaseName”).Value.ToString + Date.Today.Month.ToString.PadLeft(2, “0”c).ToString + Date.Today.Day.ToString.PadLeft(2, “0”c).ToString + Date.Today.Year.ToString + “.abf” + “</File>” + “<AllowOverwrite>true</AllowOverwrite>” + “</Backup>”

        Dts.TaskResult = ScriptResults.Success
 End Sub

Last step – configure the Analysis Services Execute DDL Task to grab its source from the BackupScript user defined variable returned from the Script Task Editor.

Build the script. Once succeeded save the solution then execute the package.

Once tested passed, schedule this as a SQL job for daily backups.

Read Full Post »

How to get the first day and last day of the previous month in SSRS?

First day of the previous month in SSRS:

=DateAdd(dateinterval.month, -1, today().AddDays(-(today().Day-1)))

Last day of the previous month in SSRS:

=DateAdd(DateInterval.DAY, -1, DateSerial(YEAR(Now()), MONTH(Now()), 1))

Read Full Post »

Parameter passing of the SSRS report via URL link on SharePoint 2010

To set up a URL for parameter passing when the SSRS reports are deployed to SharePoint 2010:

1. Deploy the SSRS reports (rds and rdls) to SharePoint.

2. Open the report in Report Builder (hover over your cursor to the right corner of the report then select Edit/Open in Report Builder)

3. In the report builder, check if the rds has been configured properly and test the connection.

4. On the Report Actions properties > Jump to URL, copy and paste the Report URL then add the parameter passing at the end of it using the following format:

&rp:Date=Parameters!Date.Value

rp:  has been used to denote that it is a report parameter (applies only when the report is integrated in SharePoint).

Example:

=Switch(Fields!Colour.Value =“Red”

, “http://intranet/Reports/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/reports/dashboard.rdl&rp:Date=&#8221; & Format(Fields!Date.Value, “yyyy-MM-dd”) & “&rp:Level1=” & Fields!Level_1.Value )

 

Read Full Post »

How to get the total of records consecutively for each month (t-SQL)?

I have a report requirement to calculate the number of days that the colour has stayed green for consecutive days within a month period. See below as an example. In Feb 2012, the number of consecutive green days would be 4 days (from 4 Feb 2012 to 7 Feb 2012). I have tried subqueries, ranking and CTE and have had to used cursor to make this work.

SQL:

DECLARE @Colour VARCHAR(10)

 ,@EventDate VARCHAR(50)

,@Counter INT= 0

DECLARE getColour CURSOR FAST_FORWARD READ_ONLY

FOR   SELECT Date, Colour, MonthlyPerfectDayCount FROM #temp

OPEN getColour

FETCH NEXT FROM getColour INTO @EventDate, @Colour, @Counter

WHILE @@FETCH_STATUS= 0

BEGIN

       SET @Counter = @Counter + 1

       IF (@Colour =‘Green’)

       BEGIN

               UPDATE #temp

               SET MonthlyPerfectDayCount = @Counter

               WHERE Date >= @EventDate

               AND ABS(DATEDIFF(day,Date, @EventDate))= 1 

               AND Colour =‘Green’

        END

       SET @Counter = @Counter + 1

       FETCH NEXT FROM getColour INTO @EventDate, @Colour, @Counter

END

CLOSE getColour

DEALLOCATE getColour

SELECT ISNULL(MAX(MonthlyPerfectDayCount)+ 1, 0) AS GreenConsecutiveDays

FROM #temp

Read Full Post »

SSRS action to send an email …

Set up the action as ‘Go to URL’ and plug in the following expression:

=”MailTo:UserName@CompanyName.com”

Read Full Post »

Using CURSOR to set a conditional background colour of a matrix SSRS report..

I have a matrix report that brings back data for each day in a month per division.

When there is an incident, the colour of the matrix cell will be red (i.e. bad day for that particular division).  If there are 6 divisions and no incidents have occurred on that day, then the colour will be green (i.e. perfect day/zero harm day) for all divisions on that day.  If there has been at least one incident happening on that day for 1 division but no incidents for the other 5 divisions, then the other 5 divisions will have blue as the colour (i.e. not so good day).

Solution:

DECLARE @Colour VARCHAR(10)

                    ,@EventDate VARCHAR(50)

DECLARE getColour  CURSOR FAST_FORWARD READ_ONLY FOR             

           SELECTDate, Colour 

           FROM #temp

OPEN getColour

FETCH NEXT  FROM getColour INTO@EventDate, @Colour

WHILE @@FETCH_STATUS= 0

BEGIN

–PRINT CONVERT(VARCHAR(50), @EventDate, 126) + ‘ ‘ + @Colour

IF (@Colour =‘Red’)

BEGIN

      UPDATE #temp

      SET Colour =‘Blue’

      WHERE Date= @EventDate

      AND Colour =‘Green’

      END

     FETCH  NEXT  FROM getColour INTO@EventDate, @Colour

END

CLOSE getColour

DEALLOCATE  getColour

SELECT * from #temp

Read Full Post »