Jacob Cook Jacob Cook - 10 months ago 41
SQL Question

SSRS 2008 R2 / SQL - How to filter groups but keep detail data?

EDIT - i'm reposting this question in an attempt to explain what i mean better

I'm using SQL 2008 R2 and I work for a retail department store and we need a report to show all the sales orders made in each department, and sections of those departments.

What i want is to group up all the sales order lines by department and section, but remove only the sections that have a total sales value of less than £50. I still want to see order lines that are over £50, though.

Here is an example of what i currently have:

Data before filtering

I want to remove the Accessories section and all lines contained within it, as it has a total section value of less than £50. So i would want it looking like this after filtering:

Data after filtering

Here is my code:

SELECT department.department_name

sales_order_detail INNER JOIN stock_item ON sales_order_detail.stock_item_code = stock_item.stock_item_code
INNER JOIN style ON stock_item.style_code = style.style_code
INNER JOIN department ON style.dept_code = department.department_code
INNER JOIN section ON style.section_code = section.section_code AND style.dept_code = section.department_code AND department.department_code = section.department_code

Can you please explain all the ways this can be done. I've tried using GROUP BY and HAVING but that then filters out all my sales order lines. I've tried using a Group Filter in the visual studio report design surface which removes the lines but then aggregates calculated at the Department group scope don't take into account the lines removed at the section level.

I appreciate any help i can get on this.

Answer Source

As you are using 2008R2, you can use the magic that are Windowed Functions to calculate the total of the group that the row belongs to (the partition part of the over clause below) and then wrap your query into a filtering select statement. Not having your data this is obviously not tested, but it should work:

select department_name
    select d.department_name
            ,sod.qty_ordered * sod.selling_price AS LineValue
            ,sum(sod.qty_ordered * sod.selling_price) over (partition by d.department_name
                                                            ) as GroupTotal

    from sales_order_detail sod
        inner join stock_item si
            on sod.stock_item_code = si.stock_item_code 
        inner join style s
            on stock_item.style_code = s.style_code 
        inner join department d
            on s.dept_code = d.department_code 
        inner join section se
            on s.section_code = se.section_code
                and s.dept_code = se.department_code
                and d.department_code = se.department_code
) a
where GroupTotal > 50