locked
Applying date range to more than one column in ssrs 2008. RRS feed

  • Question

  • HI all,

    I am new to ssrs and that i am using ssrs 2008.

    I need to apply a date range in two columns for e.g. this is the query i use to apply it to one column i.e.(EntryDate):

    SELECT     expid AS [Tr.No], InvNo AS [Inv.No], expDate AS [Exp.Date], EntryDate, costcenter, total AS Amount, Vendor,
                          Platecode + ' ' + PlateNo + ' ' + Category AS Vehicle
    FROM         view_vehicle_expense
    WHERE     (Vendor = @supplier) AND (costcenter = @costcenter)
    AND (CAST(CONVERT(varchar(10), EntryDate, 101) AS datetime) >= @startdate) AND
                          (CAST(CONVERT(varchar(10), EntryDate, 112) AS datetime) <= @enddate)
    GROUP BY expid, InvNo, expDate, EntryDate, costcenter, total, Vendor, Platecode + ' ' + PlateNo + ' ' + Category

    Is there a way to apply the startdate and enddate to entrydate and expdate, but with a checkbox option

    which will apply the date range to entrydate and expdate.

    Thanks

    Saturday, May 19, 2012 12:49 PM

Answers

  • Hi There
    You could have a radio button type parameter which is Boolean type parameter and which have  avalue either true or false

    Please define a Boolean parameter in your report, For example your parameter name is @Bool and it should be have type Boolean then you can write expression something like this inside your dataset

    If @Bool=true
    SELECT     expid AS [Tr.No], InvNo AS [Inv.No], expDate AS [Exp.Date], EntryDate, costcenter, total AS Amount, Vendor, 
                          Platecode + ' ' + PlateNo + ' ' + Category AS Vehicle
    FROM         view_vehicle_expense
    WHERE     (Vendor = @supplier) AND (costcenter = @costcenter) 
    AND (CAST(CONVERT(varchar(10), EntryDate, 101) AS datetime) >= @startdate) AND 
                          (CAST(CONVERT(varchar(10), EntryDate, 112) AS datetime) <= @enddate)
    GROUP BY expid, InvNo, expDate, EntryDate, costcenter, total, Vendor, Platecode + ' ' + PlateNo + ' ' + Category
    else
    SELECT     expid AS [Tr.No], InvNo AS [Inv.No], expDate AS [Exp.Date], EntryDate, costcenter, total AS Amount, Vendor, 
                          Platecode + ' ' + PlateNo + ' ' + Category AS Vehicle
    FROM         view_vehicle_expense
    WHERE     (Vendor = @supplier) AND (costcenter = @costcenter) 
    AND (CAST(CONVERT(varchar(10), expDate, 101) AS datetime) >= @startdate) AND 
                          (CAST(CONVERT(varchar(10), expDate, 112) AS datetime) <= @enddate)
    GROUP BY expid, InvNo, expDate, EntryDate, costcenter, total, Vendor, Platecode + ' ' + PlateNo + ' ' + Category

    Please let me know if you have any questions

    Many thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

    • Proposed as answer by Syed Qazafi Anjum Saturday, May 19, 2012 10:52 PM
    • Marked as answer by Lola Wang Monday, May 21, 2012 2:43 AM
    Saturday, May 19, 2012 10:51 PM
  • In addition, using CAST/CONVERT system functions in the WHERE condition prevents from SQL Server using an index on that column (if one exists) which may hurt performance..

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Lola Wang Monday, May 21, 2012 2:43 AM
    Sunday, May 20, 2012 3:38 AM

All replies

  • Hi There
    You could have a radio button type parameter which is Boolean type parameter and which have  avalue either true or false

    Please define a Boolean parameter in your report, For example your parameter name is @Bool and it should be have type Boolean then you can write expression something like this inside your dataset

    If @Bool=true
    SELECT     expid AS [Tr.No], InvNo AS [Inv.No], expDate AS [Exp.Date], EntryDate, costcenter, total AS Amount, Vendor, 
                          Platecode + ' ' + PlateNo + ' ' + Category AS Vehicle
    FROM         view_vehicle_expense
    WHERE     (Vendor = @supplier) AND (costcenter = @costcenter) 
    AND (CAST(CONVERT(varchar(10), EntryDate, 101) AS datetime) >= @startdate) AND 
                          (CAST(CONVERT(varchar(10), EntryDate, 112) AS datetime) <= @enddate)
    GROUP BY expid, InvNo, expDate, EntryDate, costcenter, total, Vendor, Platecode + ' ' + PlateNo + ' ' + Category
    else
    SELECT     expid AS [Tr.No], InvNo AS [Inv.No], expDate AS [Exp.Date], EntryDate, costcenter, total AS Amount, Vendor, 
                          Platecode + ' ' + PlateNo + ' ' + Category AS Vehicle
    FROM         view_vehicle_expense
    WHERE     (Vendor = @supplier) AND (costcenter = @costcenter) 
    AND (CAST(CONVERT(varchar(10), expDate, 101) AS datetime) >= @startdate) AND 
                          (CAST(CONVERT(varchar(10), expDate, 112) AS datetime) <= @enddate)
    GROUP BY expid, InvNo, expDate, EntryDate, costcenter, total, Vendor, Platecode + ' ' + PlateNo + ' ' + Category

    Please let me know if you have any questions

    Many thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

    • Proposed as answer by Syed Qazafi Anjum Saturday, May 19, 2012 10:52 PM
    • Marked as answer by Lola Wang Monday, May 21, 2012 2:43 AM
    Saturday, May 19, 2012 10:51 PM
  • In addition, using CAST/CONVERT system functions in the WHERE condition prevents from SQL Server using an index on that column (if one exists) which may hurt performance..

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Lola Wang Monday, May 21, 2012 2:43 AM
    Sunday, May 20, 2012 3:38 AM