none
How to find the total sales amount for a selected period

    Question

  • Hi ,

    I have two month dropdown fields in my SSRS report.

    I need to find the sales amount between the selected months both inclusive.

    Say i select feb in first drop down , june in second dropdown. I need to write a mdx query to show the total sales amount between the two dates. I have tried using the filter but its not working.

    Can we define a filter to get the sales amount only for the months greater than or equal to feb and less than or equal to june ?

     

    Thanks,

    Srihari Thakkelapati

     

    Tuesday, August 16, 2011 12:06 PM

Answers

All replies

  • srihari,

     

    You can write a simple MDX for this:

     

    Select {[Measures].[total sales amount]} on 0

    FROM ( SELECT ( STRTOMEMBER(@FromMonth) : STRTOMEMBER(@FromMonth) ) ON COLUMNS

    FROM  [cubename])

     

    or

     

    Sum( STRTOMEMBER(@FromMonth) : STRTOMEMBER(@FromMonth) , [Measures].[total sales amount] )

     

     

    Regards

    Manoj

     

    Tuesday, August 16, 2011 12:17 PM
  • Hi Manoj,

    The given below is the query i am using. I need to show the YTD leavers from Jan to July. OHC as on april. CHc as on July. I need to show the leavers from April to July. If i put a slicer can there be an effect on YTD leavers ?

    The given below is the query i am using:

    WITH

    MEMBER

    [YTD LEAVERS] AS

    SUM

    (YTD([TIME PERIOD].[HIERARCHY].[MONTH].&[7]),[MEASURES].[LEAVERS])

    MEMBER

    [OHC] AS

    SUM

    (MTD([TIME PERIOD].[HIERARCHY].[MONTH].&[4]),[MEASURES].[OPENING HC])

    MEMBER

    [CHC] AS

    SUM

    (MTD([TIME PERIOD].[HIERARCHY].[MONTH].&[7]),[MEASURES].[CLOSING HC])

     

     

    SELECT

    {

    [Measures].[OHC],

    [Measures].[CHC],

    [Measures].[Leavers],

    [Leavers for Period]

    ,[YTD LEAVERS]}

    ON COLUMNS,

     

    NON

    EMPTY

    {

    (

    [Location].[DESCR].[DESCR],

    [Time Period].[Month].&[1]

    )

    }

    ON ROWS

     

    FROM

    [ISG_BIM_PPL_HR]

    WHERE

    [Employee Type].[Type Id].&[1]

     

    Please suggest.

    Thanks,

    Srihari

    Tuesday, August 16, 2011 1:06 PM
  • A slicer would have no effect on YTD function, since by default it uses range starting from first sibling and ending with the given member (constrained by YEAR level).

    So to use slicer you should be able to leverage syntax provided by Manoj.

    Or you can work with PeriodsToDate:

    http://msdn.microsoft.com/en-us/library/ms144925.aspx


    http://dailyitsolutions.blogspot.com/
    Tuesday, August 16, 2011 2:50 PM