Report Builder Parameter Formatting (Datetime to Date)

[DavidS524]

Hello everyone,

I am attempting to do something which should be rather simple, but I am new to Report Builder/Paginated reports. 

Quick Overview: I want to have a parameter which is a dropdown list of dates in date format ("yyyy-mm-dd"). Time should not be shown at the end. I also do not want the calendar date selector in this case. 

Long Overview: I currently have taken the following steps to try and get this to work. I have tried multiple variations with similar  results but this is where I am at right now.

1. A date parameter labeled DateParam set to data type text with the specified value pointed to a datetime field in my dataset.

2. Under Dataset properties->Parameters I have a parameter labeled as "Date" with an expression of: =FORMAT((Parameters!DateParam.Value), "yyyy-mm-dd")

3, This results in an error message when I try to run the report: The Value expression for the query parameter 'Date' contains an error: The expression references the parameter 'DateParam', which does not exist in the Parameters collection. Letters in the names of parameters must use the correct case.

This error does not make sense to me since underneath Parameters it clearly shows "DateParam". Some searches for the error led me to discussions on changing the order of parameters where one relies on the other in order to solve it. However, in my case I only have one at this point.

Any tips on how to resolve this would be greatly appreciated.

Thank you!

[sevenhills]

Few things to consider fixing given your post,

a) Rename the dataset parameter name from "Date".

        Reason: Date is typically a reserved word.

b) If you are using SQL server, the dataset parameter dialog shows as like this

But yours show without "@".

Paginated reports are nothing but SSRS reports, FYI.

C) If above steps did not help, remove the parameter related code ONLY in the dataset and try to run the report.
Say, hard coded value for the parameter name and see the report renders.

My suspect is that you are using the reserved word (or) Parameter Name missing "@".

Hope it helps resolving or isolating the root cause!

[DavidS524]

Hi @sevenhills

Thank you for those suggesstions. I tried changing the parameter name on the dataset to "@DateParam". Unfortunately I still recieve an error. I also tried removing the parameter from the dataset settings entirely which does allow me to run the report. However, the date values still include both the date and time. I'm not sure why there is no "Date" option in the data type setting for the parameter. Here are a few more screenshots which show the current state/result. Any other ideas? 


Thanks again for the help!

[sevenhills]

Let us do iteratively one step at a time:

So far, you changed the parameter to text and dataset executes/report runs without the parameter value. 

Let us try as

- Add a textbox and display the value as Parameters!DateParam.Value. See the value and the format you are getting when you run the report. Definitely this is not the format you want.

- Go back to the same textbox and change the expression to Format(CDATE(Parameters!DateParam.Value), "yyyy-MM-dd"). Here, we are checking to see the expression works and to the desired format.

- Use a hard coded value to your dataset parameter as "2022-04-01". Just as an example. Here, we are making sure the date format is correct and working fine.

- Use this expression "

Format(CDATE(Parameters!DateParam.Value), "yyyy-MM-dd")

" and try to run the report again.

this should help!

[DavidS524 · Accepted solution]

@sevenhills 

I want to sincerely thank you again for your assistance with this. Ultimately it took more steps to resolve but you helped set me on the right path. Turns out it isn't that hard, but for someone who is used to PBI Desktop and brand new to paginated reports it was a painful learning process. 

Here is the solution for anyone who may benefit from this in the future.

1. For the original error "The expression references the parameter 'DateParam', which does not exist in the Parameter collection", I realized that my data source query, which is a DAX query pointed at a Power BI dataset, was not correctly pointing at the "@DateParam". So I added that reference to the DAX query (which you can get from the Performance Analyzer in PBI Desktop).
// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Date_Dimension'[last_day_in_month])),
'Date_Dimension'[last_day_in_month] = @DateParam
)

2. Next, I created a seperate dataset for just the date parameter and added a calculated column that takes a substring of the full datetime text that is output by default. 

=LEFT(Fields!DateParam.Value, 10)

3. Then under the DateParam-->Available Values, I made that calculated field the label field. That finally results in the correct format.

[Anonymous]

Hi @DavidS524 ,

I am also facing the same problem.

I have created a Power BI desktop report and using this dataset for a Paginated/report builder report.

I am using SalesDate as FromDate and Todate. Could you please explain how to implement it?

Do we need to create any DAX object on the Power BI desktop?

Please provide me steps. Thanks in advance.

[sevenhills]

Glad you were able to resolve it.

Dax query has additional challenges when compared to simple SQL.