tim laqua dot com Thoughts and Code from Tim Laqua

1Oct/090

Reporting Services (SSRS/MSRS) 2008 Error: Set used with the complement operator must have all members from the same level

When you use the Not In operator in a SSRS 2008 MDX query filter to exclude a named set, it uses a the complement operator in the constructed MDX. This is fine as long as "all members [are] from the same level." Since you got this error, they are not 😉 You can get around this by using the Except() MDX function instead of letting SSRS use the Complement operator

In the ReportServerService log, you'll see something like this:
Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Query (..., ...) Set used with the complement operator must have all members from the same level.

Original filter
Dimension: Time
Hierarchy: Calendar Date
Operator: Not In
Filter Expression: [Today]

New filter
Dimension: Time
Hierarchy: Calendar Date
Operator: MDX
Filter Expression: Except([Time].[Calendar Date].[Calendar Date].MEMBERS, [Today])

8Mar/096

Notepad++ User Defined Language file for MDX Syntax Highlighting

Following up on my previous post for GeSHi MDX Syntax Highlighting, here is a version for Notepad++. The only real issue I ran in to here is that the Nodepad++ User Language Definition system only allows two sets of boundary open/boundry close characters. One has to be square brackets - [...] - for MDX, and the other here would be a string delimiter. This means only one string delimiter can be used at a time (because the square brackets took up the first slot) and I went with single quotes. If you use double-quotes in your MDX rather than single quotes, you may want to switch the Delimiters element from:

            <Keywords name="Delimiters">[&apos;0]&apos;0</Keywords>

to:

            <Keywords name="Delimiters">[&quot;0]&quot;0</Keywords>

If you don't have any existing user defined languages, you can simply download the following file and put it in the appropriate location. The appropriate location will vary depending on if you're using %APPDATA% or not. If you are using %APPDATA%, the location will be %APPDATA%\Notepad++

If you're not using %APPDATA%, the location is the Program Files folder for Notepad++ - usually something like C:\Program Files\Notepad++.

Download: userDefineLang.xml

If you already have existing user defined languages, you will need to edit your current userDefineLang.xml file and add the following UserLang node to your file (NodepadPlus is the root node - you only want to add in the UserLang node):

<NotepadPlus>
    <UserLang name="MDX" ext="mdx">
        <Settings>
            <Global caseIgnored="yes" />
            <TreatAsSymbol comment="no" commentLine="no" />
            <Prefix words1="yes" words2="yes" words3="yes" words4="yes" />
        </Settings>
        <KeywordLists>
            <Keywords name="Delimiters">[&apos;0]&apos;0</Keywords>
            <Keywords name="Folder+">SELECT</Keywords>
            <Keywords name="Folder-">FROM</Keywords>
            <Keywords name="Operators">- ( ) * , . / : ; { | } + &lt; = &gt;</Keywords>
            <Keywords name="Comment">1/* 2*/ 0// 0--</Keywords>
            <Keywords name="Words1">WHERE COLUMNS ROWS NON EMPTY AS ON CALCULATE CASE EXISTING FREEZE IF SCOPE ALTER CUBE CREATE ACTION CELL CALCULATION GLOBAL WITH MEMBER SESSION SET SUBCUBE DROP SUBCUBE REFRESH CALL CLEAR CALCULATIONS DRILLTHROUGH UPDATE SetToArray This Leaves Current Item Root StrToTuple Error Hierarchy Dimension Dimensions Level Levels IsAncestor IsEmpty IsGeneration IsLeaf IsSibling CalculationPassValue CoalesceEmpty Generate IIf LookupCube MemberToStr Name Properties SetToStr TupleToStr UniqueName UserName</Keywords>
            <Keywords name="Words2">Aggregate Avg CalculationCurrentPass CalculationPassValue CoalesceEmpty Correlation Count Covariance CovarianceN DistinctCount IIf LinRegIntercept LinRegPoint LinRegR2 LinRegSlope LookupCube Max Median Min Ordinal Predict Rank RollupChildren Stddev StddevP StrToValue Sum Value Var Variance VarianceP VarP&#x0009;&#x0009;</Keywords>
            <Keywords name="Words3">AllCalculatedMembers AllMembers Ancestors Ascendants Axis BottomCount BottomPercent BottomSum Children Crossjoin CurrentOrdinal Descendants Distinct DrilldownLevel DrilldownLevelBottom DrilldownLevelTop DrilldownMember DrilldownMemberBottom DrilldownMemberTop DrillupLevel DrillupMember Except Exists Extract Filter Generate Head Hierarchize Intersect LastPeriods Members Mtd NameToSet NonEmptyCrossjoin Order PeriodsToDate Qtd Siblings StripCalculatedMembers StrToSet Subset Tail ToggleDrillState TopCount TopPercent TopSum Union Unorder VisualTotals Wtd Ytd</Keywords>
            <Keywords name="Words4">Ancestor ClosingPeriod Cousin CurrentMember DataMember DefaultMember FirstChild FirstSibling Item Lag LastChild LastSibling Lead LinkMember NextMember OpeningPeriod ParallelPeriod Parent PrevMember StrToMember UnknownMember ValidMeasure</Keywords>
        </KeywordLists>
        <Styles>
            <WordsStyle name="DEFAULT" styleID="11" fgColor="000000" bgColor="FFFFFF" fontName="Courier New" fontStyle="0" />
            <WordsStyle name="FOLDEROPEN" styleID="12" fgColor="0000FF" bgColor="FFFFFF" fontName="Courier New" fontStyle="1" />
            <WordsStyle name="FOLDERCLOSE" styleID="13" fgColor="0000FF" bgColor="FFFFFF" fontName="Courier New" fontStyle="1" />
            <WordsStyle name="KEYWORD1" styleID="5" fgColor="0000FF" bgColor="FFFFFF" fontName="Courier New" fontStyle="1" />
            <WordsStyle name="KEYWORD2" styleID="6" fgColor="FF0000" bgColor="FFFFFF" fontName="" fontStyle="0" />
            <WordsStyle name="KEYWORD3" styleID="7" fgColor="804000" bgColor="FFFFFF" fontName="" fontStyle="0" />
            <WordsStyle name="KEYWORD4" styleID="8" fgColor="804000" bgColor="FFFFFF" fontName="" fontStyle="0" />
            <WordsStyle name="COMMENT" styleID="1" fgColor="000000" bgColor="FFFFFF" fontName="" fontStyle="0" />
            <WordsStyle name="COMMENT LINE" styleID="2" fgColor="000000" bgColor="FFFFFF" fontName="" fontStyle="0" />
            <WordsStyle name="NUMBER" styleID="4" fgColor="800040" bgColor="FFFFFF" fontName="" fontStyle="1" />
            <WordsStyle name="OPERATOR" styleID="10" fgColor="000000" bgColor="FFFFFF" fontName="Courier New" fontStyle="1" />
            <WordsStyle name="DELIMINER1" styleID="14" fgColor="000000" bgColor="FFFFFF" fontName="" fontStyle="0" />
            <WordsStyle name="DELIMINER2" styleID="15" fgColor="6C6C6C" bgColor="FFFFFF" fontName="Courier New" fontStyle="1" />
            <WordsStyle name="DELIMINER3" styleID="16" fgColor="000000" bgColor="FFFFFF" fontName="" fontStyle="0" />
        </Styles>
    </UserLang>
