Integrating Dekho and SQL Server Reporting Services (Part 5)

Dec 13, 2011   //   by Trevor H   //   How to, Other  //  2 Comments
Not UsefulUseful (No Ratings Yet)
Loading ... Loading ...

This is the fifth part of blog series on integrating Dekho with SQL Server Reporting Services. In case you missed the first four parts you can access them from here;

http://www.dekho.com.au/integrating-dekho-and-sql-server-reporting-services-part-1
http://www.dekho.com.au/integrating-dekho-and-sql-server-reporting-services-part-2
http://www.dekho.com.au/integrating-dekho-and-sql-server-reporting-services-part-3
http://www.dekho.com.au/integrating-dekho-and-sql-server-reporting-services-part-4

In the previous part of this series we now used a second Dekho servlet to display information from a feature info query from selection in the users Dekho session. Let’s have a look at the data flows from the last part.

At this stage we have only used Dekho to retrieve data and while this is useful we haven’t moved beyond what is already displayed on the selection review panel within Dekho. In this part we are going to delve out of the Dekho comfort zone and start querying the database directly via SQL.

Firstly, I think I should point out something in SSRS which may not be immediately apparent to new users. We’ve seen in previous posts that we get a dataset from a data source which we can then use in a table object to visualise on the report. What troubled me for a while is that you cannot join datasets together. I found this out the hard way when implementing an SSRS report which consisted of 7-8 datasets. Although the datasets had common keys with which I could join (if it was possible) there is no way to join them in SSRS to form a single dataset. Keep this in mind that for instance you cannot take two datasets, join them together and then present them in a table in the report. You would need to have two tables in the report to achieve this.

The first thing we need to do is add a new data source to our report. Right click the data sources node in the report data panel and select add new data source. In this example we will create a connection to a SQL Server database.

Enter a name for the connection and select “Microsoft SQL Server” from the type drop down. Next click the edit button to enter the connection details so we can build a connection string.

Make sure you use a database login (ie SQL Server Authentication) as the SSRS service users (who executes the report) will most likely not have access to the database. Press OK when the details are completed.

The connection string should now be completed

And the credentials of the connections also stored on the next TAB

Press OK to create the connection.

Now that we have a connection we can create a dataset. Right click the dataset node in the report data panel and select add new dataset. The dataset will use a dataset embedded in our report – select the data source we created as the data source. The query type will be text as we will enter a SQL expression. You can use the query designer or copy paste (I usually prototype the query in SQL Management Studio first).

In this case I have a query that is joining three tables. Remember we can’t join datasets so I’ve had to do the joining in my SQL query. The other thing to note here is I have included the Parcel tables OBJECTID field. This is very important because (if you refer back to previous posts) the Dekho Selection Servlet returns us a list of OBJECTID’s for the selection set in Dekho. The screenshot below is a servlet response showing the OBJECTID’s of the selected features in the users Dekho session.

Now, back on our Dataset properties window, if we click the Fields TAB we can see that it has picked up our fields from our query correctly

Press OK to save the dataset.

Don’t run the report just yet! Remember our SQL query?

There is no filter currently on the query so if we ran this report as is it would bring back every single record from the query (in this case close to 2 million rows). What we need now is a filter! Your instincts may tell you to rush to the “Filters” TAB on the dataset properties but this is actually the wrong approach.

In some cases this is a valid option but when you have millions of rows this can become very slow. The reason for this is that the filter is only applied “post query” so in fact SSRS would go off and retrieve all 2 million rows and only then would it filter the rows. What we actually need to do is apply a filter to the actual query.

In order to apply a filter we need to somehow get the relevant OBJECTID’s from our DekhoSelection dataset (that we set up in part 3). This dataset has the OBJECTID’s of the Dekho selection. Now you are probably saying to yourself “didn’t he just say you can’t join two datasets together?” – That is correct, we can’t actually join these two datasets together. So how do we filter our SQL query based on the Dekho selection? We turn one dataset into a multi-value parameter!

