?
Solved

generate multiple pdf files from SSRS report

Posted on 2013-11-08
11
Medium Priority
?
2,569 Views
Last Modified: 2013-11-13
I have an ssrs report that lists all invoices paid by each check

The report is grouped by check# and exported out into a single pdf file with multiple pages.
 
we were in the beginning stages of automating the process by  setting up a subscription to export the pdf file to a file share which seemed pretty straight forward.

We have just learned that the vendor has changed their application so now there needs to be  a separate pdf file for each check.   The slight twist is that there can be enough entries for a check to result in multiple pages


(fyi  .... we will be using a separate process to ftp the files to the vendor)

I have done some research and looks like data driven subscription can be used but the details on how are very sketchy.  (The generic msdn description)

I am using ssrs 2008 enterprise version so data driven subscription is available to me

any one know of a link that might show step by step?  or perhaps a different approach

anything we come up with needs to be automated with no manual intervention
0
Comment
Question by:johnnyg123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 1

Expert Comment

by:FriedTyGuy
ID: 39634944
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.
0
 

Author Comment

by:johnnyg123
ID: 39642375
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
0
 
LVL 1

Expert Comment

by:FriedTyGuy
ID: 39642582
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.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:johnnyg123
ID: 39642733
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
0
 
LVL 1

Expert Comment

by:FriedTyGuy
ID: 39642893
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
0
 

Author Comment

by:johnnyg123
ID: 39643034
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?
0
 
LVL 1

Expert Comment

by:FriedTyGuy
ID: 39643044
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.
0
 

Author Comment

by:johnnyg123
ID: 39643055
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
0
 
LVL 1

Accepted Solution

by:
FriedTyGuy earned 2000 total points
ID: 39643083
Just modify the SP to something like

WHERE a.doctype=6 and @CheckNo IS NULL and a.docdate = @CheckDate OR a.doctype=6 and @CheckNo IS NOT NULL and a.docnumbr = @CheckNo

And add the CheckNo and CheckDate Parameters allowing NULL value.  You may have to play with it a bit as the report set up as such would cause it to fire when opened, but would produce some error allowing you to uncheck the Null for CheckDate and enter it.
0
 

Author Closing Comment

by:johnnyg123
ID: 39645312
Worked like a champ!!!!!

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

Expert Comment

by:FriedTyGuy
ID: 39645377
Glad I could help!  Enjoy the powers of data driven subscriptions!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question