Forum Discussion
Report Builder Parameter Formatting (Datetime to Date)
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.
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!
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!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!
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.