It is pretty straightforward to get measure values for members in a hierarchy. In Adventure Works, say you wanted to get [Internet Sales Amount] for all products by color, you can use this query

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Product].[Color].[Color].MEMBERS ON 1
FROM [Adventure Works]

Sales By Color

and, to get [Internet Sales Amount] for all products by class, you can use this query

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Product].[Class].[Class].MEMBERS ON 1
FROM [Adventure Works]

 Sales By Class

It gets trickier if you want to get the output to show both results in the same query

Sales By Color And Class

You can UNION the two result sets by creating a set of members from the 2 hierarchies, using their [All] members respectively. Something like

WITH MEMBER [Measures].[Attribute] AS
IIF([Product].[Color].CurrentMember IS [Product].[Color].[All]
, [Product].[Class].CurrentMember.NAME
, [Product].[Color].CurrentMember.NAME)

SELECT {[Measures].[Attribute], [Measures].[Internet Sales Amount]} ON 0,
([Product].[Class].[Class].MEMBERS, [Product].[Color].[All]),
([Product].[Class].[All], [Product].[Color].[Color].MEMBERS )} ON 1
FROM [Adventure Works]

Union Sales By Color And Class


I had a requirement recently where the customer wanted a measure to be formatted differently, based on its value.

In this case, it was two numeric measures with 2 decimal point precision, and for cases where there were no decimal digits, it was supposed to look like an integer.

I looked for a MOD function in VBA or SSAS that can be used here, but came up empty ūüė¶

I used the SCOPE statement to override the default formatting

SCOPE({[Measures].[Sales], [Measures].[Cost]});
FORMAT_STRING(This)= IIF(Round([Measures].CurrentMember.VALUE, 2)=[Measures].CurrentMember.VALUE, “#,###;;0;”, “#,###.00;;0;”);

For the measures in question, the SCOPE statement compares its ROUNDed value to its actual value. If these are equal, that means that the number is a whole number, and has to be formatted without decimal places shown. Hence the FORMAT_STRING expression.

Many a times, people ask the question on how to disable or hide the Total and Grand Total rows in the result set when using the Cube Browser in SSMS (SQL Server Management Studio).

Here is a quick way to do that

When you browse a cube, you see Totals as below

Click on the dimension header in the Row Fields area

Right-click the highlighted region to bring up the menu as

Uncheck the Subtotal, and voila ‚Äď it disappears

For more, just repeat the above steps


A lot of times the MDX query needs to look at current date/today date/system date as the reference member. There is no function equivalent to TSQL getdate(), but you can use the following piece of cide snippet to solve the purpose:

StrToMember(“[Date].[Date].[” + Format(now(), “yyyyMMdd”) + “]”)

As an example, if you want to see the Sales for the last 7 days from the system date:

 [Measures].[Sales]  ON COLUMNS,
¬†LastPeriods(7,¬†¬†StrToMember(“[Date].[Date].[” + Format(now(), “dd-MM-yyyy”) + “]”)¬†)¬†¬†ON ROWS
FROM [Cube]

Installed MOSS 2007 SP2 on a 64-bit machine running Windows Server 2008, and restored a site collection from another domain. Running into SSRS issues (but lets save that for later !). Tried to open the site url in Windows Explorer and ran into errors. Then tried openning a sharepoint list using¬†Actions -> Open with Windows Explorer, and the same problem. Got this error message”

Your client does not support opening this list with Windows Explorer.

Turns out, this is because the Desktop Experience feature was not installed. Installed as follows:

1.Click Administrative Tools -> Server Manager
2.Expand the Features node
3.Click Add Features
4.Check Desktop Experience, and install
5.Restart the server once done.

Quite often during data conversion processes, I am required to get only the date part from a datetime or a timestamp value. Here is a trick that does not involve CASTing and is quick to respond:

DATEADD(DAY, 0, DATEDIFF(DAY, 0, <ColumnName>))

Here is how it works: Whether smalldatetime or datetime, DATEDIFF(DAY, 0 returns the number of days since the base date, and DATEADD(DAY, 0 returns the integer as a date value.

Neat, isn’t it ?

Use this query to verify the details on the SQL Server:

SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,
SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,
SERVERPROPERTY(‘Edition’) AS Edition;

Of course, there are other ways, but the above is the most ‘coherent’.

Other convenient methods:

  • Check the file version of sqlservr.exe

SQL Server Surface area configuration is deprecated in SQL Server 2008, so this is how you should enable adhoc queries by setting OPENROWSET and OPENDATASOURCE options.

Run the following commands, one at a time:

sp_configure “show advanced options”,1

Output: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.


Output: Command(s) completed successfully.

sp_configure “Ad Hoc Distributed Queries”,1

Output: Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.


Output: Command(s) completed successfully.

And that is it.

Lets say you have a report created using Report Builder 2.0, in my case and want to open the report in Business Intelligence Development Studio (BIDS) using Report Designer.

 There are umpteen number of places where they talk about the fact that you can open a report created in Report Builder using Report Designer, but exactly how to go about doing that is not provided.

 So, I had to go hunt for myself and am providing the steps to be able to do that.

  1. Go to the report using Report Manager, found at http://ReportServerURL/Reports
  2. Click the Properties tab across the top
  3. Click the Edit button under the Report Definition section
  4. Now you can save the report definition as a .rdl file
  5. Open the saved file in BIDS Project using Add Item functionality

Created a report in SSRS (SQL Server Reporting Services) with the SSAS (SQL Server Avalysis Services) cube as the data source. In order to make the report dynamic, since the date hierarchy (defined as day, month, quarter) and the week hierarchy (defined as day, week) don’t align (weeks can span months, quarters, etc), I defined a parameter in the report, type text (SSAS can only pass string data to the reports as parameter values), named @ParameterValue.

The behavior is dynamic as based on the passed parameter, the underlying query changes the hierarchy

This report was then linked to the cells in the cube with the Reporting Action defined in the Actions Form Editor in the cube definition in SSAS, as

Target Type: Cells
Target Object: All Cells
Condition: [Ticket Report Date].[Date Hierarchy].CURRENTMEMBER.Level.ORDINAL > 1
Report Server Name:
<ReportServer URL>
Report Path: ReportServer?/CubeReports/TicketsOpenedDetails
Parameters: UrlEscapeFragment(MEMBERTOSTR( [Ticket Report Date].[Date Hierarchy].CURRENTMEMBER ))

For week report data, the following were changed:

Condition: [Ticket Report Date].[Week Hierarchy].CURRENTMEMBER.Level.ORDINAL = 2 
Parameters: UrlEscapeFragment(MEMBERTOSTR( [Ticket Report Date].[Week Hierarchy].CURRENTMEMBER ))


July 2018
« Mar    

Blog Stats

  • 44,396 hits