0

I have made a simplified example to illustrate the question i'm trying to ask here. In my example i have sales orders and each sales order has multiple lines, i group by Sales Order Number, then by Sales Order Line (row groups)

I have found Group Filters very useful/flexible in filtering report data in specific areas of a table, so in my example i filter the SOLine group to exclude the SO line if it equals 3.

Then, i want to have a group aggregate for the entire SO, to tell me a count of the SO lines within it. Unfortunately when doing COUNT() expression from a textbox within the Sales Order Number group scope it counts all the lines, including the SO Line 3, whereas i want it to take into consideration the line filtered out from its child group.

Below is a screenshot of my tablix and grouping:

enter image description here

On the SOLine group i have the following filter:

enter image description here

And below is the output i get when previewing the report:

enter image description here

I want that count to evaluate to 4, but i ideally want to keep using groups as i've found they are much more efficient than using SUM(IIF) which completely slowed down my actual report which has thousands of rows.

If this is not possible, please give all best alternatives i could use.

Many thanks. Jacob

jarlh
  • 42,561
  • 8
  • 45
  • 63
Jacob Cook
  • 41
  • 1
  • 8
  • Could you count the rows in the group? http://stackoverflow.com/questions/16320495/total-row-count-for-grouped-values-in-ssrs-2008 – Ola Ekdahl Mar 29 '17 at 10:37
  • As shown in the first screenshot i am doing this, using =COUNT(Fields!sales_order_line.Value). However, it is scoping to the SONumber grouping and calculating the number of rows before the inner group SOLine filters out the SO line 3. I want it to take into account the filters applied at the inner group – Jacob Cook Mar 29 '17 at 10:41
  • You're doing a count. I was suggesting a countrows("groupname") – Ola Ekdahl Mar 29 '17 at 11:16
  • I see, i have just tried that and it returns a row count of 5, but i need it to return 4. – Jacob Cook Mar 29 '17 at 12:05

0 Answers0