2. Home
  3. Articles
  4. Microsoft

SQL Server Reporting Services
Hits: 14008
Why?
I've recently written a report for SQL Server Reporting Services 2008 R2 (SSRS) which will compare up to 4 reports and will compare the time taken for each one. The breakdown or what I was able to measure with the default installation are the times taken for "data retrieval", "processing", "rendering", and then the totals of these.

I haven't Googled this at the time of print so there may be a million better solutions out there, this is just how I did it. This may look like a horrible report which would fail an accessibility test but visually it says straight away which is the better report; and when comparing to the previous runs (using a second dataset) you can tell where changes were made and how this affected the reports' performance.

What?
What I'm trying to do is display a set of results (comparing various reports) in a table and then to color the backgrounds based on whether they are the fastest or slowest in the set.

Something like:
Displaying a color-based resultset

SQL Server Reporting Services
Hits: 35100
How to Display Report Execution Time in SQL Server Reporting Services 2008 R2
So there are other articles out there but I was looking to display in milliseconds the execution time it took for a particular report (which searches for results matching the submitted parameter) to run.

Lifted from Dattatray Sindol's blog
Other sites have this solution as well so who copied off who is not my concern as this is not the solution to our problem. This is the solution that I initially used but my end-users were asking why is it always 0 seconds. This was because we were using the following MDX statement which had seconds as its smallest denominator:
copyraw 
="Execution Time: " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + " hour(s)" + " , " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + " minute(s)" + ", " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)"

// yields
// 0 hour(s), 1 minute(s), 2 second(s)
  1.  ="Execution Time: " + 
  2.  CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + " hour(s)" + " , " + 
  3.  CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + " minute(s)" + ", " + 
  4.  CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)" 
  5.   
  6.  // yields 
  7.  // 0 hour(s), 1 minute(s), 2 second(s) 
This isn't what I want... It wasn't accurate enough and always saying 0 seconds was just confusing the end-user...
Category: SQL Server Reporting Services :: Article: 401

SQL Server Reporting Services
Hits: 201417
Quick Reminder
I didn't want to do this at the database level, mainly because it meant modifying the SQL query. The zero padding would need to be applicable within an MDX query.

The Situation
We have a database using Oracle 10g, and a SQL Server Reporting Services v2008 R2 environment. My use for this was when displaying an audit log displaying the oracle errors.

Oracle Errors
An Oracle error usually returns in the format of -12345. If we want to look them up the error is ORA-12345. Unfortunately Oracle also returns errors of less than 10000 so ORA-00201 would actually be returned as "-201". As I wanted a link so that the user can just click on this link and it would take them to http://ora-00201.ora-code.com/.

SQL Server Reporting Services
Hits: 10893
Intro
If you ever want to check the parameters submitted with a report for alpha numeric characters (so it doesn't contain symbols, punctuations, etc) then you should do this at the database level, and then get the report to complete the check:

The Plan
  1. User enters value in parameters and clicks on "View Report"
  2. Report passes parameter to dataset which gets formatted by the database
  3. Report retrieves (select) formatted parameter as a field value to use
  4. Report loads with changes based on returned value.

The Gist
  1. Add database level parameter check
  2. Add IIF in SSRS to confirm

SQL Server Reporting Services
Hits: 11111
What?
We have a report in SQL Server Reporting Services 2008 R2 (SSRS) reading from an Oracle 10g database which works great and lists all the details on a specific student. An additional request is that there appears a link that will run a stored procedure which
  1. Updates a timestamp in an existing table
  2. Inserts a row into an audit table
I need the report to run the stored procedure, then based on the errorlevel, return a message.

How?

SQL Server Reporting Services
Hits: 9445
My Setup
  • XP SP3 Workstation
  • Business Intelligence Development Studio 2008 (BIDS)
  • SQL Server Reporting Services 2008 R2 (SSRS)
What do I want?
I have a report displaying room bookings. Each row lists the day, date, room name, start/finish times, booking details and the staff contact. I want an empty row to appear between each day in the list, so I have:

SQL Server Reporting Services
Hits: 13607
The Problem
I've set some tablix headers but when I request the same report in PDF format, the tablix headers only appear once on the first page. Every subsequent page simply displays the report header (which does not include the tablix header... obviously). I right-clicked on the header row of the tablix and checked the box "Repeat header columns on each page.
This is not working!

Excel
Hits: 10980
The Situation
I have a Microsoft Excel 2007 file that connects to a SQL Server 2008 R2 database. The Excel file pulls data using lookup tables and displays the data in an Excel Spreadsheet.

The Problem
We can select all cells and set row height to be 30 for example, but everytime we refresh the data in the Excel spreadsheet, all the rows get re-adjusted to fit the data and lose that consistency.

A Workaround: New line inserted before and after
So this is where I am at the moment without VBCode and other suggestions. Instead I add a newline in front of and after the smallest data value (one that I know will never be two lines (or two words)) within the SQL query itself. We have a DEPT column that is an acronym of the departments so for example:

SQL Server Reporting Services
Hits: 53165
The Situation
We want some photos taken from another website of ours to display in our report. These are not stored locally on the Reporting Server because other services use these photos on our Intranet and we only want one location to upload the photos. These images are provided via a website address (http) or network share (file).

Before you ask, we have several MS Sharepoint sites but we didn't go down the route of uploading 10000+ images into a Sharepoint database though we are still considering it; so this article is more about just getting external images to display when they are provided over an authenticated URL (You shouldn't be having any issues with this if the image is available to anonymous users - eg. Google Logo).

Our Setup
  1. Windows XP Workstation
  2. Business Intelligence Development Studio 2008 (BIDS)
  3. SQL Server 2008 R2 Reporting Server (SSRS) running on Windows Server 2003
  4. Team Foundation Server 2010 (TFS)

Problem #1: Image does not appear within SSRS
When previewing the report in BIDS, the photo appears fine. If we deploy the report to our Reporting Server and attempt to view it using a Web-Browser, the image does not display:
  1. Get report to see images hosted on an external URL
  2. Check if image exists to display alternative placeholding image.

Excel
Hits: 17629
So I googled this for a while and there are a lot of solutions out there, none of which applied to what we meant and lots of people in the same boat.

The Situation
We have an Excel report which summarizes for our guys at the top, all the activities and time spent by staff. There are several filters available on the report (only a few to keep it simple silly). When you click on the filter, a dropdown appears with all available values listed.

The Problem
The values are listed in alphabetical order at first. If any new values come along then they get added to the bottom of the list... This is the problem. For example, if the year dropdown has a list of 2010, 2011, 2013; then if you add an entry which has year 2012, then the dropdown list will be in the following order: 2010, 2011, 2013, 2012.

The Solution

SQL Server Reporting Services
Hits: 16699
I'm storing a note here because it took a while to figure out and googling other solutions did not answer our questions but did lead us to a workaround.

So this is regarding an error when trying to add parameters to a Stored Procedure of an Oracle database from within Microsoft SQL Server Reporting Services.

The Error
copyraw 
ORA-00911: invalid character 
ORA-06512: at "SYS.DBMS_UTILITY", line 114 
ORA-06512: at line 1 (System.Data.OracleClient)
  1.  ORA-00911: invalid character 
  2.  ORA-06512: at "SYS.DBMS_UTILITY", line 114 
  3.  ORA-06512: at line 1 (System.Data.OracleClient) 

The Situation
I'm using Business Intelligence Development Studio 2008 to develop a Reporting solution on a Windows XP workstation. We are connecting to a SQL Server 2008 R2 Reporting Service (SSRS) with Team Foundation Server 2010. The database with our stored procedure is Oracle 10g.

The Stored Procedure
Rather than a returned dataset, our Stored Procedure updates a field in one of our Oracle tables and returns nothing. It accepts 3 parameters: The StudentID (reference) varchar2, Username (who's running the report) varchar2 and a JobID (request reference) number.
Category: SQL Server Reporting Services :: Article: 380

SQL Server Reporting Services
Hits: 10904
What?
This article serves to describe several workarounds or proofs of concept.

Why?
We have a first report which acts like a search page. You enter the student you are looking for by reference or name and if there is only 1 result in the results page, then we want it to redirect to the details report for that student automatically.

How?
So there are various solutions out there, here's an overview of some:

Method #1
  1. Create one report with all selectable reports as subreports - toggle visibilty based on parameter.

Method #2
  1. Add a button below "View Report" linked to some script code (requires change to ASP pages on the ReportingServer, ie. affects all reports on that server).

Method #3
  1. Use ASP instead of SSRS.

Method #4
  1. Use a TimeInterval refreshing the page after a certain time.

Conclusion
In the end, this came down to a design decision. Time spent on trying to find a solution was not considered to be warranted. My reports use a parameter for the connection string to specify which server and database to connect to and the follow on link was enough as a solution. My seniors advised that if we had time in the future we could expand on this further.


Google Searches that got me nowhere:
  • ssrs process parameter after report execution
  • auto-redirect after are a report is run
  • ssrs vbscript post report processing
  • ssrs auto redirect based on a dataset value

Excel

Windows OS

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

RSS Feed

feed-image

Related Articles

Joes Revolver Map

Joes Word Cloud

using   source   client   form   joomla   report   field   license   create   added   server   case   following   date   order   work   value   code   mysql   file   note   name   uploaded   deluge   website   system   page   would   table   script   list   need   user   function   find   first   google   error   display   creator   parameter   database   used   where   files   time   zoho   windows   version   data   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.