If you cast your mind back to part three of this series, we dealt with the Dekho selection servlet which we filtered based on the layer field of the XML dataset.

We had our dataset called DekhoSelectionResults

Which we applied a filter to of “Basemap.land” to only return our land parcels from the overall selection set

We are going to turn this dataset into a multi-value parameter. To start, in the Report Data panel, right click Parameters and select “Add Parameter”. Fill in the details following the screenshot below. Take note that the data type is an interger and the “Allow multiple values” checkbox is checked. In this case I have also hidden the parameter from being displayed on the report.

Next, click the “Default Values” TAB and choose the “Get values from a query” radio button. This will allow you to now select our dataset (ie DekhoSelectionResults) and the field we require (ie id – which is actually the OBJECTID field).

Lastly on the “Advanced” TAB set the data refresh to “Always refresh” and uncheck the “Notify me…” checkbox.

Press OK to create the parameter.

The next thing we need to do is incorporate this parameter (which is a list) into our legal description query. Open the LegalDescription dataset properties window.

To the right of the query text you will notice an equation symbol; this will allow us to use the built in expression builder. Click the button to open the expression builder.

Have a look around the three panels at the bottom. You might notice our parameters are available here;

As are our datasets fields;

You might ask yourself at this stage “can’t we just use the dataset?” and the answer is unfortunately “No”. The problem being that the values it give us are limited to single values – for example in the picture is shows First(id) meaning we would only get the first id value from our dataset and not the whole list. The parameter on the other hand is the “complete list” but we still need to parse this list into a way the expression builder can craft our query. Again this isn’t something that is immediately obvious.

To add the parameter into our query we would normally just double click the parameter on the panel at the bottom. Unfortunately this is what you get;

So effectively it has erased your query out. So, at this stage, don’t press OK to save the change but copy the text in the expression window and press cancel to back out the change. When you go back into review the expression the SQL query will still be there and one would think it would be as simple as doing something like this;

Or this in order to add the values to the query;

What we actually need to do is expand the list out into a single string and after searching high/low for the best possible way I present you with the “Join” function

The “Join” function joins our parameter list into a single concatenated string and even comma delimits it for us. If you pressed OK now you would think you were done but actually you aren’t. Look at the query below;

What’s happened here is that although we have added our parameter to the query it’s actually not going parse correctly. SSRS still thinks this is an SQL query – a clue is the query is still just text while an expression shows up as “<<Expr>>”. In order for SSRS to recognise the query as an expression we need to change a few things. Go back into the expression builder to edit the query/expression.

Firstly we need to add an “equals” sign at the start of the expression;

What you will see is that the expression builder has now highlighted our “select” keyword as an error. Why? It expects the expression to be a string that it can evaluate. So in other words we need to change our entire SQL query into a string which SSRS can then pass to the database to execute the query. To do this we simply add a double quote to the start and end of the string (see below)

What? The expression is still wrong? Unfortunately, yes it is. This brings me to another annoying “gotcha” with SSRS – expressions can only be on a single line. To fix the above we actually have to write our entire SQL query on a single line. Not only is this tedious and error prone it’s just plain frustrating. To get around this I would normally shorten the query (by selecting from a view ie “select * from view”) but in this case I will join the query onto a single line.

Also at the end of the query we need to concatenate the “joined” parameter list into our “super string”

So now we have one long line of query and the expression is happy. The full query looks like this;

="select p.objectid, d.type,d.status,d.ttl_title_no,d.legal_desc_text from crs.sdeadmin.parcel p,crs.sdeadmin.lgp l,crs.sdeadmin.lgd d where p.par_id = l.par_id and l.lgd_id = d.lgd_id and p.objectid in (" + Join(Parameters!LandOBJECTIDs.Value,",") + ")"

Now if we press OK to save the change you will notice the query text has changed to an expression;

Before you save the dataset changes check the fields are still correct;

