none
SSRS expression for today,yesterday,Lastweek ,Last fortnight,Last Month, Year to date

    Question

  • Hi All;

    I have a field called createdon 

    Using this field i need to create the SSRS expression for the table as below 

    Any help much appreciated

    Thanks


    Pradnya07

    Tuesday, September 02, 2014 8:26 AM

Answers

  • use expressions as below

    assuming this is to be done in SSRS

    Today
    ---------
    
    
    =COUNT(IIF(
    DateDiff(DateInterval.Day,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Day,Cdate("01/01/1900"),Now()),Fields!YourRequiredField.Value,Nothing))
    
    
    
    Yesterday
    --------------
    
    =COUNT(IIF(
    DateDiff(DateInterval.Day,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Day,Cdate("01/01/1900"),Now())-1,Fields!YourRequiredField.Value,Nothing))
    
    
    LastWeek
    --------------
    
    =COUNT(IIF(
    DateDiff(DateInterval.Week,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Week,Cdate("01/01/1900"),Now())-1,Fields!YourRequiredField.Value,Nothing))
    
    
    
    
    Last fortnight
    --------------
    
    =COUNT(IIF(
    (DateDiff(DateInterval.Week,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Week,Cdate("01/01/1900"),Now())-1)
    Or (DateDiff(DateInterval.Week,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Week,Cdate("01/01/1900"),Now())-2),Fields!YourRequiredField.Value,Nothing))
    
    
    Last Month
    --------------
    
    =COUNT(IIF(DateDiff(DateInterval.Month,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Month,Cdate("01/01/1900"),Now())-1,Fields!YourRequiredField.Value,Nothing))
    
    
    
    Year To Date
    --------------
    
    =COUNT(IIF(DateDiff(DateInterval.Year,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Year,Cdate("01/01/1900"),Now())
    And 
    DateDiff(DateInterval.Day,Cdate("01/01/1900"),Fields!createdon.Value) <= DateDiff(DateInterval.Day,Cdate("01/01/1900"),Now()),Fields!YourRequiredField.Value,Nothing))


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Simran08 Monday, September 08, 2014 8:56 AM
    Wednesday, September 03, 2014 12:05 PM

All replies

  • Hi Simran08,

    Based on your information, we are not clear about your requirement. Could you clarify some questions so that we can reproduce this scenario in our local environment?

    • What does column Open mean in your table?
    • What's the relationship between those two columns and "createdon"?
    • What does the expression work for?

    Best Regards,
    Simon Hou 

    Wednesday, September 03, 2014 11:35 AM
    Moderator
  • use expressions as below

    assuming this is to be done in SSRS

    Today
    ---------
    
    
    =COUNT(IIF(
    DateDiff(DateInterval.Day,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Day,Cdate("01/01/1900"),Now()),Fields!YourRequiredField.Value,Nothing))
    
    
    
    Yesterday
    --------------
    
    =COUNT(IIF(
    DateDiff(DateInterval.Day,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Day,Cdate("01/01/1900"),Now())-1,Fields!YourRequiredField.Value,Nothing))
    
    
    LastWeek
    --------------
    
    =COUNT(IIF(
    DateDiff(DateInterval.Week,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Week,Cdate("01/01/1900"),Now())-1,Fields!YourRequiredField.Value,Nothing))
    
    
    
    
    Last fortnight
    --------------
    
    =COUNT(IIF(
    (DateDiff(DateInterval.Week,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Week,Cdate("01/01/1900"),Now())-1)
    Or (DateDiff(DateInterval.Week,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Week,Cdate("01/01/1900"),Now())-2),Fields!YourRequiredField.Value,Nothing))
    
    
    Last Month
    --------------
    
    =COUNT(IIF(DateDiff(DateInterval.Month,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Month,Cdate("01/01/1900"),Now())-1,Fields!YourRequiredField.Value,Nothing))
    
    
    
    Year To Date
    --------------
    
    =COUNT(IIF(DateDiff(DateInterval.Year,Cdate("01/01/1900"),Fields!createdon.Value) = DateDiff(DateInterval.Year,Cdate("01/01/1900"),Now())
    And 
    DateDiff(DateInterval.Day,Cdate("01/01/1900"),Fields!createdon.Value) <= DateDiff(DateInterval.Day,Cdate("01/01/1900"),Now()),Fields!YourRequiredField.Value,Nothing))


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Simran08 Monday, September 08, 2014 8:56 AM
    Wednesday, September 03, 2014 12:05 PM