-
Notifications
You must be signed in to change notification settings - Fork 0
/
07-reports.qmd
332 lines (191 loc) · 13.6 KB
/
07-reports.qmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
# Reports {#sec-reports}
```{r, file = "R/webex.R", include = FALSE}
```
::: callout
**OBJECTIVES**
- For which you can use reports.
- How to create labels with the Wizard.
- Creating an automatically generated report with manual adjustments.
- Creating a grouped report.
- Creating a report with images of chocolates on labels.
:::
Reports are overviews that are usually meant for printing. Also, labels are a form of reports.
## About creating reports {#sec-reports-about}
Reports are usually overviews and summaries of large amounts of information.
If you want to print a sales report clearly on paper, then you use a report. In a report, you can also calculate subtotals and grand totals. You can create reports by hand, but it's easier to use the Wizard.
Reports can be dynamically by using parameters. When generating the report you will be prompted for additional information. As an example see report [Sales per box]{.uicontrol}. When opening this report you are asked to enter a start date and after that for an end date.
## Creating Labels {#sec-reports-labels}
How to create labels with the Wizard.
In this task, you are going to create address labels for the customers with the Label Wizard. First, you have to select the table with the necessary fields.
::: {#prp-reports-labels}
[File]{.smallcaps}: `{{< var database >}}`
1. Open the database.
2. Select table [Customers]{.uicontrol}.
3. Choose [tab Create \> Labels (group Reports]{.uicontrol}.
4. Select measures `Metric`, manufacturer `Avery` and then product `Avery L7160`, see @fig-label-wizard-avery7160.
{#fig-label-wizard-avery7160}
5. Click [Next]{.uicontrol}. In the screen that is displayed now, you can change font and color for the text.
6. Accept the default settings and click [Next]{.uicontrol}.
{#fig-label-wizard-fields}
::: {.callout-note}
By double-clicking on a field name, the field is inserted at the cursor location. You can also use button [\>]{.uicontrol}. The field name appears between brackets on the prototype label. Also, text and spaces can be typed. With the Enter button, you can create a new line.
:::
7. Create the following prototype label (with 1 space between the first and last name, and 2 spaces between zip code and city):
```{=html}
<!-- -->
```
{FirstName} {LastName}
{Address}
{ZipCode} {City}
8. Click op [Next]{.uicontrol}. In the screen that is displayed now you can specify if the labels should be sorted, and if so, by which fields.
9. There must be sorted by [ZipCode]{.varname}. Add this field and click [Next]{.uicontrol}. The last screen of the Wizard is displayed now. Here you can specify the name for the report.
10. Name the report [Labels Customers]{.varname} and click [Finish]{.uicontrol}.
{#fig-report-customers-printpreview}
11. Close the report.
:::
## Generated report {#sec-reports-autoreport}
Access can automatically generate a report based on a table or a query. Sometimes the generated report is sufficient, but mostly you need to make manual adjustments.
::: {#prp-reports-autoreport}
[File]{.smallcaps}: `{{< var database >}}`
1. Open the database.
2. Select query [Sales per region per box]{.uicontrol}.
3. Click [tab Create \> Report (group Reports)]{.uicontrol}. The report is generated and opened in [Layout View]{.uicontrol}.
It's not beautiful that the value of [Region]{.varname} is repeated for each record and that the Sales values are not properly formatted. In the following steps, this will be changed.
4. Close the report and answer the question to save the changes with [Yes]{.uicontrol}. The dialog box ***Save As*** is displayed.
5. Name the report [Sales per region per box]{.varname} and click [OK]{.uicontrol}.
6. Open the report [Sales per region per box]{.uicontrol} in [Design View]{.uicontrol}.
7. Select in section [Detail]{.uicontrol} the field [Region]{.uicontrol}. In the [Property Sheet]{.uicontrol} change the value of property [Hide Duplicates]{.uicontrol} in `Yes`.
::: {.callout-note}
The [Property Sheet]{.uicontrol} is on the right side of the screen and can be made visible and invisible by clicking on button [Property Sheet]{.uicontrol} or by using hotkey [F4]{.uicontrol}.
:::
8. Select in section [Detail]{.uicontrol} the field [Sales]{.varname}. In the [Property Sheet]{.uicontrol} set the value of property [Format]{.uicontrol} on `Currency`.
9. Switch to [Report View]{.uicontrol}. The value of field [Region]{.varname} is now displayed only once and the sales values are in currency format.
10. Close the report and save the changes.
:::
## Report with grouping {#sec-reports-grouping}
INFORMATION NEEDS
: Create a report which shows over a specified period the sales per box, as well as the details of each order. As an example, see @fig-group-report-result which shows part of the report for November 2009.
{#fig-group-report-result}
ANALYSIS
The required data are [BoxName]{.varname}, [OrderCode]{.varname}, [OrderDate]{.varname}, [Quantity]{.varname}, and a calculated field [Sales]{.varname} from the expression `[Quantity]*[BoxPrice]`. A query for this data is already available with the name [Sales per box per order per period]{.varname}.
::: {#prp-reports-grouping}
[File]{.smallcaps}: `{{< var database >}}`
1. Open the database.
2. Select query [Sales per box per order per period]{.varname}.
3. Choose [tab Create \> Report Wizard (group Reports)]{.uicontrol}.
{#fig-group-wizard-fields}
4. Add all query fields, then click [Next]{.uicontrol}. In the screen that is displayed now, you can specify if you want to add grouping levels.
5. Remove existing grouping levels ([OrderCode]{.varname}) and add [BoxName]{.varname} as grouping level
{#fig-group-wizard-levels}
6. Click [Next]{.uicontrol}. In the screen that is displayed now, you can specify the sort order.
7. Specify to sort ascending on [OrderCode]{.varname}.
{#fig-group-wizard-sort}
8. Click button [Summary Options...]{.uicontrol} and select that the Sum should be calculated for the fields [Quantity]{.varname} and [Sales]{.varname}.
{#fig-group-wizard-summary}
9. Click [OK]{.uicontrol} and after that [Next]{.uicontrol}. Now you can specify the layout of the report.
10. Select [Outline]{.uicontrol} layout.
{#fig-group-wizard-layout}
11. Click [Next]{.uicontrol}. The last screen of the Wizard is displayed now. Here you can specify the name for the report.
12. Name the report [Sales per box per order per period]{.varname} and click [Finish]{.uicontrol}.
The report is created and displayed in [Print Preview]{.uicontrol}. Because the query has parameters for the start and end date, you should enter values for these parameters.
13. Test with Start date `11/1/2009` and End date `11/30/2009`.
14. Close the report.
:::
## Praline Pictures {#sec-reports-pictures}
In this task, you create a report with pictures of the pralines and the code and name above it. For that, labels are used, and on each label the data of the praline.
::: {#prp-reports-pictures}
[File]{.smallcaps}: `{{< var database >}}`
1. Open the database.
2. Select table [Pralines]{.uicontrol}.
3. Choose [tab Create \> Labels (group Reports)]{.uicontrol}.
4. Select measures `Metric`, manufacturer `Zweckform` and then product `Zweckform 3415`.
{#fig-label-wizard-zweckform3415}
5. Click [Next]{.uicontrol}. In the screen that is displayed now, you can change font and color for the text.
6. Accept the default settings and click [Next]{.uicontrol}.
7. Create the following Prototype label ( with 1 space between the fields):
{PralineCode} {PralineName}
8. Click [Next]{.uicontrol}. Specify to sort by [PralineCode]{.varname}.
9. Click [Next]{.uicontrol}. Name the report [Labels Pralines]{.varname}.
10. Click [Finish]{.uicontrol}. The report is generated and displayed in [Print Preview]{.uicontrol}.
11. Switch to [Design View]{.uicontrol}.
12. Click op [tab Design \> Bound Object Frame (group Controls)]{.uicontrol}  and draw a rectangle of approximately 1 inch by 1 inch in the [Detail]{.uicontrol} section.
{#fig-label-praline-objectframe}
13. Make sure the frame remains selected, and then apply through the [Property Sheet]{.uicontrol} the following changes:
- In tab [Format]{.uicontrol}: set Width and Height on 1 inch. Access may adjusts the dimensions a little bit. That's no problem.
{#fig-label-praline-dimensions}
- In tab [Data]{.uicontrol}: specify for property [Control Source]{.uicontrol} field `Picture`.
{#fig-label-praline-controlsource}
14. Select the label which is located substantially behind the frame.
{#fig-label-praline-label}
15. Remove the label with the [Delete]{.uicontrol} key.
16. Switch to [Print Preview]{.uicontrol}.
It is now almost good. Only the images don't all start at the same height so that the representation is chaotic. The text of [PralineCode]{.varname} and [PralineName]{.varname} should get a fixed height so that all images are placed at the same height.
17. Switch to [Design View]{.uicontrol}, select the text box and set property [Height]{.uicontrol} on 0.4 inch. Set also the properties [Can Grow]{.uicontrol} and [Can Shrink]{.uicontrol} on `No`.
{#fig-label-praline-textbox}
18. Align text box and frame to the left.
{#fig-label-praline-alignment}
19. Switch to [Print Preview]{.uicontrol}.
{#fig-report-pralines-result}
20. Close the report and save the changes.
:::
## Exercises {#sec-reports-exercises}
::: {#exr-rapp001}
**Sales per region per box (rapp001)**
Create a report with the sales per region per box. The report should also show the total sales for each region and the percentage across all regions. Name the report [rapp001]{.varname}.
:::
::: {#exr-rapp002}
**Sales per box per region (rapp002)**
Create a report with per box the sales per region. Name the report [rapp002]{.varname}.
:::
::: {#exr-rapp003}
**Box and PralineCosts (rapp003)**
Create a report that shows all pralines in each box, together with the costs of these pralines. Also, display the sum of all praline costs. Name the report [rapp003]{.varname}.
:::
::: {#exr-rapp004}
**Box and Praline costs grouped (rapp004)**
Create a copy of the report [rapp003]{.varname} and name the copy [rapp004]{.varname}. Make the report more legible to print all data of a box on a separate page. This can be done by inserting a page break before the header of each group. You need to set the property Force New Page of the groupheader with the value Before Section.
Furthermore, make some small changes to the layout. The text box Sum should be moved to the right with only a small horizontal line above the sum.
::: {.content-visible when-format="html:js"}
`r hide("Tip")`
Every box is a group, so the group header in this report is named Box Header.
`r unhide()`
:::
:::
::: {#exr-rapp005}
**Sales per box per order per period (rapp005)**
Create a copy of the report [Sales per box per order per period]{.varname} and name the copy [rapp005]{.varname}. Change the design of the report so that the total amount per box is printed next to the name box.
:::
::: {#exr-rapp006}
**Box data on separated pages (rapp006)**
Create a copy of report the [rapp005]{.varname} and name the copy [rapp006]{.varname}. Change the design of report rapp006 so that each box starts on a new page.
::: {.content-visible when-format="html:js"}
`r hide("Tip")`
Set property [Force New Page]{.uicontrol} of the group header (BoxName Header) on `Before Section`.
`r unhide()`
:::
:::
::: {#exr-rapp007}
**Box Labels (rapp007)**
Create a report with labels for all boxes according to the following picture. Name the report [rapp007]{.varname}.
label type Avery J8163 1 1/2" x 3 9/10", Font Consolas 12pt normal black.
All Seasons
Code : ALLS
Weight: 150 gram
:::
::: {#exr-rapp008}
**Yearly sales per region (rapp008)**
A report based on a parameter query and grouping.
Create a report showing the number of boxes sold in a given year per region. Upon opening the report you should be asked for which year you want to see the report. Name the report [rapp008]{.varname}.
In the following picture, you see the beginning of the output for the year 2009.
::: {.content-visible when-format="html:js"}
`r hide("Tip")`
First create a parameter query [Yearly sales per region]{.varname} which provides the necessary data for the report and asks for the sales year.
`r unhide()`
:::
:::