cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Paginated Reports "All" parameter

I have connected Paginated Reports to a dataset that I have previously published in Power BI. I'd like to have a parameter to limit the results by Manufacturing Plant. But I'd also like to be able to return all data.

In SSRS, I used to append a row into the parameter's available values query with the value "All" and then, in the sql, check for the "ALL" value and, if detected, process the query with no filter on that field. I've tried to do the same thing in Paginated Reports.

Howver, I can't seem to append my 'All' or 'NoFilter' row. The dax works in DAX stuidio, but Paginated Reports isn't processing it. Can anyone offer some advise? Here is the Dax that I'd like to use to cause the aparemter to provide a list of available Plants and also a row for "All," or "No Filter."

EVALUATE
 (
    UNION (
        DATATABLE (
            "Plant Number", STRING,
            "Plant Name", STRING,
            {
                { "ALL", "No Filter" }
            }
        ),
        SUMMARIZE ( 'PLANT PR1', 'PLANT PR1'[Plant Number], 'PLANT PR1'[Plant Name] )
    )
)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate I
Advocate I

Re: Paginated Reports "All" parameter

Blast.  It was a picky little syntax error.

 

EVALUATE
 ( ...

The command shown above is no good.  Report Builder does not like that white space between the EVALUATE keyword and the open pren.  The following variation worked fine.

EVALUATE( ...

Thanks for taking the time to look into the problem.  

 

 

 

 

View solution in original post

11 REPLIES 11
Highlighted
Super User III
Super User III

Re: Paginated Reports "All" parameter

What error message are you getting? I just tried doing the same thing against a copy of the Adventure Works database with the following query and I was able to create a dataset in my report with no problems.

 

EVALUATE
 (
    UNION (
        DATATABLE (
            "Country Region Code", STRING,
            "Country Region Name", STRING,
            {
                { "ALL", "No Filter" }
            }
        ),
        SUMMARIZE ( Geography, Geography[Country Region Code], Geography[Country Region Name] )
    )
)
Highlighted
Advocate I
Advocate I

Re: Paginated Reports "All" parameter

Blast.  It was a picky little syntax error.

 

EVALUATE
 ( ...

The command shown above is no good.  Report Builder does not like that white space between the EVALUATE keyword and the open pren.  The following variation worked fine.

EVALUATE( ...

Thanks for taking the time to look into the problem.  

 

 

 

 

View solution in original post

Highlighted
Super User III
Super User III

Re: Paginated Reports "All" parameter

The Report Builder Query editor does it's own parsing and sometimes it can't handle things which are actually valid syntax. In terms of pure DAX syntax the whitespace/linebreaks are not significant, plus EVALUATE is a keyword not a function so you don't even need the ( ) you can just do EVALUATE UNION(...)

 

Or the other thing that could have gone wrong is that are at least 2 different types of space characters. The unicode character set has both a normal space and a non-breaking space (which affects where soft line breaks can appear). Report Builder does not work properly with non-breaking spaces, but you typically only get these when copying from web pages or other documents.

 

Note that when you paste into DAX Studio these characters get replaced with "standard" spaces so sometimes pasting into DAX Studio then re-copying and pasting the same query back into Report Builder will fix these weird "syntax" errors.

Highlighted
Advocate I
Advocate I

Re: Paginated Reports "All" parameter

Hi @RBunting , I have the same business case - I already have my Parameter values with All., but now the problem is with the Dataset query.

How d you manage to filter - is All - Show All, otherwise the selected value.

I'm using this condition RSCustomDaxFilter(@Measure,EqualToCondition,[v_MeasureDetail_Latest].[MeasureCode],String) in the main dataset query. but here I can't add any OR condition. 

I tried to do it in the expression of the Parameter itself like:

IIF(@Measure = "All", Nothing, Parameters!Measure.Value) or IIF(@Measure = "All", True, Parameters!Measure.Value), but no luck there either, I get error for passing invalid value to the parameter.

How did you apply the parameter to your dataset afterwards?

 

Thanks!

Highlighted
Advocate I
Advocate I

Re: Paginated Reports "All" parameter

pdacheva,

 

My entire query is a dynamic expression that generates a valid DAX statement.

 

During the evaluation of the query text, I check the parameters and build the specific query language to send to the source system.

 

 

="EVALUATE(FILTER(...  " + iif( Parameters!Plant.Value <> "ALL", "&& 'PLANT PR1'[Plant Number] = """ & Parameters!Plant.Value & """", "") + "  ...

 

 

so, if the parameter is not set to "all," this line in the query becomes a piece of the filter conditions "&& 'PLANT PR1'[Plant Number] = "99999999".  But if the plant number IS set to "all" then this whole bit just gets omitted from the DAX query and no filter on Plant is ever applied.

 

 

 
Highlighted
Advocate I
Advocate I

Re: Paginated Reports "All" parameter

Hi RBunting,

Thank you for your reply!

Sorry for not replying earlier - I changed the filtering of this dimension within the DAX (not using the RSCustomDaxFilter formula) and it worked as expected.

 

Best,

 

Highlighted
Regular Visitor

Re: Paginated Reports "All" parameter

Hi @pdacheva  @RBunting 

 

   Can you please share me the format of the filter in DAX? I tried but getting error.

 

Thanks

 

Highlighted
Advocate I
Advocate I

Re: Paginated Reports "All" parameter

Hi @harieee ,

 

Make sure you add the filter at the end of the SUMMARIZE or SUMMARIZECOLUMNS. If you share the error message maybe the community could help better. See below how I am filtering my DAX:

dax filter.PNG

Highlighted
Regular Visitor

Re: Paginated Reports "All" parameter

Hi @pdacheva 

 

   Thanks. "All" is working. If i set All as default then working good and if i add available values from query then All is not picking up as a default. Any idea? Can you share your email?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.