QlikView – Access Data from SSRS

March 3, 2015

Since QlikView can’t access certain data sources like MS Analysis Services (SSAS) or other exotic data sources (SAP NetWeaver BI, Hyperion, TERADATA) natively.

We can fall back on the perfect middleware for this being MS SQL Reporting services

image

The approach is a simple as can be. Setup an SSRS server (can even be the MS SQL Express (Free) Edition & SSRS add-on)

The SSRS report server has natively a web service interface, exposing a SOAP and URL Interface.

Next develop your SSRS reports (which can handle multi data sources in 1 report Smile)

image

Like for example a SharePoint List combined with an Oracle database, or anything else.

Simular to PowerPivot that can access an SSRS Data Source. We can do the same with QlikView.

Use a Web File connection as Data Source

image

Fill in your report URL link and add the rs:Format=XML parameter to get an XML output from you report

image

If all goes well you will get the Report XML output and see the SSRS TABLIX and FIELDS Smile

image

That’s it, now you are ready to build your QlikView GUI

Once you know this technique you can as well use this to access an SSRS in the MS Azure cloud.Winking smile

Enjoy!


SharePoint – SSRS Web Part Parameter Issue

January 31, 2015

We had an annoying problem with a published SSRS report in SharePoint that uses a Parameter.

Most likely this appears only in combination with a external data source like an ODBC connection that retrieves data from a data source not within the current domain ?

This strange ‘bug’ I would call it so far is not showing when your run the report from within the Report Designer, but only from within SharePoint.

So I suspect that this is a malfunction in the SSRS front end Web Part.

Problem :

When selecting the first time a parameter in the report, it returns all the filtered data. But when selecting a new parameter value, for the second time, it returns a blank page ? And the parameter box jumps down.

image

Solution :

After debugging with trial and errors, we found out that if you add a Default Value, in the parameter field, the problem is solved.

image

If you get the parameter values from a different Dataset, you can also specify the Default Value using the Get Values from a Query option. But this is not needed to make it work.

Just give any valid default value in the Specify Values field will do the job as well.

image

Again, this problem is not occurring in the Report Designer itself?

So the SharePoint users are happy to see that this annoyance is gone Smile


PowerPivot – Connect to an SSRS Report

September 17, 2014

SSRS is a very nice reporting platform that comes along with MS SQL.

Using the free Report Builder client you can make some very nice reports and publish or schedule them to run on your intranet.

But what if you want to reuse valuable information in the reports ?

Well we can you use MS PowerPivot to access the data over the internet on you client.

Open PP and choose the in the menu From Report.

Next point to the URL on you Reporting Intranet or SharePoint if you are running in mixed mode.

image

When you click next it will grab the TABLIX data grid in your report.

image

As easy as that.

This is how the bits and pieces fall together in your MS BI environment. Smile


SSRS – Connect to an OLAP cube using XML/A

September 16, 2014

While there is native way in Report Builder 3.0 to connect to an MS SQL OLAP Cube.

I wan’t to demonstrate an alternative way to connect to ANY kind of OLAP cube that supports XML/A.

For MS SQL you first need to configure your OLAP Analisys Services to be able to connect using XML/A protocol.

For Windows Server 2003 look here for Windows Server 2008 look here

Make sure you have the IIS Application Pool settings set to be 32Bit compatible.

image

Once you have it running you can connect SSRS using the msmdpump.dll

Like this :

http://YourServer/olap/msmdpump.dll

You can use multiple XML/A compatible clients like for example Kettle Spoon.

image

Where you can enter your MDX query and test it.

As well as just plain MS Excel to connect to the OLAP Cube using XML/A connection

image

Or even PowerPivot as a client to connect.

image

But back to SSRS, it goes like this. Choose SQL Analysis Server, and point the to the XML/A URL as connection string.

image

And you can access the Dimensions using regular MDX Queries.

image

Bonus:

Apart from the fact that you access multiple OLAP Cube brands like for example the In-Memory IcCube

image

image

And using Multiple Clients – see above.

It comes also is also is not blocked by the firewall since XML/A uses the HTML (80) or SHTML (443) port.

Enjoy !


SSRS – Using SharePoint List Views to Filter & Sort

September 16, 2014

By default when reporting against a SP List it will use the DEFAULT View.

clip_image002

clip_image004

When you want to access the data using a Specific VIEW you need to add the following to the data source XML :

clip_image006

Create a View and pass Parameters of the View

<rssharepointlist xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <listname>HelpDesk</listname>
 <query>
 <parameters>
 <parameter name="viewName">
 <defaultvalue>All Issues</defaultvalue>
 </parameter>
 </parameters>
 </query>
 <viewfields>
 </viewfields>
 </rssharepointlist>

You need to add –> query –> parameters –> parameter name –> BEFORE the viewfields xml node.

The properties of this view are applicable to the dataset.

Also the sorting will be applicable to that dataset. 🙂


Debug SSRS Connection Problems

September 12, 2014

Sometimes you get an error like this.

image

For more information about this error navigate to the report server on the local server machine, or enable remote errors

So how to enable Remote Errors for SSRS

Go to Central Admin.

  1. In Site Actions click Site Settings for the site you want to modify.

  2. Click Reporting Services Site Settings in the Reporting Services group.
  3. Click Enable remote errors in local mode.

image

image

Bu keep in mind that SSRS will not pass most errors forward for the WFE to display. Sad smile

You can set the Option as well using SSMS

Connect to Reporting Services

image

Go to Server Properties –> Advanced –> Remote Errors

image

Solution:

Since the BCS data resides in an external data source (like MS SQL database).

Just don’t make the round trip using the SharePoint External Content Types (BCS). Because it will overcomplicate things security wise. It’s more then a double hop to jump over.

Just use SSRS to go directly to the external data source. It will save you a trip and give you a better performanceSmile


Get Extensive Web Analytics Reports from SharePoint, using SSRS

August 31, 2014

Well SharePoint offers OOB some basic Site Web Analytics reports.

If you go to the Site Settings ….

image

image

All of the data is stored in the WSS_Logging database.

image

When you run this Qry you get all information needed to build your own statistics reports.

image
[Sourcecode language=”SQL”]
SELECT RequestUsage.RowId ,RequestUsage.LogTime ,RequestUsage.UserLogin,
RequestUsage.CorrelationId ,RequestUsage.SiteId ,RequestUsage.SiteUrl,
RequestUsage.WebId ,RequestUsage.WebUrl ,RequestUsage.DocumentPath,
RequestUsage.ContentTypeId ,RequestUsage.QueryString,RequestUsage.BytesConsumed,
RequestUsage.HttpStatus ,RequestUsage.ReferrerUrl,
RequestUsage.ReferrerQueryString ,RequestUsage.Browser,
RequestUsage.UserAgent ,RequestUsage.UserAddress,
RequestUsage.RequestCount ,RequestUsage.QueryCount,
RequestUsage.QueryDurationSum ,RequestUsage.ServiceCallCount,
RequestUsage.ServiceCallDurationSum ,RequestUsage.OperationCount,
RequestUsage.Duration ,RequestUsage.RequestType ,RequestUsage.Title,
RequestUsage.RowCreatedTime

FROM RequestUsage

WHERE RequestUsage.SiteUrl NOT LIKE N”

AND RequestUsage.RequestType IN (N’GET’, N’POST’)
AND (RequestUsage.DocumentPath NOT LIKE N’%/_layout%’

OR RequestUsage.DocumentPath LIKE N’%/_layouts/upload%’
OR RequestUsage.DocumentPath LIKE N’%/_layouts/download%’)

AND RequestUsage.UserLogin NOT LIKE N’%spfarmacc’
AND RequestUsage.UserLogin NOT LIKE N’%syst%’
AND RequestUsage.UserLogin NOT LIKE N’%admin%’
AND RequestUsage.DocumentPath NOT LIKE N’%.asmx’
AND RequestUsage.DocumentPath NOT LIKE N’%.dll’
AND RequestUsage.DocumentPath NOT LIKE N’%_vti_%’
AND RequestUsage.DocumentPath NOT LIKE N’%siteassets%’
AND RequestUsage.DocumentPath NOT LIKE N’%default%’
AND RequestUsage.DocumentPath NOT LIKE N’%.axd’
AND RequestUsage.DocumentPath NOT LIKE N’%.js’

Order by documentpath
[/sourcecode]

image

image

 

image

image

Enjoy !