March 4, 2009 at 11:15 am
I am trying to create a report that shows total physician referrals for each month in the year sorted by physician. In one column, I want to include the total from the previous year and in another, I want to show the goal for the current year. The current year goal is the sum of last year + 15%. The query I am using is as follows:
SELECT { fn MONTHNAME(ADMIT.ADMIT_DATE) } AS Month, RESOURCE.NAME_FULL, ADMIT.ADMIT_DATE,
(SELECT DISTINCT COUNT(RESOURCE.NAME_FULL) AS Expr1
FROM ADMIT AS PA LEFT OUTER JOIN
RESOURCE AS RB ON PA.REFERRAL_SOURCE = RB.RESOURCE_ID
WHERE (PA.ADMIT_DATE BETWEEN '01-01-2008' AND '01-01-2009')) AS LAST_YEAR
FROM ADMIT LEFT OUTER JOIN
RESOURCE ON ADMIT.REFERRAL_SOURCE = RESOURCE.RESOURCE_ID
WHERE (ADMIT.ADMIT_DATE BETWEEN '01-01-2009' AND '01-01-2010')
GROUP BY RESOURCE.NAME_FULL, ADMIT.ADMIT_DATE
ORDER BY RESOURCE.NAME_FULL, ADMIT.ADMIT_DATE
Am I going about this the right way? The above query in my matrix appears to return everything accurately, I am just stumped on providing the goal column...(LAST_YEAR + 15%)
Thanks in advance for your help.
March 4, 2009 at 1:47 pm
ok so you've got your matrix all set up and you just want to add one more column right for the goal correct?
can you add the column and specify it's value as =sum(Fields.Last_Year.Value) *.15 + sum(Fields.Last_Year.Value) ? or does that give you an error?
-Luke.
March 4, 2009 at 2:01 pm
Thanks, that did work. Now I'm having trouble with the referrers from last year not showing up if they did not refer this year. I changed the subquery from a column to a full outer join and it's still only showing the Drs from this year.
Thanks for your help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply