01-04-2011 12:34 AM
Hi all,
Is it possible to get a daily report on item archiving rate through reporting services ?
I've created a subscription but in the settings of the subscription you have to select a start/end date for the report.
However these dates are not incremented by one each day, it's a fixed range.
So is there a way to solve this through RS or do I need to e.g. script a sql query ?
Which is kind of a pain if you want to do this through e-mail as well :)
Cheers
Fredrik
Solved! Go to Solution.
01-04-2011 10:04 AM
Have been messing around with this today and have (I think) managed to get the update query to be in the correct format.
I guess it's a simple thing to create a stored procedure or using SQLCMD to update the parameters column.
USE ReportServer
GO
Update dbo.Subscriptions SET Parameters = '<ParameterValues><ParameterValue><Name>StartMonth</Name><Value>'+CASE WHEN month(GETDate()) < 10 THEN CONVERT(VARCHAR(1),month(GETDate())) ELSE CONVERT(VARCHAR(2),month(GETDate())) END+'</Value></ParameterValue><ParameterValue><Name>StartYear</Name><Value>'+CONVERT(VARCHAR(4),year(GETDATE()))+'</Value></ParameterValue><ParameterValue><Name>EndMonth</Name><Value>'+CASE WHEN month(GETDate()) < 10 THEN CONVERT(VARCHAR(1),month(GETDate())) ELSE CONVERT(VARCHAR(2),month(GETDate())) END+'</Value></ParameterValue><ParameterValue><Name>EndYear</Name><Value>'+CONVERT(VARCHAR(4),year(GETDATE()))+'</Value></ParameterValue><ParameterValue><Name>Granularity</Name><Value>1</Value></ParameterValue><ParameterValue><Name>EndDay</Name><Value>'+CASE WHEN day(GETDate()) < 10 THEN CONVERT(VARCHAR(1),day(GETDATE())+1) ELSE CONVERT(VARCHAR(2),day(GETDATE())+1) END+'</Value></ParameterValue><ParameterValue><Name>StartDay</Name><Value>'+CASE WHEN day(GETDate()) < 10 THEN CONVERT(VARCHAR(1),day(GETDATE())) ELSE CONVERT(VARCHAR(2),day(GETDATE())) END+'</Value></ParameterValue></ParameterValues>' WHERE SubscriptionID = '67b5bd91-aea9-4669-a839-11f6bdaabce0'
01-04-2011 02:30 AM
I dont think by default this kinda of report exists as you probably know.
the only ones I know si the SQL queries as per below.
■ Use the following SQL query to show how much has been archived in a certain
period as well as time the first and last items were stored during that period.
Replace the dates and times in this example with those you want to use.
Select count(*),min(archiveddate),max(archiveddate) from saveset
where archiveddate >'2002-04-26 18:00' and archiveddate <
'2002-04-27 5:00'
■ Use the following SQL Query to obtain the number of different vaults processed
in a specified period.
Select distinct(vaultidentity) from saveset where
archiveddate >'2002-04-26 18:00' and
archiveddate < '2002-04-27 5:00'
01-04-2011 05:27 AM
I thought there was a way for it to send you the previous 24 hours but I could be wrong.
If you want to just set up a sql query you can use these, if you run these you do not have to put in a date range, it will give you rates for the past 24 hours:
-- This one gives a hourly rate:
--Runs against the VaultStore Database
select "Archived Date" = left (convert (varchar, archiveddate,20),14),
"Hourly Rate" = count (*),
"Av Size" = sum (itemsize)/count (*)
from saveset
where archiveddate > dateadd("hh", -24, getdate ())
group by left (convert (varchar, archiveddate,20),14)
order by "Archived Date" desc
-- This one gives a daily rate:
--Runs against the VaultStore Database
select "Archived Date" = left (convert (varchar, archiveddate,20),10),
"Daily Rate" = count (*),
"Av Size" = sum (itemsize)/count (*)
from saveset
where archiveddate > dateadd("hh", -24, getdate ())
group by left (convert (varchar, archiveddate,20),10)
order by "Archived Date" desc
01-04-2011 05:34 AM
Thanks guys but I know about the queries, what I need is to be able to automaticly run a report or query every day and send the results to a mailbox.
Kind of what RS are doing except that it doesn't work in regard to just showing the last 24 hours and getting the parameters incremented every day.
I had a look at the subscription stored in Reporting Services and I'm checking now if I could create a stored procedure/job to update the parameter column every day.
So that is what I'm looking at for the time being.
If anyone have any better ideas in regard to do this in a better way then by all means :)
01-04-2011 10:04 AM
Have been messing around with this today and have (I think) managed to get the update query to be in the correct format.
I guess it's a simple thing to create a stored procedure or using SQLCMD to update the parameters column.
USE ReportServer
GO
Update dbo.Subscriptions SET Parameters = '<ParameterValues><ParameterValue><Name>StartMonth</Name><Value>'+CASE WHEN month(GETDate()) < 10 THEN CONVERT(VARCHAR(1),month(GETDate())) ELSE CONVERT(VARCHAR(2),month(GETDate())) END+'</Value></ParameterValue><ParameterValue><Name>StartYear</Name><Value>'+CONVERT(VARCHAR(4),year(GETDATE()))+'</Value></ParameterValue><ParameterValue><Name>EndMonth</Name><Value>'+CASE WHEN month(GETDate()) < 10 THEN CONVERT(VARCHAR(1),month(GETDate())) ELSE CONVERT(VARCHAR(2),month(GETDate())) END+'</Value></ParameterValue><ParameterValue><Name>EndYear</Name><Value>'+CONVERT(VARCHAR(4),year(GETDATE()))+'</Value></ParameterValue><ParameterValue><Name>Granularity</Name><Value>1</Value></ParameterValue><ParameterValue><Name>EndDay</Name><Value>'+CASE WHEN day(GETDate()) < 10 THEN CONVERT(VARCHAR(1),day(GETDATE())+1) ELSE CONVERT(VARCHAR(2),day(GETDATE())+1) END+'</Value></ParameterValue><ParameterValue><Name>StartDay</Name><Value>'+CASE WHEN day(GETDate()) < 10 THEN CONVERT(VARCHAR(1),day(GETDATE())) ELSE CONVERT(VARCHAR(2),day(GETDATE())) END+'</Value></ParameterValue></ParameterValues>' WHERE SubscriptionID = '67b5bd91-aea9-4669-a839-11f6bdaabce0'