MDX Query Help

[Sukhdev]

Hi,
I am writing a custom application that creates an MDX expression on the fly
and passes it to the pivot table.
The following mdx query is generating an error when passed to the pivot table.

Select
{[Time].[All Time].Children} on rows,
{{[Product].[All Product].Children}*{[Outlet].[All Outlet].Children}} on
columns,
{
[Measures].[Adj Rate Of Sale],
[Measures].[Num Distribution],
[Measures].[Retail Sell Price],
[Measures].[Unit Sales Perc],
[Measures].[Value Sales],
[Measures].[Value Sales Perc],
[Measures].[Volume Sales],
[Measures].[Weight Distibution]} on pages
from [myCube]

Note: When the above query is passed to the pivot table it generates the
following error:
"The query could not be processed:
o The data provider didn't supply any further error information."

I've tried a lot and could figure out what's wrong with the query.
Please some advise me and show me how to solve this problem.

Note: I have another cube, which i pass a similar query and it worked
perfectly.
Here's the query that worked fine.

Select {[Month].[All Month].Children} on rows,
{{[Outlet].[All Outlet].Children} * {[Product].[All Product].Children}} on
columns,
{[Measures].[Sales Units],
[Measures].[Sales Value],
[Measures].[Sales Value Usd]} on pages
from Sales

Now I'm not sure, if there's a problem within the cube (myCube) or the mdx,
because when I pass certain queries to myCube, it works fine.

Example of a query that works fine on myCube cube

Select {[Time].[All Time].Children} on rows,
{[Product].[All Product].Children} on columns,
{[Measures].[Adj Rate Of Sale],
[Measures].[Num Distribution],
[Measures].[Retail Sell Price],
[Measures].[Unit Sales Perc],
[Measures].[Value Sales],
[Measures].[Value Sales Perc],
[Measures].[Volume Sales],
[Measures].[Weight Distibution]} on pages
from [myCube]
Where [Outlet].[All Outlet].[Location1]

i'm really confused and need help desperately.

Thank you for your time.
Sukhdev.

 

[Deepak]

How large is the cross-joined column set - is there some limit that it could
be hitting? In case you don't need empty result cells and the set is sparse,
does using the NON EMPTY clause help, like:

NON EMPTY {{[Outlet].[All Outlet].Children} * {[Product].[All
Product].Children}}

 

[Guest]

I'm sorry; I don't know MDX that well

but have you tried rewriting this

NON EMPTY {{[Outlet].[All Outlet].Children} * {[Product].[All
Product].Children}}

to this:

nonemptycrossjoin(outlet.children, product.children)

i just don't think that you need the extra brackets; and i dont think that
you need the all levels

How large is the cross-joined column set - is there some limit that it could
be hitting? In case you don't need empty result cells and the set is sparse,
does using the NON EMPTY clause help, like:

NON EMPTY {{[Outlet].[All Outlet].Children} * {[Product].[All
Product].Children}}

Hi,
I am writing a custom application that creates an MDX expression on the fly
and passes it to the pivot table.
The following mdx query is generating an error when passed to the pivot table.

Select
{[Time].[All Time].Children} on rows,
{{[Product].[All Product].Children}*{[Outlet].[All Outlet].Children}} on
columns,
{
[Measures].[Adj Rate Of Sale],
[Measures].[Num Distribution],
[Measures].[Retail Sell Price],
[Measures].[Unit Sales Perc],
[Measures].[Value Sales],
[Measures].[Value Sales Perc],
[Measures].[Volume Sales],
[Measures].[Weight Distibution]} on pages
from [myCube]

Note: When the above query is passed to the pivot table it generates the
following error:
"The query could not be processed:
o The data provider didn't supply any further error information."

I've tried a lot and could figure out what's wrong with the query.
Please some advise me and show me how to solve this problem.

Note: I have another cube, which i pass a similar query and it worked
perfectly.
Here's the query that worked fine.

Select {[Month].[All Month].Children} on rows,
{{[Outlet].[All Outlet].Children} * {[Product].[All Product].Children}} on
columns,
{[Measures].[Sales Units],
[Measures].[Sales Value],
[Measures].[Sales Value Usd]} on pages
from Sales

Now I'm not sure, if there's a problem within the cube (myCube) or the mdx,
because when I pass certain queries to myCube, it works fine.

Example of a query that works fine on myCube cube

Select {[Time].[All Time].Children} on rows,
{[Product].[All Product].Children} on columns,
{[Measures].[Adj Rate Of Sale],
[Measures].[Num Distribution],
[Measures].[Retail Sell Price],
[Measures].[Unit Sales Perc],
[Measures].[Value Sales],
[Measures].[Value Sales Perc],
[Measures].[Volume Sales],
[Measures].[Weight Distibution]} on pages
from [myCube]
Where [Outlet].[All Outlet].[Location1]

i'm really confused and need help desperately.

Thank you for your time.
Sukhdev.