How to implement True Or False Image in Reports

Suppose we have a form that should appear automatically filled out based on information pulled from a database. We have several datatype fields. Printing out “True” or “False” , “Yes” or “No” on the required field as per the information wuldn't look nice, as this is supposed to look like a form that has been auto-filled out, so we want to have a checkbox or a series of checkboxes that are either checked or unchecked.  We can add checkbox control through following ways1.by Images2.by Textboxes Through Images: 1.First to create 2 Images of checkbox.Then add embedded Images to the report(named as “Checked” and “Unchecked”). 2.Insert an Image object and display 1 of the 2 images as per the field value or as per your conditional expression like as follows IIF(First(Fields!Field.Value,”DataSetName”)=true,”Checked”,”Unchecked”)                     or IIF(Conditional_Expression=true,”Checked”,”Unchecked”)

Through Texboxes: 1.Create a Texbox that must look like checkbox. 2.Change font to Webdings. 3.Display value  “tick” or “cross” mark as per the field value or conditional expression like as follows IIF(First(Fields!Field.Value,”DataSetName”)=true,”a”,”r”)                     or IIF(Conditional_Expression=true,”a”,”r”)

Displaying Checkboxes In An SSRS Report

On the forums I now and then encounter questions asking for the possibility to display a checkbox control on a Reporting Services report.  And the methods usually presented are either through images or by using a certain font one way or another.  However, as of SQL Server 2008 R2 there is actually a third and interesting alternative which comes really close to actually having a control!

In this article I’ll be presenting those three methods.

Screenshots are made using SSRS 2012, and so is the Checkboxes.rdl available for download on my Skydrive.

Checkbox Control, huh?

You may be wondering why people would want to put a checkbox on a report.  After all, reports are not capable of accepting input – except through parameters but that’s a different story – and isn’t that what a checkbox is all about?

Not entirely.  Reporting Services is not only used for data exploration.  Sometimes people use it to produce data-driven printouts, such as letters or even checklists.  In that perspective, having checkbox control functionality would indeed be useful.

A Silly Scenario

My imagination is failing me a little today so I came up with this silly example: a recipe checklist.  And today we’ll be cooking some pasta sauce!

This is the query that produces the list of ingredients:

select 'Yummy Pasta Sauce' as Recipe, 'zucchini' as Ingredient, 1 as Quantity, 'piece' as Unit, 1 as Needed
union select 'Yummy Pasta Sauce', 'mushrooms', 500, 'g', 1
union select 'Yummy Pasta Sauce', 'minced meat', 1, 'kg', 1
union select 'Yummy Pasta Sauce', 'Brussels sprout', 1, 'kg', 0
union select 'Yummy Pasta Sauce', 'onion', 2, 'piece', 1
union select 'Yummy Pasta Sauce', 'tomato sauce', 1, 'L', 1
union select 'Yummy Pasta Sauce', 'potato', 1, 'piece', 0
union select 'Yummy Pasta Sauce', 'Brussels sprout', 1, 'kg', 0

I’m cheating a bit here, all the data is hardcoded in the query.  Normally you’d of course have a database that contains your recipes.

And I’ve also introduced some ingredients which I wouldn’t really want in my sauce, such as Brussels sprouts. Silly, but it gives me a good excuse to use the red checkbox.

Time to explore the possibilities.

METHOD 1: IMAGES

I will not be going into full detail to explain the usage of images in SSRS reports.  If you need additional information on that, please first read my previous article that covers all possibilities of putting images on reports.

As I don’t have access to an inventory of stock images, I opened my favorite drawing program, Paint.NET, and created two images myself.  Then I embedded both images in the report:

I created a dataset using the query above.  Then I set up a Table with a group on the Recipe field whilst adding a header row to display the name of the recipe.

The first column will show the ingredient details, using the following expression:

=Fields!Quantity.Value & " "
    & Fields!Unit.Value
    & " of " & Fields!Ingredient.Value

In the second column in the detail level cell, I did a drag & drop of one of my images.  That gives the following pop-up:

Clicking the fx button brings up the brings up the Expression editor, in which I created the following expression:

=IIF(Fields!Needed.Value = 1, "checkbox_true", "checkbox_false")

Rendering the report shows us:

Finding Nearest Stores using SSRS Map Reports

