Troubleshooting the SharePoint Add-in for Reporting Services

/0 Comments/in /by

A common issue when setting up Reporting Services integration with SharePoint is that the SharePoint add-in is not installed properly although the setup program doesn’t indicate any issues. More than likely, the culprit is that the person who installed the add-in is not SharePoint Web farm administrator and Site Collection administrator.

As a first stop for troubleshooting SharePoint integration issues, you should examine the add-in log after the add-in setup completes to verify if it was installed successfully. You’ll find the log file (RS_SP_<N>.log) in your temp folder, such as \Documents and Settings\<login>\Local Settings\Temp. Specifically, check that all activation tasks have completed successfully and that there are no errors. If all is well, the log files should look as follows:

User: <userid>

Installing Report Server feature.

Calling copyappbincontents command.

copyappbincontents command completed successfully.

Adding Report Server feature to farm.

Installed Report Server feature.

Activating Report Server feature to root level site collections.

Activating feature in web app ‘SharePoint – 80’

Activating feature to root site collection: http://<server>

Activated feature for root site collection: http://<server>

Activating Report Server feature in all admin site collections.

Activating feature in web app ”

Activating feature to root site collection: http://<server>:35000

Feature already activated in site collection http://<server>:35000

Cumulative Update 2 for SQL Server 2008

/0 Comments/in /by

Microsoft has released the Cumulative Update 2 build (build number 1779) for SQL Server 2008. The update is not publicly available as of now. It has to be released individually based on whether you are running into a bug fixed in the build and it is password-protected. Among other things, it fixes the freeze issue associated with opening SSAS projects in BIDS.

Referencing Parameters in SSRS Calculated Members

/0 Comments/in /by

Issue: You author an SSRS report that connects to a cube. You want to create a calculated member that uses a report parameter. For example, the Selector calculated member in the following query returns 1 if a given employee has exceeded 1 million in sales for the dates specified by the user in the report parameter.

WITH MEMBER [Measures].[Selector] AS SUM (

StrToSet(@DateCalendar), iif([Measures].[Reseller Sales Amount]>1000000, 1, NULL)

)

select non empty {[Measures].[Reseller Sales Amount],[Measures].[Reseller Order Quantity], selector} on 0,

non empty (Filter([Employee].[Employee].[Employee].Members, [Measures].[Selector]>0) * [Date].[Calendar].[Calendar Year]) on 1

from (select StrToSet(@DateCalendar) on 0 from [Adventure Works])

 

However, when you run the query you get the following error if you target Analysis Services 2005:

The DateCalendar parameter cannot be resolved because it was referenced in an inner subexpression.

Obviously, the built-in SSAS data provider has an issue parsing parameters in a calculated member. This issue is apparently fixed in SQL Server 2008 where the above query executes just fine.

Workaround: As a workaround with SQL Server 2005, try defining a set that references the parameter and use the set in the calculated members, as follows:

WITH SET [SchoolYears] AS STRTOSET(@DateCalendar)

MEMBER [Measures].[Selector] AS SUM (

[SchoolYears], iif([Measures].[Reseller Sales Amount]>1000000, 1, NULL)

)

select non empty {[Measures].[Reseller Sales Amount],[Measures].[Reseller Order Quantity], selector} on 0,

non empty (Filter([Employee].[Employee].[Employee].Members, [Measures].[Selector]>0) * [Date].[Calendar].[Calendar Year]) on 1

from (select StrToSet(@DateCalendar) on 0 from [Adventure Works])