Share via


Supported Microsoft Access Report Features (SSRS)

When you import a report into Report Designer, the import process converts the Microsoft Access report into a Reporting Services Report Definition Language (RDL) file. Reporting Services supports several features of Access; however, because of differences between Access and Reporting Services, some items are modified slightly or are not supported. This topic describes how Access report features are converted to RDL.

Importing Access Reports

Some queries contain code that is specific to Access. Access code is not imported with the report. Also, if a query contains embedded strings, the report may not import correctly. To correct this, replace the strings with a character code. For example, replace the comma (,) character with CHAR(34).

The import process does not properly pass the semicolon (;) or XML markup characters (<, >, etc.) in connection string information. If a connection string contains a semicolon or XML markup character, you will have to manually set the password in the new report after the report is imported.

The import process does not import the connection or general timeout settings in the connection string. You may have to adjust these settings after the report is imported.

If you import a report that has a query that contains query parameters, the query will not be converted when the report is imported. To import the query with the report, temporarily replace the query parameters in the Access report with hard-coded values, and then replace them with query parameters after the report is imported.

Page Layout

Page layout in Access is different than in Reporting Services. Access arranges items on the page using "bands," that is, sections arranged vertically on the page. These sections may include the report header, report footer, page header, page footer, groups, and detail. Reporting Services provides a more flexible layout. Data regions provide grouping and detail, and you can place multiple data regions anywhere in the body of the report, even side-by-side. Reporting Services also includes a "banded" page header and footer, similar to the page header and footer in Access.

When a report is imported from Access into Report Designer, the page header and footer from the Access report are converted into a Reporting Services report page header and footer. Groups and detail are converted into a list data region. The report header and footer are placed into the body of the report, rather than in a separate band. This may result in item placement that is slightly different than what is in the Access report.

Note

In some Access reports, report items that appear to be adjacent to each other may actually overlap. When the report is imported using Report Designer, this overlap is not corrected and may lead to unexpected results when the report is run.

Data Sources

Reporting Services supports OLE DB data sources, such as SQL Server. If you are importing reports from an Access project (.adp) file, the connection string for the data source is taken from the connection string in the .adp file. If you are importing reports from an Access database (.mdb or .accdb) file, the connection string may point to the Access database and you may have to correct it after the reports are imported. If the data source for the Access report is a query, the query information is stored without modification in the RDL. If the data source for the Access report is a table, the conversion process creates a query based on the table name and the fields in the table.

Reports with Custom Modules

If there is custom Microsoft Visual Basic code contained within modules, it is not converted. If Report Designer encounters code during the import process, a warning is generated and displayed in the Task List window.

Report Controls

Reporting Services supports the following Access controls and includes them in converted report definitions.

Image

Label

Line

Rectangle

SubForm

SubReport

Note   While a SubReport control is converted within the main report, the subreport itself is converted separately.

TextBox

Reporting Services does not support the following controls:

BoundObjectFrame

CheckBox

ComboBox

CommandButton

CustomControl

ListBox

ObjectFrame

OptionButton

TabControl

ToggleButton

If Report Designer encounters any of these controls during the import process, a warning is generated and displayed in the Task List window.

Other controls, like ActiveX and Office Web Components, are not imported. For example, if an Access report contains an OWC Chart control, it will not be converted when the report is imported.

Report Properties

Reporting Services supports the following properties, which are available through the Access user interface. Properties available only in code are not supported and are not listed here.

BackColor

BackStyle

BorderColor

BorderStyle

BorderWidth

BottomMargin

CanGrow (textbox)

CanShrink (textbox)

Caption

FontBold

FontItalic

FontName

FontSize

FontUnderline

FontWeight

ForceNewPage

ForeColor

Height

HideDuplicates

Hyperlink

IsHyperlink

IsVisible

KeepTogether (group)

Left

LeftMargin

LineSlant

LineSpacing

LinkChildFields

LinkMasterFields

NewRowOrCol

PageFooter

PageHeader

Pages

Picture

PictureTiling (report)

ReadingOrder

RepeatSection

RightMargin

RunningSum

SizeMode

TextAlign

Top

TopMargin

Width

Reporting Services does not support the following properties, which are available through the Access user interface.

CanGrow (section)

CanShrink (section)

DecimalPlaces

FastLaserPrinting

Filter

FilterOn

Format

FormatConditions

GrpKeepTogether

KeepTogether (section)

NumeralShapes

Orientation

PaintPalette

PaletteSource

PictureAlignment

PicturePages

PictureSizeMode

PictureTiling (image)

ScrollBars

SpecialEffect

Vertical

Grouping

Access defines a group level using a combination of three properties: the group expression, the GroupOn property, and the GroupInterval property. A group that does not have a group header or footer is merged with the group contained within it. If the group does not contain another group, sorting is applied to the detail section and the group is dropped.

Expressions

Access uses expressions to specify values that appear in text boxes. Access uses Visual Basic as its expression language in addition to some aggregate functions. Report Designer converts these Access expressions to report expressions.

Functions

A Reporting Services report definition uses Visual Basic .NET as its native expression language, while Access 2002 uses Visual Basic. The following lists describe the functions that are supported by Reporting Services.

