• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

SQL procedure call SSRS report

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
vzorn
Asked:
vzorn
  • 8
  • 6
1 Solution
 
Jason Yousef, MSSr. BI  DeveloperCommented:
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
 
sammySeltzerCommented:
Well, I *think* it is possible *IF* you can tell us *how* you can determine when a client needs report run.
0
 
vzornAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jason Yousef, MSSr. BI  DeveloperCommented:
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
 
vzornAuthor Commented:
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
 
Jason Yousef, MSSr. BI  DeveloperCommented:
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
 
vzornAuthor Commented:
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
 
Jason Yousef, MSSr. BI  DeveloperCommented:
can you explain more about the records and clients and how do you get their data?
0
 
vzornAuthor Commented:
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
 
Jason Yousef, MSSr. BI  DeveloperCommented:
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
 
Jason Yousef, MSSr. BI  DeveloperCommented:
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
 
vzornAuthor Commented:
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
 
Jason Yousef, MSSr. BI  DeveloperCommented:
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
 
vzornAuthor Commented:
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
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Great :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now