?
Solved

Need to pass parameter today to calculate year to date metric

Posted on 2011-02-27
5
Medium Priority
?
470 Views
Last Modified: 2012-05-11
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
Comment
Question by:Projack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 34993239
0
 

Accepted Solution

by:
Projack earned 0 total points
ID: 35089046
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
 

Author Comment

by:Projack
ID: 35089084
The expert comment was not helpful so I digged around and resolved
0
 

Author Closing Comment

by:Projack
ID: 35126453
The expert comment was not helpful so I digged around and resolved.
0

Featured Post

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month10 days, 19 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question