</NotepadPlus>
7Mar/090

GeSHi Language file for MDX Syntax Highlighting

For years now, it's bothered me that there was no syntax highlighting for MDX in GeSHi - so I put a language file together for MDX. To use this file: download it, unzip it, and put the mdx.php file in to your GeSHi languages folder (for WordPress wp-syntax, this is themes/wp-syntax/geshi/geshi) - it's the folder with all the languagename.php files in it.

Let me know if it doesn't catch something or if you have suggestions for how to highlight keywords differently.

Download: geshi-mdx.zip

You can also copy and paste the following in to a new file and name it mdx.php - then place it in the appropriate folder:

23Feb/090

Wierd Slice – Revenue for the Most Recent Hour in the Cube Across the Last 14 Days Sliced by Week

Well - there's two parts to this - one is to figure out the most recent hour in the cube that has data. Let's face it, it finishes as soon as it can - but that time may vary now and then. Then, for this particular report, we wanted to see the last seven days on one series and the previous seven days on another series. To do this, we just slice by two calculated measures. But this creates an odd dateset where the This Week column will have no data for the "Date Time" columns that actually belong to the Previous Week.

This isn't really a problem as in SSRS, you can simply define the Category Group for the Series in your chart (ya, I didn't mention that yet - this is all for a chart) as DatePart("w", Fields!DateTime) - which will group everything by the number of the given weekday and then you can just have your "This Week" and "Prevoius Week" series.

WITH 
  MEMBER [Measures].[This Week] AS 
    Aggregate
    ([Time].[Calendar Date].LastChild:[Time].[Calendar Date].LastChild.Lag(6)
     ,[Measures].[Revenue])
 
  MEMBER [Measures].[Previous Week] AS 
     Aggregate([Time].[Calendar Date].LastChild.Lag(7)
     :
     [Time].[Calendar Date].LastChild.Lag(13)
     ,[Measures].[Revenue])
SELECT 
  NON EMPTY 
    {[Measures].[Previous Week], [Measures].[This Week]} ON 0
 ,NON EMPTY 
    { 
        [Time].[Date Time].[Date Time].MEMBERS 
        * 
        [Time].[24 Hour].[24 Hour].ALLMEMBERS
    } ON 1
FROM 
(
  SELECT 
    Tail(
         Filter(
               [Time].[24 Hour].[24 Hour].MEMBERS
               ,Sum([Time].[Calendar Date].LastChild,[Measures].[Revenue]) > 0
         ), 1
    ) ON 0
  FROM 
  (SELECT 
      {
            [Time].[Calendar Date].LastChild
            :
            [Time].[Calendar Date].LastChild.Lag(13)
      } ON 0
    FROM [Cube]
  )
)

And to top it all off - after seeing the chart for a while, nobody liked it (after all, it's a relatively abstract view of revenue) and we dumped it. 😉

21Jul/082

Dynamic MDX queries in TSQL

Say you want to run the same MDX query for each row in a given rowset. I needed to do this for alerting purposes, where there were different alert thresholds for different attribute values in a given dimension attribute. After struggling with passing a variable to the query argument of the OPENROWSET command, I finally found the documentation that clearly stated that the query argument CAN'T be a variable. Or a concatination of a string and a variable. I still don't understand why... but the suggested workaround is to construct a giant TSQL string and run it using the EXEC command.

Ok - but how do we get the results of the query? Basically, the only way to do this is to create a temporary table in the current scope and do an INSERT INTO that temp table in your giant TSQL query. It all ends up looking something like this: