Prologika Forums
Making sense of data

Filtering a Hierarchical Paramete list

rated by 0 users
This post has 1 Reply | 1 Follower

Top 500 Contributor
Male
Posts 2
SQLMonger Posted: Mon, Sep 25 2006 1:09 AM

I’m hoping an MDX guru can help me out.  I've solved some tough MDX problems, but this seemingly simple one has caused me to lose too much sleep already, so I'm throwing in the towel and asking for help.

I'm trying to come up with an MDX solution to writing a query to return a filtered hierarchical parameter list.  The list returned is of all the levels above the leaf level.  The query is used to drive a parameter drop-down in SQL Reporting Services 2000, which in turn is fed into another MDX query to return data for a report. The report query returns sample data for all the leaf nodes of the selected hierachy level.  SRS 2000 does not handle empty results from MDX queries too gracefully, so the goal is to present the user with only parameter selections that will return results.

The first query below works, but it returns the full hierarchy, when only part of the hierarchy has data for the time period selected in the sub-cube by time range.  What I want to be able to do is filter the hierarchy down to only the nodes that have leaf levels that actually have [Sample Value] data for the time period specified.

The first query below works, but it returns the full hierarchy, when only part of the hierarchy has data for the time period selected in the sub-cube by time range.  What I want to be able to do is filter the hierarchy down to only the nodes that have leaf levels that actually have [Sample Value] data for the time period specified.

The first query below works, but it returns the full hierarchy, when only part of the hierarchy has data for the time period selected in the sub-cube by time range.  What I want to be able to do is filter the hierarchy down to only the nodes that have leaf levels that actually have [Sample Value] data for the time period specified.

 

WITH MEMBER [Measures].[ParameterCaption]

AS '[Site Hierarchy].[Site Hierarchy].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue]

AS '[Site Hierarchy].[Site Hierarchy].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel]

AS '[Site Hierarchy].[Site Hierarchy].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption]

,[Measures].[ParameterValue]

,[Measures].[ParameterLevel]}

ON COLUMNS,

generate({[Site Hierarchy].[Site Hierarchy].[COMPANY].Members }

,{[Site Hierarchy].[Site Hierarchy].CurrentMember

,Generate([Site Hierarchy].[Site Hierarchy].Children

,{[Site Hierarchy].[Site Hierarchy].CurrentMember

,[Site Hierarchy].[Site Hierarchy].Children })})

ON ROWS 

FROM ( SELECT ( [Sample Date].[Time].[2002 Q1]:[Sample Date].[Time].[2006 Q3]  ) on 0

FROM [Water Quality]);

I’ve tried using filter() statements along with the Generate() statements, but then I only get nodes for one [Company] level, when I know there is data for several others.  I’ve tried filtering on the [sample count] measure as well as filtering on nonempty [Measures].[Sample Value] measure.  An example query using a filter() is below. It appears to work correctly at first glance, but under close testing, it is not returning expected results, albeit they may be correct results for the query, just not the results I need.

WITH MEMBER [Measures].[ParameterCaption]

AS '[Site Hierarchy].[Site Hierarchy].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue]

AS '[Site Hierarchy].[Site Hierarchy].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel]

AS '[Site Hierarchy].[Site Hierarchy].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption]

,[Measures].[ParameterValue]

,[Measures].[ParameterLevel]}

ON COLUMNS,

generate({filter([Site Hierarchy].[Site Hierarchy].[COMPANY].Members, [Measures].[sample count] > 0)}

,{[Site Hierarchy].[Site Hierarchy].CurrentMember

,Generate([Site Hierarchy].[Site Hierarchy].Children

,{[Site Hierarchy].[Site Hierarchy].CurrentMember

,[Site Hierarchy].[Site Hierarchy].Children })})

ON ROWS 

FROM ( SELECT ( [Sample Date].[Time].[2002 Q1]:[Sample Date].[Time].[2006 Q3]  ) on 0

FROM [Water Quality]);

Anyone who has run into this problem and/or has a solution, please let me know what worked for you.  This seems like a generic enough problem that others ought to have run into it already and solved it.

Thanks,

Clayton

 (also posted on MSDN forum)

I'm trying to come up with an MDX solution to writing a query to return a filtered hierarchical parameter list.  The list returned is of all the levels above the leaf level.  The query is used to drive a parameter drop-down in SQL Reporting Services 2000, which in turn is fed into another MDX query to return data for a report. The report query returns sample data for all the leaf nodes of the selected hierachy level.  SRS 2000 does not handle empty results from MDX queries too gracefully, so the goal is to present the user with only parameter selections that will return results.

The first query below works, but it returns the full hierarchy, when only part of the hierarchy has data for the time period selected in the sub-cube by time range.  What I want to be able to do is filter the hierarchy down to only the nodes that have leaf levels that actually have [Sample Value] data for the time period specified.

The first query below works, but it returns the full hierarchy, when only part of the hierarchy has data for the time period selected in the sub-cube by time range.  What I want to be able to do is filter the hierarchy down to only the nodes that have leaf levels that actually have [Sample Value] data for the time period specified.

The first query below works, but it returns the full hierarchy, when only part of the hierarchy has data for the time period selected in the sub-cube by time range.  What I want to be able to do is filter the hierarchy down to only the nodes that have leaf levels that actually have [Sample Value] data for the time period specified.

 

WITH MEMBER [Measures].[ParameterCaption]

AS '[Site Hierarchy].[Site Hierarchy].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue]

AS '[Site Hierarchy].[Site Hierarchy].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel]

AS '[Site Hierarchy].[Site Hierarchy].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption]

,[Measures].[ParameterValue]

,[Measures].[ParameterLevel]}

ON COLUMNS,

generate({[Site Hierarchy].[Site Hierarchy].[COMPANY].Members }

,{[Site Hierarchy].[Site Hierarchy].CurrentMember

,Generate([Site Hierarchy].[Site Hierarchy].Children

,{[Site Hierarchy].[Site Hierarchy].CurrentMember

,[Site Hierarchy].[Site Hierarchy].Children })})

ON ROWS 

FROM ( SELECT ( [Sample Date].[Time].[2002 Q1]:[Sample Date].[Time].[2006 Q3]  ) on 0

FROM [Water Quality]);

I’ve tried using filter() statements along with the Generate() statements, but then I only get nodes for one [Company] level, when I know there is data for several others.  I’ve tried filtering on the [sample count] measure as well as filtering on nonempty [Measures].[Sample Value] measure.  An example query using a filter() is below. It appears to work correctly at first glance, but under close testing, it is not returning expected results, albeit they may be correct results for the query, just not the results I need.

WITH MEMBER [Measures].[ParameterCaption]

AS '[Site Hierarchy].[Site Hierarchy].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue]

AS '[Site Hierarchy].[Site Hierarchy].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel]

AS '[Site Hierarchy].[Site Hierarchy].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption]

,[Measures].[ParameterValue]

,[Measures].[ParameterLevel]}

ON COLUMNS,

generate({filter([Site Hierarchy].[Site Hierarchy].[COMPANY].Members, [Measures].[sample count] > 0)}

,{[Site Hierarchy].[Site Hierarchy].CurrentMember

,Generate([Site Hierarchy].[Site Hierarchy].Children

,{[Site Hierarchy].[Site Hierarchy].CurrentMember

,[Site Hierarchy].[Site Hierarchy].Children })})

ON ROWS 

FROM ( SELECT ( [Sample Date].[Time].[2002 Q1]:[Sample Date].[Time].[2006 Q3]  ) on 0

FROM [Water Quality]);

Anyone who has run into this problem and/or has a solution, please let me know what worked for you.  This seems like a generic enough problem that others ought to have run into it already and solved it.

Thanks,

Clayton

 (also posted on MSDN forum)

Top 10 Contributor
Posts 2,304
tlachev replied on Mon, Sep 25 2006 7:16 PM
Not sure if this will help but when filtering did you try the HAVING clause, as I shown in this post?
Page 1 of 1 (2 items) | RSS