MS SQL Reporting Services running slow

For a few weeks, my Business Intelligence Architect has been complaining that the SQL Reporting server has been running slowly.

At first, we thought that the services seem to “go to sleep” and need awakening once a day. However, it seems to run slow more than once a day. After reading this article:

it seems that the idle processes “goes to sleep” after 20 minutes. After the service has gone to sleep, the time consuming handshake has to take place again next time the report it run. This can take up to 60 seconds and gives the impression to the user that the system is slow.

It is possible to increase this “idle time out” limit. We have increased it to 120 minutes (someone should run the report at least once every 2 hours, keeping the service alive). We didn’t want to increase it further than this as we are not sure what effect this may have on the server.

Here is how we made the change on IIS 6:



Installing and configuring Sage ERP

·         Navigate to <Software path of Sage>\WSSetup

·         Run the Setup.exe

·         Set the Programs path to: c:\Sage

·         Set the Data path as: c:\Sage

·         Run ‘sqlncli.msi’ From SQL 2008 R2 install folder

·         Go to Control Panel\User Accounts\User Accounts

·         Set the UAC settings to never notify

·         Go to:

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Administrative Tools

·         Run ‘Data Sources (ODBC)’

·         Go to System DSN tab

·         Click Add

·         Select SQL Server Native Client and click next

·         Set the name as ACCPAC and the server as <SQLSERVERNAME\INSTANCENAME – (you can leave the description blank)

·         Click next

·         The username should be set as ‘sagerpadmin’ (Or whatever you set it to in the DB)

·         Click Next then Finish

·         Copy the lic and Site folders from here: \\dubbur-fp02\Public Data\UAE\IT\SageFiles to c:\Sage

Open Database setup & configure database setup based on above

When printing (For example; a PO) for the first time, it will aim at the local C:\ drive so when printing:

·         Navigate to \dubbur-sage01\accpac\Program Files (x86)\Sage\Sage Accpac\PO60A\ENG and select your .rpt file name. (purchase order-other than KPM)

Create a Sales Break down reports from Sage CRM

First go to “Reports” -> “Sales Reports”

Then got to “Territory Sales Report”

The Select your Territory and “Include Sub Territory”. Select “Won”  under “Opportunity Status”.  Select “Screen” for the Display options and press “Go”:-

Currently you can not export to Excel. I am told that this will be fixed in the next Sage CRM version, due at the end of April 2012.

Once you report displays on screen copy and paste this into Excel and add your formulas.