A common question I've been asked a lot lately is how to replace the icon in the Report Manager to my company's logo. 

It is actually fairly simple to do by following these steps: 

1.  On the server reporting services is installed on open the folder C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager\images

2.  Rename the file 48folderopen.jpg to anything else (Ex. 48folderopenOLD.jpg)

3.  Resize your logo to be 48 x 48 pixels jpg file
4.  Rename it 48folderopen.jpg
5.  Copy your logo to the folder C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager\images

Open the Report Manager again to see the change.

Read my blog at my regular blog site http://www.bidevelopernetwork.com/blogs/DevinKnight

Data Warehouse latency is often a complaint I have heard from end users when trying to access data via either Reporting Services reports or Excel.  Generally, I promise 24 hour latency unless the job mandates updates hourly or even sooner.  Screenshots for this blog are available at my regular blog site

With these complaints in mind I decided to create a report that could kick off the SQL Agent job that processed my Data Warehouse load and Cube update. It is a pretty simple report to create. Here are the steps I did:

Step One

Create a Data Source that points to MSDB on the server that the SQL Agent job that you want to run is located.

Step Two

Create a Dataset that runs the system stored procedure sp_start_job with the name of the job.

Step Three

Add some text! Let the user know what’s going on after they click on the report otherwise it will just show a blank report. Drag a textbox over and add the appropriate text.

Deploy the report and test!

There are some circumstances where you would not want to use this method:

· Running the job in the middle of the day could severely cripple a transactional system that the Data Warehouse load pulls from.

· The job takes longer than just a few minutes to process. Remember you are trying to improve latency. You don’t want to expose a poorly performing load process (even if the performance time is due to the size of the load not bad code)

· You haven’t trained your end users in what the report does. You don’t want end users clicking this report over and over again because it is running a major process. 

Reports that use an Analysis Services cube as a data source can often have performance problems during pre-execution.  Before you even see the classic Report is being generated sign the report is slow to start.

The most common reason I’ve found for this performance lag has to do with parameters that are filtering the main dataset.  For example, you have a report that uses a sales cube and you want the user to be able to select the date range that the report will return results for.  This would mean you would add a filter to the dataset using a date dimension and check the box to make it a parameter.

The reason this can be a performance problem is because you’ve now added a parameter that includes every date that exist in your date dimension.  So before the report can even begin to generate it has to populate all the records in the parameter first.  Depending on how many dates you’re storing in the dimension this could be a major issue.  Not to mention if you are using cascading parameters that depend on the previous parameters results. 

It’s likely the end users of the report aren’t interested in seeing 70 years worth of data.  It’s much more likely that the user would want a rolling years worth of dates to choose from.  So here’s the solution:

Step One

Show Hidden Datasets by right clicking on the report Data Source and clicking Show Hidden Datasets.  When you check to include parameters on a dataset it creates hidden datasets behind the scenes. 

Step Two

Open the properties to the newly shown dataset that is used for the date parameter and select Query Designer.  Add the following MDX where clause to the query.  This query may vary greatly depending on the design of your cube.  This will just give you a starting point.

Now when I view this report I will only have the past years worth of dates available in the date parameter.  Lag(366) is bringing back all dates more than a year old to Lag(0), which is the current date.  Again this could be very different for you depending on how the date dimension is setup in your cube.