Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Adding an optional for Parameter setting in report builder/paginated report

Hi guys, 

I am currently working on a paginated report that focuses on financial data. The report comprises three main columns: Level 4, representing account details; YTD actual, showing year-to-date actual values; and YTD PY actual, displaying year-to-date prior year actual values.

One of the parameters in the report is called "Company." I am attempting to enhance the functionality of this parameter by adding a new option to the dropdown list, labeled as "All." The purpose is to allow users to select this option, indicating that they want to include data from all companies when generating and printing the reports. (the default setting will be the first parameter option) 

 

However, I have got errors in implementing this feature. Could you assist me in finding a solution to this issue?

 

The original query designer looks like: 

 

EVALUATE
SUMMARIZECOLUMNS (
    'DimGroupAccountStructure'[Level4],
    RSCUSTOMDAXFILTER (
        @DimDateFinYear,
        EqualToCondition,
        [DimDate].[FinYear],
        Int64
    ),
    RSCUSTOMDAXFILTER (
        @DimEntityCompanyName,
        EqualToCondition,
        [DimEntity].[CompanyName],
        String
    ),
    "YTD Actual", [YTD Actual],
    "YTD PY Actual", [YTD PY Actual]
)

 

 

 

What I have done:

 

  1. motified the Data set query in Query designer: 
EVALUATE
SELECTCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            ROLLUPADDISSUBTOTAL ( 'DimEntity'[CompanyName], "h0" )),
        "ParameterLevel", IF ( [h0], 0, 1 )
    ),
    "ParameterCaption", SWITCH ( [ParameterLevel], 1, 'DimEntity'[CompanyName], "All"),
    "ParameterValue", SWITCH([ParameterLevel],1,'DimEntity'[CompanyName], "All Companies"),
    "ParameterLevel", [ParameterLevel],
    "'DimEntity'[CompanyName]", SWITCH ( [ParameterLevel], 1, 'DimEntity'[CompanyName], "All" )
)
ORDER BY
    [ParameterLevel],'DimEntity'[CompanyName]

2. Added a new line in CompanyName parameter Query Designer: 

EVALUATE
SELECTCOLUMNS (
ADDCOLUMNS (
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL ( 'DimEntity'[CompanyName], "h0" )),
"ParameterLevel", IF ( [h0], 0, 1 )
),
"ParameterCaption", SWITCH ( [ParameterLevel], 1, 'DimEntity'[CompanyName], "All"),
"ParameterValue", SWITCH([ParameterLevel],1,'DimEntity'[CompanyName], "All Companies"),
"ParameterLevel", [ParameterLevel],
"'DimEntity'[CompanyName]", SWITCH ( [ParameterLevel], 1, 'DimEntity'[CompanyName], "All" )
)
ORDER BY
[ParameterLevel],'DimEntity'[CompanyName]

 

Changed the Parameter filter expression: 'DimEntity'[CompanyName]= "All" || 'DimEntity'[CompanyName]= @DimEntityCompanyName

 

  Finnaly, I got error message: the parameter CompanyName is not declear. 

 

I am suffring from this query designer, every Dax is working on Dax studio, but when I apply in Query designer is not working. 

Thanks for your help!!

 

 

 

 

 

 

"Report Builder"
@PaginatedReports
@ssrs
@ssrs report builder help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    The error message "the parameter CompanyName is not declared" typically indicates that there's a mismatch or an issue with how the parameter is defined or referenced in your DAX query. This can happen if the parameter is not correctly set up in the report properties or if there's a typo or inconsistency in the parameter name.

    • Ensure that the "Company" parameter is correctly declared in your report. This involves checking the Parameter Properties to confirm that it's listed and correctly configured.
    • Double-check the syntax of your modified DAX query. Ensure that all references to the "Company" parameter are consistent and correctly spelled. It's crucial that the parameter name in the DAX query matches exactly with the one defined in the report parameters.

     

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

     

    If it does not help, please provide more details.

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.