CODEWITHSUNDEEP
reporting-servicesparametersreportrangebuilder
Thatguy
Thatguy

Reputation: 5

Report Builder 3 Creating a current, 30, 60, date range parameter to generate reports

I am new to Report Builder and I have been tasked with creating a date range search parameter. They are looking for current, 30 day, and 60 day values. The overall report is working and I have only made a few changes to it. So in parameters I made a dateRange parameter and set data type up as Date/Time and no boxes are check below. in the available values properties I have specified 3 values:

  1. Current: =DateAdd("d", -1, Today())
  2. 30 day Range: =DateAdd("d", -30, Today())
  3. 60 day Range: =DateAdd("d", -60, Today())

Default values and advanced properties have remained untouched.

Why did I go with those expressions? I was looking at the StartDate and EndDate values and the enddate value has =DateAdd("d", -1, Today()) and so my logic said going back 30 and 60 days i would need add a neg in front. This I think is errant thinking. The customer is looking for the parameter to return the the values starting from the first day of the month so I need to make sure I use a month.minValue to have the report always start on the first day of the month. I hope all of this makes sense. Again I am very new to Report Builder so forgive me for my ignorance.

Jim

Upvotes: 0

Views: 2284

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Based on your question and feedback comments, this is what I got.

Define DateRange parameter as follows:

Available values:

=DateAdd("d", -1, Today())
=DateAdd("d", -30, Today())
=DateAdd("d", -60, Today())

For use current =DateAdd("d", -1, Today()) as default you must to put it in the default values tab of DateRange parameter.

Now for handle the end date I've created other parameter named EndDate set it default value to current:

=DateAdd("d", -1, Today())

Now in the dataset query you have to use the parameter DateRange to get the first date of month. Check this example.

select *
from mytable a
where a.DocDate between DATEADD(month, DATEDIFF(month, 0, @DateRange), 0)
and @EndDate

DATEADD(month, DATEDIFF(month, 0, @DateRange), 0) will produce the first date of month based on the daterange selection example using today date:

current: 01/10/2015 (dd/MM/yyy format)

30 days range: 01/09/2015

60 days range: 01/08/2015

EndDate parameter will produce 26/10/2015

Let me know if this was helpful

Upvotes: 0

Related Questions