Writing data from SSRS to a folder multiple times

  • 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

  • have a look at data driven subscriptions , i believe this will cover what you would like to achieve

  • I don't think I have data driven subscriptions as I am using the Standard edition

    Is there a work around?

    Thanks

    - Damian

  • 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.

  • 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.

  • 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

  • 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

  • Jason Selburg wrote an article with his code that does all this. without Enterprise edition. Probably worth a read.

    HTH,

    Pieter

  • 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