I had a report (ASP) that I found and customized in CM07 that allowed me to
choose a metering rule and then choose a month/year and it would give me all of
the metering data from that date until the current date. It gave me the
following:
1. Netbios Name
2. Username
3. Last usage date
4. Total usages count
5. Average usages per day count
6. Total duration (min)
7. Average duration of use (min)
8. Average duration per day (min)
I'm having trouble getting it to work in SRS though. Does anyone have a report
that they'd be willing to share that already has this info? Or if not, what's
it take to get this working in SRS? I was able to export the report from CM07
but I don't see a way to then import that into SRS? Maybe its as simple as
that, I just missed it.
Here's the original report query. I started down the road of building a new
report in Report Builder but this SQL is over my head so I'm hoping someone has
already done the work...
declare @TimeKey int
declare @days float
declare @currTimeKey int
declare @currYear float
declare @currMonth float
declare @currDay float
set @TimeKey=100*@Year+@Month
select @CurrYear=datepart(yyyy, getDate())
select @CurrMonth=datepart(m, getDate())
select @CurrDay=datepart(d, getDate())
set @currTimeKey=100*@currYear+@currMonth
select @Days=DATEDIFF(day,IntervalStart,DATEADD(month,1,IntervalStart))
from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey< @currTimeKey
set @Days = @Days+@CurrDay
if IsNULL(@days,0) > 0
select sys.Netbios_Name0, sys.User_Name0,
DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)) as LastUsage,
SUM(UsageCount) + SUM(TSUsageCount) as C021,
ROUND((SUM(UsageCount) + SUM(TSUsageCount))/@days,2) as C022,
ROUND(SUM(UsageTime)/60.0,2) as C023,
ROUND(SUM(UsageTime)/60.0/(SUM(UsageCount) + SUM(TSUsageCount)),2) as
C024,
ROUND(SUM(UsageTime)/60.0/@days,2) as C025
from v_R_System sys
join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID
left join v_RA_System_SMSInstalledSites inst on sys.ResourceID=inst.ResourceID
where mf.ProductName = @RuleName
and mus.TimeKey >=@TimeKey
group by sys.Netbios_Name0, sys.User_Name0
having SUM(UsageCount) + SUM(TSUsageCount) > 0
order by sys.Netbios_Name0
Here's the query for the @RuleName
begin
if (@__filterwildcard = '')
select distinct ProductName as C018 from v_MeteredProductRule order by
ProductName
else
select distinct ProductName as C018 from v_MeteredProductRule
WHERE ProductName like @__filterwildcard
order by ProductName
end
Here's the query for the @Month
begin
if (@__filterwildcard = '')
select distinct DATEPART(month,IntervalStart) as Month,
DATEPART(year,IntervalStart) as Year from v_SummarizationInterval order by
DATEPART(year,IntervalStart), DATEPART(month,IntervalStart)
else
select distinct DATEPART(month,IntervalStart) as Month,
DATEPART(year,IntervalStart) as Year from v_SummarizationInterval
WHERE DATEPART(month,IntervalStart) like @__filterwildcard
order by DATEPART(year,IntervalStart), DATEPART(month,IntervalStart)
end
Here's the query for the @Year
begin
if (@__filterwildcard = '')
select distinct DATEPART(year,IntervalStart) as Year from
v_SummarizationInterval order by DATEPART(year,IntervalStart)
else
select distinct DATEPART(year,IntervalStart) as Year from
v_SummarizationInterval
WHERE DATEPART(year,IntervalStart) like @__filterwildcard
order by DATEPART(year,IntervalStart)
end
Thanks,
James
________________________________
IRS Compliance: Any tax advice contained in this communication (including any
attachments) is not intended or written to be used, and cannot be used, for the
purpose of (i) avoiding penalties imposed under the Internal Revenue Code or
applicable state or local tax law or (ii) promoting, marketing, or recommending
to another party any transaction or matter addressed herein.
________________________________
Confidentiality Notice: This e-mail is intended only for the addressee named
above. It contains information that is privileged, confidential or otherwise
protected from use and disclosure. If you are not the intended recipient, you
are hereby notified that any review, disclosure, copying, or dissemination of
this transmission, or taking of any action in reliance on its contents, or
other use is strictly prohibited. If you have received this transmission in
error, please reply to the sender listed above immediately and permanently
delete this message from your inbox. Thank you for your cooperation.
