I want to get all the country names from a cube where another Key figure is NOT equal spaces or zeros. Please let me know if some one can help me.
My Cube is CFS_PERF/CFS_PERF_CFSPERF_GL
Want to get 0PAYER_COUNTRY where
[Measures].[3M1DTA8FO1HVYVOI6HUABEXHX] is NOT equal to zero
Please help !!!
Try this:
SELECT [Measures].[3M1DTA8FO1HVYVOI6HUABEXHX] ON 0,
NON EMPTY [OPAYER_COUNTRY].members ON 1
FROM CFS_PERF_CFSPERF_GL
I may have made a discovery why I am not seeing the filter and parameter pane in the MDX Query Designer in SQL Server Business Intelligence Development Studio.The filter and parameter pane does appear in the MDX Query Designer in SQL Server Business Intelligence Development Studio on a report created from a SQL Server 2005 database, but not on a SQL Server 2000 database. Am I on to something and is there a work around for SQL Server 2000 databases?
I may have made a discovery why I am not seeing the filter and parameter pane in the MDX Query Designer in SQL Server Business Intelligence Development Studio.The filter and parameter pane does appear in the MDX Query Designer in SQL Server Business Intelligence Development Studio on a report created from a SQL Server 2005 database, but not on a SQL Server 2000 database. Am I on to something and is there a work around for SQL Server 2000 databases?
I would appreciate any help with the following problem:
Other than cube redesign, which I know we can accomplish, if theres an actual way through MDX to filter structure between 2 dimensions. As of right now I can only accomplish filtering measures by 2 dimensions. Example:
SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )
What occurs is all members from Dimension1 is returned and the Measures are filtered accordingly. What I want is it to only bring back the rows which have a relationship with Dimension2. Is this possible?
But if I try to filter structure across 2 different heirarchies in the same dimension it works fine.
SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[Heirarchy2].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension1].[Heirarchy1].[AnyHeirarchyLevel].[AnyMember] )
This only returns rows in which the two heirarchy members are related.
So obvisouly If I moved my 2nd Dimension under the 1st Dimesion and make it a heirachy it would work, but any other way around this?
Thanks a lot.
When you say: "only bring back the rows which have a relationship with Dimension2", are they only related via the fact table, or in some other way? In the former case, try Non Empty, like:
SELECT { [Measures].Members } ON COLUMNS,
Non Empty [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )
Thanks for the reply.
Yes, they are only related via the fact table. If I try non empty, and please correct me if im wrong, that will filter by measures (sales, units, etc), and only show members that have sales/units. In essence exluding the real relationships (members that have no sales won't be shown)
I would like to bring back every member that is related to Dimension 2's member, regardless of no sales or not.
Thanks.
|||The fact of sales is a relation between dimension1 and dimension2. There isn't another relation between dimension1 and dimension2.
If you have another one the it should exists as another fact table or anything else.
