ReportServer Forum

karolina

Member

Registered: 2014-08-09

Filters in the Dynamic List

Hi,

In the Dynamic List view I would like to display all rows in case value in one of the columns are duplicated.

For instance: I have a list of invoices and I'd like to quickly find all cases where the invoice number is duplicated (let's say the relevant column is INVOICE_NUMBER)

My understanding is that this could be done by prefiltering and expressions like ${agg.count()}

Am I right? I made some attempts, trying to figure it out from the documentation, but without success.

Another case would be to show all the invoices with net value (NET_VALUE) > 10000. I guess there is a similar way to achieve that - I just need some hints and examples.

Cheers,
Karolina

karolina

Member

Registered: 2014-08-09

Re: Filters in the Dynamic List

OK, I found the answer for the second question:

I had to put "10000 - " in the text input page of the "Include range" part of the filter confuguration, as described in 6.4 section of the User Guide.

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Filters in the Dynamic List

Hi Karolina,

The agg.count won't really help in this situation. The easiest is to use a window function and add an extra attribute either directly in the underlying report or as a computed column. Assuming the table is TEST_TABLE and the field you are interested in is INVOICE_NUMBER you can use the following query:

SELECT *, COUNT(*) OVER (PARTITION BY INVOICE_NUMBER) AS CNT_INVOICES FROM TEST_TABLE

(The example is for PostgreSQL.) This adds the field CNT_INVOICES which contains the number of records with the same INVOICE_NUMBER in the table. Once you have that, you can simply use a regular filter and filter on

2 -

to get all the records that have duplicate INVOICE_NUMBERS.

Hope this helps.

Cheers,
Arno

karolina

Member

Registered: 2014-08-09

Re: Filters in the Dynamic List

Thanks Arno!

If possible, please share some more examples of filtering expressions in one of your future blogposts about the Dynamic List

Cheers,
Karolina

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Filters in the Dynamic List

Sounds like a plan