Mdx query to retrieve measure for working days in a month

I Have the following dimension and measure group in SSAS

Dim Date      Sales
Date  Days Working Day Month  Amount  Qty  
2010/12/01 1  0 Jan  2216.00  21
2010/12/01 2  1 Jan  216.00  25
2010/12/01 3  1 Jan  2000.00  45
2010/12/01 4  1 Jan  6788.00  67
2010/12/01 5  1 Jan  4532.00  32
2010/12/01 6  1 Jan  567.00  11
2010/12/01 7  0 Jan  2566.00  23

How can I write MDX query in a SSRS Dataset to retrieve the sum of amount and qty by days and working day for a month?

Hi Projack.

Before I continue, allow me to say this:

Multi dimension analysis, as SSAS, is intendend to aggregate information and display in reports/dashboards. In your case, seems that your time table isnt aggregate. You should have time dimension in day, this is:

Date         DaysWorking Day Month

20101201 1                  0    Dez   

20101202 2                  1    Dez

In you fact table you should have also that date code in YYYYMMDD to avoid country format problems.

MDX to solve your problem, will be something very similar to:

SELECT {Measures.[Amount], Measures.[Qty]} on rows,

{DimDate.[Year].[Quarter].&[201012]} on columns

FROM Cube

To achieve this, you should have this date code in YYYYMMDD both in fact table and Dim Date.

Search the web for "date dimension" and you will see tons of information. Here, take this links also:

http://www.bidn.com/blogs/MikeDavis/ssis/1400/script-to-load-dim-date-and-dim-time

http://www.bidn.com/forums/microsoft-business-intelligence/bi-query-languages-mdx-dmx/21/ssas-calculation-to-pull-previous-month-last-day

and by the way http://www.sqlservercentral.com/articles/Analysis+Services+(SSAS)/65803/