Posts

Power View Automatic Metadata Filtering

/0 Comments/in /by

Similar to Report Builder models (now obsolete), Power View (formerly known as Crescent) filters metadata automatically. The reasoning behind this feature is that the tool should guide the user to choose only tables that are related to the ones used on the report to avoid Cartesian joins. For example, if the user has selected Sales Amount from the Reseller Sales table, Power Pivot automatically disables Currency and Geography tables because they are not related to the Reseller Sales table. The Metadata pane shows these tables greyed out and the user cannot choose a column from these tables.

101911_0020_PowerViewAu1

As useful as this feature is, it can get in the way for the same reasons the Report Builder metadata filtering didn’t work so well with multidimensional cubes. Marco Russo has already reported an issue that this feature ignores M2M calculations in the model. I believe this will be even a bigger issue one day when Power View supports multidimensional cubes because there are scenarios where dimensions are not directly related to a fact table but still can be used for slicing, such as a shim dimension for time calculations that is populated with scope assignments.

If you agree, please vote for the suggestion I’ve submitted on connect. Ideally, there should be a visual indicator that a table is not related but the user should still be able to select a column. Or, the modeler should be able to overwrite this behavior per table.

Rendering Reports in Chrome and Safari

/0 Comments/in /by

A client complained that Reporting Services ReportViewer doesn’t render reports in Google Chrome or Apple Safari browsers. The magic property to apparently fix this issue was SizeToReportContent=”True”

<rsweb:ReportViewer
ID=”reportViewer” runat=”server” Height=”100%” Width=”100%”
SizeToReportContent=”True”>

</rsweb:ReportViewer>

Here is a link to Microsoft’s official stand on the browser support for Reporting Services. A couple of notes:

  1. Currently Microsoft doesn’t support report rendering in Chrome but are re-visiting the browser support matrix periodically.
  2. Report Manager doesn’t work in Safari or Firefox. Report Manager supports IE only. However, report rendering by URL or ReportViewer should work with the above change.

Specifying a Default Report Name on Export with ReportViewer

/0 Comments/in /by

Normally, when you export a report, the Save As dialog box will default to the report name. However, if you the SSRS LoadReportDefinition API and view the report in the ReportViewer, you’ll find that when you export the report it will default the name to a system generated name, such as Report(N).pdf. That’s because LoadReportDefinition creates a transient report on the server that doesn’t have a name. However, the ReportViewer LocalReport and ServerReport objects support a DisplayName property that can be used to specify a custom report name, such as:

reportViewer.ServerReport.DisplayName = “My Report”;

// the following lines are for reference only

reportViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;

reportViewer.ServerReport.ReportServerUrl = new
Uri(ConfigurationSettings.AppSettings[Shared.CONFIG_RS_ENDPOINT]);

Reporting Services "Denali" Brings Export to Excel 2007

/0 Comments/in /by

One welcome improvement in the forthcoming “Denali” release of Reporting Services is exporting to Excel 2007 (*.xlsx) file formats. I bet many companies will upgrade just to get this feature. In fact, the default rendering option when you export to Excel is to xlsx. The old Excel 2003 renderer is still there but it’s hidden by default. And, yes, BI projects now integrate with Visual Studio 2010!

For more details about Excel 2007 rendering, please read the Exporting to Microsoft Excel (Report Builder 3.0 and SSRS) topic in BOL.

082011_1609_ReportingSe1

082011_1609_ReportingSe2

Reporting Services SharePoint Integration in SQL Server Denali

/0 Comments/in /by

A week ago, I raved about how the new PowerPivot Configuration Tool would simplify configuring PowerPivot for SharePoint. Guess what? Setting up Reporting Services for SharePoint integrated mode got simplified too! In SQL Server Denali, Reporting Services leverages the SharePoint service application infrastructure and it doesn’t require installing a Reporting Services server. Not only this simplifies setup but improves performance because there is no round-tripping between SharePoint and report server anymore. Configuring Reporting Services for SharePoint integration mode is a simple process that requires the following steps:

  1. Run the SQL Server Denali CTP3 setup and Install Reporting Services and the add-in on the SharePoint application server.081411_2142_ReportingSe1
  2. Once the bits are installed, open SharePoint Central Administration, and click Manage Service Applications. Expand the New button and click SQL Server Reporting Services Service application.081411_2142_ReportingSe2
  3. In the page that follows, configure the service application details as per your specific environment and click OK.081411_2142_ReportingSe3
  4. As with the previous releases, one more step is required to enable the SSRS content types in a given document library.

    a) Go to the document library settings page and click Advanced Settings. In the Advanced Settings page, select Yes for the Allow management of content types?

    081411_2142_ReportingSe4

    b) Back to the document library settings page, click the Add from Existing Site Content Types link.
    081411_2142_ReportingSe5
    c) In the Add Content Types page, add BISM Connection File (assuming PowerPivot for SharePoint is configured), and the Report Builder content types.

    081411_2142_ReportingSe6

Voila! Once this is done, you can create Crescent reports. You just need to create a BISM Connection File that points to a PowerPivot model or BISM Tabular model.

081411_2142_ReportingSe7

The Load Balancing Act

/0 Comments/in /by

I had some fun lately setting up a Reporting Services farm of two SSRS 2008 R2 nodes and a hardware load balancer. We followed the steps in BOL only to find out that the report server will return sporadic empty pages or MAC viewstate validation errors although the machine key was identical on both servers. We fixed the issues by:

  1. Enabling sticky sessions in the load balancer (not documented).
  2. Configuring the ReportServerURL setting (BOL says “Do not modify ReportServerUrl”).

Despite what BOL says or doesn’t say, it appears that sticky sessions required with R2 probably due to the AJAX-based ReportViewer. Here is an example configuration that demonstrates the three settings you need to change in the rsreportserver.config:

<UrlRoot>http://atltstssrsibo/reportserver</UrlRoot>

<Hostname>atltstssrsibo</Hostname>

<ReportServerUrl>http://atltstbir02ibo/reportserver</ReportServerUrl>

On each node, set up ReportServerURL to point to the node itself. In this scenario, atltstssrsibo is the load balancer name and atltstbir02ibo is the node server name.

Finding Kerberos in Forests

/0 Comments/in /by

I’ve made a decision. My next book will be Applied Kerberos BI. Jokes aside, I’ve been doing a lot of security work lately to get Kerberos working for internal and external users. Here is my latest success story.

043011_0143_FindingKerb1

In this case, the customer has two AD forests. The internal users are on a DOMAINA in FORESTA. The production servers are in DOMAINB. The external user accounts are in DOMAINC. Both DOMAINB and DOMAINC are in a separate forest. There is a bi-directional trust between DOMAINA and DOMAINB and between DOMAINB and DOMAINC. DOMAINC trusts DOMAINA.

After registering SPNs, etc. we’ve found that Kerberos doesn’t work. Or, to be more specific, it works partially. If a service account from DOMAINB is used to run SSRS and SSAS, the double hop (user-SSRS-SSAS) is successful for users on DOMAINC but not for DOMAINA users. And vice versa, if a DOMAINA service account is used, only DOMAINA users can run reports. Upon a fair amount of tracing, we saw that when DOMAINB tried to resolve the SSAS SPN it fails with KDC_ERR_S_PRINCIPAL_UNKNOWN error. For some obscure reason, it was trying to resolve the SPN by using the SSAS NetBIOS name against the DOMAINA domain controller and DOMAINA was failing to find it. You would expect Kerberos to fall back and do a second search in the domain the end user belong to but it doesn’t. Perhaps, it gets lost in the forests.

How do we solve this horrible issue? Use a FQDN SSAS server name in the report data source. For example, instead of SSASServer, we would use SSASServer.DOMAINB.LOCAL. This forces the Kerberos to look up the SSAS SPN in DOMAINB. Please note that in this case, the SSRS server runs in a native mode. I don’t know what will happen if the SSRS server is configured for SharePoint integration. The Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products whitepaper states “Basic Kerberos delegation can cross domain boundaries in a single forest, but cannot cross a forest boundary regardless of trust relationship. Kerberos constrained delegation cannot cross domain or forest boundaries in any scenario.” This makes me somewhat skeptical but it might apply to the scenario where SharePoint and SSAS are in two different forests.

Data Driven Subscriptions and Row-level Security

/2 Comments/in /by

Scenario: A customer wants to have report row-level security and data-driven subscriptions. If you have used SSRS for a while, you will now the cornerstone of report row-level security is User!UserID which returns the user identity. But here is the catch – SSRS will refuse creating a data driven subscription if a report has an expression with User!UserID.

Solution: How do we solve this horrible issue? A great hack is in order. We will disguise the User!UserID in an embedded function inside the report or in an external assembly so SSRS can’t see it. (The reason why this function is named GetHashedUser although no hashing is in place will become obvious in a moment)

Public Function GetHashedUser() as String

return Report.User!UserID.ToLower()

End Function

 

Believe it or not, this lets the data-driven subscription going. But there is another catch. A data-driven subscription runs in unattended mode and you need to pass the user identity as a parameter. So, when the report is executed live we want to pass the internal User!UserID to the report query or stored procedure. But when the report is subscribed to, we want to pass the user identity as a parameter. A simple expression for the query parameter will do the trick.

=iif(Parameters!UserID.Value =“”, Code.GetHashedUser(), Parameters!UserID.Value)

If the parameter is empty (live delivery), we will call the GetHashedUser function, otherwise, we will pass the parameter value to the database. So far so good, but we now have a security hole. There is nothing stopping the end user from spoofing the report parameter and passing another user’s identity to it to force the report take the data-driven subscription path. To fix this, we will need some level of encryption. I suggest a solution that encrypts the user identity with SHA1 hash both in the report and the database. This should keep spoofy users at bay.

You can download the complete code with an AdventureWorks sample report here (requires registration).

Note: The above solution assumes a report connection string with stored credentials which is what you would typically have for T-SQL (relational) reports. What about SSAS with Windows and dimension security? Not a good story and no changes in Denali unless you vote for the following suggestions on connect:

  • Enabling Subscriptions for non-stored credentials reports only has 3 votes on Connect (see link)
  • Fixing the SSAS connection string issue has only 4 votes on Connect (see link)
  • Enabling Shared Data Sources to have Expression Based Connection strings has only 4 votes on Connect (see link)
  • Can’t create a subscription if data source is set to impersonate the authenticated user (see link)

 

SSAS and data-driven subscriptions has been the bane of my existence. Greg Galloway has an excellent writeup here. In a nutshell, clumsy workarounds include duplicating the report definitions and using an embedded data source with expression-based connection strings, or implementing the row-level security in the report (as you would do with relational reports). So, please vote! Let’s see if can convince Microsoft that we do use SSAS and this is a wide-spread issue that deserves to be fixed ASAP.

SSRS Query Performance Issues with Parameters

/0 Comments/in /by

I recall seeing “fast queries in SSMS but slow in SSRS” issues reported a few times in the public discussion list but I’ve always dismissed them as unrelated to SSRS. Alas, I happened to see one today when a report would take a very long time (7 min) to execute. As usual, the first stop was to take a look at the SSRS Execution Log which showed that all the time was spent in data retrieval. However, executing the report query in SSMS would take a few seconds. In this case, the report had a complex free-text T-SQL SELECT statement that referenced a single report parameter several times in the query.

The workaround we found at the end was to declare a variable at the beginning of the query that referenced the parameter once, as follows:

DECLARE @invoice_number as int

SET      @invoice_number = @inv_parameter

Then, we referenced the variable in the report query instead of the query parameter. This solved the mysterious query performance issue which appears to be a bug with parameter passing in the SQL Server dataset extension. In this case, the customer had SQL Server 2008. The issue might be fixed in SQL Server 2008 R2, I haven’t checked.

Report Actions for Report Server in SharePoint Mode

/0 Comments/in /by

Report actions are an Analysis Services extensibility mechanism that lets end users run a Reporting Services report as they browse the cube. A customer reported that they have trouble setting up a Reporting Services action for a report server configured in a SharePoint integrated mode – a scenario which appears that wasn’t tested properly by Microsoft.

First, the customer wanted to pass multiple parameters to the report. This was achieved by setting up the report action Target Type to Cells and Target Object to All Cells. This setup allows the user to right-click any cell on the cube to initiate the action. What’s more important is that an action-capable browser, such an Excel, will be able to collect the coordinates of all dimensions used in the browser, such as those added to the filter area, so you can pass them as parameters to the report.

121210_0238_ReportActio1

A condition is further specified to restrict the action. Because the cube browser will be passing three parameters to the report, the condition enables the action only if the corresponding members’ current member is not the All member.

This is where things get interesting. You need to specify the server name and report path. Instead of pointing to the report server, you should point to the SharePoint library using the syntax that Prash Shirolkar from the Reporting Services explains in his blog. I haven’t tested this but I’d expect that if you reference the report server, the report will use the report definition copy on the report server and not the one stored in SharePoint. Recall that the latter is the master copy of the report definition and the one in the report server might be outdated. However, when you reference the SharePoint library, you’ll find that Analysis Services doesn’t prefix the parameters with rp:, which in turn makes the report server ignores the parameters. The ultimate hack is to prefix the parameters yourself:

121210_0238_ReportActio2

Here, the server name is set to the SharePoint site URL. I’ve found that actually it doesn’t matter where you break the URL to server name and report path. SSAS will concatenate them, so I could have set the server name to elite only and moved the rest of the URL to the Report Path line. Also, notice that you don’t use http: as in http://elite/sites/reports. SSAS will add the protocol for you.