Solved

SQL procedure call SSRS report

Posted on 2011-09-23
15
270 Views
Last Modified: 2012-05-12
I have a SSRS report I need to run per client.
I would like to run a SQL procedure every hour to check which clients need the report run.
Then be able to run the report for each client and pass the @client parameter.
Possibly place the report in a folder specific to the @client parameter
Is this possible?
0
Comment
Question by:vzorn
[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
  • 8
  • 6
15 Comments
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36588441
Yes Possible, do you just need to use T-SQL and SSRS or you can involve SSIS too?

Also what do you mean by which clients need the report, can you explain how would you determine that?
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 36588445
Well, I *think* it is possible *IF* you can tell us *how* you can determine when a client needs report run.
0
 

Author Comment

by:vzorn
ID: 36588490
Yes I can use SSIS too.
I am querying a linked server to determine if a client's team has been updated within the last hour.
If is has this client needs to have a report generated.  I can create a temporary table to store the clients to send to.  
I just cant quite figure out how to pass a parameter to the SSRS report and tell it where to save.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36588510
I was thinking about a central location and SSIS will rename and move or even e-mail ..

is there's a flag in a table on that linked server ?
0
 

Author Comment

by:vzorn
ID: 36588551
For the table in the linked server I am looking for a record with a specific eventID within a specific time period.  It is a history/audit table.  It captures new events.  
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36588652
Alright..
1-create your temp table
2-build your report on that table
3-schedule that report to render as pdf or XLS into a specific directory, also put the schedule as once so it creates the SQL agent job.
4-will create a SSIS package, schedule it every hour to run.

that package will run the SP to check if the flag is in, then will fire the sql agent job for the report, rename and move or email. per your requirement.

let me know which part you need help with?

0
 

Author Comment

by:vzorn
ID: 36588700
I need help generating a separate report for each record.
If there are 4 clients who need the report I actually need 4 separate reports.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36588799
can you explain more about the records and clients and how do you get their data?
0
 

Author Comment

by:vzorn
ID: 36588909
Well I could use a procedure to check for new records and store them in another table.
Then I could update each one as the report is sent out.
I could use a SELECT TOP 1 to pull just one record, create the report, then update that record?
But I would need to have the report renamed before I can get the next report run.
0
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 500 total points
ID: 36588946
so store that in a temp table
use "foreachloop" container to do it one by one.

loop over the table, pull one row a time, create the report, rename and move, then process the second ...etc...
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36588958
can you let me visualize what you'll bring to the temp table? is it information that will be in the report and one of the columns will be the client name for example?

can you share an example or screen shot? so I can be with you in the same page
0
 

Author Comment

by:vzorn
ID: 36589817
Yes that process is what I need to do
"loop over the table, pull one row a time, create the report, rename and move, then process the second "
Temp table I would just store the ClientID, DateReportCreated.
I would inner join the data fields, it is quite a bit of data.  
Data fields would contain clientname, address, city, directions, phone, etc.
Cannot share screenshot sorry, sensitive information
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36590409
Ok, let me know when you;re ready, when you're done, with the SP that populate the table, done built the report over the temp table...so we can take it to the next level...

0
 

Author Comment

by:vzorn
ID: 36894016
This worked great.  Created the SSIS package to loop.
1. Checks for first open record and assigns variable.
2. Runs job to print report to folder  for first open record
3. Renames files to match variable
4. Updates record as completed.

Thanks for your help!
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36894496
Great :)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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