This is another potential “gotcha”. BIDS cannot determine the fields based on the expression. So populate the fields first with a simple query (like we had initially) and keep this query handy in case you need to refresh or add fields to the list. If you are happy press OK to save the dataset.

Now we can start testing the report.

We will start by adding a textbox onto the report. Add a new textbox to the report. Textboxes can also use expressions, to access this right click the textbox and select “Expression”

The expression builder will display. Enter the “join” expression we prepared earlier and press OK.

The textbox will now be populated using an expression.

The reason I do this is to “debug” the selection we are getting from Dekho. We can remove this later once we are happy we are getting the correct results.

Next add a new data table and drag the fields across from the LegalDescription dataset.

Now we can save and deploy the report.

Open Dekho and make a selection

Now launch the report

We can easily see here that we have got two records back from our query and the OBJECTID’s in the textbox match.

If we select more land parcels

We get more records in the report

However if we do one more selection

We now only get one record in the report – even though our “debug” textbox shows all 4 OBJECTID’s

The reason for this is I used an inner join in the report query and not all land parcels have a legal description. So the lesson here is to make sure your report query handles data “omissions” as well!

Looking at the data flow diagram below we can now see that the only interaction SSRS has with Dekho is the initial session parameter (that is passed to the report) and then the call to the Selection Servlet to retrieve the Dekho selection. The remainder of the work is then performed by SSRS by executing SQL queries against a database before displaying the report.

This approach has a few things you should be aware of;

1) This method suits reports that are not based on existing feature info queries.
2) This method allows you to have reports based on queries that are independent of your Dekho map and its info queries.
3) Most importantly this method moves the processing load from the Dekho/Tomcat service onto the SSRS server which is from my experience scales better when dealing with large queries.

So what we now is a report that shows relevant information from a SQL query using the feature selection from a user’s Dekho session. In the next part we will look at enhancing our report even further by leveraging another Dekho servlet.

Stay tuned!

2 Comments

  • Great article, Trevor
    Looking forward to migrating to SSRS later in the year.
    For the moment, however, I am stuck with Crystal Reports.

    Have you any experience with integrating Crystal and Dekho?

    I am having performance issues when creating crystal reports with large selection sets.
    It takes approx. 15 minutes to process a crystal report when I pass a selection set of 5000 records.

    When I run a SQL server trace on the database while the process is running, I notice that a SQL select statement is run for every row in my selection set.
    For example, “select column1, column2, etc from dbo.table where objectid = 1″
    followed by
    “select column1, column2, etc from dbo.table where objectid = 2″ etc.

    It would seem more efficient to structure the SQL statement to be “select column1, column2, etc from dbo.table where objectid in (1,2,etc)”

    I was unable to follow everything in your blog postings but it looks like you are manipulating the SQL statement in this way on the SSRS side.
    Any thoughts on whether this could be achieved from Crystal Reports?

    Thanks in advance for any advice or suggestions
    Ciaran D

    • Hi Ciaran

      Glad you liked the article. Crystal Reports has always been probelmatic for me in the past so once I started using SSRS Ive never looked back. I think the main issue with Crystal is that it is processed client side rather than server side. There will be some latency in communicating with the Dekho Server, database and the Crystal engine. However your problem really does sound like its executing each SQL statement individually rather than in one statement. Looking at the Dekho help on the subject I think I can see why this is happening.

      In this article I talk about concatenating the OBJECTID’s together and then using it in a SQL statement – exactly like you said “…objectid in (1,2,etc)”

      What I can see in the Dekho help (this image: http://www.dekho.com.au/help/32/ImagesExt/image25_277.jpg) is where the Dekho Crystal Reports query servlet is joined to the SQL Query. My interpretation of this is that its taking each row from the servlet and then executing the SQL which results in 5000 sql statements.

      I think you are going to need to do is manipulate the results from the query servlet into a concatenated list (or something similar) and feed that into the query.

      Having not used Crystal for a while I couldnt tell you how to do that but the support team at Esri Australia may be able to guide you.

      Best of luck
      Trevor

Leave a comment


+ six = 12