Missing Named Set - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Missing Named Set

Author

Topic

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2009-10-21 : 06:12:09

I have coded up a named set called [Last Year] and given it a Display Folder.

It does not appear anywhere in the available metadata once deployed when using the Browser in Visual Studio - therefore it is not available for use in Calculated Members.

It does however appear when using the Pivot Table services in excel, but under a sub folder called "Sets" in the Time Dimension which in this case is called [Dim Week].

I have used the exact same code with a "sum(" at the beginning of each set declaration and a [Measures].[Sales R] to test it as a calculated member and it works perfectly, my problem is because the set is not available in metadata.... I can't reuse the code.

Does anyone have any idea what is wrong here?


CREATE DYNAMIC SET CURRENTCUBE.[Last Year]
 AS IIF
(
   [Dim Week].[Calendar].CurrentMember.Level.Ordinal = 1
   ,
   [Dim Week].[Calendar].CurrentMember.LastChild.LastChild.lag(103) : [Dim Week].[Calendar].CurrentMember.LastChild.LastChild.lag(52)
   ,
   IIF
   (
      [Dim Week].[Calendar].CurrentMember.Level.Ordinal = 2
      ,
      IIF
      (
         [Dim Week].[Calendar].CurrentMember.LastChild.Properties("Key1") - INT([Dim Week].[Calendar].CurrentMember.LastChild.Properties("Key1") / 100) * 100 = 1 OR 
         [Dim Week].[Calendar].CurrentMember.LastChild.Properties("Key1") - INT([Dim Week].[Calendar].CurrentMember.LastChild.Properties("Key1") / 100) * 100 = 4 OR 
         [Dim Week].[Calendar].CurrentMember.LastChild.Properties("Key1") - INT([Dim Week].[Calendar].CurrentMember.LastChild.Properties("Key1") / 100) * 100 = 7 OR 
         [Dim Week].[Calendar].CurrentMember.LastChild.Properties("Key1") - INT([Dim Week].[Calendar].CurrentMember.LastChild.Properties("Key1") / 100) * 100 = 10
         ,
         [Dim Week].[Calendar].CurrentMember.LastChild.lag(56) : [Dim Week].[Calendar].CurrentMember.LastChild.lag(52)
         ,
         [Dim Week].[Calendar].CurrentMember.LastChild.lag(55) : [Dim Week].[Calendar].CurrentMember.LastChild.lag(52)
      ) 
      ,
      IIF
      (
         [Dim Week].[Fin Calendar].CurrentMember.Level.Ordinal = 1
         ,
         [Dim Week].[Fin Calendar].CurrentMember.LastChild.LastChild.lag(103) : [Dim Week].[Fin Calendar].CurrentMember.LastChild.LastChild.lag(52)
         ,
         IIF
         (
            [Dim Week].[Fin Calendar].CurrentMember.Level.Ordinal = 2
            ,
            IIF
            (
               [Dim Week].[Fin Calendar].CurrentMember.LastChild.Properties("Key1") - INT([Dim Week].[Fin Calendar].CurrentMember.LastChild.Properties("Key1") / 100) * 100 = 1 OR 
               [Dim Week].[Fin Calendar].CurrentMember.LastChild.Properties("Key1") - INT([Dim Week].[Fin Calendar].CurrentMember.LastChild.Properties("Key1") / 100) * 100 = 4 OR 
               [Dim Week].[Fin Calendar].CurrentMember.LastChild.Properties("Key1") - INT([Dim Week].[Fin Calendar].CurrentMember.LastChild.Properties("Key1") / 100) * 100 = 7 OR 
               [Dim Week].[Fin Calendar].CurrentMember.LastChild.Properties("Key1") - INT([Dim Week].[Fin Calendar].CurrentMember.LastChild.Properties("Key1") / 100) * 100 = 10
               ,
               [Dim Week].[Fin Calendar].CurrentMember.LastChild.lag(56) : [Dim Week].[Calendar].CurrentMember.LastChild.lag(52)
               ,
               [Dim Week].[Fin Calendar].CurrentMember.LastChild.lag(55) : [Dim Week].[Calendar].CurrentMember.LastChild.lag(52)
            )
            ,
            IIF
            (
               [Dim Week].[Input Window].CurrentMember.Level.Ordinal = 1 
               ,
               [Dim Week].[Input Window].CurrentMember.LastChild.lag(52 + COUNT([Dim Week].[Input Window].CurrentMember.Children) - 1) : [Dim Week].[Input Window].CurrentMember.LastChild.lag(52)
               ,
               IIF
               (
                  [Dim Week].[Sales Window].CurrentMember.Level.Ordinal = 1 
                  ,
                  [Dim Week].[Sales Window].CurrentMember.LastChild.lag(52 + COUNT([Dim Week].[Sales Window].CurrentMember.Children) - 1) : [Dim Week].[Sales Window].CurrentMember.LastChild.lag(52)
                  ,
                  IIF
                  (
                     [Dim Week].[Strat Input Window].CurrentMember.Level.Ordinal = 1 
                     ,
                     [Dim Week].[Strat Input Window].CurrentMember.LastChild.lag(52 + COUNT([Dim Week].[Strat Input Window].CurrentMember.Children) - 1) : [Dim Week].[Strat Input Window].CurrentMember.LastChild.lag(52)
                     ,
                     IIF
                     (
                        [Dim Week].[Strat Sales Window].CurrentMember.Level.Ordinal = 1 
                        ,
                        [Dim Week].[Strat Sales Window].CurrentMember.LastChild.lag(52 + COUNT([Dim Week].[Strat Sales Window].CurrentMember.Children) - 1) : [Dim Week].[Strat Sales Window].CurrentMember.LastChild.lag(52)
                        ,
                        Parallelperiod([Dim Week].[Calendar].[Week Desc], 52, [Dim Week].[Calendar].CurrentMember)
                     )
                  )
               )
            )
         )
      )
   )   
), DISPLAY_FOLDER = 'Week SubSets'  ;

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources