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.