MDX, Getting ItemCount and Adding RowCount

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)

MDX, Getting ItemCount and Adding RowCount

Author

Topic

GPDynamics
Starting Member

1 Post

Posted - 2011-12-22 : 23:59:51

How would I get the RowCount (# of items) and add a column for the row number to the following query:

SELECT [MEMBER_UNIQUE_NAME]
FROM $system.mdschema_members
WHERE cube_name = 'Adventure Works'
ORDER BY [MEMBER_UNIQUE_NAME]

I get close with:

BEGIN
CREATE TABLE #Temp_MDXResults (id_num INT IDENTITY(1,1), Member VARCHAR(500))

INSERT INTO #Temp_MDXResults (Member)

SELECT * FROM OpenRowset (
'MSOLAP','DataSource=XPSM1530\XPSM1530SQL2008; Catalog=Adventure Works DW 2008; Integrated Security=SSPI;',

'SELECT [MEMBER_UNIQUE_NAME] AS MEMBER
FROM [$SYSTEM.MDSCHEMA_MEMBERS]
WHERE CUBE_NAME = [Adventure Works]
ORDER BY [MEMBER_UNIQUE_NAME]'
) MICHAELDNEWBY

SELECT id_num, Member
FROM #Temp_MDXResults

--DROP TABLE #Temp_MDXResults
END

But I get errors:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Any ideas?

Thanks in advance!

Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources