jetlounge.net - technews

Reporting Services Execution Log Reports

david — Sun, 22 Nov 2009 12:35:00 GMT

Need to scrub those RS 2008 Execution logs to determine who, what and when those reports ran? A platform for deeper optimization analysis?

There's a codeplex project with the DB Schema, SSIS Package and a set of reports:

Community Edition : http://scrubs.codeplex.com

Premium Edition (offers 15+ more optimization reports and traffic path analysis):

http://www.summitcloud.com/solutions/scrubs

SharePoint and SQL Server 2008 Administration - Free eBook!

david — Tue, 10 Nov 2009 01:25:00 GMT

Microsoft has released a free ebook for administration and performance management of SharePoint on SQL Server 2008.

http://bit.ly/2RpALX

Forms Based Authentication on SharePoint 2010 and WSS 3.0

david — Tue, 03 Nov 2009 13:44:00 GMT

Two great series of articles on FBA and SharePoint:

FBA & SharePoint 2010

FBA & SharePoint Services 3.0

PDF is blank in IE 8 (Internet Explorer)

david — Mon, 26 Oct 2009 03:11:00 GMT

An Adobe Acrobat PDF may display as a blank page in IE 8. Additionally, you may see an error popup that is also blank. This appears to be due to a compatibility issue with the plug in for IE. To fix, force the PDF to open in Adobe Acrobat Reader instead of IE.

1. Open Adobe Acrobat
2. Click Edit, Preferences.
3. Click Internet option in the right pane.
4. Uncheck “Display PDF in browser” check box.Click OK to Save.

FIX: IE ESC Won't Turn Off (Internet Explorer Enhanced Security)

david — Mon, 26 Oct 2009 03:03:00 GMT

In Terminal Services, if you cannot get IE ESC to disable, even when you have turned the setting off in Server Manager - try the following:

Option 1: Modify Internet Explorer settings for administrator accounts

loadTOCNode(2, 'resolution'); For administrator accounts, you can run the following command to turn off Internet Explorer Enhanced Security Configuration:

rundll32.exe setupapi.dll,InstallHinfSection IESoftenAdmin 128 %windir%\inf\IEHARDEN.INF

Note You must run this command by using an account that has administrative credentials. Additionally, for the changes to take effect, you must restart the computer after you run this command.

Option 2: Modify User Settings

To turn off Internet Explorer Enhanced Security Configuration for a few user accounts, you can remove the IEHarden registry entry from each standard user account profile. To do this, follow these steps:

Log on to the terminal server by using the credentials of the standard user account.
Click Start, click Search, and then search for the Regedit.exe file.
Right-click regedit.exe, and then click Run as.
Click The following user, type an account name that has administrative credentials, and then click OK.
Locate and then click the following registry subkey:
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zonemap
In the details pane, right-click IEHarden, click Modify, type 0 (zero) in the Value data box, and then click OK.

Note You can also remove this registry entry.
Locate and then click the following registry subkey:
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings
In the details pane, right-click IEHardenIENoWarn, click Modify, type 0 (zero) in the Value data box, and then click OK.

Note You can also remove this registry entry.
Exit Registry Editor, and then start Internet Explorer.
On the Tools menu, click Internet Options.
Click the Advanced tab, click Restore Defaults, and then click OK.
Close and restart the browser

SharePoint FBA Setup on IIS 7

david — Thu, 22 Oct 2009 20:00:00 GMT

Forms based authentication setup on SharePoint 3.0 is so much easier on IIS 7, and a ton easier with this series of tutorials.

Yeah, No more web.config hacks for Forms Based Authentication setup!

Blog post here.

String Building and Advanced Expressions in SharePoint Workflows? YES!

david — Fri, 09 Oct 2009 04:49:00 GMT

YES! It is possible:

http://blog.summitcloud.com/2009/10/advanced-string-functions-in-sharepoint-workflows-yes-we-can/

FTP Task Blocked When Run in SQL Server Agent

david — Thu, 24 Sep 2009 23:52:00 GMT

Got the connectionstring and credentials down pat, and no error message when executing a SQL Server Agent Job that included an FTP task. It just sat there, and sat there.

Looked and smelled like a firewall issue. And it was. Didn't occur in Visual Studio, just when running in automated mode.

To fix (on Windows Server 2008), follw instructions for:

Using Windows Firewall with non-secure FTP traffic

Open a command prompt: click Start, then All Programs, then Accessories, then Command Prompt.

To open port 21 on the firewall, type the following syntax then hit enter:
netsh advfirewall firewall add rule name="FTP (non-SSL)" action=allow protocol=TCP dir=in localport=21
To enable stateful FTP filtering that will dynamically open ports for data connections, type the following syntax then hit enter:
netsh advfirewall set global StatefulFtp enable

FIX: Report Builder 2.0 ClickOnce Deployment Fails From SharePoint

david — Wed, 09 Sep 2009 21:10:00 GMT

SQL Reporting Services Report Builder 2.0 ClickOnce broke in the CU2 of Service Pack 1.

So, if you are on Service Pack 1, get Cumulative Update 3. Be sure to install all of the files if you are running Reporting Services in SharePoint integrated mode.

The Best Point of Sales Reporting Metrics Platform

david — Sun, 06 Sep 2009 21:45:00 GMT

Great point solution for analytics that combines EDI 852, EDIFACT, Partners Online, RetailLink, retail spreadsheets and popular ERPs (to name a few) into easily digested actionable metrics to manage reorder velocity, inventory turn and manage best and worst sellers around the globe.

CloudMetrix by SummitCloud.

Gantt Charting With SQL Reporting Services 2008

david — Thu, 20 Aug 2009 23:50:00 GMT

Update: So SQL Server 2008 is here, and still no Gantt Chart. So this free native workaround will still get you through on SQL Server Reporting Services 2008.

I've got an article over at SQL Server Central that describes how to get Gantt charts in SSRS using native controls, not third party. And it has a holiday spin.

This is the abstact:

Gantt charts in SSRS aren’t included in the 2005 release. You can purchase a third-party add-on, or, if you can wait, these powerful project tools might make it in the 2008 release. Or Alternatively, you can do this now with the native RS Chart control by using the methods David Leibowitz provides with the native RS Chart control.

This is the URL: http://www.sqlservercentral.com/articles/Reporting+Services/Santa+gets+an+RDL+present/61782/

The RDL example file is found in the Attachment link below.

FIX: Me.Value Does Not Work in Reporting Services 2008 (SSRS)

david — Sat, 11 Jul 2009 04:49:00 GMT

Me.Value will not work in SQL Server Reporting Services 2008 (SSRS 2008) if you have some complex code behind. This is regardless whether you use that code in your expression.

For example, your Tooltip might simply be =Me.Value .

Not only will that not work, but the combination might leave you without a visbile value in the cell. What??

I use quite a bit of code behind the form, and references to assemblies. This did not hinder the usage of the Me.Value property in RS 2005, but it now breaks in 2008. If you need your code behind, the only way around this is to explicity reference the report item.

Example:
To dynamically change the color property, of a cell in a Tablix refrenced as Textbox2...

instead of =IIF(Me.Value<0, "Red", "Black")
try

=IIF(ReportItems!Textbox2.Value<0,"Red","Black")

Reporting Services 2008 URL Parameters for SharePoint Integrated Mode

david — Thu, 09 Jul 2009 15:30:00 GMT

Passing URL report parameters directly to a report in a SharePoint Document Library is enabled in the SQL Server Reporting Services 2008 Integration with SharePoint Add-in.

When a report is rendered in full-page view in SharePoint, the URL is displayed in the following format: http://<SharePoint_site>/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/<SharePoint_Document_Library>/<Report_Name>.rdl.
In SSRS 2008, you specify a report parameter name and value pair by explicitly specifying the prefix "rp:". Here is an example to add-on a parameter with name "Month" that accepts a string value: http://<SharePoint_site>/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/<SharePoint_Document_Library>/<Report_Name>.rdl&rp:Month=January
You can also specify a fixed set of ReportViewer parameters to alter full-page rendering experience. These ReportViewer parameters need to be explicitly prefixed by "rv:". Here is an example of rendering the report with the parameters area collapsed: http://<SharePoint_site>/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/<SharePoint_Document_Library>/<Report_Name>.rdl&rv:ParamMode=Collapsed&rp:Month=January

Examples: Here are examples using the Adventureworks sample reports assumed to be stored in SharePoint document library: http://<SharePoint_site>/Reports/ReportsLibrary· Render the AdventureWorks "Employee Sales Summary" report for 'Syed Abbas' for 'January' / '2004' with ReportViewer Toolbar showing pagination only: http://<SharePoint_site>/Reports/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/Reports/ReportsLibrary/Employee%20Sales%20Summary.rdl&rv:ToolBar=Navigation&rp:EmpID=288&rp:ReportMonth=1&rp:ReportYear=2004· Render the AdventureWorks 'Product Catalog' report with the Document Map Area collapsed and no Header Area: http://<SharePoint_site>/Reports/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/Reports/ReportsLibrary/Product%20Catalog.rdl&rv:HeaderArea=None&rv:DocMapMode=Collapsed

ReportViewer parameters for controlling full-page report rendering

ReportViewer Parameter	Description	Values
rv:HeaderArea	Control how the ReportViewer web part’s header is displayed in full-page view.	Full, None. Default: Full
rv:ToolBar	Control how the ReportViewer web part’s toolbar is displayed in full-page view.	Full, Navigation, None. Default: Full
rv:ParamMode	Control how the ReportViewer web part’s header is displayed in full-page view.	Full, Collapsed, Hidden.Default: Full
rv:AsyncRender	Control whether the report is rendered asynchronously or synchronously.	true, false. Default: true
rv:DocMapAreaWidth	Control the display width in pixels of the ReportViewer web part’s Document Map area.	Non-negative integer
rv:DocMapMode	Control the display of the Document Map area of a report.	Displayed, Collapsed, Hidden. Default: Dislayed

Report Builder Report Fails with The item 'ModeName.smdl' cannot be found. (rsItemNotFound)

david — Thu, 09 Jul 2009 15:26:00 GMT

In a SharePoint integrated SQL Server Reporting Services environment, you find you can access a model, open Report Builder, drag an drop a few items. But when you attempt to actually run the report you're told the model cannot be found. What? But you were just using it!

The error looks like:

An error has occurred during report processing. (rsProcessingAborted)

The item 'Folder/ModelName.smdl' cannot be found.

To Fix:

In SQL Server Reporting Services Configuration, check the Unattended Execution Account.

Provide this account access to the SharePoint library hosting your Model.

Enable SQL Report Builder Anonymous Access

david — Sat, 04 Jul 2009 13:07:00 GMT

Enabling Anonymous Access to Report Builder Application Files

Report Builder uses ClickOnce technology to download and install application files on the client computer. When it starts on the client computer, the ClickOnce application launcher will make a request for additional application files on the report server computer. If the report server is configured for Basic authentication, the ClickOnce application launcher will fail the authentication check because it does not support Basic authentication.

To work around this issue, you can configure Anonymous access to the Report Builder program files. Doing so allows ClickOnce to bypass the authentication check when retrieving its files. Enable Anonymous access by doing the following:

Verify that the report server is configured for Basic authentication.
Create a bin folder under ReportBuilder and copy four assemblies to the folder.
Add the IsReportBuilderAnonymousAccessEnabled element to the RSReportServer.config and set it to True. After you save the file, the report server creates a new endpoint to Report Builder. The endpoint is used internally to access program files and does not have a programmatic interface that you can use in code. Having a separate endpoint allows Report Builder to run in its own application domain within the Report Server service process boundary.
Optionally, you can specify a least-privilege account to process requests under a security context that is different from the report server. This account becomes the anonymous account for accessing Report Builder files on a report server. The account sets the identity of the thread in the ASP.NET worker process. Requests that run in that thread are passed to the report server without an authentication check. This account is equivalent to the IUSR_<machine> account in Internet Information Services (IIS), which is used to set the security context for ASP.NET worker processes when Anonymous access and impersonation are enabled. To specify the account, you add it to a Report Builder Web.config file.

The report server must be configured for Basic authentication if you want to enable Anonymous access to the Report Builder program files. If the report server is not configured for Basic authentication, you will get an error when you attempt to enable Anonymous access.

For more information about authentication issues and Report Builder, see How to: Configure Report Builder 1.0 and Report Builder 2.0 Access.

To configure Report Builder access on a report server configured for Basic authentication

Verify the report server is configured for Basic authentication by checking the authentication settings in the RSReportServer.config file.
Create a BIN folder under the ReportBuilder folder. By default, this folder is located at \Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\ReportBuilder.
Copy the following assemblies from the ReportServer\Bin folder to the ReportBuilder\BIN folder:

Microsoft.ReportingServices.Diagnostics.dll

Microsoft.ReportingServices.Interfaces.dll

ReportingServicesAppDomainManager.dll

RSHttpRuntime.dll
Optionally, create a Web.config file to process Report Builder requests under an Anonymous account:
Copy Code
```
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<system.web>
<authentication mode="Windows" />  
<identity impersonate="true " userName="username" password="password"/>
</system.web>
</configuration>
```
Authentication mode must be set to Windows if you include a Web.config file.

Identity impersonate can be True or False.
- Set it to False if you do not want ASP.NET to read the security token. The request will run in the security context of the Report Server service.
- Set it to True if you want ASP.NET to read the security token from the host layer. If you set it to True, you must also specify userName and password to designate an Anonymous account. The credentials you specify will determine the security context under which the request is issued.
Save the Web.config file to the ReportBuilder\bin folder.
Open RSReportServer.config file, in the Services section, find IsReportManagerEnabled and add the following setting below it:
Copy Code
```
<IsReportBuilderAnonymousAccessEnabled>True</IsReportBuilderAnonymousAccessEnabled>
```
Save RSReportServer.config and close the file.
Restart the report server.