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.

SSRS Report and Subreport Debugging Issues Debugged! pt 2

·        CSS Stylesheet Reporting: There are apparently no built in controls or options for doing faster formatting and CSS imitation formatting. Use this custom code as a template.

o   Function StyleColor(ByVal Style As String) As String

          Select Case UCase(Style)

            Case "HEADER"

                Return "LightBlue"

            Case "FOOTER"

                Return "SkyBlue"

            Case "MAINTITLE"

                Return "Purple"

            Case "SUBTITLE"

                Return "DarkBlue"

            Case Else

                Return "White"

        End Select

    End Function

·        Merging Cells: For some reason, SSRS doesn’t want you to be able to merge cells vertically. No clue why the designers did it this way, as it really impedes design.

·        Table Visibility: In the Text Box Properties, select Visibility. You can choose to show/hide the table on runtime, or when a condition is met. You can also configure this to individual columns and rows.

·        Control whether certain groups are shown for the user at runtime: Say your user doesn’t want to see certain groups. Make a parameter as a Boolean and have them choose obviously the True or False at runtime. Next, set each Group you want to show/hide based on the bool value’s visibility to iif(Parameter) = False.

·        Repeating a Column Header on additional pages: Select the cells you wish this to happen to, and go to the Properties box on the bottom right corner of the screen. KeepWithGroup should be set to After and RepeatOnNewPage should be set to True.

·        Hyperlinks: Text Box Properties à Action. In here, you can enable the text/cell as a hyperlink to a webpage, a bookmark, or a separate report.

·        Checkboxes: SSRS doesn’t support clickable checkboxes, for some reason. However, you can simply type one in, use a wingdings font, or import a checkbox image. None of these are particularly pleasing, but I’ve found just a simple [ ] works.

o   Note: The Report Server distorts your custom formatting slightly, therefore, a box might only be so big, or isn’t centered correctly. In this case, an image might be best.

o   Note: =iif(Fields!BookingMade.Value = "1",chr(0254),"o") creates a wingdings checkbox while using the Wingdings font in that cell/text box. ((From http://notethat.blogspot.com/2007/10/reporting-services-tips-tricks-and.html ))

Formatting Cells, Columns, and Rows in SSRS

            There’s a lot to cover here, but not a whole lot to say. But I’ll start with:

TextBox Properties

            Right clicking on a text box or tablix and selecting Properties brings us to where we want to start. 

General: Allows us to name our textboxes, give them a value or expression, and even add a tooltip for when the end user hovers over it. We also have the ability to “Allow height to increase/decrease” This becomes important when you have an image in the cell or need to hide things.

Number: Crystal Reports users will recognize this. This allows you to perform formatting on a number/string/date in the textbox. You can add, remove, or format data from your query with this tool. Such as removing the time off of a parameter that would normally display the date and time. 

            Alignment: Allows for more control over moving and padding text along.

            Font: Self explanatory. Allows you to play with the font, font colors, size, and effects. 

Border: To make your cells have borders, choose your choice of a style, solid, dotted, etc. Then, choose a width and color. Then use None or Outline to place the border. The Preview also allows you to play with taking away sides from the border. 

            Fill: Fill allows you obviously, fill the background of the cell with a color or image. 

Visibility: Probably the most used tab by me. Allows for you to write an expression on when to show or hide the cell. Expressions here are in the format of  “Hide if <<exp>> evaluates to true”. Such as LineNum = 5. Also allows for toggled visibility. 

Interactive Sorting: A really cool feature I’ve never used. Adding it to a cell allows you to sort ascending or descending at runtime by clicking an arrow put into the textbox. 

Action: Allows you to jump to another report, bookmark, or URL.

Report Properties and Format Menu Bars

            Found by clicking off of any report items in the Design region. The Report and Format menu items will appear on the menu bar. Click Report, and select Report Properties. 

Report Properties

            Page Setup

                        Sets your margins and printing options.

            Code

                        Where you write or paste your custom Visual Basic Code.

            References

Where you can add or remove assemblies or classes. I’ve not had to use this section yet.

Variables

            Where you can declare report variables. I’ve not had to use this yet. 

Format

            The Format menu bar is the same thing you see in most word processors. It helps your align, color, and fill text. There is also a toolbar that does the same thing.

Properties

            Report Properties

There’s a section section devoted to Report Properties in SSRS. This is in the Properties box in the lower right hand corner (by default). If it is not enabled you can renable it by clicking View -> Properties Window

Notable fields: 

Author: Set the report’s author. 

BackgroundImage: Sets the background in one or more cells. 

BorderColor, BorderStyle, BorderWidth: Much quicker than using the Border method above. 

Size: The easiest way to line up columns, especially when dealing with subreports. No one likes a messy reports with unaligned columns, so learning to copy paste from this section should become second nature pretty quick.

SSRS Report and Subreport Debugging Issues Debugged!

Excuse the awful formatting; this is from a Word Doc. 

Report isn’t compiling: Go to Project - <Project’s name> Properties. Change the Start Item to the main report, and apply your changes. The report will now compile correctly.

  

The value expression for the query parameter ‘@param1’ refers to a non-existing report parameter ‘assigned_value’. Letters in the names of parameters must use the correct case: This comes from spelling the assigned value or parameter wrong. It also will not compile correctly even when fixed.

o   I’ve found, in order to recognize that the change has been made, copy the query and delete the dataset.

o   Next, create a new dataset and paste the query back in, and the error will be gone

The value provided for the report parameter is not valid for its type.

o   If this error occurs, it is due to a bug with the Allow Blank Value checkbox in the parameters definition.

o   Open up the properties of the parameter, switch it to the default datatype of string, uncheck the checkbox, then switch back to your previous datatype.

Error: dataset or values are undefined/do not exist: This can be solved a couple different ways depending on the exact problem.

o   Value:

§  Re-add it in the query. It will clear the error.

§  The value was from a different dataset, or a dataset with a different name. Change out the old ‘name’ for the new one. Yes, it actually matters to SSRS.

o   Dataset: If you had to delete a dataset and redo it, it will throw errors that the old table was the one it wanted.

§  Also, see below

§  Recreate your dataset.

§  Or you can delete the values in the report throwing the error.

Compile Warning: Report item not linked to a dataset

o   Right click on the table, Tablix Properties.

o   Drop down Dataset Name:, and select your dataset.

Subreport Errors: The subreport “X” could not be found at the specified location. Please verify the subreport has been published. This is an incredibly misleading error, and could mean a lot of different things. The best way to troubleshoot this is to go to the subreport and preview it; then you will return errors that make sense. Usually it’s a missing value, or your parameters arent being passed correctly or at all, etc.