• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

Need to pass parameter today to calculate year to date metric

I need a bar chat and a tablix to display year to date culmulative using a parameter Pmonth and Pdate to limit to Jan 2011 to today for everyday. I used period to date function with level month and member date in SSMS. But i need help to edit this query for SSRS:

WITH

MEMBER [Measures].[year to Date Sales] AS

Aggregate(

PeriodsToDate(

[Date].[Calendar].[Month],

[Date].[Calendar].CurrentMember

),

([Measures].[Sales Amount])

)

SELECT

{

([Measures].[Sales Amount]),

([Measures].[Month to Date Sales])

} ON COLUMNS,

{[Date].[Calendar].[Date].Members} ON ROWS

FROM [Sales Cube]

This query is currently giving me the culmulative for date in all available year but I need parameter that will limit to for example Jan 2002 till 12/31/2002. For example pmonth = jan 2011 and pdate = today e.g 02/27/2011. I need to run this query in SSRS. Thanks
0
Projack
Asked:
Projack
  • 3
  • 2
1 Solution
 
oleggoldCommented:
0
 
ProjackAuthor Commented:
I am not a notive I am aware of that I just need on the MDX query And resolved it. I pass


WITH

MEMBER [Measures].[year to Date Sales] AS

Aggregate(

PeriodsToDate(

[Date].[Calendar].[Month],
    STRTOMEMBER("[Date].[Calendar].[Date].&[20071125]") : strtomember("[Date].[Calendar].[Date].&[" + Format(Now(), "yyyyMMdd") +"]"),


),

([Measures].[Sales Amount])

)

SELECT

{

([Measures].[Sales Amount]),

([Measures].[Month to Date Sales])

} ON COLUMNS,

{[Date].[Calendar].[Date].Members} ON ROWS

FROM [Sales Cube]

0
 
ProjackAuthor Commented:
The expert comment was not helpful so I digged around and resolved
0
 
ProjackAuthor Commented:
The expert comment was not helpful so I digged around and resolved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now