March 29, 2011 at 10:12 am
I have a report with multiple Group Levels. It has 4, but for this discussion only the top 2 levels should matter (Group1 & Group2).
At the detail level, each row of data contains a field (fieldX) that has a value of either 1 or 0. I created this field to evaluate a Y/N field numerically.
At Group1, I want to evaluate the sum of fieldX as it is for the Group2's.
If at least one Group2 has the sum of fieldX at 0, then I want to include all data (no filtering). But if all Group2's have a sum higher than 0, then I want to filter out the entire group. So you see that I can't put the filter right at Group2. That would be easy to do, but I only want to filter all or nothing.
I was hoping filter would allow me to do something like: Min(Sum(FieldX, "Group2Name")) = 0. But it's not letting me nest the aggregates like that. Nor does it seem to know what I mean when I put the Group2 name into the sum like that.
Hopefully this makes sense. Anyway I slice it I find myself back to this same core problem. And because I have data below these 2 levels that I want to report, I can't see a way of just building my sql in such a way that the data is easier to filter at the data set. It seems that I need to use filters to get what want. Or else 2 separate data sets and subreports, which I'd like to avoid.
Thanks for any direction anyone can offer.
March 29, 2011 at 11:54 am
hi tmiller
I'd be happy to help, do you think you could show a sample of the data and query the report is working with? You can use fictive data if necessary, just to give me an idea what we are working with because I am not sure how group 1 and group 2 relate.
Maxim
March 30, 2011 at 7:43 am
Hi Maxim,
Thanks for the reply. I worked around the problem using subqueries in my sql. I was afraid of that slowing down data retreival badly, but it did not, so it was acceptable.
I originally had this:
SELECT
p.Project_id, p.Description,
pb.Purchase_Order, pb.Amount_Booked,
oe.Order_no,
cust.Customer_id, cust.Customer_name,
inv.Invoice_no, inv.Invoice_date, inv.Amount_paid, inv.Terms_taken, inv.Tax_amount, inv.Total_amount_home, inv.Allowed_home, inv.Memo_amount_home,
((inv.total_amount_home - inv.amount_paid - inv.terms_taken - inv.allowed_home) + inv.memo_amount_home) Remaining_balance,
(((inv.total_amount_home - inv.amount_paid - inv.terms_taken - inv.allowed_home) + inv.memo_amount_home) / inv.total_amount_home) Remaining_percent,
(DATEDIFF(day,inv.invoice_date,getdate())) Days_Invoiced,
(CASE oe.completed WHEN 'Y' then 0 else 1 end) complete_check
FROM
PTS.dbo.Project p
JOIN PTS.dbo.ProjectBid pbid ON p.project_id = pbid.project_id
JOIN PTS.dbo.ProjectBooking pb ON p.project_id = pb.project_id
JOIN p21_view_oe_hdr oe ON
p.project_id = oe.job_name AND
pb.purchase_order = oe.po_no AND
pbid.customer_id = oe.customer_id
JOIN p21_view_customer cust ON oe.customer_id = cust.customer_id
LEFT JOIN p21_view_invoice_hdr inv ON oe.order_no = inv.order_no
WHERE
oe.cancel_flag = 'N' AND
oe.delete_flag = 'N' AND
p.lien = 1 AND
pbid.awarded = 1 AND
inv.total_amount_home > 0
I added this (and another similar check for something else). With this I can now filter at the highest group level and evaluate the Min(fields!Complete_checker.value). The field it puts in the datastream is redundant, but effective.
JOIN
(SELECT p.Project_id, pb.customer_id, SUM(comp) complete_checker
FROM PTS.dbo.Project p
JOIN PTS.dbo.ProjectBooking pb ON p.project_id = pb.project_id
JOIN (SELECT (CASE completed WHEN 'Y' THEN 0 ELSE 1 END) comp, customer_id, job_name, po_no FROM p21_view_oe_hdr
WHERE class_1id is null or class_1id <> 'OS_SVC') oe
ON pb.customer_id = oe.customer_id and p.project_id = oe.job_name and pb.purchase_order = oe.po_no
WHERE p.lien = 1
GROUP BY p.project_id, pb.customer_id) complete ON p.project_id = complete.project_id and pb.customer_id = complete.customer_id
March 31, 2011 at 6:56 am
I learned more officially today that SSRS 2005 "cannot create expressions that calculate an aggregate of an aggregate " in the way I was attempting to do it here. However I'm told that SSRS 2008 could. I thought that was info worth adding to this post.
March 31, 2011 at 7:55 am
That's good to know, thanks for sharing the resolution and extra info with us 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply