SQL Server Performance Forum – Threads Archive
NON EMPTY
Hello<br /><br />I have an MDX statement that looks like this: <br /><br />SELECT<br /> { Measures.members } on Columns ,<br /> { CrossJoin( [Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].[July], <br /> [Business].[User].[Business Entity Id].[212].Children ) } on Rows<br />FROM UsageStats<br /><br /><br />And it returns this via a matrix report:<br /><br /> 2004<br /> |——|——|——|——|<br /> | May | June | July |Total |<br />————|——|——|——|——|<br />Alan Smith | 2 | 3 | 2 | 7 |<br />Amy Marcus | | 3 | 3 | 6 |<br />Bob Fields | | | | |<br />Carry Grant | 1 | | | 1 |<br />————|——|——|——|——|<br />TOTAL | 3 | 6 | 5 | 14 |<br />————|——|——|——|——|<br /><br />The report above represents hits to a website on a per-Monthly basis for each user.<br /><br /><br />I would like to supress the empty rows so that Bob Fields, above, won’t show up at all. I tried using NonEmptyCrossJoin like this:<br /><br />SELECT<br /> { Measures.members } on Columns ,<br /> { NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].[July], <br /> [Business].[User].[Business Entity Id].[212].Children ) } on Rows<br />FROM UsageStats<br /><br />and just NON EMPTY like this:<br /><br />SELECT<br /> { Measures.members } on Columns ,<br /> { NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].[July], <br /> [Business].[User].[Business Entity Id].[212].Children ) } on Rows<br />FROM UsageStats<br /><br /><br />Both methods above supress the empty rows, but they also supress anyone who hasn’t had any hits to the site in the first month, even if they do have hits in the 2nd or 3rd months (so both Bob Fields, and Amy Marcus are both left out of the report above). [V]<br /><br />Does anyone know how to possibly get around this? Any help at all will be greatly appreciated!!! [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]<br /><br />Best regards<br />Maria
]]>