Post reply

SQL Date filter/expression

  • June 19, 2020 at 11:03 am

    #3764337

    Hi

    I have a report that uses a stored procedure but it wasn't built by me.

    It basically lists completed cases from our customer management system but I want to tweak the report to only show open/live cases.

    I have two fields: 'CaseOpen' and 'Case Completed' and I somehow need to use an expression/ filter that will only display cases where there is no completion date i.e. open cases.

    Any help much appreciated!

    Thanks

    Liam

  • June 19, 2020 at 1:15 pm

    #3764361

    Liam

    Without seeing your table and the data in it, it's difficult to advise.  If open cases have a null completion date, then just do WHERE CaseCompleted IS NULL.

    John

  • June 19, 2020 at 1:32 pm

    #3764365

    Assuming you cannot modify the stored procedure, and you want to be able to filter out all records WHERE CaseCompleted IS NULL, you can add a filter to your report dataset inside of SSRS. To do that, do the following:

    1. Right-click on your DataSet, and select 'Dataset Properties'.
    2. Click on 'Filters' in the left pane.
    3. Click the 'Add' button.
    4. Select [CaseCompleted] from the Expression dropdown.
    5. Operator: =
    6. In the textbox type 'Null'.
    7. Click the OK button,

    then run your report.

  • June 19, 2020 at 1:58 pm

    #3764370

    Thanks both

    I'm not an SQL expert, just very basis SQL skills. Wouldn't know how to add the information into the SP.

    Got this:

     

    SQL 1SQL 2

  • June 19, 2020 at 4:50 pm

    #3764413

    Oh, sorry... left out a bit of critical information/part of a step. If you want to allow the parameter to be null, do this:

    With the report in design mode, right-click the Date parameter you created, and select "Parameter Properties" from the dropdown. Under the "data type" section, make sure "Allow null value" is checked. If it's not, and you try to run your report, it won't run, and you'll get the error you described.

  • June 19, 2020 at 5:02 pm

    #3764417

    format YYYY-MM-DD

    With time - format: YYYY-MM-DD HH:MI: SS

    STAMP - format: YYYY-MM-DD HH:MI: SS

    With YEAR - format YYYY or YY

    This is an expression of SQL data format if you want more about format details of script using SQL Query go through in this SQL Insert Multiple Rows

     

     

     

  • June 25, 2020 at 12:06 pm

    #3765037

    Sorry but I don't understand. I have not created a parameter, I am just creating a filter and in the filtering screen (dataset proprties) I cannot see an option to allow NULL values.

  • June 25, 2020 at 12:06 pm

    #3765038

    Not sure that will help me.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply