topic Re: Paginated Reports "All" parameter in Report Server

Paginated Reports "All" parameter

RBunting — Mon, 20 May 2019 16:28:29 GMT

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] )
    )
)

Re: Paginated Reports "All" parameter

d_gosbell — Wed, 22 May 2019 02:53:40 GMT

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] )
    )
)

Re: Paginated Reports "All" parameter

RBunting — Wed, 22 May 2019 15:35:02 GMT

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.

Re: Paginated Reports "All" parameter

d_gosbell — Wed, 22 May 2019 21:00:36 GMT

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.

Re: Paginated Reports "All" parameter

pdacheva — Fri, 07 Feb 2020 10:34:21 GMT

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!

Re: Paginated Reports "All" parameter

RBunting — Fri, 07 Feb 2020 16:00:27 GMT

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.

Re: Paginated Reports "All" parameter

pdacheva — Fri, 20 Mar 2020 11:44:30 GMT

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,

Re: Paginated Reports "All" parameter

Anonymous — Tue, 14 Jul 2020 10:43:37 GMT

Hi @pdacheva @RBunting

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

Thanks

Re: Paginated Reports "All" parameter

pdacheva — Tue, 14 Jul 2020 12:17:11 GMT

Hi @Anonymous ,

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:

Re: Paginated Reports "All" parameter

Anonymous — Tue, 14 Jul 2020 12:50:40 GMT

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?

Re: Paginated Reports "All" parameter

pdacheva — Tue, 14 Jul 2020 12:54:26 GMT

Well, in this case you need to make sure you have "All" in the available values of your parameter query. Just do a UNION and add dummy record "All" in the available values of your parameter list.

Re: Paginated Reports "All" parameter

mvabalaitis — Fri, 31 Jul 2020 16:04:50 GMT

@RBunting - Thanks so much for all the info, super helpful in converting our SSRS to Paginated. One question regarding the addition of the "All" value. I was able to get the union working in my dataset, however when I attempt to add the ORDER BY functionality the expression breaks throwing the following error: "A single value column cannot be determined". The issue is resolved by removing the union, which gets me back to square one.

Has anyone ran into this issue and can offer any advice?

Re: Paginated Reports "All" parameter

RuchikaSoni — Tue, 03 Nov 2020 22:18:41 GMT

Hello,

Your post was very helpful. However the filter condition fails when multiple values are selected in report parameter.

Here is what I am using: The report execution fails when I supply multiple values on Program Name parameter. Any tips @pdacheva

FILTER('TreatyMaster_BI',(@TreatyMasterBIProgramName= " ALL" || 'TreatyMaster_BI'[Program Name] = @TreatyMasterBIProgramName)),

Re: Paginated Reports "All" parameter

pdacheva — Wed, 04 Nov 2020 07:43:33 GMT

Hi @RuchikaSoni ,

Yes, you cannot use "=" for a multiselect parameter. Set it up like this:

FILTER('TreatyMaster_BI',(@TreatyMasterBIProgramName= " ALL" || PATHCONTAINS(@TreatyMasterBIProgramName,'TreatyMaster_BI'[Program Name]))

And then, in your dataset's parameters setting, set the @TreatyMasterBIProgramName as

=JOIN(Parameters!TreatyMasterBIProgramName.Value,"|")

This should work.

Cheers,

Re: Paginated Reports "All" parameter

RuchikaSoni — Wed, 04 Nov 2020 16:11:04 GMT

Thanks very much @pdacheva . The solution worked.

Re: Paginated Reports "All" parameter

RuchikaSoni — Thu, 19 Nov 2020 19:24:30 GMT

Hello @pdacheva ,

I am currently using sql logic to append "ALL" in the parameter list. The logic is working fine but since I have a lot of parameters on my paginated report I feel the report is taking longer to load initally.

Can you suggest How to union "ALL" using DAX?

Logic to append "all" in parameters using sql is as follows:

select DISTINCT [Reporting SBU]
from TreatyMaster_BI
WHERE (([Company] in (@TreatyMasterBICompany)) OR ' ALL' IN (@TreatyMasterBICompany))
and [Reporting SBU] is not null
UNION
SELECT ' ALL' as 'Reporting SBU'
ORDER BY 'Reporting SBU'

Re: Paginated Reports "All" parameter

pdacheva — Fri, 20 Nov 2020 07:19:06 GMT

Hi @RuchikaSoni ,

It should be something like that. Just check the syntax.

// DAX Query
EVALUATE
UNION(
SUMMARIZE(
FILTER(TreatyMaster_BI, (PATHCONTAINS(@TreatyMasterBICompany,'TreatyMaster_BI'[Company]) || @TreatyMasterBICompany = "All") && NOT(ISBLANK('TreatyMaster_BI'[[Reporting SBU]])) )
TreatyMaster_BI[Reporting SBU])
, DATATABLE (
"Reporting SBU", STRING,
{
{" All"}
}
)
)

Re: Paginated Reports "All" parameter

RuchikaSoni — Fri, 20 Nov 2020 20:09:12 GMT

Thanks very much, @pdacheva . The query worked! Appreciate all the help you extended.

Re: Paginated Reports "All" parameter

Craig_Donoghue — Sat, 05 Jun 2021 01:24:03 GMT

Amazing. I've been trying to figure this out for two days. Thanks!

Re: Paginated Reports "All" parameter

V_20 — Fri, 26 Jul 2024 18:56:31 GMT

Hi @RBunting @Craig_Donoghue @RuchikaSoni @pdacheva @mvabalaitis

I am running into the same requirement. I could not use RsCustomdaxfilter and filter dax function in the same query. The query is not picking up the parameter.
would you let me know how you used both like in the image shared above?