create template sql server reports

Question

I rerun the same reports each month, but use a different date. Can I set up templates for these reports so that I don't have to keep keying in the same information/parameters each time I run the reports?

Answer 1

An example of what we'd do it if we run a monthly report and today's day number is less than 7, we set the start date and end date to the last month. If it's 7 or more, we set it to the current month, to get month to date values.

There are two ways to do this.

The first is to use an expression to calculate a value for the default value for the report parameters. Here's an example of doing this:

I've created a report parameter called From Date:

Then added a default value:

And I've set an expression that uses the current month's start date if we're at the 7th of the month or later, else it uses the start of last month:

=Format(Iif(Day(Now) < 7, DateAdd(DateInterval.Month, -1, DateSerial(Year(Now), Month(Now), 1)),DateSerial(Year(Now), Month(Now), 1)), "Short Date")

When you run that, it shows the appropriate value:

You can then assign that report parameter value as the value for the query parameter. That's easy as it keeps it all within expressions.

The second option is to change the parameters so they get a value from a query/dataset. Then we just have that dataset execute a simple SQL query that works out what the date should be, based on the current date.

If you wrap it in a stored procedure and call it from a query, it's then really easy to reuse across a number of reports, and you have one place to change the logic if you ever need to.

But there's nothing to stop you pasting in the expression code in each report if that's preferred.

Answer 2

Hi @Rowland, Donna S,

You may set default value for your report if you have date parameter.

In the "Default Values" tab of the Report Parameter Properties like you've already opened

• Click on "Specify Values"
• Click on the Expressions button (fx)
• enter the following for the your date parameter

    getdate()
    
  

You may change the expression to match your date format.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.