Monthly Report Using SSAS and SSRS

• ssismaddy

  SSCarpal Tunnel

  Points: 4850

  More actions

  September 24, 2008 at 1:32 pm

  #213024

  Hi.... My Boss wants me to generate monthly and daily reports for the number of transactions using SSAS. The measures I have are Transaction Count, Transaction ID and Date. The Dimensions are Date Dimension,Fact Transaction and Transaction TypeID. I used the following query to generate the number of transactions for the all the months for different Transaction Types.

  select [Transaction Type Description].members on columns,

  [tbl date dimension].[date month name].members on rows

  from [Reports]

  where measures.[tbl Transaction Count]

  But, I m getting same total number of transactions for all the months. Actually I m very much new to SSAS. Please help me out. But using sql I got the result using the following query:

  SELECT convert(varchar(10),tdd.date,101) Date, tt.transactiontypedescription, count(*) as TotalTransactions

  FROM dbo.tblTransaction ft

  INNER JOIN dbo.tblTransactionID tt

  ON ft.transaction = tt.transactionid

  INNER JOIN dbo.tblDateDimension tdd

  ON ft.dateSID = tdd.dateSID

  WHERE tdd.date between @StartDate and @EndDate

  Thanks in advance..

• Dave Balsillie

  SSCertifiable

  Points: 5589

  More actions

  September 26, 2008 at 12:02 pm

  #877885

  Sounds like you're having a problem with your cube design.

  Check the Dimension Usage tab. Make sure that the cube has your time reference.

• ssismaddy

  SSCarpal Tunnel

  Points: 4850

  More actions

  September 26, 2008 at 12:05 pm

  #877889

  yeah ...I did nt have proper cube design....I got it now...Thanks a lot ....