April is turning out to be a busy month for me. Apart from taking the beta SQL Server 2012 certification exams and some reviews, I am also speaking at the Charlotte SSUG and at SQL Saturday #118 (Madison) and #130 (Jacksonville). I have also submitted at a couple of other SQL Saturdays, so if you do see me speaking at a SQL Saturday close to you, don’t forget to give a shout! As my topic for the first two sessions are on Spatial Reporting in SSRS, I was preparing for it and that is when I thought of posting one of my demos as a blog.

This post will teach you how to implement a report to find the nearest stores using the spatial features in SQL Server and SSRS. As some of you might know, I am living in Charlotte and Harris Teeter is one of the grocery stores that I frequent. For this demo, I would be finding some of the nearest Harris Teeter stores from a list of my hangouts. Follow the instructions below to replicate it:-

1) Create the table structure for storing the locations of Harris Teeter.

CREATE TABLE [dbo].[Harris Teeter](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Location] [varchar](255) NULL,
    [Latitude] [float] NULL,
    [Longitude] [float] NULL,
    [PostCode] [varchar](20) NULL,
    [GeoL] [geography] NULL
) ON [PRIMARY]
Also, create the table structure for storing a list of the hangouts.
CREATE TABLE [dbo].[MyHangouts](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Location] [varchar](255) NULL,
    [Latitude] [float] NULL,
    [Longitude] [float] NULL,
    [PostCode] [varchar](20) NULL,
    [GeoL] [geography] NULL
) ON [PRIMARY]

2) Load some sample data for Harris Teeter stores in Charlotte.

INSERT INTO [dbo].[Harris Teeter] ( [Location], [PostCode], [Latitude], [Longitude] )
VALUES ('Quail Corners - 204','NC 28210-5803',35.116603,-80.858247),
('Old Towne Mall - 294','NC 28226-7159',35.117117,-80.824515),
('Morrocroft Village - 160','NC 28211-3570' ,35.152698,-80.825796),
('Park Selwyn Terrace - 35', 'NC 28209',35.1616695,-80.8492303),
('Colony Place - 4','NC 28226',35.106549,-80.806327),
('Park Road - 218','NC 28209-2229',35.1767066,-80.8510191),
('Cotswold Mall - 208','NC 28211-2802',35.177524,-80.801119),
('Arboretum - 30','NC 28226',35.096321,-80.78463),
('Myers Park - 12','NC 28207',35.1901493,-80.8231644),
('Kenilworth Commons - 61','NC 28203',35.2026843,-80.8455712),
('Ballantyne Commons - 11','NC 28277',35.05313,-80.848995),
('Stonecrest Shopping Center - 66','NC 28277',35.059911,-80.816675),
('Providence Commons - 45','NC 28277',35.066452,-80.7717459),
('Sardis Crossing - 171','NC 28270',35.138476,-80.740138),
('Central Avenue Location - 201','NC 28205-5108',35.219757,-80.809982),
('The Shops at Blakeney - 27','NC 28277',35.036336,-80.806711),
('Uptown Charlotte - 205','NC 28202-1603',35.2330664,-80.846148),
('Rea Village Shopping Center - 40','NC 28277',35.052441,-80.770867),
('The Shoppes at Ardrey Kell - 317','NC 28277',35.02431,-80.847881),
('Plantation Market - 147','NC 28105-6725',35.082745,-80.732972),
('Steele Croft - 88','NC 28278',35.103305,-80.990847),
('Matthews Township - 157','NC 28105',35.125179,-80.710001),
('Mintworth Commons - 174','NC 28227',35.172913,-80.709081),
('Weddington Corners Shopping Center - 343','NC 28104',35.023379,-80.760665)

For getting the data, I used the Harris Teeter website to get the address of the stores in Charlotte and then used this site to geocode the address to latitude and longitude. Using the same method, I also populated the MyHangouts table with the data below

INSERT INTO [dbo].[MyHangouts] ( [Location], [PostCode], [Latitude], [Longitude] )
VALUES ('Home','NC 28210',35.1537875,-80.8502022),
('Office','NC 28211',35.1493742,-80.8272008),
('PetSmart','NC 28217',35.1385661,-80.8764557),
('The EpiCentre','NC 28202-2538',35.225324,-80.842187),
('Library','NC 28211',35.1513557,-80.8225257)

3) Once that is done, we will have to convert the Latitude and Longitude to spatial data of type geography. Execute the code below for the same:-

