How do I count the amount of distinct values from a column in a fact table as a calculated measure
Assume the primary key of my fact table is a composite of three columns (a,b,c).
How can I code a calculated measure to count every distinct value of column a, not the amount of rows in my fact table.
Any advise help would be appreciated!
All you need to do is change the measure type from Count to Distinct Count ... make sure you select Column A as your key column....Hi everybody,
I've got a Fact Data table with a value and 16 dimensions.
Now I want to create a second measure group Color with a value and 3 dimensions.
I've filled this table with values and id's for each dimension.
Bu when I make an mdx query with a measure from the FactData and a measure from the second measure group (Color), only the first measure has a value, the second (the measure from Color) is null.
Is these something I've forgotton to set in the cube?
thanks in advance
Filip
Hi,
If you run a simple query (below) do you get two columns of data in the results?
select {[Measures].[Measure Group 1],[Measures].[Measure Group Color]} on 0
from [Cube]
results:
Measure Group 1 Measure Group Color
123231 123213
If not I suspect something else is wrong, perhaps check that you have set up the joins between the dimensions and the facts correctly. If you do get values in both, perhaps it is worth while posting your query.
Hope it helps,
Matt
|||
Hi,
that query runs.
but I have another problem now, this measure group I've created for storing color information, only can't have a Sum or Count as aggregation.
I cannot see the 65280 or ... value for the color I want to use.
I didn't included a period dimension to this measure group.
Is that the cause?
Filip
|||Hi,
Sorry for delay I was investigating. If the measure is not aggregatable then you will get Null in the measure unless you go down to the granularity in which the data is held at. This is an area not that familar with and finding it difficult to prove.
But if you don't include a dimension in measure and the measures are aggregatable it will not causes nulls to appear, you get funny results. e.g.
select {[Measures].[Measure Group 1],[Measures].[Measure group 2]} on 0,
[Dimension only on measure group 1] on 1
from [cube]
Results:
Measure Group 1 Measure Group 2
dim a 45 1234 1234 being the total in measure group 2
dim b 23 1234
dim c 78 1234
Sorry I could answer it more positively.
Matt
|||thank you for your help!
it works at a certain level.
at the granularity level, it works fine,
but once it starts aggregating, the value 256 becomes a sum of x times 256.
I have to solve that problem...
thansk for you help
Filip
Hi,
I am using the Standard Edition of SQL Server 2005. I have a financial reporting cube with the measure Amount and several dimensions (Time, Account, etc). A simplified version of the data is:
If I use the default cube then this is how data will show. What I would like to do is to add a YTD-Balance measure to the cube. This YTD-Balance measure would show the following data:
So instead of showing the difference in a month (eg 20) like the underlying data, I would like to show a running total for the year (eg 200+20=220). My attempt so far is:
Create Member CurrentCube.[Measures].[YTDBalance]
AS SUM({PeriodsToDate([Time].[(All)])}, [Measures].[Amount]),
NON_EMPTY_BEHAVIOR = { [Amount] },
VISIBLE = 1;
This doesn't do what I want however: it sums all months in a quarter, all quarters in a year, and all years in the cube. What I instead want is for each month to be a summary of the months in the year so far. Is anyone able to help me out at all with this?
Thanks, Matt
Assuming that there is a [Year] attribute in the [Time] dimension, whose type is "Years"; and that there is a [Time].[Calendar] hierarchy (as in the Adventure Works Date dimension), then you should be able to use the YTD() function:
Create Member CurrentCube.[Measures].[YTDBalance]
AS SUM(YTD([Time].[Calendar].CurrentMember), [Measures].[Amount]),
NON_EMPTY_BEHAVIOR = { [Measures].[Amount] },
VISIBLE = 1;
Hi,
Here is my question.
For the last year special store sales amount, we only calculate sales amount based on special store lists in current selected date.
If a store in current date is not a special store then we do not include this store for last year sales amount (even if this store was special store in last year). This means we don't care about last year store status and we only identify the special store status of all stores according to the date we selected.
In the fact, I join the flag ( which represents a special store) and added one colum for special sales calcualtion. (flag * net sales). In the cube, Currnet speical sales are correct but I couldn't get correct last year specail sales amount.
Below is the calculation for last year specail store sales amount but the numbers do not match.
IIF(([Measures].[flag], [Date].[Fiscal Hierarchy].CurrentMember)=False,0,
(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))
Please give me some comments.
Thanks in advance.
Boolean values as measures just don't work well. There probably would be a way to write the MDX calculation, but the performance would be really bad probably to the point of being unusable.
You could implement the flag as it's own dimension, but probably the best approach is to implement the store dimension as a slowly changing dimension. What you would be after is a type 2 changing dimension. If you are not aware of what this is, you are basically adding a new record to the store dimension when the value of the flag changes
Below is a simplified example, notice that "Store 1" has StoreID = 1 and StoreID = 3. It is often common to store effective dates against the records for slowly changing dimensions to make it easier to figure out which dimension record applies to a given fact record.
StoreID StoreName SpecialFlag
1 Store 1 False
2 Store 2 False
3 Store 1 True
Then when you insert facts you insert the StoreID that was effective for the date of the respective facts.
StoreID DateID SalesAmount
1 20060101 100
3 20070601 100
Using this technique you would be able to create a "Special Store" attribute in your store dimension and filter using that.
|||Thanks for the reply. I think I didn't give you a right explanation.
I understand what you suggested but could you take a look at this one more time?
Below is the details how it looks like :
Selected date : 20070101
Last year date : 20060101
Sales Fact
ID Store_key Date Sales
1 store 1 20070101 100.00
2 store 2 20070101 150.00
3 store 3 20070101 100.00
4 store 1 20060101 30.00
5 store 2 20060101 30.00
6 store 3 20060101 30.00
There is an anther fact table which has a flag.
Special_Store Fact
Special_Store_key Store_key Date Key SS_flag
1 store 1 20070101 True
2 store 1 20060101 False
3 store 2 20070101 False
4 store 2 20060101 True
5 store 3 20070101 False
6 store 3 20060101 True
Now I joined this 2 Fact to add Specail Store Sales into the Sales Fact
ID Store_key Date Sales SS_flag SS_Sales (SS flag * Sales)
1 store 1 20070101 100.00 True 100.00
2 store 2 20070101 150.00 False 0.00
3 store 3 20070101 100.00 True 100.00
4 store 1 20060101 30.00 False 0.00
5 store 2 20060101 30.00 True 30.00
6 store 3 20060101 30.00 True 30.00
From this data,
we calulate SS sales amount and the total is 130.00 which is not a problem to calculate in the cube.
My problem is that for the last year SS sales amount with time calculation we developed inside the cube,
time calculation sums up all SS_Sales, so last year total is 60.00.
But from the selected date , we do not consider last year store status. We only care current Special Store and From this list we calculate last year value. So, in this data, we need to get 30 (excluding store 2 since store 2 is not special store in the selected date 20070101).
As you mentioned, if boolean value does not work properly and affects performance then how can I approach in this situation? Any Suggestions?
Please let me know.
Thanks.
|||I am pretty sure that my original suggestion of not adding this flag to the fact table and implementing a slowly changing dimension would work. But possibly a simpler solution might be to insert NULL insead of 0.00 into the SS_Sales measure and then do something like the following, which basically checks if there is a value for [Special Sales] in the current period.
IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,
(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))
The only problem with this is that, if you have a hierarchy over the stores so that they roll up into groups of some sort then you will get incorrect results at the higher levels. If any store in the group was "sepcial" last year the value for the entire group would be included
Which would mean that you might have to do something like the following to force this expression to be evaluated over individual store members.
SUM( EXISTING [Store].[Store].[Store].Members ,
IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,
(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))
)|||There seems to be some inconsistency in the sample data: Store 3, 20070101 is shown as a special sale in the joined fact table, but not in Special_Store_Fact - I assume that is just a typo?
Anyway, since SS_Flag is already a field in the joined fact table, another approach would to create a simple true/false dimension like [Special Flag]. Then the MDX expressions could be:
For current year [Special Sales]:
([Special Flag].[SL_Flag].[True], [Measures].[Sales])
And for previous year [Special Sales]:
Sum(NonEmpty([Store].[Store].[Store].Members,
([Special Flag].[SL_Flag].[True], [Measures].[Sales])),
(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),
[Measures].[Sales]))
|||That's probably a reasonable compromise and would be relatively easy to implement.
The only comment I would make is that modelling changing attributes in this matter should be the exception, not the rule. You would not want to take this to the extreme and end up with a cube that has lots of small dimensions as it increases the size of the aggregations and indexes which will reduce performance.
|||
Hi,
It's been a quite long time since you posted this.
Now, we have some changes regarding this special store's calculation.
Previously, we only select the current special store list, and for the last year value, we use current lists of special stores and if any store which was not special store in parallel period then we excluded it from the calculation.
I've tried this MDX into the cube and current year value works perfectly, but last year value doesn't work properly.
MDX for the last year :
Sum(NonEmpty([Store].[Store Hierarchy].currentmember,
([Special Flag].[SL_Flag].[True], [Measures].[Sales])),
(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),
[Measures].[Sales]))
> This mdx calculate all the last year value that store became a special in last year. it can't get the current special store lists.
But now, whether store was not a spcial store in last year, if store became a special store in selected date ( current) then we sum up all store's sale amount into the last year value.
below is the MDX for the last year:
([Special Flag].[SL_Flag].[True],
(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),
[Measures].[Sales]))
But when i browse the cube, it seems last year special flag is used for last year calculation. So, I can see all special store sales amount in last year. (not exclude store which is not a special store in current year)
I've tried to do different ways but still couldn't get any solutions yet.
It seems the MDX is something wrong.
If I want to use current special flag for the parallelperiod calculation how should I write MDX for that?
I think this logic makes sense to me but I don't know why it doesn't work.
I would appreciate if anybody can give me some comments.
Thanks.
Hi,
Here is my question.
For the last year special store sales amount, we only calculate sales amount based on special store lists in current selected date.
If a store in current date is not a special store then we do not include this store for last year sales amount (even if this store was special store in last year). This means we don't care about last year store status and we only identify the special store status of all stores according to the date we selected.
In the fact, I join the flag ( which represents a special store) and added one colum for special sales calcualtion. (flag * net sales). In the cube, Currnet speical sales are correct but I couldn't get correct last year specail sales amount.
Below is the calculation for last year specail store sales amount but the numbers do not match.
IIF(([Measures].[flag], [Date].[Fiscal Hierarchy].CurrentMember)=False,0,
(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))
Please give me some comments.
Thanks in advance.
Boolean values as measures just don't work well. There probably would be a way to write the MDX calculation, but the performance would be really bad probably to the point of being unusable.
You could implement the flag as it's own dimension, but probably the best approach is to implement the store dimension as a slowly changing dimension. What you would be after is a type 2 changing dimension. If you are not aware of what this is, you are basically adding a new record to the store dimension when the value of the flag changes
Below is a simplified example, notice that "Store 1" has StoreID = 1 and StoreID = 3. It is often common to store effective dates against the records for slowly changing dimensions to make it easier to figure out which dimension record applies to a given fact record.
StoreID StoreName SpecialFlag
1 Store 1 False
2 Store 2 False
3 Store 1 True
Then when you insert facts you insert the StoreID that was effective for the date of the respective facts.
StoreID DateID SalesAmount
1 20060101 100
3 20070601 100
Using this technique you would be able to create a "Special Store" attribute in your store dimension and filter using that.
|||Thanks for the reply. I think I didn't give you a right explanation.
I understand what you suggested but could you take a look at this one more time?
Below is the details how it looks like :
Selected date : 20070101
Last year date : 20060101
Sales Fact
ID Store_key Date Sales
1 store 1 20070101 100.00
2 store 2 20070101 150.00
3 store 3 20070101 100.00
4 store 1 20060101 30.00
5 store 2 20060101 30.00
6 store 3 20060101 30.00
There is an anther fact table which has a flag.
Special_Store Fact
Special_Store_key Store_key Date Key SS_flag
1 store 1 20070101 True
2 store 1 20060101 False
3 store 2 20070101 False
4 store 2 20060101 True
5 store 3 20070101 False
6 store 3 20060101 True
Now I joined this 2 Fact to add Specail Store Sales into the Sales Fact
ID Store_key Date Sales SS_flag SS_Sales (SS flag * Sales)
1 store 1 20070101 100.00 True 100.00
2 store 2 20070101 150.00 False 0.00
3 store 3 20070101 100.00 True 100.00
4 store 1 20060101 30.00 False 0.00
5 store 2 20060101 30.00 True 30.00
6 store 3 20060101 30.00 True 30.00
From this data,
we calulate SS sales amount and the total is 130.00 which is not a problem to calculate in the cube.
My problem is that for the last year SS sales amount with time calculation we developed inside the cube,
time calculation sums up all SS_Sales, so last year total is 60.00.
But from the selected date , we do not consider last year store status. We only care current Special Store and From this list we calculate last year value. So, in this data, we need to get 30 (excluding store 2 since store 2 is not special store in the selected date 20070101).
As you mentioned, if boolean value does not work properly and affects performance then how can I approach in this situation? Any Suggestions?
Please let me know.
Thanks.
|||I am pretty sure that my original suggestion of not adding this flag to the fact table and implementing a slowly changing dimension would work. But possibly a simpler solution might be to insert NULL insead of 0.00 into the SS_Sales measure and then do something like the following, which basically checks if there is a value for [Special Sales] in the current period.
IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,
(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))
The only problem with this is that, if you have a hierarchy over the stores so that they roll up into groups of some sort then you will get incorrect results at the higher levels. If any store in the group was "sepcial" last year the value for the entire group would be included
Which would mean that you might have to do something like the following to force this expression to be evaluated over individual store members.
SUM( EXISTING [Store].[Store].[Store].Members ,
IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,
(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))
)|||There seems to be some inconsistency in the sample data: Store 3, 20070101 is shown as a special sale in the joined fact table, but not in Special_Store_Fact - I assume that is just a typo?
Anyway, since SS_Flag is already a field in the joined fact table, another approach would to create a simple true/false dimension like [Special Flag]. Then the MDX expressions could be:
For current year [Special Sales]:
([Special Flag].[SL_Flag].[True], [Measures].[Sales])
And for previous year [Special Sales]:
Sum(NonEmpty([Store].[Store].[Store].Members,
([Special Flag].[SL_Flag].[True], [Measures].[Sales])),
(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),
[Measures].[Sales]))
|||That's probably a reasonable compromise and would be relatively easy to implement.
The only comment I would make is that modelling changing attributes in this matter should be the exception, not the rule. You would not want to take this to the extreme and end up with a cube that has lots of small dimensions as it increases the size of the aggregations and indexes which will reduce performance.
|||
Hi,
It's been a quite long time since you posted this.
Now, we have some changes regarding this special store's calculation.
Previously, we only select the current special store list, and for the last year value, we use current lists of special stores and if any store which was not special store in parallel period then we excluded it from the calculation.
I've tried this MDX into the cube and current year value works perfectly, but last year value doesn't work properly.
MDX for the last year :
Sum(NonEmpty([Store].[Store Hierarchy].currentmember,
([Special Flag].[SL_Flag].[True], [Measures].[Sales])),
(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),
[Measures].[Sales]))
> This mdx calculate all the last year value that store became a special in last year. it can't get the current special store lists.
But now, whether store was not a spcial store in last year, if store became a special store in selected date ( current) then we sum up all store's sale amount into the last year value.
below is the MDX for the last year:
([Special Flag].[SL_Flag].[True],
(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),
[Measures].[Sales]))
But when i browse the cube, it seems last year special flag is used for last year calculation. So, I can see all special store sales amount in last year. (not exclude store which is not a special store in current year)
I've tried to do different ways but still couldn't get any solutions yet.
It seems the MDX is something wrong.
If I want to use current special flag for the parallelperiod calculation how should I write MDX for that?
I think this logic makes sense to me but I don't know why it doesn't work.
I would appreciate if anybody can give me some comments.
Thanks.
Hi,
Here is my question.
For the last year special store sales amount, we only calculate sales amount based on special store lists in current selected date.
If a store in current date is not a special store then we do not include this store for last year sales amount (even if this store was special store in last year). This means we don't care about last year store status and we only identify the special store status of all stores according to the date we selected.
In the fact, I join the flag ( which represents a special store) and added one colum for special sales calcualtion. (flag * net sales). In the cube, Currnet speical sales are correct but I couldn't get correct last year specail sales amount.
Below is the calculation for last year specail store sales amount but the numbers do not match.
IIF(([Measures].[flag], [Date].[Fiscal Hierarchy].CurrentMember)=False,0,
(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))
Please give me some comments.
Thanks in advance.
Boolean values as measures just don't work well. There probably would be a way to write the MDX calculation, but the performance would be really bad probably to the point of being unusable.
You could implement the flag as it's own dimension, but probably the best approach is to implement the store dimension as a slowly changing dimension. What you would be after is a type 2 changing dimension. If you are not aware of what this is, you are basically adding a new record to the store dimension when the value of the flag changes
Below is a simplified example, notice that "Store 1" has StoreID = 1 and StoreID = 3. It is often common to store effective dates against the records for slowly changing dimensions to make it easier to figure out which dimension record applies to a given fact record.
StoreID StoreName SpecialFlag
1 Store 1 False
2 Store 2 False
3 Store 1 True
Then when you insert facts you insert the StoreID that was effective for the date of the respective facts.
StoreID DateID SalesAmount
1 20060101 100
3 20070601 100
Using this technique you would be able to create a "Special Store" attribute in your store dimension and filter using that.
|||Thanks for the reply. I think I didn't give you a right explanation.
I understand what you suggested but could you take a look at this one more time?
Below is the details how it looks like :
Selected date : 20070101
Last year date : 20060101
Sales Fact
ID Store_key Date Sales
1 store 1 20070101 100.00
2 store 2 20070101 150.00
3 store 3 20070101 100.00
4 store 1 20060101 30.00
5 store 2 20060101 30.00
6 store 3 20060101 30.00
There is an anther fact table which has a flag.
Special_Store Fact
Special_Store_key Store_key Date Key SS_flag
1 store 1 20070101 True
2 store 1 20060101 False
3 store 2 20070101 False
4 store 2 20060101 True
5 store 3 20070101 False
6 store 3 20060101 True
Now I joined this 2 Fact to add Specail Store Sales into the Sales Fact
ID Store_key Date Sales SS_flag SS_Sales (SS flag * Sales)
1 store 1 20070101 100.00 True 100.00
2 store 2 20070101 150.00 False 0.00
3 store 3 20070101 100.00 True 100.00
4 store 1 20060101 30.00 False 0.00
5 store 2 20060101 30.00 True 30.00
6 store 3 20060101 30.00 True 30.00
From this data,
we calulate SS sales amount and the total is 130.00 which is not a problem to calculate in the cube.
My problem is that for the last year SS sales amount with time calculation we developed inside the cube,
time calculation sums up all SS_Sales, so last year total is 60.00.
But from the selected date , we do not consider last year store status. We only care current Special Store and From this list we calculate last year value. So, in this data, we need to get 30 (excluding store 2 since store 2 is not special store in the selected date 20070101).
As you mentioned, if boolean value does not work properly and affects performance then how can I approach in this situation? Any Suggestions?
Please let me know.
Thanks.
|||I am pretty sure that my original suggestion of not adding this flag to the fact table and implementing a slowly changing dimension would work. But possibly a simpler solution might be to insert NULL insead of 0.00 into the SS_Sales measure and then do something like the following, which basically checks if there is a value for [Special Sales] in the current period.
IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,
(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))
The only problem with this is that, if you have a hierarchy over the stores so that they roll up into groups of some sort then you will get incorrect results at the higher levels. If any store in the group was "sepcial" last year the value for the entire group would be included
Which would mean that you might have to do something like the following to force this expression to be evaluated over individual store members.
SUM( EXISTING [Store].[Store].[Store].Members ,
IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,
(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))
)|||There seems to be some inconsistency in the sample data: Store 3, 20070101 is shown as a special sale in the joined fact table, but not in Special_Store_Fact - I assume that is just a typo?
Anyway, since SS_Flag is already a field in the joined fact table, another approach would to create a simple true/false dimension like [Special Flag]. Then the MDX expressions could be:
For current year [Special Sales]:
([Special Flag].[SL_Flag].[True], [Measures].[Sales])
And for previous year [Special Sales]:
Sum(NonEmpty([Store].[Store].[Store].Members,
([Special Flag].[SL_Flag].[True], [Measures].[Sales])),
(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),
[Measures].[Sales]))
|||That's probably a reasonable compromise and would be relatively easy to implement.
The only comment I would make is that modelling changing attributes in this matter should be the exception, not the rule. You would not want to take this to the extreme and end up with a cube that has lots of small dimensions as it increases the size of the aggregations and indexes which will reduce performance.
|||
Hi,
It's been a quite long time since you posted this.
Now, we have some changes regarding this special store's calculation.
Previously, we only select the current special store list, and for the last year value, we use current lists of special stores and if any store which was not special store in parallel period then we excluded it from the calculation.
I've tried this MDX into the cube and current year value works perfectly, but last year value doesn't work properly.
MDX for the last year :
Sum(NonEmpty([Store].[Store Hierarchy].currentmember,
([Special Flag].[SL_Flag].[True], [Measures].[Sales])),
(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),
[Measures].[Sales]))
> This mdx calculate all the last year value that store became a special in last year. it can't get the current special store lists.
But now, whether store was not a spcial store in last year, if store became a special store in selected date ( current) then we sum up all store's sale amount into the last year value.
below is the MDX for the last year:
([Special Flag].[SL_Flag].[True],
(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),
[Measures].[Sales]))
But when i browse the cube, it seems last year special flag is used for last year calculation. So, I can see all special store sales amount in last year. (not exclude store which is not a special store in current year)
I've tried to do different ways but still couldn't get any solutions yet.
It seems the MDX is something wrong.
If I want to use current special flag for the parallelperiod calculation how should I write MDX for that?
I think this logic makes sense to me but I don't know why it doesn't work.
I would appreciate if anybody can give me some comments.
Thanks.
Hi,
I am having a query like this
SELECT NON EMPTY { [Measures].[Check Status],
[Measures].[Payment Amount], [Measures].[Check Type] }
ON COLUMNS, NON EMPTY
{ ([Activity].[Activity Identity].[Activity Identity].ALLMEMBERS *
[Activity].[Activity Description].[Activity Description].ALLMEMBERS
* [Faculty].[Faculty Name].[Faculty Name].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [cubActivity]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Now i want to add a filter condition for this.
Where [Measures].[Payment Amount] > 4500 and [Measures].[Check Type] = 1
how to add and where to add this line the above MDX query
Can you please help me
Thanks
Dinesh
Something like the following might work
SELECT NON EMPTY { [Measures].[Check Status],
[Measures].[Payment Amount], [Measures].[Check Type] }
ON COLUMNS, NON EMPTY
FILTER({ ([Activity].[Activity Identity].[Activity Identity].ALLMEMBERS *
[Activity].[Activity Description].[Activity Description].ALLMEMBERS
* [Faculty].[Faculty Name].[Faculty Name].ALLMEMBERS ) }
, [Measures].[Payment Amount] > 4500 and [Measures].[Check Type] = 1)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [cubActivity]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
