Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Removing 0 value measures from result sets

Status
Not open for further replies.

spolap

Technical User
Apr 1, 2004
1
US
Any suggestions would be greatly appreciated..
The below query returns results in less than 3 sec.'s.
However, when the application substitues the FuncUnitList set with '{[FuncUnit].&[All]}' the results take over a minute to come back.
This appears to be related to the filtering of SUM({Axis(0)},[Measures].[Activity Count])> 0)

Any help would be greatly appreciated..

-- MDX Query from sampleapp
WITH
SET [DateRangeSpan] AS
'{[DueDate].[YQMD].[All Dates].[2004].[Quarter 1].[February].[26] :[DueDate].[YQMD].[All Dates].[2004].[Quarter 1].[March].[25]}'
SET [FuncUnitList] AS
'{[FuncUnit].&[Merchandise/Media]}'

SET [ActivityList] AS
'{[Activity].[Activity].Members}'

SELECT
{[OrgUnit].&[4-040], [OrgUnit].&[4-040].CHILDREN} DIMENSION PROPERTIES [OrgUnit].[Org Type] ON COLUMNS,
Subset (
FiLTER (
CROSSJOIN (
{EXTRACT ( NONEMPTYCROSSJOIN ( [ActivityList], [DateRangeSpan] , [FuncUnitList] ), Activity) },
{[Measures].[Stores Included],[Measures].[Stores Complete],[Measures].[% Complete]})
, SUM({Axis(0)},[Measures].[Activity Count])> 0)
, 0, 150)
DIMENSION PROPERTIES [Activity].[Activity].[Event ID], [Activity].[Activity].[Process ID], [Activity].[Activity].[Activity ID],
[Activity].[Activity].[Event Name], [Activity].[Activity].[Process Name],
[Activity].[Activity].[Activity Due Date], [Activity].[Activity].[FuncUnit Name] ON ROWS

FROM [CompletionMonitor]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top