UPDATE [Harris Teeter]
SET [GeoL] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
UPDATE [MyHangouts]
SET [GeoL] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

4) Create a new report and a datasource pointing to the database where the tables are stored. Create a dataset query named DST_Hangouts for selecting the data from the [MyHangouts] table.

5) Create a new report parameter which will source data from DST_Hangouts. The value will be the spatial field - GeoL while the label will be the Location.

6) Create another report parameter – RP_Cnt which will be of integer data type. This will be used for specifying the number of closest stores you want to see.

7) Make another dataset query – DST_HT which will filter the list of hangouts based on the parameter selected. This dataset query will be used in the map to show the selected hangout.

8) Now, make the pivotal dataset query of this post – DST_TopHT which will be used in the map to calculate the closest stores from the selected hangout

SELECT        TOP (@RP_Cnt) Location, GeoL.STDistance(@RP_MyLoc) AS distance, GeoL, Latitude, Longitude
FROM            [Harris Teeter]
ORDER BY distance

9) Optionally, we can also include a spatial query – DST_Circle which will draw a circle of 1 km diameter around the selected hangout so that we get an idea of the scale.

SELECT        ID, Location, Latitude, Longitude, PostCode, GeoL.STBuffer(1000) AS GeoLocation
FROM            MyHangouts AS H
I have filtered this dataset by the selected hangout in the Filters tab of the daataset properties.

10) Now drag and drop a map from the toolbox and then add two point layers based on the datasets - DST_Top HT and DST_HT. Also add a Bing maps layer and a polygon layer for the dataset DST_Circle.

You can see that I have used the marker type as PushPin for the DST_HT point layer and circles for the DST_TopHT point layer to differentiate both of them. It would be good to add a table also which will show the top stores and the distance.

11) Now preview the report and you should be able to see the top N stores nearest to your selected hangout.

You can change the selections or the top count value and see that your report changes accordingly. You will also notice that the Bing Maps layer is data aware and centres / zooms dynamically based on the data.

You can do much more like visualizing the colour of your points based on the distance or any other measure in your warehouse, which will be helpful in making a decision. For eg, there might be a store which is not the closest but is running a sale as shown in the image below.

Risk Matrix Chart in SSRS

Of recent, I have been getting mails from my readers whether I have forsaken Reporting Services for Analysis Services in my blog. I want to reassure all of you that this is not the case and that I will be equally focussing on both the technologies. Just that there is a lot of development happening in the AS side and it is very important for all of us to keep on expanding our skillsets and building our expertise. That said, I am writing this post on how to create a Risk Matrix chart in SSRS for all of you guys as a new year gift from my side.

A risk matrix chart, as the name suggests, is used for performing risk analysis. Typically, we combine the Likelihood and Impact ratings of an event to arrive upon a risk score, which aids in deciding on what action to take in view of the overall risk. For the purpose of this post, I have just got 3 levels for the Impact and Likelihood (and this can be increased/decreased as per your requirement). For both Likelihood and Impact, a rating of 1 means Low, 2 means Medium and 3 means High. Now I have categorized the risk score into Low, Medium, High & Critical and would like to plot my events as a scatter chart against this. For that, follow the steps below:-

1) Create an image with the required risk scores in excel / PowerPoint / Paint. I came up with the following image.

This will server as the background image of our scatter chart. Add this image to the report.

2) Create a report with the required data sources and dataset. In this case, I have just made a sample dataset

SELECT     'Project A' AS Project, 0.5 AS Likelihood, 0.8 AS Impact
UNION ALL
SELECT     'Project B' AS Project, 0.8 AS Likelihood, 1.3 AS Impact
UNION ALL
SELECT     'Project C' AS Project, 0.9 AS Likelihood, 2.5 AS Impact
UNION ALL
SELECT     'Project D' AS Project, 1.4 AS Likelihood, 0.9 AS Impact
UNION ALL
SELECT     'Project E' AS Project, 1.5 AS Likelihood, 1.5 AS Impact
UNION ALL
SELECT     'Project F' AS Project, 1.2 AS Likelihood, 2.9 AS Impact
UNION ALL
SELECT     'Project G' AS Project, 2.1 AS Likelihood, 0.2 AS Impact
UNION ALL
SELECT     'Project H' AS Project, 2.4 AS Likelihood, 1.4 AS Impact
UNION ALL
SELECT     'Project I' AS Project, 2.7 AS Likelihood, 2.9 AS Impact
UNION ALL
SELECT     'Project J' AS Project, 1.6 AS Likelihood, 1.2 AS Impact
UNION ALL
SELECT     'Project K' AS Project, 2.2 AS Likelihood, 2.2 AS Impact
UNION ALL
SELECT     'Project L' AS Project, 1.1 AS Likelihood, 0.7 AS Impact

