Top 100 items - 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)

Top 100 items

Author

Topic

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-07-10 : 09:42:21

Hi, i need to write this mdx query as top 100 keywords.
How do i do this. Where do i put the topcount. Can someone please help me.

Thanks

SELECT NON EMPTY { [Measures].[Searches] } ON COLUMNS, NON EMPTY { ([VAR Program].[VAR Program ].[VAR Program].ALLMEMBERS * [Time].[By Calendar Month].[Date].ALLMEMBERS * [Keyword].[Keyword ].[Keyword].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@GeographyGeography, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@BrowserBrowser, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@TimeByCalendarMonth, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@VARProgramVARProgram, CONSTRAINED) ) ON COLUMNS FROM [ Analytics])))) WHERE ( IIF( STRTOSET(@BrowserBrowser, CONSTRAINED).Count = 1, STRTOSET(@BrowserBrowser, CONSTRAINED), [ Browser].[Browser].currentmember ), IIF( STRTOSET(@GeographyGeography, CONSTRAINED).Count = 1, STRTOSET(@GeographyGeography, CONSTRAINED), [Geography].[Geography].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-07-16 : 14:00:45

Please help. I want to get the top 100 keywords using this query. How do i write this to get the top 100 items
SELECT NON EMPTY { [Measures].[Searches] } ON COLUMNS,
NON EMPTY { ([Keyword].[Keyword Id].[Keyword Id].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS FROM [B2R Analytics]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-07-16 : 14:03:55

I figured it out i think.
SELECT NON EMPTY { [Measures].[Searches] } ON COLUMNS,
NON EMPTY { topcount([Keyword].[Keyword Id].[Keyword Id].ALLMEMBERS,100 , [Measures].[Searches]) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [B2R Analytics] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources