Visualizing Spatial Data – Toggling map layers

Last time I showed you how to add a PushPin to your map control. But what if you want to toggle that (or any other) layer in your map control?

The reason I wanted to do this, is that I want to allow the users of my report to toggle different layers in the map control. That way, you can pre-build all the different layers you think your users need into a single report, and just let them toggle the layers themselves. That saves you a lot of time building a report for every single combination of layer-types.

Multi-layer map control
In order to keep it simple, I used the report from my previous post. Specifically, the version with the standard PushPins. And the only layer I want them to be able to toggle, is the PushPin layer on top of the basic map.

Creating a parameter
Because your want to present an easy solution to your users, I want to create a Boolean parameter that they can use to toggle the PushPin layer. To create this parameter, right-click on Parameters in Report Builder, and choose “Add Parameter”:

In the general options window, make sure you fill in a Parameter Name, a question for your users (so they understand what they need to do), and a Data Type (in this case a Boolean, that’s either True or False):

Because you don’t want to keep your users waiting if they open your report, you might want to give the Boolean a default value. You can do this by adding a default value to your Boolean:

By doing this, the Boolean is True by default. So the user doesn’t have to pick a value if he runs the report for the first time. This might come in handy if you need this as an extra feature in your report, and not a feature you need to configure before the report shows your data.

Expression
Now you need to configure your map layer to use the parameter you created to toggle it. Double-click on your map control, and once you see the Map Layers menu on the right, you can do this by right-clicking on “Layer Data”:

Now click on Visibility, and under Layer visibility, click the last option, and press the Fx button:

In the Expression window, fill in the following expression:

Now if you run the report, you’ll see the Boolean appear in the top bar of your report. By default, it’ll show the PushPins:

And if you change the Boolean to False and press “View Report”, you’ll see the may without PushPins:

With a little help from my friends…
This post isn’t just written by me, but also with the help of Valentino Vranken (Blog | @ValentinoV42. He helped me out on Twitter, because my expression didn’t work as expected. So Valentino: I really appreciated your help! Thank you! ;)

Visualizing Spatial Data – Show locations with a PushPin

After my last two posts (Creating a spatial report, Create an interactive report), I came across a feature I never saw before. Apparently if you add a point layer to your report, you get different layer-options in the list.

Starting with a single layer
To start off easy, you could use the same steps I used to create my first report. This is a single layer report with a full map of (in this case) the Netherlands.

The next step is adding a few pushpins. How about visualizing the three biggest cities in the Netherlands: Amsterdam (790.000 people), Rotterdam (616.000) and Den Haag (The Hague, 502.000).

Creating a Point layer
Before creating a point layer, you need to determine what data you want to present to your users. In my case, I chose the center of each city. This might not be the actual center, but I tried to get as close to the center as I could.

Now that you have your data, you can create the Point layer in your report. Start by double-clicking on your map control, and in the “Map Layers” window, click on “New layer wizard:

Now choose the bottom option “SQL Server spatial query”, and click next.

Choose the bottom option to add a new dataset to your report, and click next.

Click next to choose the datasource that’s already in your report, and click next.

Now click on the option “Edit as Text” in the query designer:

Type or paste your query in the editor, and press the Run button. Once you’ve done that, you’ll see the result in the bottom window:

In the next window, make sure you add a Point layer, and not a Polygon layer:

Now you will see the new layers I mentioned before:

For this report, choose the bottom one: Analytical Marker Map, and click next.

In the next window, select the dataset that contains your points, and click next.

In the window with options, make sure you select the right type of marker (in this case PushPin):

Once you’ve done that, click Finish. Now you see a double layer map with 3 PinPoint on your map:

Using a custom PinPoint
I can imagine that you might want to use your own PinPoint image on your report. This is possible, but remember this might screw up the readability of your report.

In the Map Layers menu, right click your point layer, and click Point Properties:

Now change your marker type from PinPoint to Image, and click Import:

Now if you select an image, the name of your image will show in the “Use this image” text box. If you want to, change the “Marker size”, so your logo will appear larger on the map. Now if you click OK, and run the report, you’ll see something like this:

And if you can’t resist, you can download this EPIC marker right here ;)

Visualizing Spatial Data – Create an interactive report

In my previous blog post I showed you how to create a report containing spatial data. But what if you want to give your users a little bit more information. How about a mouse-over tooltip, of what they’re actually looking at? Or maybe even a click-through to another report or website? Let’s take a look at how to achieve this.

Report
I’m starting with the report that I created in my previous blog post. I’m not going to add data to the report, but instead change some options.

Mouseover tooltip
Because you’re working with a full map in your report, it might be hard to see what kind of location you’re looking at. So the easiest way to fix this, is to add labels to your map control. But if you have a lot of small polygons, you might end up with somethings that looks like this:

In the image above you see the province of South-Holland. In the red rectangle, you see the 2nd largest city of the Netherlands: Rotterdam. But this illustrates the issue, that if you have a lot of small polygons, you won’t be able to see the labels.

So a workaround for this, is to add a tooltip (a mouseover label) to your report. This will show the label you selected, if you (or the user of your report) can see what the polygon represents. So how can we do that?

Go to your Design window, and right-click on a polygon in your map control, and click on “Polygon Properties”:

In the next window, click on the “Fx” button next to the Tooltip textblock:

In this window, enter the following expression:

Now if you hover over your report, you’ll see the following tooltip:

 
Clickable polygons
If you want to spice things up even more, you could also add a click-event to your report. In my case, I wanted to add a click-event that showed the users the Wikipedia page of the city.

Start by right-clicking again on a polygon in your map control, and click “Polygon Properties”. Now go to the Action tab, and click the “Fx” button again:

Now fill in the following expression:

Now if you go to your report, you can click on a polygon and it will open up the Wikipedia page for that specific city.

Another option would be to open up a report via a click-event, to show your users more information from another report. Or maybe you want to redirect your users to a specific page on your intranet. Just imagine all the opportunities… ;)

Visualizing Spatial Data – Creating a spatial report

In the last few posts in the series, I showed you how to enrich your dataset with spatial data. Now you need to start thinking about how you want to visualize your data, so that (for example) your business analysts can start using the data. But how do you start with that?

For every report I create, I use Report Builder 3.0 myself. This tool is a bit smaller and more responsive than the Business Intelligence Development Studio (BIDS).

Create the basic report first
Starting to build your report starts with creating a blank report from the menu in Report Builder:

Once you’ve done that, you need to add a data source to your report:

Then add a map control to your report, by right-clicking on you report, and choose Map:

Double-click on the map control, and in the menu that appears on the right, choose “New layer wizard”:

Click the last option (SQL Server spatial query) and click next.

Now you’re asked if you would like to create a new dataset in you report. In this case, you can just click next.

The same counts for the next step, datasources. Just select the datasource you created earlier, and click next.

In the next menu, we can select the data we want to use. In this case, I just want to select a city name and a spatial object. You can write this query in your SQL Server Management Studio (SSMS), or in this window. I wrote it in SSMS, and I want to copy it to my report. If you want to do the same, you need to click the option “Edit as Text”:

Just paste your query into the editor, and press the run button. Report Builder will retrieve your data, and shows you the results below:

Click next, and select the options you like in the next few menus. I’ll stick with the basic options. I didn’t change anything in the first menu, and in the next chose “Basic Map”:

Click next, click Finish. There you go, you just created your very first Spatial Report! :)

Reporting Services – Query database

After reading a tweet by Ray Martinez, I decided to share the scripts for Reporting Services that I often use.

One of the scripts I used a lot, is a script that shows the Reports that were successfully executed in 2012. For these Reports you will see the last execution time, rows and bytes returned, and how long it took to gather and show the results:

USE ReportServer

SELECT
	CL.Name						AS ReportName,
	CL.Description				AS ReportDescription,
	CL.Path						AS ReportPath,
	CL.CreationDate				AS ReportCreationDate,
	SUM(1)						AS TotalNumberOfTimesExecuted,
	MAX(EL.TimeStart)			AS LastTimeExecuted,
	AVG(EL.[RowCount])			AS AVG_NumberOfRows,
	AVG(EL.TimeDataRetrieval)	AS AVG_DataRetrievalTime,
	AVG(EL.TimeProcessing)		AS AVG_TimeProcessing,
	AVG(EL.TimeRendering)		AS AVG_TimeRendering
FROM ExecutionLog EL
JOIN Catalog CL
	ON CL.ItemID = EL.ReportID
WHERE 1 = 1
AND CL.Name IS NOT NULL
AND EL.Status ='rsSuccess'
GROUP BY
	CL.Name,
	CL.Path,
	CL.CreationDate,
	CL.Description
HAVING YEAR(MAX(EL.TimeStart)) = 2012
ORDER BY COUNT(EL.ReportID) DESC

The second script I want to share with you, shows the first 1000 successfully executed Reports. The data that is returned includes Report format, parameters used to query the data, information about the returned resultset and time needed to return and render the data and Report, etc. I uses a top 1000 because our Report server returned a lot of rows, where I only needed a few for my analysis.

USE ReportServer

SELECT TOP 1000
	EL.InstanceName				AS SQLInstanceName,
	EL.UserName					AS ExecuterUserName,
	EL.Format					AS ReportFormat,
	EL.Parameters				AS ReportParameters,
	EL.TimeStart				AS TimeStarted,
	EL.TimeEnd					AS TimeEnded,
	EL.TimeDataRetrieval		AS TimeDataRetrieval,
	EL.TimeProcessing			AS TimeProcessing,
	EL.TimeRendering			AS TimeRendering,
	EL2.Source					AS Source,
	EL.ByteCount				AS ReportInBytes,
	EL.[RowCount]				AS ReportRows,
	CL.Name						AS ReportName,
	CL.Path						AS ReportPath,
	CL.Hidden					AS ReportHidden,
	CL.CreationDate				AS CreationDate,
	CL.ModifiedDate				AS ModifiedDate,
	EL2.Format					AS RenderingFormat,
	EL2.ReportAction			AS ReportAction,
	EL2.Status					AS ExectionResult,
	DS.Name						AS DataSourceName,
	DS.Extension				AS DataSourceExtension
FROM ExecutionLog EL
JOIN Catalog CL
	ON CL.ItemID = EL.ReportID
LEFT JOIN ExecutionLog2 EL2
	ON EL2.ReportPath = CL.Path
JOIN DataSource DS
	ON DS.ItemID = CL.ItemID
WHERE 1 = 1
AND EL.Status = 'rsSuccess'
ORDER BY EL.TimeStart DESC

The next script returns an overview of the folder and Reports on your Report server. For every object on your server, you can see the creation- and modify date. The joined Executionlog table is used to get the number of times the Report was executed, by which user, and how much data was returned in which time:

USE ReportServer

SELECT
	CASE CL.Type
		WHEN 1 THEN 'Folder'
		WHEN 2 THEN 'Report'
		WHEN 3 THEN 'Resource'
		WHEN 4 THEN 'Linked Report'
		WHEN 5 THEN 'Data Source'
	END									AS ObjectType,
	CP.Name								AS ParentName,
	CL.Name								AS Name,
	CL.Path								AS Path,
	CU.UserName							AS CreatedBy,
	CL.CreationDate						AS CreationDate,
	UM.UserName							AS ModifiedBy,
	CL.ModifiedDate						AS ModifiedDate,
	CE.CountStart						AS TotalExecutions,
	EL.InstanceName						AS LastExecutedInstanceName,
	EL.UserName							AS LastExecuter,
	EL.Format							AS LastFormat,
	EL.TimeStart						AS LastTimeStarted,
	EL.TimeEnd							AS LastTimeEnded,
	EL.TimeDataRetrieval				AS LastTimeDataRetrieval,
	EL.TimeProcessing					AS LastTimeProcessing,
	EL.TimeRendering					AS LastTimeRendering,
	EL.Status							AS LastResult,
	EL.ByteCount						AS LastByteCount,
	EL.[RowCount]						AS LastRowCount,
	SO.UserName							AS SubscriptionOwner,
	SU.UserName							AS SubscriptionModifiedBy,
	SS.ModifiedDate						AS SubscriptionModifiedDate,
	SS.Description						AS SubscriptionDescription,
	SS.LastStatus						AS SubscriptionLastResult,
	SS.LastRunTime						AS SubscriptionLastRunTime
FROM Catalog CL
JOIN Catalog CP
	ON CP.ItemID = CL.ParentID
JOIN Users CU
	ON CU.UserID = CL.CreatedByID
JOIN Users UM
	ON UM.UserID = CL.ModifiedByID
LEFT JOIN ( SELECT
				ReportID,
				MAX(TimeStart) LastTimeStart
			FROM ExecutionLog
			GROUP BY ReportID) LE
	ON LE.ReportID = CL.ItemID
LEFT JOIN ( SELECT
				ReportID,
				COUNT(TimeStart) CountStart
			FROM ExecutionLog
			GROUP BY ReportID) CE
	ON CE.ReportID = CL.ItemID
LEFT JOIN ExecutionLog EL
	ON EL.ReportID = LE.ReportID
	AND EL.TimeStart = LE.LastTimeStart
LEFT JOIN Subscriptions SS
	ON SS.Report_OID = CL.ItemID
LEFT JOIN Users SO
	ON SO.UserID = SS.OwnerID
LEFT JOIN Users SU
	ON SU.UserID = SS.ModifiedByID
WHERE 1 = 1
ORDER BY CP.Name, CL.Name ASC

The last query is a short one. This returns the Reports that don’t inherit permissions, that are set in the site settings menu.

USE ReportServer


SELECT
	Path,
	Name
FROM Catalog
WHERE PolicyRoot = 1 

Hopefully there will be more where this came from. So follow my blog, or contact me by email or Twitter!

Create XMLA file for OLAP Cubes

In order to refresh your OLAP Cubes (SQL Server Analysis Services), you need to create XMLA files. These XMLA files can be used in a SQL Server Agent Job, so that the Cubes are refreshed and re-processed. To accomplish this, you need to follow the next steps:

1) Deploy your Cube to your SQL Server
2) Right click on the database, and choose “Script database as” –> “Alter To”
3) Add the following node to the top of the XML:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

4) Change the “Alter” node into the following node:

<Alter AllowCreate="true" ObjectExpansion="ExpandFull">

5) Add the following nodes to the bottom of the XML:

<Parallel>
  <Process>
   <Type>ProcessFull</Type>
   <Object>
    <DatabaseID>SandBox</DatabaseID>
   </Object>
  </Process>
 </Parallel>
</Batch>

6) Change the “DatabaseID” in the nodes of Step 5 to the Cube name you deployed
7) In your SQL Job, choose the “Type” of task “SQL Server Integration Services Package”
 
So from now on, your SQL Job will re-create the SSAS Cube, and process it. But be aware, every time you change your Cube, change your XMLA file as well!!!

Deploying Report Builder 3.0 reports on 2005 Report Server

How fun is it to use new software? As a developer you always want to use the latest software, and in many cases it’s backwards compatible. This (unfortunately) isn’t the case with Report Builder 3.0 and SQL Server 2005 Reporting Services. This is a “by design” issue, but it’s such an annoying “feature”. If you try to deploy a Report built in RB 3.0, you will get the error:
 

“The report definition is not valid. Details: The report definition has an invalid target namespace ‘http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition&#8217; which cannot be upgraded. (rsInvalidReportDefinition)”

In most cases you will copy-paste the tables or objects from RB 3.0 to the Business Intelligence Development Studio (BIDS), and save it again. But sometimes you just want to take the shortcut, and that is possible as well. As always, I have to mention that I don’t guarantee anything, but it worked for me several times.
 
 
The first step is to open the .RDL file with a text editor, and replace the following line of XML:

 
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" 
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">

with:

 
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">

 
 
Step two is removing a set of nodes in the RDL, that is not supported by versions older then Report Server 2008:

<ReportSection>     <ReportSections>

And it’s closing tags:

    </ReportSection>
</ReportSections>

Don’t remove the text inside these tags, only remove the tags themselves!!!
 
 
If you follow these steps, you will be able to deploy a “new” Report to an “old” Report Server without weird copy-paste actions.

Follow

Get every new post delivered to your Inbox.

Join 50 other followers