Report Builder; provide parameter selection but have report default to most recent date

[ldwf]

A unique request from customer...they would like to have the report parameterized where they can select a date from the parameter dropdown, but when they click on link to open the report, they do not want to select anything from the parameter dropdown; i.e, they want the report to open and already show the data for the most recent date by default, then if they are interested in seeing a date from the past, only then do they want to use the parameter and select a date. Is it even possible to be able to provide both capabilities? if so, how?  I can just create two separate reports for the user, i.e, one defaulting to the most recent date, and another one that is parameter driven, but trying to avoid having two separate reports.

[v-jialongy-msft]

Hi @ldwf 

You want to create a report that opens by default with the latest data, but also allows users to select a different date when they want to view past data, right?

This can indeed be achieved by setting a default value for the date parameter that dynamically gets the latest date. Here's how to achieve this:

1. Select Create Parameter for Date:

In the report, create a parameter that the user can interact with to select a date. We call this parameter "ReportDate".

2. Set dataset for most recent date:

Create a dataset that returns the most recent date from the data source.

This can be a simple SQL query, such as "SELECT MAX(DateColumn) AS MostRecentDate FROM YourDataTable".

3. Set the default value for the parameter:

Go to the report parameter properties. - Under the Default values section, select Get values from the query. - Select the dataset you created for the most recent date. - Set the value field to a field that returns the most recent date (for example, "MostRecentDate").

4. Use parameters in the report:

In the dataset query for the report, use the "ReportDate" parameter to filter the data. - For SQL-based datasets, this is something like "WHERE DateColumn = @ReportDate".

5. Test Report:

Preview the report to make sure the latest data is loaded by default. - Then, try selecting a different date from the parameter drop-down list to ensure that the report is updated accordingly. Follow the steps below, and when the report initially opens, it will automatically show data for the most recent date. If users want to see data for a different date, they can use the drop-down list to select the date they are interested in.

If the issue is more complex, or if you need further assistance, Consider opening a support ticket through the Power BI support page .

https://powerbi.microsoft.com/en-us/support/

^{Best Regards,}

^Jayleny

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[ldwf]

@v-jialongy-msft .  Thanks.  In this case, the user does not want to select the 'View Report' button at all.  They want the SQL to execute and default to the most current date and display the results but still have the parameter dropdown appear even if they don't want to select from it.  I am not aware that Report Builder can do this.  For now, I have created two versions of this report; one that defaults to the maximum date with no prompt, and one for historical reporting with a prompt

[v-jialongy-msft]

Hi @ldwf 

Based on what I've learned so far, Report Builder doesn't implement your needs at the same time, and you can continue to use your work aroundif you already have one.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sayaliredij]

Hi @ldwf 

in Date table , you can create calculated column as isRecentDate and make it as your default filter

IsRecent = IF(Fact[Date]=CALCULATE(MAX(Fact[Date]),ALL(Fact)),1,0) 

so when your report will open it will always have recent date selected.

and offcourse you can provide filter pane where your client can change to past dates

Thanks,

Sayali

[ldwf]

@sayaliredij .  Hi, thanks.  Your solution uses filters.  This is Report Builder report, not Power BI.  In this Report Builder report, there is a parameter dropdown.  Can you build a Report Builder report that uses a parameter dropdown, but when clicking on the link to open the report, it defaults to displaying the report for the maximum date without having the user actually select this date in the dropdown?  They don't want to be prompted; they want to default to showing most recent data but still have ability to select a past date in the dropdown.