SSRS Report export to PDF removes spaces from text

I saw this issue when working with an old colleague Hammad Arif earlier this year and he came up with a solution written up here that worked nicely at the time, however I’ve come across this again and this time it proved more of a challenge.

Problem

When exporting to PDF Reporting Services reports containing multi-line text from a SQL datasource the first line of text loses all spaces between words, see screenshot taken from a PDF file:

ssrspdfexport

 

Nope that’s not one particularly long German conjugation, that is the problem I’m talking about! It only seems to affect the first line of each multi-line based custom field value in the SSRS dataset and additionally when looking at the data in SQL or in the HTML source there is nothing different visible! However oddly on the affected projects a single odd character (&#8203;) does exist before the html <span>.

 

Resolution

After much head scratching it took a call to Microsoft to identify that hidden away in the database tables are some characters, specifically HEX ‘0B 20’ which turns out to be the Zero Width Space or in HTML (&#8203;), the same character you can see in the HTML. Now this makes sense as despite these being invisible in the SQL tables using a SELECT statement, they still exist and cause these symptoms in the PDF but not when viewed on the Web or exported to Word or other formats.

Fortunately the resolution for the report is nice and simple;

SELECT REPLACE(Problemstellung, NCHAR(8203), '') AS Problemstellung

Simply wrap each multi-line text field affected with the SQL REPLACE command removing the character completely!

 

To answer the question of where they came from, that is one I leave to the Microsoft escalation engineers. :)

HTH,

Project Server DR Failover SQL Cube Issue

Fortunately I do not often have to enact my customer’s disaster recovery plans, but when I do I am glad for things like SQL mirroring and SharePoint database fail-over.

No plan is perfect though, in this particular case Project Server OLAP cubes would refuse to build while failed over to the mirror server, giving only the following errors;

[8/01/2013 8:35 AM] Failed to build the OLAP cubes. Error: Error Adding DataSourceView ‘Project Reporting data source’ to database ‘FullCube1_Srv2’. Error: Error Adding Table ‘MSP_TimeByDay_OlapView’ to database. Error: Login timeout expired

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

 

This problem is clearly caused by the SQL connection string, which in the configured cube looks like this:

Provider=SQLNCLI10;Data Source=primarysqlserver;Initial Catalog=ProjectServer_Reporting;Integrated Security=SSPI;Persist Security Info=False

 

As you can see the ‘Failover Partner’ parameter is missing, at first I thought that this was a classic case of not RTFM, if you neglect the steps detailed in the TechNet article (http://technet.microsoft.com/en-us/library/ff872145.aspx) on configuring SQL server mirroring for Project Server but instead only do the SharePoint configurations (which does get things online) then some Project Server specific connection strings are not updated.

The fix would seem to be pretty simple:

Set-SPProjectWebInstance …[insert all required parameters – ie all of them]… -PrimaryDBMirrorServer mirrorsqlserver -ReportingDBMirrorServer mirrorsqlserver

(Reference: http://technet.microsoft.com/en-us/library/ff607532(v=office.14).aspx)

 

HOWEVER, this does not appear to fix the cubes, which will continue to fail with the above error on build! The above steps do update the Sample report data sources in the BI site, but even if you create a new cube it still builds without a ‘Failover Partner’ configured:

clip_image001[6]

To get to the above open SQL Management Studio and connect to your Analysis Server, then from the Cube –> Data Sources properties you can edit the data source used.

The good thing is that there is a temporary workaround while in a failed-over scenario, just manually update that connection string with the correct failover partner. The problem with this is that each time PWA builds the cube this change will be overwritten, but you could automate that if need be (or disable PWA build and schedule the build in SSAS directly).

 

Hurrah! Now I can get back to my day job.

Update: Reporting from SharePoint lists with SQL2008R2

A long time ago (it seems) I blogged about reporting directly from the SharePoint workspace sites using SQL Reporting Services to get around the limitations with the reporting database refresh. Using XML provided a neat method for pulling data directly from all the out-of-the-box lists such as Risks and Issues, as well as other custom lists.

Unfortunately setup and maintenance was difficult requiring a little knowledge of web services as well as some XML syntax, but fortunately now with SQL Server 2008 R2 things have improved drastically!

What’s New: Now you can directly create ‘Microsoft SharePoint List’ data sources!

See the screenshot:

clip_image001

Better yet when you create a Data Set using the above data source the Query Designer is completely new:

clip_image002

Finally no more messing around with column names using XML and having to have specific views configured in your SharePoint sites.

That should make things much easier!

 

BTW. Lastly it’s definitely worth mentioning that the above also works with Report Builder 3.0!

Analysis Services 2005 and Project Server 2010 errors

Came across this issue when building my cube using SQL AS 2005 with 2010 for the first time;

[7/23/2010 9:28 AM] Failed to build the OLAP cubes. Error: Failed to process the Analysis Services database PS2010TestCube1 on the PRJDBCLUSTER server. Error: OLE DB error: OLE DB or ODBC error: Class not registered.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘Project Reporting data source’, Name of ‘Project Reporting data source’.

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘PS2010TestCube1’, Name of ‘Assignment Owner’ was being processed.

Server: The operation has been cancelled.

Fortunately the solution was not too hard to come by; it seems that you need to install the SQL 2008 Native client on the SQL 2005 server in order to fix this one.

 

Hope that helps someone!

Database names in SharePoint 2010

With SharePoint 2010 adding a good number of new databases to the default configuration one thing that is sorely missing is the ability to easily name those databases.

Is it just me who has to conform to strict database naming conventions in most installations of Project Server?

I’ve just been working on what I think is a pretty typical Project Server 2010 installation and found that in-order to simply prefix my databases names with something rather than let them be automatically named with a GUID I faced the following:

  • Central Admin and the farm configuration is the same as 2007, the PSCONFIG command line will let you name both of those databases.
  • Session State Service Application – the first hurdle, this one requires a PowerShell command to give the database a name, e.g.:

$serviceApp = New-SPStateServiceApplication -Name "State Service"

New-SPStateServiceDatabase -Name "MyPrefix_State_Service" -ServiceApplication $serviceApp

New-SPStateServiceApplicationProxy -Name "State Service" -ServiceApplication $serviceApp –DefaultProxyGroup

  • Secure Store Application – Central Admin fortunately will let you name the database through the usual creation process.
  • Search Service Application – Next hurdle and this one much bigger, three databases are required now for the Search service; Administration, Crawl and Property databases are created, this blog post has a nice PowerShell script to create the service with named databases but with 50 odd lines for the script I’m not going to repeat it here. Fortunately if you’re happy with not naming just one of those three databases, it is actually possible to create new Crawl and Property databases with specified names after creating the three using Central Admin.
  • PerformancePoint Service – Well for this one I have not found a way to name the database?! I need to revisit this as there must be a way, but looking at the PowerShell command line options and searching TechNet I couldn’t find one!
  • User Profile Service, Excel Services and WSS Usage are all well behaved and through Central Admin you can name the databases.

So it’s about time to get that installation PowerShell script done, as unless your happy with the default wizard configuration then this setup is getting quite long “just” to get Project Server 2010 installed.

SQL Reporting Services 2008 R2 Required for SharePoint 2010 Add-in

Found this one out the hard way this week; it seems that currently the only supported version of SSRS for the SharePoint 2010 SRS Add-in is SQL 2008 R2. At least one forum poster from MSFT has confirmed this.

Annoying as it can be surprisingly hard enough to convince customers to install Windows 2008 let alone upgrade their SQL servers! Ah, well at least SRS can be separated from the SQL DB servers making it a bit easier.

Update 12/07: This has now been addressed by Microsoft in the SQL 2008 SP1 CU8 release which adds support to reporting services 2008 to work with the R2 add-in.