Getting the total for last month in MDX

IIF(
        // Gets the last day of the last month when it is the last day of the current month.
        IsSibling(ParallelPeriod([Date].[Year Month Day].[Day], -1, [Date].[Year Month Day].CurrentMember),
         [Date].[Year Month Day].CurrentMember) AND
        IsLeaf(
            // Gets the last day of the month when the parallel day doesn’t exist.
            ParallelPeriod([Date].[Year Month Day].[Month], 1, [Date].[Year Month Day].CurrentMember)
            ),
        // Gets the same day last month
        ParallelPeriod([Date].[Year Month Day].[Month], 1, [Date].[Year Month Day].CurrentMember),
        // Gets the last day of last month
        ClosingPeriod(
            [Date].[Year Month Day].[Day],
            ParallelPeriod([Date].[Year Month Day].[Month], 1, [Date].[Year Month Day].CurrentMember.Parent)
            )
        ),
 [Measures].[Orders On Hand]

Advertisements

Report Builder 2.0 MDX Date Parameter Format

The following Report Builder dataset parameter expression converts a Date to an MDX string:
 
="[Date].[Day].&[" + Format(CDate(Parameters!Date.Value), "s") + "]"

The dimension ‘[ComputerAdministrator]’ was not found in the cube when the string, [ComputerAdministrator], was parsed. The END SCOPE statement does not match the opening SCOPE statement.

This error occurred the first time that I tried to compile the project "FoodMart 2008".  To get around this, I ran the following script to create an entry in the Employee table for me, as described in this thread: http://www.sqlservercentral.com/Forums/Topic352574-17-1.aspx

INSERT INTO [FoodMart 2008].[dbo].[employee]
           ([employee_id]
           ,[full_name]
           ,[first_name]
           ,[last_name]
           ,[position_id]
           ,[position_title]
           ,[store_id]
           ,[department_id]
           ,[birth_date]
           ,[hire_date]
           ,[end_date]
           ,[salary]
           ,[supervisor_id]
           ,[education_level]
           ,[marital_status]
           ,[gender]
           ,[management_role]
           ,[user_name])
SELECT
           3
           ,’First Last’
           ,’First’
           ,’Last’
           ,position_id
           ,position_title
           ,store_id
           ,department_id
           ,birth_date
           ,hire_date
           ,end_date
           ,salary
           ,supervisor_id
           ,education_level
           ,marital_status
           ,gender
           ,management_role
           ,’ComputerAdministrator’
FROM [FoodMart 2008].[dbo].[employee]
WHERE employee_id = 2