Paginated Report:The property 'ValidValues' of report parameter '...' doesn't have the expected type

[zebra]

Hi All,

I am having hard time in showing date picker control in paginated report. I have created a paginated report from power bi data source and created parameters using query designer window that automaically creates parameter along with hidden dataset for its value.  for testing purpose, I have only one row as shown below

When I select this parameter type as datetime, It shows my following error.

The property 'ValidValues' of report parameter 'dateofservice' doesn't have the expected type.

Could anybody help me in solving this issue?

thanks

zebee

[Martin_D]

Hi @zebra ,

Which of the four columns did you try to use as ValidValues? And is this column data type defined as datatime (and not as text or any) in your dataset?
BR
Martin

[zebra]

@Martin_D , well... I did not use any of these four columns myself. the parameter is automatically created and also set in dataset. I checked the dataset and it is using Value as shown in following 

and yes, this column data type is date in power bi.

 I am attaching the sample pibx file at following link

https://drive.google.com/file/d/1-gMvqLKlYMKAeM0iGFmL0r5aCmkNl4Mb/view?usp=sharing

if you publish this and create paginated report using its dataset and create the date filter in query designer, then you will get the same error.  I am using the query level parameters according to the following link

https://caf2code.com/paginated-reports/paginated-reports-101-part-4-adding-parameters-and-filters-to...

 please help me to solve this problem. thanks

[Martin_D]

Hi @zebra ,

You indeed need to fix the auto-generated parameters. The auto generated code works only with parameters of type text. Thus you need to do the following changes in the parameters:

1. Datatype: Text
2. Available Values / Value Field: ParameterValue
3. (if used) Default Values / Value Field: ParameterValue

See this file for the complete solution: https://github.com/MartinBubenheimer/powerbi-solutions/raw/main/community-solutions/report-builder-q... (right click -> save link as...)

Be aware that this creates dropdown lists, not date pickers.

If you want to have date pickers you need to create parameters of type date first and then create your own query for the dataset using these parameters. With the report parameters being named FromDate and ToDate and the dataset parameters being named FromMyTabledate and ToMyTabledate the dataset query looks like:

DEFINE
    VAR _FromDate = @FromMyTabledate
    VAR _ToDate   = @ToMyTabledate
EVALUATE
SUMMARIZECOLUMNS (
    'MyTable'[name],
    'MyTable'[date],
    FILTER (
        VALUES ( 'MyTable'[date] ),
        (
            IF(
                NOT ( ISBLANK ( _FromDate ) ),
                [date] >= _FromDate,
                TRUE ()
            )  && 
            IF (
                NOT ( ISBLANK ( _ToDate ) ),
                [date] <= _ToDate,
                TRUE ()
            )
        )
    )
)

You can ignore the warning that pops up when adding this code.

If you want to add default values, you can write per parameter queries, just loading the min resp. max date from the Power BI table and bind this column to the default values in the report parameters. The corresponding Report Builder dataset query looks like:

EVALUATE 
SELECTCOLUMNS (
    { MIN('MyTable'[Date]) },
    "Date",
    [Value]
)

Be aware that you need to define some default value if you don't want the initial report query to fail before parameter values are set by the user, but NULL would also be a vaild default. See this file for the complete solution with date picker: https://github.com/MartinBubenheimer/powerbi-solutions/raw/main/community-solutions/report-builder-q... (right click -> save link as...).

BR
Martin

[zebra]

@Martin_D , thanks Marin for your help and provide me the exact query. The next thing is that I will have other parameters of type text lets say name , city,  . these parameters will have All as its first option and then other values like in the case of autogenerated parameters.  would you please provide me the example of such report too? It would be a great favor. thanks

zeebee.

[Martin_D]

Hi @zebra ,

The following example refers to the Power BI dataset that you provided, using column "name" as an example of a text column.

Creating a text parameter works out of the box as described in the link that you referenced. So the challenge is probable how to integrate it with the date parameters?

These steps should make it simple:

• Create a dataset with text query parameter to get the report parameter and the parameter table as a temlate.
• Add the new parameter as a dataset parameter to the existing dataset (the one in which you want to have both, the existing date filters and the new text filer) to represent the selection of the report parameter of text dropdown list (in my example: MyTablename).
• Add filter (not on the filter page but in the DAX code) to the dataset query to apply dropdown selection. The final code of the MyTable dataset looks like:

DEFINE
    VAR _FromDate = @FromMyTabledate
    VAR _ToDate   = @ToMyTabledate
EVALUATE
SUMMARIZECOLUMNS (
    'MyTable'[name],
    'MyTable'[date],
    FILTER (
        VALUES ( 'MyTable'[date] ),
        (
            IF(
                NOT ( ISBLANK ( _FromDate ) ),
                [date] >= _FromDate,
                TRUE ()
            )  && 
            IF (
                NOT ( ISBLANK ( _ToDate ) ),
                [date] <= _ToDate,
                TRUE ()
            )
        )
    ),
    RSCustomDaxFilter(@MyTablename,EqualToCondition,[MyTable].[name],String)
)

• If you want to apply a default selection so you can immediately run the report if the user does not define a selection in the report parameters, then you can build a default values dataset as described above for start date and end date. As an example, the code for a default values table to preselect all text values looks like:

EVALUATE 
SUMMARIZECOLUMNS ( 'MyTable'[name] )

See this file for the complete solution: https://github.com/MartinBubenheimer/powerbi-solutions/raw/main/community-solutions/report-builder-q... (right click -> save link as...).

BR

Martin

[zebra]

Thanks @Martin_D , I will try this solution and let you know if everything goes well.  I want to ask one more question related to the same topic. If I use auto generated query along with auto generated query parameters and select None for available values and default values for date type paramters then I successfully able to show the date picker controls as shown below.

However, when I click on the view report, it gives me the following error.

Could you please let me know if I choose this way to show the datepicker control then how to remove this error? I asked this becuase I want to keep the autogenerate queries and if there is any change required then that should be minimal, Otherwise I need to write queries along with parameter queries manually for each report and that would be quite long task as there are multiple tables, measurement involves with multiple parameters in each report. so would you please let me know the solution ? 

Thanks once again for your time and answering my questions. I appriciate your efforts.

Best

Zebee

[Martin_D · Accepted solution]

Hi @zebra ,

This video illustrates what you are looking for: https://www.youtube.com/watch?v=VTRJ8fSs-3A

This file demonstrates the combination of the first approach in the video with an additonal text column filter using your dataset: https://github.com/MartinBubenheimer/powerbi-solutions/raw/main/community-solutions/report-builder-q... (right click -> save link as...).

This file demonstrates the combination of the second approach in the video with an additonal text column filter using your dataset, which is close to my approach discussed above: https://github.com/MartinBubenheimer/powerbi-solutions/raw/main/community-solutions/report-builder-q... (right click -> save link as...).

BR

Martin

[zebra]

@Martin_D , thanks marin, this is exactly what I was looking for. (y).

[zebra]

@Martin_D , Hey Martin,

Could you please let me know how to make these date parameters optional? 

[Martin_D]

What do you mean by "optional"? If you mean, no active date selection by the user should be necessary and in this case all dates should be included, then just make these dates the default selection. What else do you nedd?

[zebra]

Here optinal means the user does not need to select the date range. this means that date should not be the part  of selection criteria. I solved this by using the following code

 IF(
                NOT ( ISBLANK ( _FromDate ) ),
                [date] >= _FromDate,
                TRUE ()
            )  && 
            IF (
                NOT ( ISBLANK ( _ToDate ) ),
                [date] <= _ToDate,
                TRUE ()