topic Re: Year parameter wants to be treated as text in Report Server

Year parameter wants to be treated as text

Dellis81 — Wed, 02 Aug 2023 15:53:07 GMT

Hello

As I attempt to understand paginated reporting - came across a weird instance of where the drop down selection for year is preceeded by a 1|.

I have verified in the source pbix file, the "Year" data column is treated as a whole number.

Below is the dax query (as found in the hidden datasets) for this parameter

EVALUATE SELECTCOLUMNS (ADDCOLUMNS (SUMMARIZECOLUMNS (ROLLUPADDISSUBTOTAL ('CALENDAR'[Year], "h0")), "ParameterLevel", if ([h0], 0, 1)), "ParameterCaption", SWITCH([ParameterLevel], 1, 'CALENDAR'[Year], "All"), "ParameterValue", [ParameterLevel] & "|" & 'CALENDAR'[Year], "ParameterLevel", [ParameterLevel], "'CALENDAR'[Year]", 'CALENDAR'[Year]) order by 'CALENDAR'[Year], [ParameterLevel]

As you can see, a component of this query contains "ParameterValue", [ParameterLevel] & "|" & 'CALENDAR'[Year]. This is probably the cause - but I have no clue what it means or purpose. I have tried to delete segments of the query - only resulting in errors.

I do have a second pbix model with a connected rdl file - using Year from the calendar table - that is working correctly. So, I know my process is correct.

Thank you!

Re: Year parameter wants to be treated as text

d_gosbell — Wed, 02 Aug 2023 22:28:31 GMT

What this probably means is that someone has been into the parameter properties and in the Available Values the Label field has been set to the "ParameterValue" column. In the autogenerated parameters this should be set to the "ParameterCaption" column

Each parameter in an RDL report has 2 properties, the Value which is passed in to the query and the caption which is what the user sees. This lets you do things like show the user a ProductName, but pass a ProductID to your query. This is not as useful in DAX based queries, but is often used in SQL based reports.

Re: Year parameter wants to be treated as text

Dellis81 — Thu, 03 Aug 2023 14:02:35 GMT

Thank you for your response 🙂

Here are the parameter settings the system auto created. In my testing, I have deleted both the query and underlying parameter. Report runs successfully. Then I put the parameter back in, with auto parameter creation with same error result.

I do have auto updates turned on, and appears I have the most recent version.

thanks!