topic Re: Paginated Report not distributing Count values correctly in Report Server

Paginated Report not distributing Count values correctly

humid — Thu, 20 Oct 2022 15:03:17 GMT

Hello,

I've just started to work a bit with Report Builder with our Power BI Report Server and although I've done some progress, I'm right now stuck with a requeriment which is so simple in Power Bi, but seems a bit more complex in a paginated report.

First I've created a KPI which is a simple COUNT with some filter expresions:

=CountDistinct ( IIF
( Fields!Diff.Value > 0 And Fields!Action_Phase.Value <> "Complete" And Fields!Action_Phase.Value <> "Void",Fields!Number.Value,Nothing),"DataSet1")

If I put this as an expression in a TextBox, works perfectly and gives me the expected result, say 150

However, now I have to create a table like: City ; Count, but when I create this simple table and set the expresion also in the table, I obtain the following result:

City Count

A 150

B 150

C 150

When the expected result would be:

City Count

A 100

B 25

C 25

It seems the count is not being aggregated correctly.

I've also tried to create the count as a calculated field in the DataSet, and use this field in the table instead, but is not allowing me as it gives me the following error when I execute the report:

"The expression used for the calculated field 'Count' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions."

Any suggestions?

Thanks in advance!

Re: Paginated Report not distributing Count values correctly

Anonymous — Fri, 21 Oct 2022 09:36:19 GMT

Hi humid ,

For create paginated report in report builder, if you create matrix visual, you can set the aggregation type by select during design time.

For more details, you can read related document: Tutorial: Creating a Matrix Report (Report Builder) - SQL Server Reporting Services (SSRS) | Microsoft Learn

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Paginated Report not distributing Count values correctly

humid — Fri, 21 Oct 2022 09:42:37 GMT

Hello binbinyu, what is explained in the tutorial is quite basic and simple. In my case, the count measure I want to put in the table, is a measure created with some filter conditions.

In Power BI Report, when I create the count measure and I put it on a table with other dimensions, it is correctly aggregated. However, I'm not sure how to replicate the same behavior in the Report Builder, as when I try the same, I receive the aggregation error

Re: Paginated Report not distributing Count values correctly

humid — Tue, 08 Nov 2022 11:11:54 GMT

Hello, when I create the Matrix i receive the following error:

The expression used for the calculated field 'Metric_01' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions.

Metric_1 = CountDistinct ( IIF
( Fields!Diff.Value > 0 And Fields!Action_Phase.Value <> "Complete" And Fields!Action_Phase.Value <> "Void",Fields!Number.Value,Nothing),"DataSet1")

Any help?

Re: Paginated Report not distributing Count values correctly

Syndicate_Admin — Tue, 08 Nov 2022 11:24:13 GMT

Hello,

First I've created a KPI which is a simple COUNT with some filter expresions:

=CountDistinct ( IIF
( Fields! Diff.Value > 0 And Fields! Action_Phase.Value <> "Complete" And Fields! Action_Phase.Value <> "Void",Fields! Number.Value,Nothing),"DataSet1")

If I put this as an expression in a TextBox, works perfectly and gives me the expected result, say 150

However, now I have to create a Matrix like: City ; Count, but when I create this simple table and set the expresion also in the table, I obtain the following result:

City Count

A 150

B 150

C 150

When the expected result would be:

City Count

A 100

B 25

C 25

It seems the count is not being aggregated correctly.

I've also tried to create the count as a calculated field in the DataSet, and use this field in the table instead, but is not allowing me as it gives me the following error when I execute the report:

Any suggestions?

Thanks in advance!