Solved

MDX Query filter column with value greater than zero

Posted on 2011-02-22
6
2,771 Views
Last Modified: 2012-05-11
I have this query

Select  
{[Buckets].[Header].[AGHeader]} on columns
,

Filter(
{[Account].[Accounts Name].[Name]} ,
([Measures].[Summary_Value]>0)
) ON ROWS


from capa
Where  [Buckets].[Type].&[AR]


which produces output similar to

-----------  Col1 col2 col3

ABC CO      1      0      1
BCE Co       2      0      2
HEO CO      3      1     2


I need to filter so results only contain the rows where col2 has a value greater than zero.

I am new to mdx so not sure what other info I need to provide.

0
Comment
Question by:JonMny
  • 3
  • 2
6 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 250 total points
Comment Utility
Is AGHeader a named set? I tried basically an identical select on one of my dbs and it filtered as expected.

What if you try this:

Select  
{[Buckets].[Header].[AGHeader],[Measures].[Summary_Value]} on columns,

Filter(
{[Account].[Accounts Name].[Name]} ,
([Measures].[Summary_Value]>0)
) ON ROWS


from capa
Where  [Buckets].[Type].&[AR]
0
 
LVL 9

Author Comment

by:JonMny
Comment Utility
no not a named set,

The query seems to filter values that are zero but It's not quite what I need

I need to show all agheaders  and value for a specific account but filter to only the times when col2 has a zero value. the other columns can have a zero.




0
 
LVL 10

Expert Comment

by:itcouple
Comment Utility
Hi

Might be worth posting entire query. The syntax you used is correct. Just ensure you use the correct measures that generates values for column 2.

Regards
Emil
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Author Comment

by:JonMny
Comment Utility
I will try to create a more complete example.


here is a what i want to due but of course doesnt work.

from capa
Where  [Buckets].[Type].&[AR]
and
( [Buckets].[AgingHeader].&[52] and [Measures].[Invoice_Amount]>0)


0
 
LVL 10

Assisted Solution

by:itcouple
itcouple earned 250 total points
Comment Utility
Hi

Is that the complete query?

Using where may produce unexpected results with MDX. use [Measures].[Invoice_Amount]>0) using filter on rows.

If you know T-SQL (like me) then forget about it!! It will be much easier :)

Regards
Emil

0
 
LVL 9

Author Comment

by:JonMny
Comment Utility
The issue I am having is that all three columns are tied to the invoice_amount. So if I filter invoice amount> 0 I end up filtering columns that I need.


0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now