Designing of reports

The integral function of any program systems anyhow connected with data processing, representation of vows on хранимой information is. The report traditionally is understood by special image as the structured representation хранимых the data, deduced (as a rule) on the paper carrier. We shall list basic differences of reports from the screen forms, caused their allocation in separate program object Access:

• In the first, reports are exclusively means of a conclusion of the information
• In the second, the data structure in reports assumes an opportunity of their complex, multilevel structurization
• In the third, the structure of the information deduced in the report, should be coordinated with structure of the carrier. For example, splitting of the report into pages assumes the organization of a conclusion of regular elements in the beginning and the end of each leaf duplication of caps of tables, etc. Also on appearance of the report significant influence is rendered with parameters of the concrete printer which will be used for its conclusion

At the same time, concerns to number of important advantages Access that ideology of work both with screen forms, and with reports as much as possible универсализирована. In particular, the interface of a mode of designing of a breadboard model of the report is similar to a mode of the designer for screen forms.

Let's consider ways of the decision of problems of development of reports which can arise within the limits of a program control system described by us of the tenders of securities. The elementary reports which, most likely, will be necessary for users of system is распечатанные lists of papers and agents. For their creation it is possible to take advantage of superstructures the Autoreport in a column or Автотчет tape. On fig. the breadboard model of the report on the agents, the Autoreport created in a mode tape is shown.

From fig. it is visible, that during designing in a breadboard model of the report the same operating elements can be added, as at designing a breadboard model of the screen form. At the same time it is necessary to note, that the structure of the report as object of a database has the specificity. First, it is defined by levels of a grouping of the data deduced in the report, and secondly, contains the section, corresponding the regular elements placed in the beginning and the end of each leaf(sheet) - to the top and bottom headlines. For the task of levels of a grouping of data function of the menu the Kind> Sorting and a grouping or the same pictogram on the panel of tools the Designer of reports is used.

At work with reports are actively used (it is visible from fig. 7.24) the built in variables [Page] and [Pages], returning number of current page of the report and the general, quantity of pages in it, and also function NowQ defining current date and time on a system calendar.

Let's stop now on more complex example. We shall set the task to construct the report deducing data on a supply and demand under securities in view of their type, that is records should be structured on following levels:

• All papers
• Type of a paper
• The agent
• Offers of the agent on the given paper

Also on each of levels it is desirable to provide a conclusion of intermediate results (or corresponding average values).

The information for the given report (we shall name it РаспределЗаявок) should undertake from various tables, therefore as a source of data for it expediently uses specially constructed inquiry. For presentation we shall result SQL the expression corresponding given inquiry:

SELECT
IIf([ТипБум]= "1", "Акции", "Облигации") AS Тип,
Заявки.КодБум,
Бумаги.НаимБум,
Бумаги.Номинал,
Агенты.НаимАг,
IIf ([0бъем3аявки]<0,-1*[0бъем3аявки],0) AS
ОбъемПродажи,
IIf ([ОбъемЗаявки]<0,[ЦенаЗаявки],0) AS ЦенаПродажи,
IIf ([0бъем3аявки]>0,[0бъем3аявки],0) AS ОбъемПокупки,
IIf ([ОбъемЗаявки]>0, [ЦенаЗаявки],0) AS ЦенаПокупки,
FROM Бумаги
INNER JOIN (Агенты INNER JOIN Заявки ON Агенты.КодАг = Заявки.КодАг) ON
КодБум = Заявки.КодБум
ORDER BY IIf ([ТипБум]="1", "Акции", "Облигации"),
Бумаги.НаимБум;

On the basis of the constructed inquiry it is possible to pass to development of the report. At the initial stage it is represented rational to take advantage of services of the master of reports. It(he) in a mode of dialogue with the user allows to create resembling "preparation", relieving us from many routine operations, for example such as addition water also signatures to them.
Further the received breadboard model manually "is possible" up to a desirable kind in a mode the Designer.

The important stage at creation of the multilevel report is the task of levels of a grouping of deduced data. It is done in a window shown on fig. which is caused from the menu the Kind> Sorting and a grouping. For each of the set levels of a grouping of data the Heading deduced in the beginning of each group, and section of type the Note, formed when the group comes to an end can be certain section of type.
Problems of reception средих and final values on groups of data are solved by means of built in functions Sum () and Avg (). For example/for receptions of average value of the price of sale of a paper in a corresponding element of management property Data contains a line =Avg ([ОбъемПродажи]), and for definition of final demand the formula is used

=Sum([ОбъемПродажи]* [ЦенаПродажи])

The author: Y.N.Antonov Is added: 21.06.2008