locked
SSRS Report Need Age RRS feed

  • Question

  • I have a table

    Name DOB City State

    a 01/01/1983

     

    Age is the parameter

    how to write sql query  to findout the age in years based on DOB?

    and i want the values in parameter has:age>=59,age<59,all 

    Monday, June 13, 2011 11:53 PM

Answers

  • Hi Sqlstar07,

    Thanks for your question and Shahfaisal’s logical reply, it’s effective to use the DATEDIFF(YY, DOB, GETDATE()) to get the age. In addition, you should add a parameter to display the filters: age>=59, age<59, all, and then using the values in the Dataset’s T-SQL query.

    Generally speaking, if you want to use the parameter’s values in a Dataset, the better solution is using a dynamic T-SQL query in the Dataset. The detail steps of using a dynamic T-SQL query in the Dataset, please follow below,

    1. In the Report Data window, right-click the Parameters, select Add parameter. In the Report Parameter Properties dialog box, type p1 in the Name and Prompt textboxes.

    2. Click Available Values in the left pane, select Specify values, click Add button, click the fx button on the right of the Label textbox, type in =”>=” , click OK. Click the fx button on the right of the Value textbox, type in ="where DATEDIFF(YY,Birth,GETDATE())>=59", click OK.

    3. Repeat the previous one step to add the expressions for < and All, finally the expressions will be like this,

    =”>=”    ="where DATEDIFF(YY,Birth,GETDATE())>=59" ,
    =”<”       ="where DATEDIFF(YY,Birth,GETDATE())<59" ,
    =”All”     =" " .

    4. Click New, click Dataset, select the Data source name for current dataset in the drop-down list of Data source, type in the Query dialog box like below,

    declare @mathoperator as nvarchar(100)

    declare @selectstatement as nvarchar(4000);

     

    select @mathoperator =@p1

    select @selectstatement = 'select * from TableName '+ @mathoperator

     

    exec sp_executesql @selectstatement

     

    5. Click OK, at this time it will pop up a window of Define Query Parameters, the text of Parameter Name is @p1, please type once space key in the textbox of Parameter value, click OK. (If the window doesn’t appear, please click the button of Refresh Fields.)

     

    After the previous steps, you will see the Dataset displays the column names of the dynamic T-SQL query, please bind the Dataset’s fields to your data region, then click the Preview tab on the report, the data region dynamic display the results depended on the values you selected in the parameter @p1.

     

    I had handled the similar thread of Report Parameter Query, please see: http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/32d18615-b6a7-4540-b8a5-884d889c823a?prof=required.

     

    If your issue still exists, please feel free to let me know.


    Thanks,
    Sharp Wang


    Best Regards. Sharp Wang
    • Marked as answer by Challen Fu Monday, June 27, 2011 6:06 AM
    Wednesday, June 15, 2011 2:34 AM

All replies

  • SELECT DATEDIFF(YY, DOB, GETDATE()) should give you the age in years.
    • Proposed as answer by AlphaVictor87 Tuesday, June 14, 2011 2:45 PM
    Tuesday, June 14, 2011 12:30 AM
  • Hi Sqlstar07,

    Thanks for your question and Shahfaisal’s logical reply, it’s effective to use the DATEDIFF(YY, DOB, GETDATE()) to get the age. In addition, you should add a parameter to display the filters: age>=59, age<59, all, and then using the values in the Dataset’s T-SQL query.

    Generally speaking, if you want to use the parameter’s values in a Dataset, the better solution is using a dynamic T-SQL query in the Dataset. The detail steps of using a dynamic T-SQL query in the Dataset, please follow below,

    1. In the Report Data window, right-click the Parameters, select Add parameter. In the Report Parameter Properties dialog box, type p1 in the Name and Prompt textboxes.

    2. Click Available Values in the left pane, select Specify values, click Add button, click the fx button on the right of the Label textbox, type in =”>=” , click OK. Click the fx button on the right of the Value textbox, type in ="where DATEDIFF(YY,Birth,GETDATE())>=59", click OK.

    3. Repeat the previous one step to add the expressions for < and All, finally the expressions will be like this,

    =”>=”    ="where DATEDIFF(YY,Birth,GETDATE())>=59" ,
    =”<”       ="where DATEDIFF(YY,Birth,GETDATE())<59" ,
    =”All”     =" " .

    4. Click New, click Dataset, select the Data source name for current dataset in the drop-down list of Data source, type in the Query dialog box like below,

    declare @mathoperator as nvarchar(100)

    declare @selectstatement as nvarchar(4000);

     

    select @mathoperator =@p1

    select @selectstatement = 'select * from TableName '+ @mathoperator

     

    exec sp_executesql @selectstatement

     

    5. Click OK, at this time it will pop up a window of Define Query Parameters, the text of Parameter Name is @p1, please type once space key in the textbox of Parameter value, click OK. (If the window doesn’t appear, please click the button of Refresh Fields.)

     

    After the previous steps, you will see the Dataset displays the column names of the dynamic T-SQL query, please bind the Dataset’s fields to your data region, then click the Preview tab on the report, the data region dynamic display the results depended on the values you selected in the parameter @p1.

     

    I had handled the similar thread of Report Parameter Query, please see: http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/32d18615-b6a7-4540-b8a5-884d889c823a?prof=required.

     

    If your issue still exists, please feel free to let me know.


    Thanks,
    Sharp Wang


    Best Regards. Sharp Wang
    • Marked as answer by Challen Fu Monday, June 27, 2011 6:06 AM
    Wednesday, June 15, 2011 2:34 AM