Array Functions

Reporting Services supports the following array functions:

  • LBound

  • UBound

Conversion Functions

Reporting Services supports the following conversion functions.

Asc

CBool

CByte

CCur

CDate

CDbl

CDec

Chr

Chr$

CInt

CLng

CSng

CStr

CVar

CVDate

Format

FormatCurrency

FormatDateTime

FormatNumber

FormatPercent

Hex

Hex$

Nz

Oct

Oct$

Str

Str$

StrConv

Val

Reporting Services does not support the following conversion functions:

  • GUIDFromString

  • StringFromGUID

Database Functions

Reporting Services supports the following database functions.

CreateReport

GetObject

HyperlinkPart

Partition

Reporting Services does not support the following database functions.

CodeDb

CreateControl

CreateForm

CreateGroupLevel

CreateObject

CreateReportControl

CurrentDb

CurrentUser

DeleteControl

DeleteReportControl

Eval

IMEStatus

SysCmd

Date/Time Functions

Reporting Services supports the following date/time functions.

Date

Date$

DateAdd

DateDiff

DatePart

DateSerial

DateValue

Day

Hour

Minute

Month

MonthName

Now

Second

Time

Time$

Timer

TimeSerial

TimeValue

Weekday

WeekdayName

Year

DDE/OLE Functions

Reporting Services does not support the following DDE/OLE functions.

DDE

DDEIntitate

DDERequest

DDESend

LoadPicture

Domain Aggregate Functions

Reporting Services does not support the following domain aggregate functions.

DAvg

DCount

DFirst

DLast

DLookup

DMax

DMin

DStDev

DStDevP

DSum

DVar

DVarP

Error Handling Functions

Reporting Services supports the following error handling functions.

Err

Error

Error$

IsError

Reporting Services does not support the following error handling function:

  • CVErr

Financial Functions

Reporting Services supports the following financial functions.

DDB

FV

IPmt

IRR

MIRR

NPer

NPV

Pmt

PPmt

PV

Rate

SLN

SYD

Interaction Functions

Reporting Services supports the following interaction functions.

Command

Command$

CurDir

CurDir$

DeleteSetting

Dir

Dir$

Environ

Environ$

EOF

FileAttr

FileDateTime

FileLen

FreeFile

GetAllSettings

GetAttr

GetSetting

Loc

LOF

QBColor

RGB

SaveSetting

Seek

SetAttr

Shell

Spc

Tab

Reporting Services does not support the following interaction functions.

DoEvents

In

Input

Input$

Inspection Functions

Reporting Services supports the following inspection functions.

IsArray

IsDate

IsEmpty

IsError

IsNull

IsNumeric

IsObject

TypeName

VarType

Reporting Services does not support the following inspection function:

  • IsMissing

Math Functions

Reporting Services supports the following math functions.

Abs

Atn

Cos

Exp

Fix

Int

Log

Rnd

Round

Sgn

Sin

Sqr

Tan

Message Functions

Reporting Services does not support the following message functions.

InputBox

InputBox$

MsgBox

Program Flow Functions

Reporting Services supports the following program flow functions.

Choose

IIf

Switch

SQL Aggregate Functions

Reporting Services supports the following SQL aggregate functions.

Avg

Count

Max

Min

StDev

StDevP

Sum

Var

VarP

Text Functions

Reporting Services supports the following text functions.

Format

Format$

InStr

InStrRev

LCase

LCase$

Left

Left$

Len

LTrim

LTrim$

Mid

Mid$

Replace

Right

Right$

RTrim

Space

Space$

StrComp

StrConv

String

String$

StrReverse

Trim

Trim$

UCase

UCase$

Constants

Access does not support special Visual Basic constants (for example, vbTrue) in expressions, so no conversion is necessary. However, there is one exception: the keyword Null is converted to System.DbNull.Value.

Parameters

During the import process, Report Designer scans each expression within a report for variables that do not correspond to field names or controls. These variables are added to report parameters.

The data type for stored procedure parameters is always imported as string. After the report is imported, you must manually change the parameter to use the correct data type.

Object Names

Access allows fields to have the same name as controls; Reporting Services does not. Visual Basic 6.0 allows spaces in variable names; Visual Basic .NET does not. The import process replaces the names of all such objects with valid names and assigns unique names if more than one object has the same name. Each expression is scanned and the names of variables that correspond to renamed objects are replaced with the new names.

Rectangles and Containment

In a Reporting Services report definition, rectangles can contain other report items. Any rectangle larger than the report item and which overlaps more than 90 percent of its area becomes a container for the report item.

Bitmaps

All bitmaps that are embedded within a report are converted to .bmp format when the report is imported, regardless of their initial format. For example, if your report includes .jpg and .gif files, the resulting resources imported with the report are .bmp files. The bitmaps are stored as embedded images in the report. For information about Embedded Images, see Images (Report Builder and SSRS).

Other Considerations

In addition to the previous items, the following information applies to reports imported from Access:

  • Conditional formatting is not converted.

  • The description field in report properties in Access is not converted.