generate multiple pdf files from SSRS report

Where is the data pulled from?  If lets say you have a table where these records are created and there's a time stamp in the table, you can write a data driven subscription that query's the table like so...

SELECT checkNo FROM checkTable WHERE dateCreated >= DATEADD("mi",-5,Now())

And set the subscription to run every five minutes (or days, weeks, whatever, just make sure the dateadd(if SQL) matches.

For the report, change it so that the dataset now has a @check_no parameter (only - if it's unique).

Then select the checkNo from the data driven query for the parameter for the report.  Select the file share location, etc.

Note that you can get really technical in creating file names, different file shares, etc. from the data driven query and using the result as parameters in the subscription.

If the subscription picks up 500 check numbers, it will process 500 reports and put them where ever you specify.

If needed I can put some screen shots together, but we're running SharePoint 2013 with integrated 2012 SSRS so it may look a little different.

The report is based on a stored proc which  has a query that pulls data from a few tables in our great plains accounting systems

Check date is the input parameter

I have attached a sample file

Each check number would be a separate pdf page.  If the check number has more invoices that can fit on 1 page there would be an additional pdf page with the same header info and a continuation of invoices


There can be multiple checks on a given check date and each check can have 1 or more invoices associated with it



I'd really appreciate it if you could do some screen shots

Even if they aren't exactly the same I think it would help me immensely

Thanks!
check.docx

You're current plans for subscription may still work, your report will have to be based on the check number, not the check date - this will give you a file per check number, not per check date.  Since I'm not familiar with the stored procedure or table structure I'll have to make some assumptions for you to clarify before we look at data driven subscriptions.  
How were you identifying the check date parameter on your subscription previous subscription?

Basically, a data driven subscription returns parameters to be used in your report.  If nothing is available there's no report.  If the data driven subscription query is set up to return a check number and the report parameter is check number, you will get a report for each check number the data driven subscription picks up.  Issue lies in not having the same check number returned over and over; that's where a datetime field comes in handy.

Basing in on check date however, may cause issues.  

Can the check date change on a check that has already been created?  
Is there a table in GP that identifies that the check has been printed/processed?

Ideally, if this table exists, then you could write a simple query such as:

SELECT DISTINCT checkNo
FROM Table
WHERE checkProcessedDateTime >= DateAdd(hh,-24,Now())

Then schedule the subscription to run every 24 hours.  When it runs it will run the report (given the parameter on the SP and report is changed to checkNo) for each checkNo returned.

Is this doable?  I'll throw a doc together on creating the subscription if it is.  Also, if you could share the SP it could help as well.

Thanks so much for your replies!

I have never worked with data driven subscriptions before so I apologize for all the questions.  It seems it will allow me to do what I need to in theory.  Just having a bit of trouble with setting it up.  

I had not set up a previous subscription yet.  This is a brand new process which we originally thought would be a single pdf file.

Please let me add in some more background

Ideally I would want the file names to consist of check number and check date

The vast majority of the files would contain just one page since it is kinda rare for a single check to be associated with a more than about 15 invoices

(Looks like all will fit on 1 page if there are 15 or less invoices)

For example if there are 3 checks created on nov12 (101,102,103)
if 101 has 2 invoices and 102 has 5 invoices and 103 has 25 invoices

I am hoping for 3 pdf files

11122013_101  (would contain 1 page relating to check 101)
11122013_102  (would contain 1 page relating to check 102)
11122013_103  (would contain 2 pages relating to check 103)






The checkdate was used for flexability.   The report is designed so parameter default is current date but if need be the user could choose whatever date he/she wants (outside of subscription).   Also, due to the amount of checks written we were trying to limit the amount of data returned to be just for a single day as opposed to having all checks written from first of year until current date and then just picking a given date...Though I suppose this is possible if need be


here is the stored proc

 (which I think answers your question regarding gp check data....if necessary I could create a single table that holds result of the query)



(I changed the server name to xxxxx)

ALTER PROCEDURE [dbo].[spAPICheckForm]
      -- Add the parameters for the stored procedure here
      @checkdate Date
AS
BEGIN
      SELECT a.vendorid [VendorID]
            ,c.VENDNAME
            ,c.ADDRESS1
            ,c.ADDRESS2
            ,c.CITY
            ,c.STATE
            ,c.ZIPCODE
            ,rtrim(c.CITY) + ', '+ rtrim(c.state)+ ' ' + rtrim(c.zipcode) CityStateZip
            ,a.docdate CheckDate
            ,a.docnumbr CheckNumber
            ,a.docamnt CheckAmount
            ,b.aptodcnm InvoiceNumber
            ,b.aptodcdt InvoiceDate
            ,b.appldamt InvoicePaid
      FROM xxxxx.dbo.pm30200 a WITH (NOLOCK) -- AP History
      INNER JOIN xxxxx.dbo.pm30300 b WITH (NOLOCK) -- Apply To information
            ON a.vchrnmbr = b.vchrnmbr
            AND a.doctype = b.doctype
      INNER JOIN xxxxx.dbo.PM00200 c WITH (NOLOCK) ON a.VENDORID = c.VENDORID
      WHERE a.doctype = 6 and a.docdate = @CheckDate


Hopefully the file I attached in my previous comment will show where are the fields returned will appear




I think it is safe to assume that check date can not change on a check that has already been created

Perfect!  First off, the number of pages is irrelevant to the file and subscription unless your end users have a problem with multiple pages.

If in that SP you can change the last line to:

WHERE a.doctype = 6 and a.docnumbr = @CheckNo

Then in the SQL query box of the data driven subscription (shown in attached doc) put

SELECT  a.docnumbr AS CheckNo, a.docnumbr + "_" + a.docdate AS fileName
FROM xxxxx.dbo.pm30200 a
WHERE a.doctype = 6 and a.docdate >= DateAdd("hh",-24,Now())

(if the docdate is datetime, if not then modify the DateAdd to DateAdd("d",-1,Now()) and set the subscription accordingly)

The rest I've added in the .doc (note I modified the subscription query between the comment and the screen shot as I initially forgot to give the filename an alias).
doc.docx

I'm sorry    but I think I might be missing something

I'm looking for all checks (and their associated invoices) written on a given date

If I change the stored proc that the report is using to only look at check number won't I only get a single check?

Yes and no...  You want a single file for each check?  Then the report has to be for only a single check.  The trick comes in with the data driven subscription.  The query there will return all check numbers for the specified date and will run individual report against each.  

Note that the SP will still give you all associated invoices for the single check.  But, the only way to get a file per check is to run the report per check.

Also, you shouldn't have to modify the report in anyway aside from the SP, everything else including your grouping should work when ran by check or date.

yes ... I do want a single file for each check

I think I see.....I need a different/separate 'non-subscription' report that the user will use if they want to manually pick dates using the sp that uses date

Worked like a champ!!!!!

Thank you so much for all the time you spent.  Makes total sense now!!!

Glad I could help!  Enjoy the powers of data driven subscriptions!