Images and Barcodes -- SSRS

Images

Images are really straightforward. To put an image onto your report, right click Images in the Report Data field, and select Add Image. From here, browse to find your image that you want to add. Then click Ok.

From here, just drag the added file to the report. Resize and move it as needed. (You can even place it inside cells and the header or footer.

There are a couple ways to do images, and I will try to cover all of them

1         Right click on a cell -> Insert -> Image

a.       Find the image, embedded or external.

b.      The cell is now an Image cell.

c.       You cannot add a fill and the options in which to modify the cell are now limited. Keep this in mind if your reports require special formatting, and if so, you may want to use the method below.

2         Right click on a cell -> Properties -> Fill

a.       Find the image, embedded or external.

b.      Through this, you can still treat the cell as a cell. However, you don’t have any real control over the image itself.

 

Barcodes

Barcodes can be done a few different ways in SSRS, through VB code (difficult), or by changing the font. We will cover the latter.

1         Take your expression. =Fields!SOPNUMBE.Value

2         Trim it. =trim(Fields!SOPNUMBE.Value)

3         Add asterisks to it so that it is a readable barcode. ="*"+trim(Fields!SOPNUMBE.Value)+"*"

4         Change the font to the appropriate barcode font for the company.

Subreport basics

            Subreports aren’t a whole lot different in SSRS as they are from any other Reporting Service. For example, in SSRS, Subreports are created the same way normal reports are in your project folder. But, to implement them as subreports, you must

·        Right Click on a cell, Select Insert à Subreport.

·        Next, Right Click on the Subreport cell, and select Subreport Properties.

·        Here, you can change the name, whether it’s visible or not, and adjust its borders. But for now, click the dropdown box for “Use this Report as a Subreport.” Select the report you want to act as a Subreport for this cell.

·        You will also want to go to the Parameters tab. This is how you pass values from the original report to the subreports.

o   The flexible aspect of this is that the report only acts as a subreport for that specific cell. You can place a subreport anywhere inside a cell of a table or matrix.

Passing values to subreports

            Due to the way SSRS does parameters for subreports, it can cause a lot of headaches compared to Crystal.

1.      Take each value from your WHERE statement in your Subreport Query

2.      Add those tables, links, and values to your query in the report calling the subreport.

3.      Select the values and pass them in Subreport Properties.

4.      Test it to make sure the values are getting passed. Be sure to use correct case.

a.      Basically, if your subreport for any main report or otherwise has a WHERE Customer.id = @custid, that value needs to be in a dataset of your main report

            Note:  If you’re using a dropdown parameter list (or in general, another dataset to determine a parameter), you must find the value from clicking the Expression button on the Subreport Properties -> Parameters dialog box. Then, select Datasets and find the dataset with your parameter value. Get rid of the ‘=’ sign when you do this, or it will get angry.

Creating addresses for a single cell

Put this in your query

third.Address1 + CHAR(13) + ISNULL(third.Address2, '') + CHAR(10) + CHAR(13) + third.City + ', ' + third.State + ' ' + third.ZipCode + CHAR(10) + CHAR(13) + ISNULL(third.Company, '') + '  ' + ISNULL(third.Country, '')  + '  ' + ISNULL(third.Phone, '') AS ThirdPartyAddress

In this block of a query, we needed to simplify and crunch down the needed table cells to display a company’s full address. This is done by aliasing the entire table’s values to one address value, called ThirdPartyAddress. 

The + CHAR(10) + CHAR(13) + goes to a new line, so our address doesn’t run on and look messy .

You can also do it line by line and insert them as expressions like so

Name

Adr1

Adr2

City, State, Zip

etc. 

The beauty about SSRS is that on the surface it doesnt seem like it can do much compared to Crystal Reports in terms of formatting, and this may be true, but it is powerful and can replicate the formatting of many Crystal Reports. Just be mindful of the computers/Report Server/customer machines which can upset the formatting when deployed or ran.

Filters and Visibility

Filters and Visibility are two ways of filtering and hiding data you don’t need to show to the end user. Both are used in separate situations.

Visibility is a tab in any row, column, or tablix. It functions as, if the expression you give it evaluates to true, it hides that region.

Filters are used similarly, but with an Expression, which can be simply a value or more, select its datatype, its operator to filter with, and a value. Certain operators change the way the value will behave (BETWEEN gives you two values, obviously, to filter against)

Detailed below are some examples:

Operators that can be used as filter operators in SSRS are as follows, and most are self explanatory. (Note: Make sure your expression type and the value type match)

·   =

·   <>      

o   Does not equal.

·   LIKE

o   The same as the way it behaves in SQL in a WHERE clause.

o   *east

o   %o%th*

·   >, >=, < and <=

o   Works with dates, as well as numbers.

·   TOP N and BOTTOM N

o   As per the SQL usage with SELECT.

·   TOP % and BOTTOM %

o   As per the SQL usage with SELECT.

·   IN

o   The same as the way it behaves in SQL in a WHERE clause

o   Used to filter multiple values as an =.

·   BETWEEN

o   The same as the way it behaves in SQL in a WHERE clause

And finally, here are some examples I’ve used.

Filter Table Based on a Value(s)

1         Right click the Top left tab of the table and click Tablix Properties. Click on the Filters tab. In here you can filter your results based on expressions, and it’s really open ended, for example, I needed to filter my results based on a start and end date the customer provided. So with those are date/time parameters, I did this

a.       Expression: createdDate

b.      Operator: Between

c.       Value: =Iif(IsNothing(Parameters!startDateRange.Value), CDate("12/01/2000"), Parameters!startDateRange.Value)

d.      =Iif(IsNothing(Parameters!endDateRange.Value), Now(), Code.ConvertEndDate(Parameters!endDateRange.Value))

e.      Since createdDate specified exactly what I needed, I got the filter based off of whenever the transactions changed, which is exactly what the report was wanting to show.

Setting a Group/Row/Table’s Visibility Based on an Expression

1         Right click on the group/row/tablix and go to its specific properties. Click on the Visibility tab and select the radio button with “Show or Hide based on an expression: “. In here, put the expression that, if true, will hide the table/group/row.

2         =Fields!name.Value <> Previous(Fields!name.Value, "DataSet1") And Count(Fields!TurnTime.Value) = Previous(Count(Fields!TurnTime.Value), "DataSet1")

3         This hides extra rows if they would contain the same value. In addition, this was done over a count of appointmentId’s, grouped by Door #. It was very difficult to find the correct filter/expression to use as there were many ways to possibly get it to work.