none
SQL Query to SSRS Expression

    Question

  • How can I convert the below two queries in SQL to SSRS expression?

    1) DATEADD(MONTH,DATEDIFF(MONTH,0,@DateFrom),0)
    2) DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@DateTo)+1,0)) 

    Thank you,

    Sunday, March 23, 2014 3:09 AM

Answers

All replies

  • Hi,

    To use SQL Server date functions in SSRS, you can create/edit a dataset that contains the date functions in the column list of the SELECT statement. In your case, you may want to create two seperate datasets. See example below using a single query in a single dataset. During dataset creation, you may assign your query parameters (@DateFrom, @DateTo) values. If the parameter values are automatically coming from another dataset, you may wish to review the topic Cascading Parameters in SSRS.

    Regards,
    Carnegie

    /*
    Dataset - Query type: Text
    ==========================
    */

    SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@DateFrom),0) AS D1 , DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@DateTo)+1,0)) AS D2


    Sunday, March 23, 2014 4:02 AM
  • Thank you for your response Carnegie,

    I actually have two stored procedures as below. (columns and joined tables are excluded)

    I need to change the dates of @DateFrom and @DateTo parameter, I am passing @DateFrom_FD and @DateTo_LD as well.

                

    CREATE ROCEDURE [dbo].[Test]

    (@DateFrom SMALLDATETIME
      , @DateTo SMALLDATETIME
      , @program VARCHAR(MAX))


    AS

    DECLARE
    @DateFrom_FD SMALLDATETIME
           , @DateTo_LD SMALLDATETIME

    SET @DateFrom_FD = DATEADD(MONTH,DATEDIFF(MONTH,0,@DateFrom),0)
    SET @DateTo_LD = DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@DateTo)+1,0)) 

    SELECT 
      DimDate.[Year]
    , DimDate.[Month]
    , DimDate.PK_Date
    , CustomerID

    FROM DimDate

    WHERE DimDate.PK_Date BETWEEN @DateFrom_FD AND @DateTo_LD

    Now I am using one of the SP as a main report and the other SP as a subreport.

    In order to configure the main report and the subreport, I believe that I need to link them with parameters.

    However, in the parameters of subreport properties, the option given are only @DateFrom and @DateTo.

    No @DateFrom_FD AND @DateTo_LD

    Therefore, I am thinking to use expressions to convert @DateFrom and @DateTo the same way I did in SQL.

    I am not sure if this works....but this is the only thing that I could think of.

    Thank you,

    YJB5151

    Sunday, March 23, 2014 5:05 AM
  • Yes, you are correct that you need to link them with report parameters.

    In your response, I see only one stored procedure (dbo.Test). I presume dbo.Test is the stored procedure used for the main report. Thus, the unlisted second procedure (e.g. dbo.SPx) is used in the subreport.

    Ok, let's presume you've created two datasets in your report called DS1 and DS2. DS1 is based on stored procedure dbo.Test. DS2 is based on stored procedure dbo.SPx. Each dataset accepts two date parameters. Again we presume in the reports parameters list you have parameters defined something like: DS1_date1, DS1_date2, DS2_date1, DS2_date2. In this case, DS1_date1 represents the first parameter for the dataset DS1. It is important that the DS1 parameters are ordered first in the report parameters list.

    Configure the DS2_date1 and DS2_date2 report parameter properties: visibility = Internal; Defaut Values = Get values from a query (Dataset: DS1 respectively {DateFrom | DateTo}).

    The report will simply prompt for values for report parameters DS1_date1 and DS1_date2 which correspond to @DateFrom and @DateTo of dbo.Test stored procedure parameters. The dbo.SPx stored procedure parameters will be automatically filled.

    I hope this helps.

    Regards,
    Carnegie

    Sunday, March 23, 2014 5:47 AM
  • Try the below expression. You can replace the field value with the parameter values.

    =DateSerial(Year(Fields!startdate.Value), Month(Fields!startdate.Value), "1").AddMonths(0)

    =DateSerial(Year(Fields!enddate.Value), Month(Fields!enddate.Value), "1").AddMonths(1).AddDays(-1)


    Regards, RSingh

    Sunday, March 23, 2014 6:20 AM
  • if you want you can conver them to ssrs expression as below directly without creating any dataset

    DATEADD(MONTH,DATEDIFF(MONTH,0,@DateFrom),0)
    2) 
    
    becomes
    
    = DateAdd(DateInterval.Month,DateDiff(DateInterval.Month,Cdate("01/01/1900"),Parameters!DateFrom.Value),Cdate("01/01/1900"))
    
    
    
    DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@DateTo)+1,0)) 
    
    will become
    
    =DateAdd(DateInterval.Day,-1,DateAdd(DateInterval.Month,DateDiff(DateInterval.Month,Cdate("01/01/1900"),Parameters!DateTo.Value)+1,Cdate("01/01/1900")))
    


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

    Sunday, March 23, 2014 6:27 AM
  • Thank you so much everybody!
    • Marked as answer by YJB5151 Sunday, March 23, 2014 3:38 PM
    Sunday, March 23, 2014 3:38 PM
  • Thank you so much everybody!
    Please mark the posts that helped you as the answer rather than your own post for others benefit

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

    Sunday, March 23, 2014 4:44 PM
  • It is good to hear that the issue has been resolved. However do not marked your own post as answer. I do not find any answer there. Instead mark any other post as answer which you feel as answer.

    Regards, RSingh

    Monday, March 24, 2014 3:45 AM