Category Archives: Reporting Services

Simple Field Level Formatting in SSRS

Posted on by
Reply

Sometimes

Sometimes, just having the right information in a report doesn’t completely fill the requirement. You can enrich the content by formatting items in your report using some simple formatting codes. Follow these steps:

- Right-click the field(s) you want to format
- Choose Properties & navigate to the Format tab. **
- Click on the ellipsis (…) on the right of the Format code text box.

Setting Formatting properties

Setting Formatting properties


- Click the Standard radio button and select a basic format from the list available – an example will be provided in the right-hand box.

Choose the required format

Choose the required format


If you need to further tweak the format, click on the Custom radio button and enter you can use the following codes to do so:

Formatting Codes and examples

Formatting Codes and examples

Example below:

Formatting a Date & time field

Formatting a Date & time field


** Or select the cell you want to format and then edit the Format property in the Property pane (usually located on the right of the design environment).


Report Builder – Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)

Posted on by
4

Another potentially easy one to fix. When you know how…………..

I received the error “Log on failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)” recently when trying to ‘run’ a report in Report Builder. From what I can gather, it has everything to do with the configuration settings for SQL Server Reporting Services.

Report Builder Logon Failure

Report Builder Logon Failure

Here are the steps to fix the issue:

  • Open the Reporting Services Configuration utility (Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > Reporting Services Configuration).
  • Connect to the Report Server Instance that you are working with.
  • Go to the Execution Account settings.
  • Either uncheck ‘Specify an Execution Account’ OR Specify an account that is to be used (in my case I used a Windows account and password – as my set up uses Windows Authentication only)

Setting the Execution Account for Report Server

Setting the Execution Account for Report Server

Try running the report in Report Builder again, it should work.

Create Your Own Reporting Services Templates

Posted on by
4

Haven’t I done this before?

As a Reporting Services Developer you may have found yourself completing the same tasks over and over for each new report that you create. To alleviate this somewhat, you may have just done a quick ‘Copy & Paste’ of an existing report, renamed it and then modified all the elements within it. The trouble with doing this is that there are always a number of properties/expressions that may have been used in the copied report that tend to be obscure (such as dynamic sorting on columns in a table) and you end up having to hunt down a number of errors and fix them one by one.

It is much better/easier/faster/less frustrating (& should probably be labelled under ‘Best Practices’) to create a number of SSRS templates of your own that you can use over and over again in each of your projects. It may be a simple template that includes a header & footer formatted to fit either landscape or portrait page layouts (optimally sized for printing), with text/backgrounds formatted to the way you want then. It may also be as complex as you like, adding in pre-formatted tables/matrices, charts, etc. I have found creating my own SSRS templates to be incredibly useful and highly recommend it. I have made two basic templates available here (right-click>save as…). It is a zip file containing two .RDL files, 1 Landscape template, 1 Portrait template. Unzip the file to the directory indicated in the steps below to have access to them through BIDS. Note – these are for SSRS 2005.

Here’s how to create your own templates:
1. Open or create a new project and create a new report item.
2. Design the report to your specification, as described above (& see Fig 1. below)

Fig 1. Standard Design Template

Fig 1. User Designed Template

3. Save the report
4. Navigate to the folder where the .rdl file (that has just been saved) is stored.
5. Cut/Copy the file to the following location (giving the file a useful template-friendly name):

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

This is the default location of the templates folder. If you have installed Reporting Services on a different drive/directory update these references to reflect the different Drive/Directory. Now, to access the report template from any project, go back to BIDS, right-click the ‘Reports’ folder (in the Solution Explorer) in your project and choose Add>New Item

Fig 2. Add New Item Menu

Fig 2. Add New Item Menu


and you will see your new templates under the heading ‘Visual Studio installed Templates’.

Fig 3. Select the New User Designed Template

Fig 3. Select the New User Designed Template

Centralized Style Sheets for your SSRS Reports

For those of you who would like to take the standardization of Report Design further, there is a great article over at SQL Server Central that describes how to set up Style Sheets for Reports. This means that if a change needs to be made to the design (formatting) of an entire suite of reports, then it can be done in one place and propagated across all of your reports. I highly recommend this article.

Have you found any other innovative ways of reducing the time it takes to produce reports?

Let’s hear from you.


The JOIN() Function in SSRS

Posted on by
14

BOL’s description of the JOIN() Function: “Returns a string created by joining a number of substrings contained in an array.”. The syntax of the function is as follows:

=JOIN(List [,delimiter])

List – Required. One-dimensional array containing substrings to be joined

Delimiter – Optional. String character used to separate substrings. If omitted, a space “ “ will be used.

So, the JOIN() Function concatenates an array’s values, using either a default space or the specified separator (for example, “, “) to separate the values.

What are the practical uses of this function as it relates to Reporting Services?

A common use of the function in Reporting Services is to display the values being used in a Multi-valued Parameter. You may for example have a report that has a parameter that filters Sales Regions. A requirement of the report is to display, in the header, the Sales Regions that the report is for. In the header of the report you would include the following expression:

=”Monthly Sales Report, for Regions:  ” & JOIN(Parameters!SalesRegion.Value, “, “)

Previewing this in the report with the following regions selected from the SalesRegion Multi-Value Parameter:

  • North America
  • South America
  • Southern Africa

Will result in the following Header string:

Monthly Sales Report, for Regions:   North America, South America, Southern Africa

I have mentioned this function here as I find it very useful and have used it on many occasions. The above use is only one of many, but for me, is the most common.

The CHOOSE() Function in SSRS

Posted on by
4

BOL’s description of the CHOOSE() Function: “Selects and returns a value from a list or arguments”. The syntax of the function is as follows:

=CHOOSE(index, expression_1 [, expression_2] [, expression_3] [, expression_4] …[, expression_n])

So, the Choose() function uses an Index (of type Double) to ‘choose’ one of a list of supplied values/expressions. For example;

=CHOOSE(Index, “Red”, “Yellow”, “Green”, “Grey”, “Black”, “White”)

When Index = 3, then the result is “Green”. When Index = 5, then the result is “Black”

Let’s look at a practical example:

I have a report (a simple chart at this stage) that relies on any one of a number of values to decide which metric to display – these values are provided to the user via a parameterized drop down list in the report, they simply choose which metric they want and the appropriate data is displayed. In the chart, I use an expression to decide on which metric is to be used based on the value returned by the parameter. Previously, I would have used an IIF() statement to do this, although I now have 7 values and this makes the IIF() statement far too ‘involved’.

Figure 1. shows the Metrics parameter and some of the values used:

Fig 1. Setting the Metrics Parameter Values

Fig 1. Setting the Metrics Parameter Values

The expression that is used in the chart (to get the data) is as follows:

=CHOOSE(Parameters!Metrics.Value, Fields!TotalItems.Value, Fields!OpenItems.Value, Fields!ClosedItems.Value, Fields!AveragePrice.Value, Fields!GrossProfit.Value)

Fig 2. Setting the Expression for the Data Values

Fig 2. Setting the Expression for the Data Values

When the user selects Total Items from the Parameter drop-down, a value of ‘1’ is passed to the CHOOSE() function which in turn selects Fields!TotalItems.Value as the data to use in the Chart Object. I have also used this expression to set the appropriate Series Label value.

.