3) Go to the toolbox and drag and drop a chart item into the report body. Select the chart type as Scatter chart and click on OK.

 

4) Drag and drop Likelihood from the dataset fields list into the Values and then select Impact as the X Value in the chart. Also drop Project field into the Category Group of the chart.

 

5) Change the Axis titles to Likelihood and Impact for the Y and X axis respectively. Then go to both of the axis properties, and set the minimum as 0, maximum as 3 and Interval as 1.

6) Now click on the chart area and add the RiskMatrix image as the background image.

You might also want to hide the major gridlines for the horizontal and vertical axis.

7) Now with a bit of cosmetic changes to your marker colours, we arrive upon the end result. It is a good practice to enable the tooltips on the points, so that we can just hover our mouse to find out the project names.

The beauty of this approach is that it can be extended to a lot of other scenarios. For eg, I remember using this approach to answer a scatter chart with four quadrants question in the MSDN forum.

Watermark printing in SSRS report

Watermark printing in SSRS report

27DEC

We can print watermark in SSRS report as I have mentioned in the following any one approach,

1. Background Image Approach:

1.1 Create an image for water mark as “This is sample”.
1.2 Set this image as a Background image on Body [or header/footer] of the report.
1.3 Set the background for all cells/controls/or tables on the grid to “Transparent” so that the watermark image would show through and those colored items would overprint the image.

2. Text box Approach:

2.1 Create a text box with text as “This is sample”. Make if flat and keep border size to 0.
2.2 Place the text box at body or header or footer of the report
2.3 Set the background for all cells/controls/or tables on the grid to “Transparent” so that the watermark text would show through and those colored items would overprint the watermark.

3. Text box approach:

3.1 Create a text box with text as “This is sample”. Make if flat and keep border size to 0.
3.2 Make the background of this text box to transparent and set the z-order to top.
3.3 Place the text box at body or header or footer of the report

SQL Server – Custom sorting in ORDER BY clause

ORDER BY clause can be used to sort the results returned by SELECT statement in SQL Server. It orders the result set by specified column list. When used with character data type columns it sorts data in dictionary-order.

Sometimes, we need result set to be sorted in a custom order, for example, a specific value must appear at top of result set, and others can be sorted in standard order.

for example, consider following list of countries:

CountryName

AUSTRALIA

BANGLADESH

CHINA

FRANCE

INDIA

JAPAN

NEW ZEALAND

PAKISTAN

SRI LANKA

UNITED KINGDOM

UNITED STATES

Now based on the popularity you might need a country to appear on top of the list. In order to return results as required, we need to specify a custom sort order in ORDER BY clause. It can be used as below.

The following query will return result set ordered by CountryName, but INDIA at top and CHINA at 2nd position:

1 2 3 4 5 6 7 8 9

USE [SqlAndMe] GO   SELECT CountryName FROM   dbo.Country ORDER BY CASE WHEN CountryName = 'INDIA' THEN '1'               WHEN CountryName = 'CHINA' THEN '2'               ELSE CountryName END ASC GO

Result Set:

CountryName

INDIA

CHINA

AUSTRALIA

BANGLADESH

FRANCE

JAPAN

NEW ZEALAND

PAKISTAN

SRI LANKA

UNITED KINGDOMUNITED STATES

As you can see from the results above, both results are now in desired position, while remaining values are sorted in a standard order.

Another variation we can use to place only one row at top of result set is set it’s order to NULL, since NULLs appear first in ordered result set.

1 2 3 4 5 6 7 8

USE [SqlAndMe] GO   SELECT CountryName FROM   dbo.Country ORDER BY CASE WHEN CountryName = 'INDIA' THEN NULL               ELSE CountryName END ASC GO

Result Set:

CountryName

INDIA

AUSTRALIA

BANGLADESH

CHINA

FRANCE

JAPAN

NEW ZEALAND

PAKISTAN

SRI LANKA

UNITED KINGDOMUNITED STATES