question

doua_shamlawi avatar image
doua_shamlawi asked

i nees to count the number of bills for each customer through years and months in mdx query , i'm trying to use alot of query's but no thing true , help meeeeeeeeee ?????

this is mi mdx query , i have use it : WITH Member Measures.[BillingNum] AS 'COUNT( {[billTime].[BillNumber].[BillNumber].Mmbers })' SELECT NON EMPTY { Measures.[BillingNum] } ON COLUMNS, NON EMPTY { ( [Bill].[CustomerNum].[CustomerNum].ALLMEMBERS ) } ON ROWS FROM [data warehouse] the result shown like that : customer ------ billingNum 1 ------ 15293 2 ------ 15293 3 ------ 15293 . ------ . . ------ . 208 ------ 15293 https://ask.sqlservercentral.com/storage/temp/4070-hhh.png and the result i want(count of billing for each customer ) : customer ------ billingNum 1 ------ 100 2 ------ 300 3 ------ 50 . ------ . . ------ . 208 ------ 20 ![alt text][1] [1]: /storage/temp/4070-hhh.png
sql servermdxcount
hhh.png (1.4 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
tzvikl avatar image
tzvikl answered
Hi Doua, What your'e doing here is counting all the members in the Bills dimension and then "spreading" the same result for all the customers, meaning your counting one dimension and then displaying the result in another one. You need to connect the dimensions in the Measure definition in order for the measure to display the desired result. you can achieve this by 2 different ways: (I am assuming that both the Bills and Customers dimensions slice the same Fact table in your cube) 1. Create a new distinct count measure in you're cube, over the Bills column in the fact table, and then use that measure in your query, i.e: SELECT [Measures].[DistinctBills] on 0 , non empty ([Bill].[CustomerNum].[CustomerNum].ALLMEMBERS) on 1 from [DataWarehouse] 2. The other option is to alter your calculated measure to something like this: WITH Member [Measures].[BillingNum] AS COUNT(Exists( {[billTime].[BillNumber].[BillNumber].Members } ,{[Bill].[CustomerNum].[CustomerNum].CurrentMember},'Measure group name' ) Hope this helped Regards Zvi
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.