SSAS cube and getting data with MDX for SSRS report

Question

I'm new to OLAP cubes. Can you directed in the right direction with small example.

Let's say I have table "transactions" with 3 columns: transaction_id (int), date (datetime), amount (decimal(16,2)).

I want to create a cube and then get data with MDX query for SSRS report. I want report to show something like:

Ok. I know i can have fact table with amount and date dimention (date->month->year).

Can you explain what to do in order to get this result (including how to write MDX query). Thanks.

Can someone explain why I get amount of full 201504 and 201606 months even if I specified exact range with days?

SELECT
     [Measures].[Amount] ON COLUMNS
    ,[Dim_Date].[Hierarchy].[Month].MEMBERS ON ROWS
FROM
    [DM]
WHERE
    (
       {[Dim_Date].[Date Int].&[20150414] : [Dim_Date].[Date Int].&[20160615]}
    )

Dance-Henry Dance-Henry · Accepted Answer · 2016-09-14T00:04:20

Something like below, change the query accordingly :)

SELECT  
    { [Date].[EnglishMonthName].[EnglishMonthName]} ON COLUMNS,  
    { [Date].[DateHierarchy].[Year].&[2015],   
        [Date].[DateHierarchy].[Year].&[2016] } ON ROWS  
FROM [YourCubeName]
WHERE ([Measures].[amount])

SSAS cube and getting data with MDX for SSRS report

2 Answers