Hi All,
Have the following query in an RS report that tracks Projects and their
managers BY TIME from an Analysis services Cube. I want to be able to have a
query-based parameter on Geography.Division where by a user will be prompted
with a dropdown list and accordingly the query will be executed. The below
syntax is no working when i substituted .MEMBERS with .[" + Parameters!PDiv.
Value + "] for the {[Geography].[Div].[" + Parameters!PDiv.Value + "]} SET.

When I used the "*" instead of NONEMPTYCROSSJOIN, it worked. I would really
appreciate the help here.


='
WITH
MEMBER [measures].[Finish] AS 'iif(isempty([Measures].[Finished Count]),0,
[Measures].[Finished Count])'
MEMBER [measures].[Achieve] AS 'iif(isempty([Measures].[Passed Count]),0,
[Measures].[Achieved Count])'
MEMBER [measures].[Wait] AS 'iif(isempty([Measures].[Wait listed Count]),0,
[Measures].[Waiting Count])'
MEMBER [measures].[Hours] AS 'iif(isempty([Measures].[Time Consumed]),0,
[Measures].[Time Consumed])'
SELECT
{[Measures].[Finish],[Measures].[Achieve],[measures].[Wait],[Measures].[Hours]
} ON COLUMNS,
{NONEMPTYCROSSJOIN(NONEMPTYCROSSJOIN(NONEMPTYCROSS JOIN(NONEMPTYCROSSJOIN({
[Geography].[Div].[" + Parameters!PDiv.Value + "]} ,{[Project].[Project].
Members}),{[Manager].[Manager name].members}),{[Time].[Period Id].members}),{
[Person].[Person Job].members})}
ON ROWS
FROM [Project Evaluations]'

(I ALSO POSTED THE SAME MESSAGE ON REPORTING SERVICES DISCUSSION GROUP)

Thanks


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...-olap/200507/1

You need to be careful, "*" is not the same thing as NONEMPTYCROSSJOIN, it's
really the same as CROSSJOIN.

when you compose something like:

select ({[Customers].[Country].members} * {[Gender].[Gender].Members}) on
columns,
{[Time].[1997].children} on rows
from [sales]


....you're doing a crossjoin, not a nonemptycrossjoin. In other words, it
translates to:

select crossjoin({[Customers].[Country].members} ,
{[Gender].[Gender].Members}) on columns,
{[Time].[1997].children} on rows
from [sales]


Granted, you can use "*", but if you want to use it for nonempty
crossjoining, you have to explicity tell it to do so, for example:

select nonemptycrossjoin({[Customers].[Country].members} *
{[Gender].[Gender].Members}) on columns,
{[Time].[1997].children} on rows
from [sales]


The more nonemptycrossjoining you do as you put more and more dims on your
axes, the more likely you are to come up with an empty cellset.


- Phil


"James Woo via SQLMonster.com" wrote:

Thanks for the information. really helped.

SQL McOLAP wrote:

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...-olap/200507/1