header
sgray128 8/17/2015 8:42:29 PM

SSRS - Making Changes to an Existing Report, Part 2

This is Part 2 of our series on making changes to existing Dynamics GP report. Part 1 was a basic primer on how Report Builder works. In this article, we'll dive into the Expression Builder.

 

 

Version:
Section:


 Start by opening a standard report in SSRS. If you're not sure how to do that, we have a guide.

In the matrix area of the report (explained in Part 1) we see three types of content in the cells.

The first are labels. Labels are just plain text. You type in the cell, and that text is displayed in the report 

 

 Next we have fields. Hover over any cell and you'll see the Field Chooser icon. Click on the icon and you'll get a list of the fields that are available to you in the report. Note that if you're in a detail row, you'll get the plain field. But, if you're in a group field you'll get an aggregate of some sort. Numeric fields will SUM by default. String fields will display the FIRST value.

 Last we have expressions. In the report above we included one expressions, the screen shots below show how to access the expression builder and what it looks like for this field

 

Note that in this field we concatenate the Customer_Number field and the string ' Total'. There is a space before the word Total so that there is some space between it and the Customer_Number.

 

 In this next section will walk through all the possible options of the expression builder.

Set Expressions area

The Set Expression area is free text, you can type any value that you'd like. Report Builder is a little picky, thought, and it's sometimes easier to use the tools provided to enter values. Some areas are even case sensitive... good luck figuring that out when it won't take your input.

Expressions always start with  '='. If it doesn't have the '=', it's not treated as an expression, it's just plain text and won't be evaluated.

Constants

We'll cover this in a later tutorial

Built-in Fields

We frequently use PageNumber and TotalPages like this:

 

You might also put ExecutionTime on a report to show when it was run. ReportName is handy if you have several similar reports or lots of reports. Place it in the bottom border of the report to identify it after printing.

Parameters

 In the expression below, we concatenate the text 'Start Date ' and a formula. The formula (we'll cover this later in the Common Functions tab) takes the Start Date parameter and formats it as a Short Date, this strips off the time portion

 Fields and Datasets

The fields tab gives you access to the fields on the report. These need to be typed exactly, so it's usually easier to go to this tab and double click on them when you need them. The Fields tab will include the field selected, the dataset tab will include the field but will also wrap it in it's default aggregate. For now, just stick with the Fields tab.

 Variables

Only used in advanced reporting (even then, we rarely use them) 

Operators

The plus sign would be a mathematical operator, the ampersand is a string operator. We don't usually open this tab, it's pretty easy to type them but it wouldn't hurt to read through them and be sure that you understand what's possible

 

Common Functions

Lots of goodies here. This area contains useful functions for formatting and manipulating data. We showed the Format function above, in this window we show how to use the Absolute Value function, which strips the sign off of a numeric value.

 

 

 

 

 

 

 

 

 

 

 

4Penny.net
We make companies more profitable. Serving clients nationally, our services include database, financial, ERP, CRM and Web-based solutions.

Call us for a free evaluation of your company's technology needs.

941-74P-enny x2 (941-747-3669)
Contact Us