Solved

generate multiple pdf files from SSRS report

Posted on 2013-11-08
11
2,343 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
  • 6
  • 5
11 Comments
 
LVL 1

Expert Comment

by:FriedTyGuy
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:johnnyg123
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:johnnyg123
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Glad I could help!  Enjoy the powers of data driven subscriptions!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now