create template sql server reports

Rowland, Donna S 0 Reputation points
2024-04-22T17:21:15.6333333+00:00

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?

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,827 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Greg Low 1,495 Reputation points Microsoft Regional Director
    2024-04-23T01:16:05.4333333+00:00

    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:

    User's image

    Then added a default value:

    User's image

    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:

    User's image

    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.

    0 comments No comments

  2. ZoeHui-MSFT 33,626 Reputation points
    2024-04-23T02:03:57.8133333+00:00

    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.

    0 comments No comments