Problem with an aggregation

Hi,

I am new to the reporting services and I've been working on problem in one of my reports all day long and after 8 hours of frusturation I decided ask for a profesional help.

Ok here is my problem: I have a report that calculates the amount of meetings with our clients. The dataset contains an activity_id field that we assign for each our meetings with our clients. SSRS counts these meetings and shows it in a drilldown enabled report. Everything seems fine on the report except that someof the activities involves few different clients and SSRS is not counting the activities multiple times in region drilldown as there is only one activity id associates in that region even though it contains different companies. And I want those companies to be calculated in too.

From the crude drawing below I wanted to explain my dilemma visually. As it can be seen the total number of meetings we had is actually 40. But as we had 3 activities that involves more than 1 clients it only gives 37 as a count. I would like to know is there a way to make the report count the same activity multiple times if activity_id is associated with more than one clients.

I hope I managed to explain my problem

**********************************************************************************************

Manager Region Market Company Meeting Detail

+Manager 1 (9 meetings)

+Manager 2 (37 meetings)

- West (37 meetings)

-Denver (37 meetings)

+Company 1 (5 meetings)

+Company 2 (2meetings)

+Company 3 (2meetings)

+Company 4 (3meetings)

+Company 5 (0meetings)

+Company 6 (0meetings)

+Company 7 (5meetings)

+Company 8 (1meetings)

+Comapny 9 (19meetings)

+Company 10 (3meetings)

Total (40 meetings)

You would need to use a composite key by grouping on the combination of the activity_id and company_id rather than just the activity_id. I believe strongly in doing as much of the math as possible in the SQL statement, rather than the report. That way you can use your same report and simply show the total that was calculated rather than the calculating the total. For example:

Code Snippet

SELECT

base.Manager,

base.Region,

base.Market,

base.Company,

base.MeetingDetail,

MTot.Total as ManagerTotal,

RTot.Total as RegionTotal,

M2Tot.Total as MarketTotal,

CTot.Total as CompanyTotal

FROM

baseTable base,

(SELECT COUNT(*) AS Total FROM baseTable bt WHERE bt.Manager = base.Manager) MTot,

(SELECT COUNT(*) AS Total FROM baseTable bt WHERE bt.Manager = base.Manager AND bt.Region = base.Region) RTot,

etc.

This is really bad SQL, but I don't know the actual structure that you are working from and I think this conveys the idea.

If this isn't clear, post or send me the table structures and I will help you build a query that calculates your totals.

Larry

|||

Thank you Larry,

I will try to follow up with your suggestion hopefully it will help. if I got stuck I will post the table structure to you. Thank you again for your prompt response.

Regards,

Burak

|||

Hi Larry,

I've been trying to use your suggestion but I couldn't figure it out a way to use it.

My sql code is as follows

Code Snippet

SELECT DISTINCT
TOP 100 PERCENT ACTIVITY_ID, Date, ACTIVITY_TYP_NM, LAST_NM, FIRST_NM, COMPANY_NM, DISPLAY_NM, Region, SUMMARY, MLAE, Market,
STATE, Expr1
FROM dbo.INTV_Sales_Funnel
GROUP BY ACTIVITY_ID, Date, ACTIVITY_TYP_NM, LAST_NM, FIRST_NM, COMPANY_NM, DISPLAY_NM, Region, SUMMARY, MLAE, Market, STATE,
Expr1
ORDER BY ACTIVITY_ID, Date, SUMMARY, COMPANY_NM

MLAE represents the Manager

and Expr1 represents the Count

the thing is when I used the count im getting a value that more than I should get and the reason for this, is in some of our events we invite multiple people from a company or multiple people from multiple companies.But the report should reflect only 1 count of the event if the meeting had either 1 person or more from a single company. I managed to get rid of those extra people on the meetings over the report so activities only listed once. But it seems like I really need to find a way to make that calculation over the report. Is there a way to use Count Distinct on report with 2 values. Right now I am using Activity ID but I guess if add both activity id and company name as you mentioned earlier on your response I might be able to solve this problem.

|||

First, on re-reading your original post, I am not sure that your original solution wasn't correct. If you had 37 meetings and 3 meetings had two customers each then you would show the result of the sum of the meetings that each customer attended was 40 rather than 37 because each of the three meetings with two customers each would be counted twice.

That said, here is a method of generating the query that will return your results. Lets start with what you know that you want and build the query from left to right on your table above. This will not get us the most optimized query, but it should show the method of layering results within a query that can allow you to get complex results with simple nested queries. One advantage to this method is that you can check the results at each stage.

First, let's get a count of all of the meetings for the managers:

Code Snippet

SELECT
DISF.MLAE,
COUNT(DISF.Activity_Id) ManagerTotal
FROM
(SELECT
DISTINCT
MLAE,
Activity_Id
FROM
INTV_Sales_Funnel
) DISF
GROUP BY
DISF.MLAE

Then, let's get the count of meetings per region per manager:

Code Snippet

SELECT
DISF.MLAE,
DISF.Region,
COUNT(DISF.Activity_Id) RegionTotal
FROM
(SELECT
DISTINCT
MLAE,
Region,
Activity_Id
FROM
INTV_Sales_Funnel
) DISF
GROUP BY
DISF.MLAE,
DISF.Region

Next, let's get the count of meetings per market within a region for each manager:

Code Snippet

SELECT
DISF.MLAE,
DISF.Region,
DISF.Market,
COUNT(DISF.Activity_Id) MarketTotal
FROM
(SELECT
DISTINCT
MLAE,
Region,
Market,
Activity_Id
FROM
INTV_Sales_Funnel
) DISF
GROUP BY
DISF.MLAE,
DISF.Region,
DISF.Market

Lastly, let's get the count of meetings per customer within each market and region for each manager:

Code Snippet

SELECT
DISF.MLAE,
DISF.Region,
DISF.Market,
DISF.Company_NM,
COUNT(DISF.Activity_Id) CompanyTotal
FROM
(SELECT
DISTINCT
MLAE,
Region,
Market,
Company_NM,
Activity_Id
FROM
INTV_Sales_Funnel
) DISF
GROUP BY
DISF.MLAE,
DISF.Region,
DISF.Market,
DISF.Company_NM

Then, when you want to combine them and return all of the values, join each of the four queries to the main query as follows:

Code Snippet

SELECT
main.Activity_Id,
main.Date,
main.ACTIVITY_TYP_NM,
main.LAST_NM,
main.FIRST_NM,
main.COMPANY_NM,
main.DISPLAY_NM,
main.Region,
main.SUMMARY,
main.MLAE,
main.Market,
main.STATE,
mgr.ManagerTotal,
rgn.RegionTotal,
mkt.MarketTotal,
cmp.CompanyTotal
FROM
INTV_Sales_Funnel main
JOIN
(
SELECT
DISF.MLAE,
COUNT(DISF.Activity_Id) ManagerTotal
FROM
(SELECT
DISTINCT
MLAE,
Activity_Id
FROM
INTV_Sales_Funnel
) DISF
GROUP BY
DISF.MLAE
) mgr
ON
main.MLAE = mgr.MLAE
JOIN
(
SELECT
DISF.MLAE,
DISF.Region,
COUNT(DISF.Activity_Id) RegionTotal
FROM
(SELECT
DISTINCT
MLAE,
Region,
Activity_Id
FROM
INTV_Sales_Funnel
) DISF
GROUP BY
DISF.MLAE,
DISF.Region
) rgn
ON
main.MLAE = rgn.MLAE
AND
main.Region = rgn.Region
etc.

I hope this helps. Please let me know how it turns out.

Larry