SSRS Reports: Everything You Need To Know
by Christian Ofori-Boateng on Jun 10, 2020 1:57:01 PM
Reasons to use SSRS to Design, Generate, and Deploy Business Reports
SQL reporting services is a server based report designer, builder, and deployment engine made by Microsoft. This can be used to prepare and deliver various interactive and printed reports. With SQL reporting services combined with SQL Server, firms have a unified solution for both report design and data warehousing. SSRS can help you to create tabular, graphical and free-form reports from relational, multidimensional and XML based data sources. The reports can also be published and accessed on demand. SSRS also has a built in scheduling tool to perform basic report deployments via email, file share, or SharePoint.
SSRS can be used in different ways and with other systems to deploy business reports. SQL reporting services can be used with business process management tools such as PBRS to automate SSRS reports, move data across various databases, and even drive tasks based on events.
The Payables Management department can use SQL server reporting services to generate vendor summary analysis, vendor cash requirements reports and all transaction history details. The Payroll unit may use it for generating and updating employee wage and work hours report, pay history reports and for maintaining earnings register. For the manufacturing units, SQL Reporting Services manages Picking Reports and item standard costs changes history. Some of the typical features of SQL Reporting services that make report deployment and consumption easier include:
- Easy access to report formats: The reports can be accessed easily from within Microsoft Dynamics GP. There is also a one click access from the user’s personalized ‘My Reports’ list of frequently used formats. In terms of generating the report, SSRS supports a variety of formats including PDF or Excel. Additional formats can be used via plugins or an external tool such as PBRS.
- Drilldown action: The user can quickly access essential information with the ability to drill down within the reports.
- Chart options: SQL reporting services offer various report layout options including pie chart, line chart and bar chart capabilities. These can be used to highlight key information and enhance business presentations.
- Customized Filtering: With SQL server reporting services the users can filter report data using dynamic parameters.
- Flexible report views: SQL reporting services allow for a collapsible report view to expand sections, reducing complex reports to manageable proportions.
- Sub-reports: Users can create sub reports with a main report and also the main report to one or more sub reports through a set of parameters.
- Table View: This report layout option quickly presents the data in a table format for better viewing and report distribution across different units of a business.
Many will find that SSRS has a bit steeper of a learning curve, especially for users that transitioning from Crystal Reports. However the benefits of a unified data warehouse and reporting solution add a greater amount of flexibility. Eventually you will have the need to expand SSRS’ feature set by relying on plugins and external tools to add more report formats, distribution methods, and advanced scheduling. If you are beginning to transition to SQL Server as your reporting system, with a bit of learning, you will not be disappointed!
How To Transform Decision Making with Self-Service Reporting Tools
Business Intelligence software used to belong in the IT department, but not because the decision-makers all worked in IT. Before self-service reporting tools became available, IT did this work because they were the only ones with the training to operate it. New software, however, means new opportunities. Self-service reporting tools allow decision-makers to escape the stress of delegating sensitive work and give them the rapid access to data they need. Learn how you can transform decision making with self-service reporting tools.
Limits on Decision-Makers
Decision-makers typically face more responsibilities than they can shoulder alone. They come from top managerial positions, or they may be owners themselves. Each of these roles comes with extra work. In addition to overseeing labor, handling advanced customer care issues, and working on sensitive tasks, such as bookkeeping, they're also expected to move the business forward. It's a lot to ask from anyone, and without the proper tools, decision-makers can break under these demands. They certainly can't do their best work without help.
Even if the decision-makers in your business are all expert work-jugglers, traditional BI tools still restrict their abilities. The average piece of BI software does not make it easy for untrained users to run searches or analyze results. These programs need an expert eye and a skilled hand to master. That is why rudimentary BI tasks often fall to IT personnel. However, this isn't what the IT department is really for, so this division of labor benefits no one. Your IT team wastes valuable time and resources performing the legwork of someone else's task, and the decision-maker who requested a particular report has to sit and wait until the IT team has a chance to fulfill the request. Your decision-makers can't act quickly, and your IT department moves even more slowly.
How Self-Service Reporting Tools Help
Self-service reporting software relies on intuitive features and simple layouts. These give everyone access to the data they most need when they need it. IntelliFront BI is an excellent example of these programs. This specialized tool gives users access to KPIs, stored reports, and separate dashboards through a single portal. It offers both scheduled reports and on-demand reports, giving decision-makers the power to take control of all their regular BI needs. Communication isn't slowed by needless delegation.
As useful as business intelligence tools are, the traditional model consumed so much time that small or overworked businesses simply couldn't afford the labor investment. Every report required a careful order from the end user that traveled down the chain of command to the IT department. Once there, the report request would either delay other important work or would sit in a long list of report requests from other decision-makers. Whenever the report was ready, it would travel back to the end user. This process consumed the decision-maker's time, the IT department's time, and often intermediaries' time. Decision-makers had to choose report criteria extremely carefully because each report was a relatively costly investment.
Real-time business intelligence reporting tools offer end users direct control over a variety of reporting features. They can order reports on their own, schedule regular work in advance, and even take advantage of real-time KPI dashboards. The traditional report cycle disappears, and decision-makers gain the opportunity to research more thoroughly and respond to crises faster. Reports cost less to run, and real-time reporting allows users to simply open a window to check on critical numbers, such as KPIs. This gives decision-makers more direct power. It also helps them make faster, better-informed choices.
A Broader Support Structure
Although there will always be the need for some hierarchy and a chain of command, decision-makers don't need to be as isolated as the traditional BI model forces them to be. By removing the time constraints and financial obstacles posed by the conventional model, real-time reporting tools offer decision-makers the chance to develop a wider collection of insights. The best way to do this is to utilize more high ranking employees from various departments.
This secondary decision-maker tier probably wouldn't enjoy the same range of data sets higher-ups would, but their insights represent the foundation of the business. No single decision-maker can know everything about his or her business. It makes sense to equip departmental experts to collect and present new data and ideas. BI tools help the second tier weed through the validity of many options before they ever make it to a presentation. That saves the ultimate decision-makers time. It also means presented ideas already have the analytical groundwork to back up their validity. Decision-makers' work becomes even more efficient, and the business as a whole benefits from a better application of its various human resources.
How much more could your business accomplish if your decision-makers weren't hampered by the traditional BI model? You'll never know until they have the opportunity to do their best. Real-time reporting tools can't solve every business issue, but they can give your decision-makers the power to solve a lot more problems in much less time.
Business Schedulers for SQL Reporting Services: SSRS Report Format, Delivery, and Options
The SSRS report has been written, parameters have been set, and now it’s time to go live with the report. There are still a few lingering questions before users can start happily reading the report. How will they subscribe to the report? What format will the report be? What kind of security needs to exist around the report? These questions can determine what kind of tool to use to schedule your SSRS report.
Report Formatting Options
Let us first tackle how the report will be formatted. By default, SSRS can export to Word, PDF, CSV, Tiff, XML, and Excel. In most cases these formats will satisfy a wide variety of reporting needs. There are cases however that require additional security, or format types. Using external tools or plugins such as PBRS can automatically format SQL Reporting Services reports in extensions that are unavailable in normal SSRS. Lotus Notes, Rich Text, and dBase are just a few atypical format types that are supported. To further enhance the capabilities of your SSRS reports, PDF or Excel outputs can be encrypted. PDF files can have user based permissions added, or the report can be set to expire after a certain period. ZIP or PGP encryption can be added to the SSRS report output as well.
Report Delivery Options
Using a business scheduler or external tool for SSRS can expand report delivery options as well. Typically email, file share and SharePoint (if configured) are possible delivery locations for an SSRS report. Also a business scheduler opens up new report destination types such as fax, FTP, or even delivers the report to another database via an ODBC connection. This destination can even be configured based on information in SQL Server. For example, if the data stipulates that one report version must be delivered to an FTP site, while another must be sent to a printer, then the business scheduler will automatically format and deliver the report accordingly.
Report Scheduling Options
Obviously, a business scheduler would offer a greater variety of scheduling options than those offered in SQL Reporting Services. Customized calendars, dedicated thread management, and event driven reporting are just a few options external SQL Reporting Services schedulers offer. Combining SSRS with these types of systems can give your reporting system the flexibility to handle any scenario. From a wider variety of output formats, to advanced security options, using an external business scheduler will maximize SQL Reporting Services capabilities!
How to Schedule SSRS Reports for Emailing
The ability to schedule SQL Server Reporting Services (SSRS) Reports for automatic emailing is essential for your business. With an automated SSRS report scheduler, your reports are always emailed on time. Moreover, you never have to worry about forgetting to send a report. SSRS report schedulers let you customize your automatic report delivery system to fit your businesses unique needs. We’ve put together a 'how-to' schedule SSRS reports for emailing.
Automate SSRS Reports
Scheduling your SSRS report to automatically email reports is actually quite simple. All you need to do is determine how often you want each report to run. You also need to designate the output format you want to use. These options include Word, Excel, HTML, RTF, Acrobat, RPT, or text format. Lastly, tell the program where you want the report sent. You can schedule your reports to be automatically sent to email, but that’s not all. You can also send reports to a designated folder, printer, or fax. You can also have them sent to Dropbox, SharePoint, FTP, or SFTP.
Schedule SSRS Reports For Emailing
The ability to customize your SSRS report scheduler to fit your unique business needs makes it the perfect addition to your reports. With PBRS SSRS report scheduler, you can schedule reports to run on a specific date and time. You can even schedule them to run hourly, daily, weekly, or monthly. You can even set the frequency of your reports, like running every third Monday of the month. If you need the same report to run multiple times, but need it in different formats, that option is also available along with varying formats and destinations. For some businesses, setting up an SSRS report schedule based on time and date is far from ideal. That’s why SSRS reports can also be scheduled to run based on event triggers. These triggers include events such as present database values or the modification of certain files. With PBRS report scheduler, your SSRS report schedule can be set up to fit your business’s delivery needs.
When running your business, receiving, reading, and understanding reports are an everyday occurrence. Simplifying the process with scheduled reports that run and send automatically, means one less thing you need to worry about. Not only is it simple and easy to set up, this automation tool schedules, exports, distributes and delivers SSRS reports with precision, exactly how you want it to. You also know that your reports are sent automatically to their destinations in almost any format, making it easy for others to read and understand the same reports. ChristianSteven Software offers PBRS report scheduling software to help with your businesses scheduling needs. See how PBRS can help your business with a free trial.
Why Should You Automate Report Delivery in SQL Reporting Services?
Unsure whether you should automate report delivery in SQL reporting services? You may be surprised at how important the benefits are. If you want to get the most out of your reporting services and transform your business through the power of business intelligence, then you need to get comfortable with automated reports. They are key to efficient BI implementation and reliable BI habits and skills.
Leave More Time for BI Implementation
Any form of business intelligence relies on the recipient to further analyze and implement the contents of the report. Without those additional steps, even the most advanced programs are entirely useless. However, the more time users spend trying to configure and run reports, the less time they have for the most critical part of any BI process. This part of the process involves using the actionable information provided by the system. If you automate SSRS report delivery, you eliminate the need for repeated labor. More importantly, you increase the amount of time available for real work with the resulting data. It’s a simple step with significant financial and developmental potential.
Rather than spending thirty minutes every day, week, or month setting up the same report over and over, you could use that time to organize regular meetings. Although SQL reporting services provide plenty of valuable insights, that data will reveal far more under multiple pairs of eyes. No one analyzes the same data sets exactly the same way, after all, and creative thinking is a key development tool. Working with coworkers to get the most out of your automated report is a far more productive use of your time than repeating the same task you performed yesterday.
Develop Regular Analysis Habits
Free time is pointless unless you have a productive way to use that time. Using BI effectively is all about developing regular habits. This gives you the knowledge you’ll need to successfully guide your business. After all, a single chart can only tell you what’s happening today. It won’t reveal the past, and it’s very inadvisable to make predictions based on a single data set. Seeing frequent reports regarding various issues and data sets gives you a broader view. As a result, you are better informed and able to decide if a loss is a temporary slip, an established trend, or a hidden opportunity. The only way to know this is to know how the same numbers performed previously.
To use BI, you need to know your business. This means examining regular reports. It’s easy to forget to run a report during a busy week, and that will hurt both your existing knowledge and your habits. If you automate report delivery in SQL reporting services, however, you can rest assured that a little distraction won’t compromise your BI analysis skills.
The best way to use BI is to use it regularly, and automating reports makes it easy to develop such habits. Whether you’re looking to expand or simply want to improve daily operations, automating your reports will make a big difference. Success through BI tools is all about how you use them.
Benefits of Automated Report Delivery in SQL Reporting Services
When it comes to gathering data, the process of building the report and sending it out is possibly the most intense. With SQL reporting, accessing and delivering reports is made simple. By automating the service, you only improve its usefulness.
This list of benefits shows how automated report delivery in SQL reporting services can help serve your company.
- Timely SSRS reports
- Automated SSRS report delivery
- Event triggered SSRS subscriptions
Reports on Time
One of the biggest frustrations with reporting is that end-users often complain that reports are not received in a timely manner. Even if they are received, they are still outdated or contain errors. By automating the delivery of SSRS reports, scheduled reports are automatically delivered to the correct end-user. No more delays and errors due to human interference. Best of all, reports are sent in a variety of formats from Word, Excel, HTML, RTF, Acrobat, RPT, to text format. These are all available to choose from for the output format. After determining the output format, you also have the option to send the reports through any and all means. These include saving to a folder, attaching to an email, printing, or faxing. You can also choose to automatically save to services such as DropBox and Sharepoint.
SSRS comes with the standard, everyday scheduling options you would expect. But, third-party tools like PBRS for SSRS Automated scheduling lets your company take advantage of advanced functions. With PBRS automated report delivery in SQL reporting services, scheduling your SSRS reports is made easy. Simply set the SSRS reports to run on a specific date at a specific time. You can even schedule them to run on an hourly, daily, weekly, or monthly basis. You also have the option of setting up your own custom frequencies, such as having reports delivered every other day or every third Tuesday of the month. Another feature included with scheduling is that one report can be scheduled to be run multiple times with different parameters, formats, frequencies, and destinations.
Ways To Make Automating SSRS Reports Easy
Report publishing can often be a chore for SQL server administrators. Users constantly requesting reports or the unending duty of managing report subscriptions. Luckily, when using a third party program, automating SQL reports can actually be easy, and if you are nerdy enough, fun! Using an external tool such as SQL, there are a few ways to automate your SSRS reports.
Single Report Subscription
This is the most basic schedule type found in PBRS. It involves specifying your SSRS report, setting the execution time, report format, and the destination of the report. All of the schedule types discussed in this article will share this basic concept. As basic as the Single Report Schedule is, there are still a great many features built into it. Report parameters such as date parameter types can be auto-calculated using inserts. Blank report detection ensures that a report containing no data is not sent to a recipient. The Single Report Subscription also has a package variant that enables you to batch multiple SSRS reports and distribute them to users. Moreover, a group of reports can be merged into a single text, PDF, or Excel file. Most of the schedule types discussed in this article will share these features, but the Single Schedule is truly the “Granddaddy” of all schedule types.
Data Driven Report Subscription
Many refer to this function as the “Dynamic Schedule on Steroids.” This function performs a similar action as the Dynamic Report Subscription; reeling through a database, populating the key parameter, and sending the report to a corresponding email address. However, the Data Driven Schedule can customize every part of the report distribution process. Using your database, report format, report destination, database settings, and multiple parameters can all be customized based on records in your table or view. Even the messaging behind the report’s email can be customized. For example, using a data driven subscription, a unique email containing the unique report can be sent to each record. The body of the email can be customized to included the recipient’s name or possible points of data that preview the report.
Event Driven Report Subscription
Event Driven Subscriptions can monitor for certain conditions such as changes in a database, file system, processes, or an email inbox. If the conditions are met, the schedule will trigger the SQL report. This can all occur in real time. The report can even reflect the changes in the database that occurred. This enables you to build extremely complex report workflows without the need for you to build any logic yourself. Other condition options include monitoring a port or IP address for data, or watch for certain windows to be present on the machine.
All of these subscription types have a package variant where you can batch and merge multiple SSRS reports. There are many different ways to process reports either within SSRS or using a 3rd party system such as PBRS. The schedule type you require largely depends on the type of report and how it must be distributed.
4 Business Software “DO's and DONT's” of Report Generator Software
It is one thing to build report generator software, but it’s another thing to create software that is intuitive, functional, and effective. There’s no shortage of software that generates reports. From Crystal Reports to SSRS and everything else in between, there are plenty of tools for businesses to not only format their data but generate and view it.
In this portion, I’m going to discuss the DOs & DO NOTs of business software:
- Allow For Both Ad Hoc and Scheduled Report Generation
- Web Based Interface Is A Necessity
- Be Scalable
- Must Be Compatible
The "DO NOTs"
- Never Be Rigid And Non- Adaptable
- Don't Sunset Platforms Too Soon
- Don't Forget About Design
- Never Assume That Every User Is An Expert
4 Business Software “DOs”
Allow for both Ad Hoc and Scheduled Report generation
Some report generator software is great at automating reports, while others are excellent at On Demand report generation. The reality is that most firms want both. The business’ demands can often differ from department to department. For example, the sales team prefers to generate their revenue reports on an ad hoc basis, while the accounts department would rather their credit limit notification reports to be sent automatically to clients. Finding software that can accommodate both needs is the key to satisfying all of your stakeholder's requirements.
Web Based interface is a necessity
With just about every tech firm pushing the “cloud” concept, and every business moving to mobile solutions, more than ever report generator software needs to have a web based interface. A “thick client” can be just as important, however, businesses require systems that they can access anywhere from any machine. This interface should also accommodate ad hoc report generation as well as scheduled reporting.
Businesses of varying sizes have different needs. Moreover, these businesses may adopt a lower end system in the beginning but will eventually find themselves requiring a more robust system within 2-5 years. Report generator software needs to be able to grow as the company it inhabits grows. Features such as disaster recovery and failover may not be required for smaller reporting volumes. However, this changes as the business grows so does the number of reports and the number of users who rely on these reports, making scalability a key decision making factor.
Must Be compatible
Report generation software needs to work with a variety of different environments and systems. Every business is different, using different software and tools to complete their objectives. Report generator software must be compatible with not only latest Windows Server edition, but be compatible with other Windows operating systems such as Windows 7. Business Intelligence reporting software needs to have the forethought to work towards being compatible with soon coming operating systems such as Windows 8. Report generator software should also be prepared to work with ERP systems that generate reports such as Lawson, or BMC software packages. This ensures that no matter the system the business uses or moves towards, their reporting infrastructure will require no alteration. This keeps costs low as well!
4 Report Generation Tool “DON’Ts”
Never be rigid and non-adaptable
Report generation software should be able to conform to its user’s needs. No business is exactly the same, and every firm needs software that can accomplish it’s goals according to their processes or procedures. For example, if a firm has Crystal reports that they frequently change, however the report generator software they use does not refresh the report automatically. This forces the firm to constantly rebuild that instance of the report in their reporting server, costing unnecessary time. Software that is adaptable often maintains a close relationship with its developers and customers, meaning that the tool is often customized for each user.
Don’t sunset platforms too soon
Businesses often house different systems of varying ages. Some machines may be running Windows Server 2012, while other systems are still using Windows XP. Many firms abandon support for certain platforms while there are still many businesses using older technology. Granted for the sake of technological progress, no software can support older systems indefinitely. However many businesses cannot upgrade these systems due to cost or other dependent systems. For example, a manufacturing ERP system that is nearly ten years old still runs on Windows XP. The business requires an updated report generation tool that can work with this older operating system. Upgrading the ERP system is out of the question as it could cost the business far more than the benefit of an automated report generator can provide.
Don’t forget about design
Business to business commerce often forgets about aesthetics in its products. Since firms focus on substance more so than style, they way software looks is often put on the back burner. Report generator software should be intuitive, easy to navigate, and yes, pleasing to look at. Business Intelligence software that looks too dated, or out of place will not motivate users to use it. Firms that procure software that is difficult to use, or doesn’t look like it is robust or powerful can send the wrong message to internal and external stakeholders.
Never assume that every user is an expert
Just because a user Crystal Reports guru doesn't mean they are a wiz at any piece of technology. Accompanying aesthetics, software needs to make sense, and be relatively easy to understand. Report generator software needs require as little training as possible for users to accomplish tasks. Preferably, users should not even need to understand SQL or coding to generate or schedule reports. Documentation needs to be clear and detailed. There should even be videos walking both administrators and users through configuring and using the software. Businesses already spend large amounts of time and money on training and initially deploying software. Money, as well as headaches, are saved when a report generation tool is easy to understand and configure.
Benefits of a 3rd Party Scheduler
SQL Server Reporting Services (SSRS) Reports has a scheduling system that can manage and distribute your report subscriptions. Does it do the job? Do SSRS’ internal features satisfy all of the business’ needs? In some cases, firms look to 3rd party solutions to complete their projects. Some solutions enable a greater flexibility in subscription publication, or offer a wider array of scheduling options, while others help manage SSRS Reports. Most importantly, using a 3rd party solution such as PBRS can provide a much higher ROI for initial, ongoing and future costs.
- Data Driven scheduling in SSRS
- Event Triggered SSRS report delivery
- Additional "quirky" business demands
Data Driven Scheduling
In many scenarios, businesses need a highly flexible scheduling system to augment their existing SSRS reporting environment. For example, a firm must distribute a report of their current inventory and price list to each of its vendors each day. Different vendor systems must accept these price lists in different ways; vendor 1 must have a CSV file uploaded via FTP, while another accepts the price list by email. With tools such as PBRS, SSRS Data Driven subscriptions can be customized so that each unique report subscription goes to a unique destination and format. Multiple SSRS reports can also be distributed in batches.
Event Triggered Delivery
In some cases, SSRS reports must deliver based on events or conditions. 3rd party tools can trigger your reports based on conditions such as changes in a database, the existence or modification of specific files, or even unread emails. This type of scheduling can build a real-time alert system with the ability to interface with databases and systems outside of SQL Server reporting services. Event based scheduling is a standard feature of PBRS.
PDF Encryption, Zipped Reports & More
Lastly, external SQL Server tools can help manage the SSRS Reports system. If there are multiple SSRS reporting environments within the same organization, why not manage them from a single interface? With these tools, multiple reports from various SSRS reporting environments can be exported to multiple SharePoint sites easily. More importantly, what if you wanted to add extra layers of security to your report outputs? PBRS could be used to automatically encrypt PDF, excel, and zipped reports.
How To Get Extra Umph Out Of Your SSRS Reports
Are you using PBRS to schedule SQL reports? If so you are already pretty familiar with PBRS’s powerful schedule functions such as the Event Driven scheduler or the Data Driven Subscription scheduler, as we just discussed. These functions are powerful tools that enable you to dynamically send a report to various users, perform various business process management tasks, and much more. However, there are a number of tools in the PBRS toolbox that can make the scheduling experience even better.
These little-known features don't often get the limelight, however, they make PBRS into a highly robust, dynamic report and task scheduling system. Let’s look at how to get extra umph out of your SSRS Reports.
This is a very simple feature that can come in handy when you schedule SQL reports. Custom calendars enable you to create a specific calendar that a report will run by. For example, an SSRS report needs to run 3 times in one March, 4 times in April, but only once in May. There is no specific interval to this schedule; the dates are determined by management needs. The PBRS administrator can create a Custom Calendar that runs the report on those specific dates. Custom Calendars give users the flexibility to schedule reports even when PBRS doesn't have a way of calculating the interval. Alternatively, a Custom Calendar can be used as an Exception Calendar. For example, an SSRS report is delivered to customer’s every day. However, the customer should not receive the report on national holidays. With Custom Calendars, the administrator can set an exception calendar that will not run the report on the holidays they choose.
Well, this feature is actually pretty well known if you are an Event-Based Schedule or Data-driven Schedule user. Inserts give you the ability to place constant or dynamic values into report parameters, email messaging, or customizing the report’s destination. What is not very well known is all the various applications for inserts. For example, a date can auto-calculated for date parameters. A user can select “CurrentDate” as an insert, and simply drag and drop it into their date parameter field in PBRS. Now the report will run every day with data from that current day. Insert’s usefulness doesn't stop there. Combining Inserts with the Event Driven system and the Database Custom Actions can create an automated database update workflow. Do you have your own formula that needs to be placed in report parameters, database records, or anywhere else? Inserts can do that too. Inserts can even be used to create default email signatures and messaging as well. One could argue that inserts are the life blood of PBRS!
Multiple SMTP Servers
With this feature, you can have PBRS send emails from differing email accounts. For example, the HR Department wants all HR related reports to be delivered from HR@Acme.com, versus the Sales Department wants all reports to be delivered from their Sales@Acme.com account. With Multiple SMTP servers this is accomplished. This feature makes it much easier to serve reports for multiple departments without the emails coming from a cryptic email address. One can also use this feature, to send emails various individual accounts, making the experience a bit more personal for the recipient.
Multi-threading and Collaboration
This is probably the most powerful feature discussed in this article. PBRS has the ability to process up to 8 reports at the same time with Multi-threading. This ensures that reports are being churned out to users as quickly as possible. This also allows you to throttle the load on your SQL database by limiting the number of reports that can be generated at one time by reducing the thread count. Collaboration allows administrators to farm out multiple PBRS servers and assign various reports to different PBRS clients. With report processing being distributed across multiple PBRS installations, there is no need to worry about certain reports slowing down or holding the reporting process. Combining Multi-threading with Collaboration brings out even more power. Each PBRS instance gets 8 threads. In other words, when using collaboration, with 2 PBRS instances you can run 16 reports simultaneously. With 4 PBRS instances, 32 reports can be processed simultaneously! There is no limit to the number of PBRS servers that can collaborate.
This little-known feature allows you to see what reports are running when in a calendar view. It is helpful to have a tool where you can see when certain schedules are set to run, helping you manage scheduling load and understand where possible bottlenecks can occur. Via the outlook’s Gant Chart view, you can see how long it takes for a schedule to complete tasks. PBRS outlook gives you the ability to effectively plan how your reports and tasks are deployed and adjust your plans as necessary.
These features may not present the same “wow” factor that PBRS’s powerful schedule types provide, but they do help make the scheduling process more efficient. From inserts ability to customize every part of your report, to multi-threading scalability, these tools can help solve a number of challenges that may arise. These are the only tools in the toolbox, however. There is much more to explore inside of PBRS!
How to Publish Outside of SSRS
Continuing our series on PBRS and ways “out of system workflows” maximize SSRS’ capabilities, let’s explore exactly how PBRS can publish SQL Server reports. In the past, we have looked at why an administrator would want to schedule SSRS reports outside of SQL Server. Reasons ranged from increased flexibility, more export options, and report output security. In this article, we will run through setting up two of PBRS’s most basic scheduling functions: the Single Report Schedule and the Packaged Report Schedule. Though this portion of the article isn’t meant to be a fully fledged tutorial, it will give administrators feel for how PBRS can be used to publish SQL Server reports.
The Single Schedule is PBRS’s most basic form of SSRS report schedule. It involves calling on the desired SSRS report, setting the schedule for said report, specifying where the report will be delivered, and the format for the report. A single schedule is not unlike the basic report subscription in SSRS. However, there is some distinct difference we will discuss throughout this article. To setup, a scheduled SSRS report, open PBRS. Select “Single schedule” from the Home Ribbon near the top.
The Single Report Schedule wizard will appear. One of the great things about PBRS is that reports can be scheduled from various instances of SSRS. In the General section of the wizard, place the report service URL where the desired report is contained. The report service URL can be changed on a report by report basis. PBRS by default automatically pulls in the report’s name and description, however, this information can be change at any time. Clicking the triple dots, browse the Report Service URL for the report you wish to schedule.
Next up, it's time to set the schedule’s well, schedule. There is a ton of flexibility in determining the SSRS report’s timing. From daily, daily weekly or monthly reports, administrators can set the report to run whenever they wish. Custom calendars can be created as well.
SSRS report parameters are easily handled in PBRS. Simply enter the desired value for the report parameter, or select it from the drop-down list. PBRS will automatically run the report for the given parameter each scheduled time. In the case of performing multiple passes against a certain parameter, it would be best to look at using a Data driven schedule. Date parameters can be automated too! Using the floating inserts window, date values can be auto-calculated when the report runs. For example, if a report always needed to run for the current year, you can use the “current year” insert.
Next up, let's discuss the destination and format. There are a number of destinations SSRS reports can be sent to such as email, file share, and SharePoint. However, PBRS adds previously unavailable destinations such as Fax, SFTP, ODBC (sending the report to a database), Printer, Dropbox and even SMS. Also, PBRS adds an impressive array of report formats. Not only the PDF, Excel, and CSV are available, but reports can be outputted in other formats such as HTML. SSRS report naming can be customized as well. The file output can be renamed with a department code, customer name and more. A time or date stamp can be appended to the file name to help keep track of when the report is generated. As an added bonus to file output customization, administrators can add multiple layers of encryption to PDF and Excel files.
Finally, the last step is Custom Tasks. This tool enables you to create workflows before or after the report has generated. Tasks range from simple tasks such as opening a program or document, to powerful system tasks that grant users the ability to update SQL databases, create tables, run scripts and even trigger SSIS packages! For example, the SSRS report can be sent to SharePoint, then custom tasks can be set to send an email notifying the recipient that the report is ready. We will go a bit deeper into custom tasks in another article, but suffice it to say that custom tasks can certainly take your report scheduling to the next level!
Clicking finish, your SSRS report schedule has been completed. PBRS will run your report and deliver your report to its destination every day without fail! The package schedule works in a similar fashion to the Single Schedule, however, it allows you to batch multiple SSRS reports for distribution! As opposed to a user receiving multiple emails and files filled with various reports, the Package schedule sends all the desired reports in a single email, and can even combine multiple reports into a single PDF, Excel, or Text file. The Single Schedule and Package Schedule are PBRS’s more basic functions that enable you to publish SQL Server Reports outside of SSRS.