January 6, 2016 at 4:31 am
Hello
I am using SQL Server 2012 Standard Edition
I have a report that uses a query to prompt for a specific piece of information
There's approximately 50 values to choose from
It then filter and returns the results
I'd like to achieve the following:
Run the report against each value in turn
Write the results out to a specific folder
Use the chosen file with a timestamp as the filename
Is this the sort of thing that's easily achievable through SSRS or should I uses SSIS?
Thanks
Damian.
- Damian
January 6, 2016 at 4:41 am
have a look at data driven subscriptions , i believe this will cover what you would like to achieve
January 6, 2016 at 5:06 am
I don't think I have data driven subscriptions as I am using the Standard edition
Is there a work around?
Thanks
- Damian
January 6, 2016 at 12:41 pm
Easiest workaround is using the SSRS URL https://msdn.microsoft.com/en-us/library/ms153586(v=sql.110).aspx
You can use any language, like PowerShell or Scripting Task in SSIS, to send the command to SSRS.
Powershell example: http://blogs.msdn.com/b/cdndevs/archive/2015/10/14/automate-ssrs-report-generation-using-powershell.aspx
SSIS example: https://technet.microsoft.com/en-us/library/ff793463(v=sql.105).aspx
Native SSIS does not provide the rendering options SSRS provides.
January 6, 2016 at 5:53 pm
There is a free report generator task https://reportgeneratortask.codeplex.com/ for SSIS that I've used before, it's pretty smart and well maintained. The one thing to be aware of is that you need to have it installed on the server the package runs on, this can be a sticking point when it's production.
January 7, 2016 at 4:43 am
Thanks for the suggestions
Will take a look over the next day or so
I've also found this article which looks quite useful:
http://www.sqlservercentral.com/articles/Development/datadrivensubscriptions/2432/
- Damian
February 10, 2016 at 10:12 am
Hi
Just come back to this
I can get e-mail scheduling working but I've no idea how to get Windows File Share working
Does anybody know what the equivalent of this for Windows File Share would be
http://geoffbi.blogspot.co.uk/2012/04/data-driven-subcriptions-in-standard.html
Thanks
Damian.
- Damian
February 18, 2016 at 2:09 am
Thanks
Re-read Jasons article and went with this in the end:
USE [ReportServer$MI_REPORT_SERVER]
GO
/****** Object: StoredProcedure [dbo].[uspDataDriven_Fileshare_Parameter1] Script Date: 18/02/2016 08:58:40 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[uspDataDriven_Fileshare_Parameter1]
( @scheduleID uniqueidentifier,
@parameter1 varchar(256) = ' ',
@includeinfile bit
)
as
-- NOTE, make the filename optional
declare
@PARAMptrval binary(16),
@EXTENptrval binary(16),
@textpos int,
@textlength int,
@subscriptionID uniqueidentifier,
@parameterreplace varchar(256),
@badcharacters varchar(20),
@newfilename varchar(256)
-- Obtain report name to use as the starting point for the file name
select @newfilename = e.name FROM dbo.ReportSchedule a
JOIN dbo.Subscriptions d ON a.SubscriptionID = d.SubscriptionID
JOIN dbo.Catalog e ON d.report_oid = e.itemid
WHERE a.ScheduleID = @scheduleID
-- Create the filename by stripping out any bad characters
-- Bad characters cannot appear in a filename
select @badcharacters = ';/\?*:<>|+,[]"&'
-- Add the parameter to the file name if requested
if @includeinfile = 1
select @newfilename = @newfilename + ' - ' + @parameter1;
WITH CTE AS
(
SELECT SUBSTRING(@badcharacters, 1, 1) AS [String], 1 AS [Start], 1 AS [Counter]
UNION ALL
SELECT SUBSTRING(@badcharacters, [Start] + 1, 1) AS [String], [Start] + 1, [Counter] + 1
FROM CTE
WHERE [Counter] < LEN(@badcharacters)
)
select @newfilename = replace(@newfilename, CTE.[String],'') from CTE
-- set the subscription id
select @subscriptionID = SubscriptionID
from ReportSchedule
where ScheduleID = @scheduleID
-- obtain text point for records Parameter info
select @PARAMptrval = TEXTPTR(Parameters)
from Subscriptions
where SubscriptionID = @subscriptionID
-- obtain text point for records ExtensionSettings info
select @EXTENptrval = TEXTPTR(ExtensionSettings)
from Subscriptions
where SubscriptionID = @subscriptionID
-- Parameter Update
-- Obtain start position for Parameter 1; Note, ensure the default parameter value is |P1|
select @textpos = patindex('%|P1|%', Parameters) - 1
from Subscriptions
where SubscriptionID = @subscriptionID
-- Update Parameters with the new value; Note, length is 4 as we are replacing |P1|
--select @parameterreplace = replace(@parameter1, '[','[[]')
IF isnull(@textpos,'') <> '' and @textpos > 0 and len(@parameter1) > 0
UPDATETEXT Subscriptions.Parameters
@PARAMptrval
@textpos
4
@parameter1
-- Filename Update
select @textpos = patindex('%TemplateName%', ExtensionSettings) - 1
from Subscriptions
where SubscriptionID = @subscriptionID
-- Update ExtensionSettings with the new value; Note, length is 12 as we are replacing TemplateName
-- Refer to @newfilename as this removes all non-file compliant characters
--select @parameterreplace = replace(@parameter1, '[','[[]')
IF isnull(@textpos,'') <> '' and @textpos > 0 and len(@newfilename) > 0
UPDATETEXT Subscriptions.ExtensionSettings
@EXTENptrval
@textpos
12
@newfilename
-- run the job
exec msdb..sp_start_job @job_name = @scheduleID
-- delay gives server time to execute the job
WAITFOR DELAY '00:00:10'
-- Now revert back
-- Need to replace [ if exists as this acts as a widlcard and casuses issues
-- Use original for length though
select @parameterreplace = replace(@parameter1, '[','[[]')
select @textpos = patindex('%' + @parameterreplace + '%', Parameters) - 1
from Subscriptions
where SubscriptionID = @subscriptionID
select @textlength = len(@parameter1)
-- Update Parameters with the old value (|P1|)
IF @textlength > 0
UPDATETEXT Subscriptions.Parameters
@PARAMptrval
@textpos
@textlength
'|P1|'
-- Revert filename back to TemplateName
select @parameterreplace = replace(@newfilename, '[','[[]')
select @textpos = patindex('%' + @parameterreplace + '%', ExtensionSettings) - 1
from Subscriptions
where SubscriptionID = @subscriptionID
select @textlength = len(@newfilename)
-- Update ExtensionSettings with the old value TemplateName
IF @textlength > 0
UPDATETEXT Subscriptions.ExtensionSettings
@EXTENptrval
@textpos
@textlength
'TemplateName'
Same principles
So, create a subscription with generic placeholders
Call the filename TemplateName
Note, @includeinfile allows you to include the parameter in the filename that it writes
I'm then calling this using:
declare @ReportParameter varchar(100)
declare cur_cursor cursor for ([criteria to produce a list of parameters to pass])
open cur_cursor
Fetch Next from cur_cursor into @ReportParameter
while @@FETCH_STATUS = 0
begin
exec uspDataDriven_Fileshare_Parameter1_v3 '[Job ID]', @ReportParameter, 1
Fetch Next from cur_cursor into @ReportParameter
end
close cur_cursor
deallocate cur_cursor
This could be made a lot tidier e.g. multiple parameters, auto-generate Job ID, more data quality checks etc.
Starting point works for me though
Thanks
Damian.
- Damian
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply