March 11, 2009 at 10:27 pm
Hi All,
I am new to reporting srevices, I just need to generate a report of the format
Regional Summary Africa Asia Australia
Total Overdue 105 705 129
Total Companies 144 1032 698
Percentage Overdue72.92% 68.31% 18.48%
My table gives me the following count of total and overdue companies
RegionName TotalCompanies TotalOverdue
Africa 144 105
I am trying to use a matrix for this where the columns are Regions but I am not able to use two rows
your help is much appreciated
March 12, 2009 at 8:11 am
Can you post SQL query you use?
March 12, 2009 at 7:03 pm
There is a company's table where I get the total of the company count group by regions and then there is a overdue company table where I get the count of the overdue companies in the region.
I just have to take a percentage of the total overdue/total company in each region and represent in a matrix.
This is just a part of the code
--Published company
SELECT
vLocationOverview.RegionID,
vLocationOverview.RegionName,
COUNT(vCompanyUnsecure.Id) AS PublishedCompanyCount
FROM
Company.vCompanyUnsecure
JOIN
Definition.vLocationOverview
ON vCompanyUnsecure.LocationID = vLocationOverview.LocationID
WHERE vCompanyUnsecure.IsPublished = 1
GROUP BY
vLocationOverview.RegionID,
vLocationOverview.RegionName
--Overdue Company
SELECT
CompanyRegionID,
CompanyRegionName,
COUNT(CompanyRegionID) AS ResearchAuditCount
FROM
Research.vCompanyAuditOverview
WHERE
(NextAuditDate < @AsAtDate)
AND CompanyIsPublished = 1
GROUP BY
CompanyRegionID,
CompanyRegionName
And then I left join with the region tables to see if there is any published/overdue companies in the region. Hope this helps
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply