Index
Symbols
+ (addition) operator, 105
/ (division) operator, 105
= (equals) operator, 105
> (greater than) operator, 105
< (less than) operator, 105
* (multiplication) operator, 105
“” (quotation marks), SQL operators, 106
– (subtraction) operator, 105
% (wildcard) operator, 105
3D effects (charts), 226
A
accessing
collections, 293
protected folders, 296
Access Required to ConfigMgr Collection field (report request form Security section), 152
accounts
AD security user, creating, 44
network service, configuring, 16
Windows user account credentials (RPs), 49
chart values as parameters, passing, 239
hyperlinks, adding, 254
table cell values, adding, 245-246
AD (Active Directory), 319
security groups
applying security roles, 62-65
deleting permissions, 70
Inventory Report Reader security role, applying, 76, 325-326
Report Reader security role, applying, 75-76, 323-324
Software Updates Report Reader security role, applying, 76, 324-325
viewing permissions, 65
security user accounts, creating, 44
addition (+) operator, 105
Add New Item dialog, 168
Add Reference dialog, 297
Add Site System Roles Wizard, 45-46
AVG, 108
COUNT, 108
Adobe Reader XI installed, counting example, 128, 331-332
online content, 354
date and time, 109
common, 110
complete listing of website, 109
date parts, 109
datetime format, 111
date values, storing, 110
demonstration example, 128-129, 332-333
format, 109
online content, 354
query example, 110
defined, 107
listing of, 107
MAX, 108
SUM, 108
AI (Configuration Manager Asset Intelligence), 319
aliases
SQL queries
online content, 354
alternating row colors, 183-185
Alternating Row Colors field (report request form Table Details section), 151
AND operator, 105
applications, 319
APP-V (Microsoft Application Virtualization), 319
arithmetic operators, listing of, 105
automatic logon (SSRS website), 288
Chrome, 311
Firefox, 311
Internet Explorer, 310
Opera, 312
Yandex, 311
Available Mock-up of the Report field (report request form Report Information section), 149
AVG function, 108
B
background color
backing up custom reports, 160
bar charts, 220
BIDS (SQL Server Business Intelligence Development Studio), 319
BITS (Background Intelligent Transfer Service), 319
blank reports, troubleshooting, 313-315
building. See creating
C
CAS (central administration site), 319
Cascade Prompt field (report request form Parameters section), 149
cascade prompts, 210
displaying all computer names and system enclosure chassis types query demonstration, 129-130, 333-335
resources, 112
syntax, 111
all system names and last hardware scan date, converting last hardware scan date format, 130, 334-335
online content, 353
resources, 113
sample query, 113
syntax, 113
central administration site (CAS), 319
Chart Area Properties dialog, 226
Chart field (report request form Report Information section), 148
Chart Name field (report request form Charts section), 150
actions
chart values as parameters, passing, 239
adding to SSDT-BI reports, 221-226, 238, 341
charts, selecting, 222
data, adding, 224
previewing report data, 226
RBA reports, 316
sizing, 225
bar, 220
column, 220
Computer Hardware Information report, adding, 239, 343
3D effects, 226
color, 266
displaying labels outside charts, 229-231
labels, 228
previewing customizations, 229, 230
spacing between labels, 230, 232
doughnut, 221
labels
customizing, 228
displaying outside charts, 229-231
line, 220
online content, 356
Patch Compliance Progression report, adding, 238-239, 341
pie, 221
sizing, 225
Charts section (report request forms), 150
Chart Title field (report request form Charts section), 150
Chrome SSRS website automatic logon, 311
CI (configuration item), 319
classes
collection. See collections
hardware inventory
cycles, changing, 85
hard drive size changes from previous inventory cycle query, 85-86
online content, 352
overview, 84
software title query example, 85
SQL view name identifiers, 84
heartbeat discovery
cycles, changing, 83
query example, 83
software inventory
cycle, changing, 55
Internet Explorer versions query, 87
online content, 352
overview, 86
RBA functions with SQL views, 290
SQL views, listing of, 86
software metering
overview, 89
RBA functions with SQL views, 290
rules, creating, 90
SQL views, listing of, 89
software update
missing software updates query, 88-89
overview, 87
permissions, 57
point (SUP), 320
RBA functions with SQL views, 290
SQL views, listing of, 88
state messages
last enforcement message for software updates query, 91-92
online content, 352
overview, 91
topic types, listing of, website, 92
views, 91
status message
last site server backup query, 90-91
online content, 352
overview, 90
RBA functions with SQL views, 290
views, 90
CM (System Center Configuration Manager), 319
CM12 (System Center 2012 Configuration Manager), 319
CM12R (System Center 2012 R2 Configuration Manager), 319
accessing with RBA security role, 293
computer names within collection query, 93
online content, 352
RBA functions with SQL views, 290
RBA query performance
moving RBA into collection prompt, 308-309
non-RBA collection prompt, 308
RBA collection prompt, 308
views, 93
custom palettes based on values, 263-267, 284, 345-346
previewing, 266
table background color, 266-267
custom palettes cycling through color codes, 259-263, 284, 345-346
expression based on status values, 266
multiple same colored wedges, troubleshooting, 263
out-of-the-box palettes, 259-260
rows (reports)
Column Action field (report request forms Table Layout subsection), 151
column charts, 220
Column Data field (report request forms Table Layout subsection), 151
Column # field (report request forms Table Layout subsection), 151
columns
average number, returning, 108
largest number, returning, 108
naming in query results, 106
ResourceID, 116
total number of items, 108
total value, 108
Column Title field (report request forms Table Layout subsection), 151
combining SQL views. See joins
Computer Hardware Information Chart report, 356
Computer Hardware Information Prompt report, 356
Computer Hardware Information report
3D exploded pie chart, adding, 239, 343
charts, adding, 316
dataset, customizing, 238, 341-342
drillthrough series example, 283-284, 344-345
page sizes, customizing, 237, 341-342
ConfigMgr (System Center Configuration Manager), 319
Asset Intelligence (AI), 319
Reporting Services Point. See RPs
security group permissions
deleting, 70
viewing, 65
security roles
applying to security groups, 62-65
Security Roles node, opening, 52-53
SQL schema websites, 82
ConfigMgr RBA SQL queries
converting SQL queries to, 289-291
collection data, 290
current hardware data, 289
discovery data views, 289
history hardware data, 290
software data, 290
status message data, 290
configuration item (CI), 319
Configure Subscription field (report request form Security section), 152
configuring
SSMS default database, 96
SSRS
credentials, 22
network service accounts, 16-24
Report Server Status page, 16-17
connections
database servers with SSMS
creating, 94
consistency (reports)
printed versus screen layout, 142-144
all system names and last hardware scan date, converting last hardware scan date format, 130, 334-335
resources, 113
sample query, 113
syntax, 113
converting
SQL queries to RBA queries, 289-291
Computer Hardware Information query, 316, 348-349
Patch Compliance Progression query, 315
SSDT-BI reports to RBA reports
adding SrsResources.dll file, 295-296
Patch Compliance Progression report, 315
referencing SrsResources.dll file, 296-298
copying existing security roles, 52-54
COUNT function, 108
Adobe Reader XI installed, counting example, 128, 331-332
online content, 354
RBA prompts, 302
collection data, 290
converting SQL queries to, 289-291
current hardware data, 289
discovery data, 289
history hardware data, 290
software data, 290
status message data, 290
RBA templates, 303
Computer Hardware Information report, 196-197, 338-340
Patch Compliance Progression report, 196, 338-339
requirements, questioning, 156-157
SSDT-BI versus Report Builder, 163-164
charts information, 150
completed report based on request form example, 153-155
customizations information, 153
parameters information, 149
requirements, questioning, 156-157
table details information, 150-151
table layout information, 151
prerequisites, 44
site server database/database name verification, 46, 48
site system roles, 45
user accounts, selecting, 46, 48
validating installation, 51
Windows user account credentials, 49
existing roles, copying, 52-54
read-only auditor role, copying, 52
Security Roles node, opening, 52-53
software metering, rules, 90
SQL queries with aliases, 127, 330
subscriptions in SSRS, 277-278
body size width, 203
demonstration example, 236, 340-341
expanding width, monitoring, 202
headers/footers, 203
InteractiveSize property values, 201
report height size, 203
credentials (SSRS), configuring, 22
CSV (comma-separated values) file format, 145
current hardware inventory data views
RBA functions, 289
SQL views, listing of, 84
custom color palettes
based on values, 263-267, 284, 345-346
cycles through color codes, 259-263, 284, 345-346
Custom Fill Colors field (report request form Charts section), 150
Customization Required from the Default Template Report field (report request form Report Customizations section), 153
3D effects, 226
displaying labels outside charts, 229-231
labels, 228
previewing customizations, 229, 230
spacing between labels, 230, 232
color palette that cycles through provided colors, 259-263
hardware inventory cycles, 85
heartbeat discovery cycles, 83
default images, restoring, 75
hyperlinks for company website on, creating, 246-248
software inventory cycles, 55
custom reports
backing up, 160
process, handling, 146
Charts section, 150
completed report based on request form example, 153-155
Parameters section, 149
publishing, 155
Report Customizations section, 153
Report Information section, 147-149
requirements, questioning, 156-157
Table Details section, 150-151
Table Layout subsection, 151
reviewing, 159
subscriptions, 158
cycles, customizing
hardware inventory, 85
heartbeat discovery, 83
software inventory, 55
D
data
adding to charts, 224
collections. See collections
discovery
discovery options, 82
heartbeat discovery, 83
online content, 352
SQL views with RBA functions, 289
views, 82
hardware inventory
cycles, changing, 85
hard drive size changes from previous inventory cycle query, 85-86
online content, 352
overview, 84
software title query example, 85
SQL view name identifiers, 84
software inventory
cycle, changing, 55
Internet Explorer versions query, 87
online content, 352
overview, 86
RBA functions with SQL views, 290
SQL views, listing of, 86
software metering (SWM)
overview, 89
RBA functions with SQL views, 290
rules, creating, 90
SQL views, listing of, 89
software update
missing software updates query, 88-89
overview, 87
permissions, 57
point (SUP), 320
RBA functions with SQL views, 290
SQL views, listing of, 88
state messages
last enforcement message for software updates query, 91-92
online content, 352
overview, 91
topic types, listing of, website, 92
views, 91
status message
last site server backup query, 90-91
online content, 352
overview, 90
RBA functions with SQL views, 290
views, 90
stored in databases, 111
converting data types, 112-113
returned data NULL values, checking for, 114
translating data based on results, 111-112
data bars (reports), 177
databases
server connections with SSMS, 94-95
SQL Server Management Studio default, configuring, 96
stored data, transforming, 111
converting data types, 112-113
returned data NULL values, checking, 114
translating data based on results, 111-112
Dataset Properties dialog, 171, 298
datasets
Data Source Properties dialog, 170
data sources
uploaded reports on SSRS website, updating, 270-273
date and time functions, 109
common, 110
complete listing of website, 109
date parts, 109
datetime format, 111
date values, storing, 110
demonstration example, 128-129, 332-333
format, 109
online content, 354
query example, 110
DATEDIFF function, 110
DATEPART function, 110
datetime format, 111
day date part, 109
DCM (desired configuration management), 320
Default Value field (report request form Parameters section), 149
deleting
permissions from security groups, 70
reports, 159
Description field (report request form Report Information section), 148
designing reports, 140
consistent look and feel, 140-142
printed versus screen layout consistency, 142-144
body size width, 203
creating, 200-204, 236, 340-341
creating new reports from, 204-206
expanding width, monitoring, 202
headers/footers, 203
InteractiveSize property values, 201
online content, 356
RBA, creating, 303
report height size, 203
desired configuration management (DCM), 320
Detail Each Customization Required field (report request form Report Customizations section), 153
Detailed Computer Information report, creating, 242-244
detailed PC reports, 139
dialogs
Add New Item, 168
Add Reference, 297
Chart Area Properties, 226
Data Source Properties, 170
Expression, 234
Legend Properties, 226
Report Parameter Properties, 215
Report Properties, 296
Select Chart Type, 222
Series Properties, 234
Subreport Properties, 249
Text Box Properties, 184
discovery data
discovery options, 82
heartbeat discovery
cycles, changing, 83
query example, 83
online content, 352
SQL views with RBA functions, 289
views, 82
division (/) operator, 105
doughnut charts, 221
downloading SSDT-BI, 38
DP (distribution point), 320
Drillthrough field (report request form Table Details section), 151
Drillthrough on Chart Items field (report request form Charts section), 150
drillthroughs, 242
actions on table cell values, adding, 245-246
Computer Hardware Information series example, 283-284, 344-345
Patch Compliance Progression series example, 282-283, 343-344
report for drilling through, creating, 242-244
subreports, adding, 257
dropdown prompts (reports), adding, 213-217
E
editing. See customizing
email subscriptions, configuring, 24-26, 279-281
enabling
3D effects (charts), 226
SSDT-BI ruler, 174
Enhansoft Logo Changer. See Logo Changer utility
equals (=) operator, 105
invalid object name error message (SQL Server Management Studio), 96-97
Microsoft Update errors, 32
permission uploading reports to SSRS website, 268
RBA
SrsResources not declared, 312-313
System.Security.Permissions .SecurityPermission permission request, 312
SSRS
complete listing example, 29
example, 27
remote errors, enabling, 26-29
subscriptions, 277
Excel
editing reports, 145
exporting reports to, 145
existing security roles, copying, 52-54
Exportable field (report request form Export section), 152
exporting
reports, 144
to CSV, 145
to Excel, 145
to MHTML, 145
request form information, 151-152
from SSRS, 271
to TIFF, 145
to Word, 146
to XML, 145
Export section (report request forms), 151-152
Expression dialog, 234
F
fallback status point (FSP), 320
fields
label, 209
report request form sections
Charts, 150
Parameters, 149
Report Customizations, 153
Table Layout, 151
value, 209
Firefox SSRS website automatic logon, 311
folders
protected, accessing, 296
reports, 161
RPs, 51
uploading reports on SSRS website, 268-270
footers (reports)
templates, 203
FQDN (fully qualified domain name), 320
FSP (fallback status point), 320
defined, 123
online content, 354
query
sample, 123
functions
aggregate. See aggregate functions
displaying all computer names and system enclosure chassis types query demonstration, 129-130, 333-335
resources, 112
syntax, 111
all system names and last hardware scan date, converting last hardware scan date format, 130, 334-335
online content, 353
resources, 113
sample query, 113
syntax, 113
all system names and last hardware scan date, converting last hardware scan date format, 130, 334-335
resources, 113
sample query, 113
syntax, 113
COUNT
Adobe Reader XI installed, counting example, 128, 331-332
online content, 354
date and time, 109
common, 110
complete listing of website, 109
date parts, 109
datetime format, 111
date values, storing, 110
demonstration example, 128-129, 332-333
format, 109
online content, 354
query example, 110
GetColor, 265
ISNULL, 114
replacing NULL values from v_R_System_Valid view, 130, 336
resources, 114
sample query, 114
syntax, 114
SQL 2008, 314
SQL 2008 R2, 314
G
gauges (reports), 177
GetColor function, 265
GETDATE function, 110
GPO (Group Policy Object), 312, 320
greater than (>) operator, 105
GROUP BY operator, 104
groups (security)
permissions
deleting, 70
viewing, 65
security roles, applying, 62-65
Inventory Report Reader, 76, 325-326
Software Updates Report Reader, 76, 324-325
H
hard drive size changes hardware inventory query, 85-86
hardware inventory
current data views
RBA functions, 289
SQL views, 84
cycles, changing, 85
history data views
RBA functions, 290
SQL views, 84
online content, 352
overview, 84
queries
hard drive size changes from previous inventory cycle, 85-86
software titles, 85
SQL view name identifiers, 84
headers (reports)
table header rows, repeating, 187-189
templates, 203
heartbeat discovery data
cycles, changing, 83
query example, 83
history hardware data SQL views
listing of, 84
RBA functions, 290
horizontal lines separating subreports, creating, 252-253
hour date part, 109
subreports, 258
table cells, creating, 254, 255
I
images, 177
default, restoring, 75
horizontal lines separating subreports, creating, 252-253
logos
default images, restoring, 75
hyperlinks for company website on, creating, 246-248
Logo Changer utility details, 72
Images field (report request form Report Customizations section), 153
importing security roles, 53, 60-62
Include Labels field (report request form Charts section), 150
Include Legend field (report request form Charts section), 150
Include Subreports field (report request form Report Customizations section), 153
indicators (reports), 177
inefficient queries
online content, 354
INNER JOIN statement, 119, 354
IN operator, 105
installing
prerequisites, 44
site server database/database name verification, 46, 48
site system roles, 45
user accounts, selecting, 46, 48
validating installation, 51
Windows user account credentials, 49
completing installation, 35-38
global rules, 32
install rules, 35
management tools features, 35-36
SSRS, 8
default SQL instance on Windows server, 8
features, selecting, 12
preparations, 8
results, 15
Intended Usage field (report request form Report Information section), 140
interactive sorting (reports), 184-187
Interactive Sorting field (report request form Table Details section), 151
Internet Explorer
SSRS website automatic logon, 310
versions software inventory query, 87
inventory
hardware
current data views, 84
cycles, changing, 85
hard drive size changes from previous inventory cycle query, 85-86
history data views, 84
overview, 84
software title query example, 85
SQL view name identifiers, 84
online content, 352
software
cycle, changing, 55
Internet Explorer versions query, 87
online content, 352
overview, 86
RBA functions with SQL views, 290
SQL views, listing of, 86
software metering
overview, 89
RBA functions with SQL views, 290
rules, creating, 90
SQL views, listing of, 89
software update
missing software updates query, 88-89
overview, 87
permissions, 57
point (SUP), 320
RBA functions with SQL views, 290
SQL views, listing of, 88
Inventory Report Reader security role, applying, 76, 325-326
ISNULL function, 114
replacing NULL values from v_R_System_Valid view, 130, 336
resources, 114
sample query, 114
syntax, 114
J-K
defined, 116
full
defined, 123
online content, 354
query sample, 123
ResourceID column, 116
sample query, 117
user versus computer real world example, 117
v_R_System_Valid view, 117-118
Justification field (report request form Report Customizations section), 153
L
label fields (reports), 209
labels (charts)
customizing, 228
displaying outside charts, 229-231
LAN (local area network), 320
layout consistency (reports), 142-144
LEFT OUTER JOIN statement, 120-121, 354
Legal Landscape template, 143
legal-size paper reports, 144
Legend Properties dialog, 226
legends (charts), moving, 226, 228
less than (<) operator, 105
Letter Landscape template, 143
letter paper sizes, 138
letter portrait (8.5x11in) report template, creating, 142, 200-204, 340-341
licensing (SQL client tools), 32, 33
LIKE operator, 105
line charts, 220
line items (reports), 176
aggregate functions sample query, creating, 331
alternating row colors, 183
CASE function
syntax, 111
CAST function sample query, 113, 335
CodeGroup entry for RSPreviewPolicy.Config file, 295-296
collection data computer names query, 93
color expression based on status values, 266
Computer Hardware Information report
RBA query, 349
WHERE statement, 283
COUNT function sample query, 331-332
custom color codes
by value, 265
date and time functions sample query, 333
exploded pie charts, adding
original status column, identifying, 224
replacement status column, 224
footers
dates, 192
page numbers, 193
hardware inventory queries
hard drive size changes from previous inventory cycle, 85-86
software titles, 85
heartbeat discovery query example, 83
ISNULL function sample query, 114, 336
joins
sample query, 117
pageLoad function
SQL 2008, 314
SQL 2008 R2, 314
Patch Compliance Progression Details report, 248-250
Patch Compliance Progression report
multi-value parameters, adding, 217-218
original WHERE section, collection specification, 210
simple prompt, adding, 210
patch progression query, 172
patch report query example, 100-101
RBA
datasets, creating, 298
prompt for manufacturer, 349
RBA queries
converting SQL queries to, 291
mixed RBA functions and SQL views, 306-307
moving RBA into collection prompt, 308-309
non-RBA collection prompt, 309
RBA collection prompt, 308
software update sample, 293-294
remote errors
complete error listing, 29
software
inventory queries, 87
metering start/stop times, 89-90
SQL queries
aggregate functions example, 107-108
column-naming, 106
date and time, 110
editing, 329
GROUP BY operator, 104
inefficient query, 131
ORDER BY section, 103
SELECT section, 101
WHERE section, 102
without joins example, 115-116
state message software update query, 91-92
status message last site server backup query, 90-91
example, 138
local area network (LAN), 320
Location of Mock-up field (report request form Report Information section), 149
logging on SSRS website
automatic, 288
Chrome, 311
Firefox, 311
Internet Explorer, 310
Opera, 312
Yandex, 311
logical operators, listing of, 105
default images, restoring, 75
technical details, 72
default images, restoring, 75
hyperlinks for company website on, creating, 246-248
Logo Changer utility
default images, restoring, 75
technical details, 72
look and feel (reports), 140-142
M
maintenance (reports), 159-160
MAN (metropolitan area network), 320
management point (MP), 320
maps (reports), 177
margin sizes (report templates), 201
Match Chart Colors field (report request form Table Details section), 151
matrix (reports), 177
MAX function, 108
messages
error. See errors
state
last enforcement for software updates query, 91-92
online content, 352
overview, 91
topic types, listing of, website, 92
views, 91
status
last site server backup query, 90-91
online content, 352
overview, 90
RBA functions with SQL views, 290
views, 90
metropolitan area network (MAN), 320
MHTML (web archive) file output, 145
Microsoft
Application Virtualization (APP-V), 319
Installer (MSI), 320
Update
errors, troubleshooting, 32
SSRS updates, checking, 8
Visual Studio projects, creating, 165-168
MIN function, 107
minute date part, 109
month date part, 109
MP (management point), 320
MSI (Microsoft Installer), 320
multiplication (*) operator, 105
multi-value parameters, 210, 217-219
N
Name field (report request form Parameters section), 149
navigating reports
drillthroughs, 242
actions on table cell values, adding, 245-246
Computer Hardware Information series example, 283-284, 344-345
Patch Compliance Progression series example, 282-283, 343-344
report for drilling through, creating, 242-244
subreports, adding, 257
hyperlinks
subreports, 258
table cells, creating, 254, 255
drillthroughs, 257
horizontal line separating, creating, 252-253
Knowledge Base hyperlinks, 258
list of missing patches for specific computer report, creating, 248-250
missing patches table, 257-258
network service accounts, 16
NOT operator, 105
NULL values
checking for, 114
replacing from v_R_System_Valid view, 130, 336
Number of Columns field (report request form Table Details section), 151
O
online content
charts, adding, 356
Computer Hardware Information Prompt report, 356
inventory data, 352
Patch Compliance Progression report, 356
report templates, 356
SSDT-BI reports, creating, 355
System Center Configuration Manager Reporting Unleashed website, 351
Opera SSRS website automatic logon, 312
Operations Manager SSRS security permissions, 44
listing of, 105
online content, 354
quotation marks, 106
website, 105
Optional Prompt field (report request form Parameters section), 149
organizing reports, 160
OR operator, 105
OU (organizational unit), 320
P
packages, 320
SQL 2008, 314
SQL 2008 R2, 314
Page Orientation field (report request form Report Information section), 148
page sizes (reports), customizing, 207, 237, 340-342
Paper Size field (report request form Report Information section), 148
paper sizes, 138
parameters, 209
cascade prompts, 210
default values, 209
demonstrating, 237
label fields, 209
value fields, 209
Parameters section (report request forms), 149
Patch Compliance Progression Chart report, 356
Patch Compliance Progression Details report, creating, 248-250
Patch Compliance Progression report
action on PC Name column cells, adding, 245-246
drillthrough series example, 282-283, 343-344
multi-value parameters, adding, 217-218
online content, 356
original WHERE section, collection specification, 210
page sizes, customizing, 237, 340-342
simple prompt, adding, 210
patch report query example, 100-101
performance (RBA queries), 305
execution times, 306
mixed RBA functions and SQL views, 306-307
moving RBA into collection prompt, 308-309
non-RBA collection prompt, 308
RBA collection prompt, 308
permissions
Inventory Report Reader security role, 76
Operations Manager with SSRS, 44
security groups
deleting, 70
viewing, 65
software updates, 57
System.Security.Permissions .SecurityPermission error request, 312
uploading reports to SSRS website, 268
pie charts, 221
pointers (reports), 176
previewing
color, 266
RBA reports in SSDT-BI, 303
Primary Data Exists Within ConfigMgr Console field (report request form Report Information section), 148-149
printing reports
from SSRS, 271
projects
creating, 195
creating in Microsoft Visual Studio, 165-168
publishing to SSRS website, 276
cascade, 210
collection
moving RBA into collection prompt, 308-309
non-RBA, 308
RBA collection prompt, 308
Patch Compliance Progression report, adding, 237-238
RBA
creating, 302
manufacturer, 349
Prompts field (report request form Report Information section), 140
Prompt Text field (report request form Parameters section), 149
Prompt Type field (report request form Parameters section), 149
Properties window (SSDT-BI), 175
protected folders, accessing, 296
proxy settings (RPs), creating, 46, 47
publishing
projects to SSRS website, 276
reports
request forms, 155
from SSDT-BI to SSRS website, 273-275
Q
queries
collection data computer names, 93
creating with aliases, 354
hardware inventory
hard drive size changes from previous inventory cycle, 85-86
software titles, 85
inefficient, 336
missing software updates, 88-89
RBA
collection data, 290
converting Computer Hardware Information query to, 316, 348-349
converting Patch Compliance Progression query to, 315
converting SQL queries to RBA queries, 289-291
current hardware data, 289
discovery data, 289
history hardware data, 290
performance. See RBA, query performance
software data views, 290
status message data views, 290
software
inventory, 87
metering start/stop times, 89-90
SQL. See SQL queries
state message software updates, 91-92
status message last site server backup, 90-91
quotation marks (“”) (SQL operators), 106
R
RB (Report Builder), 320
RBA (role-based administration), 320
converting SQL queries to
Computer Hardware Information query, 316, 348-349
Patch Compliance Progression query, 315
defined, 287
errors
SrsResources not declared, 312-313
System.Security.Permissions .SecurityPermission permission request, 312
prompts
creating, 302
manufacturer, 349
queries
collection data, 290
converting SQL queries to RBA queries, 289-291
current hardware data, 289
discovery data, 289
history hardware data, 290
software data, 290
status message data, 290
query performance, 305
execution times, 306
mixed RBA functions and SQL views, 306-307
moving RBA into collection prompt, 308-309
non-RBA collection prompt, 308
RBA collection prompt, 308
reports
charts, adding, 316
previewing in SSDT-BI, 303
security roles. See security roles
SSDT-BI report conversions
adding SrsResources.dll file, 295-296
Patch Compliance Progression report, 315
referencing SrsResources.dll file, 296-298
SSRS reporting, working together, 288
templates, creating, 303
RBAC (role-based access control), 320
RDL (Report Definition Language), 164, 320
read-only auditor security role, copying, 52
rectangles (reports), 177
remote errors, enabling, 26-29
repeating table header rows, 187-189
Replacing a Report field (report request form Report Information section), 148
Report Builder (RB), 320
Report Builder versus SSDT-BI, 163-164
Report Customizations section (report request forms), 153
Report Definition Language (RDL), 164, 320
Report Information section (report request forms), 147-149
reporting services points. See RPs
report items
accessing, 175
Report Parameter Properties dialog, 215
Report Properties dialog, 296
Report Reader security role, applying, 75-76, 323-324
reports
access restrictions, 57
backing up, 160
color
multiple same colored wedges, troubleshooting, 263
out-of-the-box palettes, 259-260
Computer Hardware Information
3D exploded pie chart, adding, 239, 343
charts, adding, 316
dataset, customizing, 238, 341-342
drillthrough series example, 283-284, 344-345
page sizes, customizing, 237, 341-342
Computer Hardware Information Prompt report, 356
creating
Computer Hardware Information report, 196-197, 338-340
Patch Compliance Progression report, 196, 338-339
requirements, questioning, 156-157
SSDT-BI versus Report Builder, 163-164
custom color palettes, 259-263
cycling through color codes, 284, 345-346
data bars, 177
deleting, 159
designing, 140
consistent look and feel, 140-142
printed versus screen layout consistency, 142-144
templates. See templates
Detailed Computer Information, creating, 242-244
detailed PC, 139
editing in Excel, 145
exporting, 144
to CSV, 145
to Excel, 145
to MHTML, 145
request form information, 151-152
from SSRS, 271
to TIFF, 145
to Word, 146
to XML, 145
folders, 161
footers
templates, 203
gauges, 177
headers
table header rows, repeating, 187-189
templates, 203
indicators, 177
legal-size paper, 144
line items, 176
maps, 177
matrix, 177
organizing, 160
paper sizes, 138
parameters, 209
cascade prompts, 210
default values, 209
demonstrating, 237
label fields, 209
value fields, 209
Patch Compliance Progression
action on PC Name column cells, adding, 245-246
drillthrough series example, 282-283, 343-344
multi-value parameters, adding, 217-218
online content, 356
original WHERE section, collection specification, 210
page sizes, customizing, 237, 340-342
simple prompt, adding, 210
Patch Compliance Progression Chart, 356
Patch Compliance Progression Details, creating, 248-250
printing from SSRS, 271
purpose, 136
RBA
charts, adding, 316
previewing in SSDT-BI, 303
report items
accessing, 175
chart details information, 150
completed report based on request form example, 153-155
customizations information, 153
parameters information, 149
publishing, 155
request process, handling, 146
requirements, questioning, 156-157
reviewing, 159
subscriptions, 158
table details information, 150-151
table layout information, 151
row colors
series samples, 136
sizes
pages, customizing, 207, 237, 340-342
troubleshooting, 209
Software Update Compliance, 263-267
SSDT-BI
alternating row colors, 183-185
Computer Hardware Information report, 196-197, 338-340
converting to RBA, 315
dropdown prompts, adding, 213-217
multi-value parameters, adding, 210, 217-219
page sizes, customizing, 237, 340-342
patch compliance progression report, 196, 338-339
publishing to SSRS website, 273-275, 285, 347
simple prompts, adding, 210-213
table header rows, repeating, 187-189
templates, creating, 236, 340-341
summarized data
example, 137
body size width, 203
creating, 200-204, 236, 340-341
creating new reports from, 204-206
expanding width, monitoring, 202
headers/footers, 203
InteractiveSize property values, 201
online content, 356
RBA, creating, 303
report height size, 203
uploading to SSRS website, 268-274
data source, updating, 270-273
new folders, creating, 268, 269-270
permissions, 268
Report Title field (report request form Report Information section), 148
Requestor field (report request form Report Information section), 148
Requestor’s Email Address field (report request form Report Information section), 148
Requestor’s Telephone Number field (report request form Report Information section), 148
Required Role-Based Administration (RBA) field (report request form Security section), 152
ResourceID column, 116
resources
date parts, 109
functions
CASE, 112
CAST, 113
CONVERT, 113
ISNULL, 114
online content
charts, adding, 356
Computer Hardware Information Prompt report, 356
inventory data, 352
Patch Compliance Progression report, 356
report templates, 356
SSDT-BI reports, creating, 355
System Center Configuration Manager Reporting Unleashed website, 351
SQL views, 82
state message topic types, 92
restoring default images, 75
Restrict Access to an AD Group field (report request form Security section), 152
restricting report access, 57
results (queries), column-naming, 106
reviewing custom reports, 159
RIGHT OUTER JOIN statement, 121-123, 354
role-based access control (RBAC), 320
role-based administration. See RBA
roles (security), 52
See also RBA
applying to security groups, 62-65
existing roles, copying, 52-54
Inventory Report Reader, 76, 325-326
read-only auditor role, copying, 52
report access restrictions with, 57
security group permissions
deleting, 70
Security Roles node, opening, 52-53
Software Updates Report Reader, 76, 324-325
viewing, 52
rows (reports)
table header, repeating, 187-189
RPs (Configuration Manager Reporting Services Point), 44, 320
site server database/database name verification, 46, 48
site system roles, 45
user accounts, selecting, 46, 48
Windows user account credentials, 49
folders, 51
health, checking, 51
prerequisites, 44
purposes, 44
validating, 51
ruler (SSDT-BI), 174
rules (software metering), 90
running SQL queries from SQL Server Management Studio, 125-126
S
SCCM (System Center Configuration Manager). See ConfigMgr
SCEP (System Center Endpoint Protection), 320
secondary sections (SQL queries), 103
GROUP BY operator, 104
second date part, 109
sections (SQL queries)
secondary, 103
GROUP BY, 104
SELECT, 101
WHERE, 102
security
AD user accounts, creating, 44
credentials (SSRS), 22
permissions
Inventory Report Reader security role, 76
Operations Manager with SSRS, 44
security groups. See security groups, permissions
software update reports, 57
System.Security.Permissions .SecurityPermission error request, 312
uploading reports to SSRS website, 268
deleting, 70
viewing, 65
security roles, applying, 62-65
Inventory Report Reader security role, 76, 325-326
Report Reader security role, 75-76, 323-324
security roles, adding, 63, 64
Software Updates Report Reader security role, 76, 324-325
users/groups, selecting, 62, 63
security identifier (SID), 320
security roles, 52
See also RBA
applying to security groups, 62-65
security roles, adding, 63, 64
users/groups, selecting, 62, 63
read-only auditor role, copying, 52
Security Roles node, opening, 52-53
Inventory Report Reader, 76, 325-326
RBA, accessing collections, 293
report access restrictions with, 57
security group permissions
deleting, 70
viewing, 65
Software Updates Report Reader, 76, 324-325
viewing, 52
Security Roles node, opening, 52-53
Security section (report request forms), 152-153
Select Chart Type dialog, 222
Select Export Formats field (report request form Export section), 152
SELECT statements, 101
Series Properties dialog, 234
servers, connecting with, 94-95
SID (security identifier), 320
simple prompts (reports), adding, 210-213
sizing
charts, 225
paper, 138
reports
existing page size, customizing, 207
height, 203
margins, 201
page sizes, customizing, 237, 340-342
troubleshooting, 209
SMS (Systems Management Server), 320
software
inventory
cycle, changing, 55
Internet Explorer version queries, 87
online content, 352
overview, 86
RBA functions with SQL views, 290
SQL views, listing of, 86
metering (SWM), 320
overview, 89
RBA functions with SQL views, 290
rules, creating, 90
SQL views, listing of, 89
title hardware inventory query, 85
updates
missing software updates query, 88-89
overview, 87
permissions, 57
point (SUP), 320
RBA functions with SQL views, 290
SQL views, listing of, 88
Software Update Compliance report, 263-267
Software Updates Report Reader security role, applying, 76, 324-325
sparklines (reports), 177
SQL (Structured Query Language), 320
client tools, installing, 31-38
completing installation, 35-38
global rules, 32
install rules, 35
management tools features, 35-36
operators
listing of, 105
online content, 354
quotation marks, 106
website, 105
queries. See SQL queries
schema websites, 82
views. See SQL views
aggregate functions. See aggregate functions
Computer Hardware Information query, 316, 348-349
Patch Compliance Progression query, 315
creating with aliases, 127, 330
data transform demonstrations
all system names and last hardware scan date, converting last hardware scan date format, 130, 334-335
displaying all computer names and system enclosure chassis types query, 129-130, 333-335
replacing NULL values from v_R_System_Valid view, 130, 336
date and time functions, 109
common, 110
complete listing of, website, 109
date parts, 109
datetime format, 111
date values, storing, 110
format, 109
online content, 354
query example, 110
inefficient queries, 131, 336-337
joins
defined, 116
INNER JOIN, 119
ResourceID column, 116
RGB color wheel example, 118
sample query, 117
user versus computer real world example, 117
v_R_System_Valid view, 117-118
results column names, 106
return data
data types, converting, 112-114
NULL values, checking for, 114
translating based on results, 111-112
running from SQL Server Management Studio, 125-126
secondary sections, 103
GROUP BY operator, 104
sections, 100
SELECT, 101
WHERE, 102
SQL Server 2014 Management Studio with administrative rights, 27
SQL Server Business Intelligence Development Studio (BIDS), 319
SQL Server Data Tools (SSDT), 320
SQL Server Data Tools Business Intelligence. See SSDT-BI
SQL Server Management Studio. See SSMS
SQL Server Reporting Services. See SSRS
collection data classes, 93
combining together. See joins
hardware inventory, 84
hardware inventory class name identifiers, 84
RBA functions
collection data, 290
current hardware, 289
current software, 290
discovery, 289
history hardware, 290
software metering, 290
software update, 290
status messages, 290
resources, 82
software
inventory, 86
metering, 89
updates, 88
state messages, 91
status messages, 90
SrsResources.dll file
SrsResources not declared error, 312-313
SSDT (SQL Server Data Tools), 320
SSDT-BI (SQL Server Data Tools Business Intelligence), 320
charts
adding to reports, 221-226, 238, 341
bar, 220
column, 220
doughnut, 221
line, 220
pie, 221
color options, 182
converting reports to RBA
adding SrsResources.dll file, 295-296
Patch Compliance Progression report, 315
referencing SrsResources.dll file, 296-298
downloading, 38
projects, creating, 195, 338-339
Properties window, 175
publishing projects to SSRS website, 276
RBA reports, previewing, 303
Report Builder, compared, 163-164
reports
alternating row colors, 183-185
Computer Hardware Information report, 196-197, 338-340
converting to RBA, 315
dropdown prompts, adding, 213-217
multi-value parameters, adding, 217-219
page sizes, customizing, 237, 340-342
patch compliance progression report, 196, 338-339
publishing to SSRS website, 273-275, 285, 347
simple prompts, adding, 210-213
table header rows, repeating, 187-189
templates, creating, 236, 340-341
ruler, enabling, 174
versions, 165
SQL Server Management Studio, 320
database server connections
creating, 94
default database, configuring, 96
invalid object name error message, 96-97
overview, 94
queries, executing, 95-96, 98, 326
SSRS (SQL Server Reporting Services), 320
configuring
Chrome, 311
credentials, 22
Firefox, 311
Internet Explorer, 310
Opera, 312
network service accounts, 16
Report Server Status page, 16-17
Yandex, 311
defined, 7
errors
complete listing example, 29
example, 27
remote errors, enabling, 26-29
installing, 8
default SQL instance on Windows server, 8
features, selecting, 12
preparations, 8
results, 15
Operations Manager security permissions, 44
reports. See SSRS reports
SQL client tools, installing, 31-38
completing installation, 35-38
global rules, 32
install rules, 35
management tools features, 35-36
updates, checking, 8
website. See SSRS website
blank, troubleshooting, 313-315
multiple same colored wedges, troubleshooting, 263
out-of-the-box palettes, 259-260
table background color, 266-267
custom color palettes, 259-263
based on values, 263-267, 284, 345-346
cycling through color codes, 284, 345-346
drillthroughs, 242
actions on table cell values, adding, 245-246
Computer Hardware Information series example, 283-284, 344-345
Patch Compliance Progression series example, 282-283, 343-344
report for drilling through, creating, 242-244
subreports, adding, 257
exporting, 271
hyperlinks
Knowledge Base, 258
printing, 271
drillthroughs, 257
horizontal line separating, creating, 252-253
list of missing patches for specific computer report, creating, 248-250
missing patches table, 257-258
subscriptions
error, 277
automatic logon, 288
Chrome, 311
Firefox, 311
Group Policy Object (GPO) setting, 312
Internet Explorer, 310
Opera, 312
Yandex, 311
blank reports, troubleshooting, 313-315
publishing projects to, 276
publishing SSDT-BI reports to, 273-275, 285, 347
RBA, working together, 288
reports, uploading to, 268-274
data source, updating, 270-273
new folders, creating, 268, 269-270
permissions, 268
subscriptions
error, 277
username and password prompts, 309
statements
demonstrating, 131
FULL JOIN
defined, 123
online content, 354
query sample, 123
INNER JOIN, 354
JOIN, 353
LEFT OUTER JOIN, 354
RIGHT OUTER JOIN, 354
SELECT, 101
WHERE, 102
state messages
last enforcement for software updates query, 91-92
online content, 352
overview, 91
topic types, listing of, website, 92
views, 91
status messages
last site server backup query, 90-91
online content, 352
overview, 90
RBA functions with SQL views, 290
views, 90
storing date values, 110
Structured Query Language. See SQL
Subreport Properties dialog, 249
subreports, 177
drillthroughs, 257
horizontal line separating, creating, 252-253
Knowledge Base hyperlinks, 258
list of missing patches for specific computer report, creating, 248-250
missing patches table, 257-258
Subscribers/Location field (report request form Security section), 152-153
Subscription Run Schedule field (report request form Security section), 152
subscriptions, 146
requested reports, 158
SSRS website reports
error, 277
Windows File Share subscriptions, 277-278
Subscription Type field (report request form Security section), 152
Subtitle with Prompt Details field (report request form Parameters section), 149
subtraction (–) operator, 105
SUM function, 108
summarized data reports
example, 137
SUP (software update point), 320
SWM (software metering), 320
overview, 89
RBA functions with SQL views, 290
rules, creating, 90
SQL views, listing of, 89
syntax of functions
CASE, 111
CAST, 113
CONVERT, 113
ISNULL, 114
System Center 2012 Configuration Manager (CM12), 319
System Center 2012 R2 Configuration Manager (CM12R), 319
System Center Configuration Manager. See ConfigMgr
System Center Configuration Manager Reporting Unleashed online content website, 351
System Center Endpoint Protection (SCEP), 320
System.Security.Permissions.SecurityPermission error request, 312
Systems Management Server (SMS), 320
T
Table Details section (report request forms), 150-151
Table field (report request form Report Information section), 148
Table Layout subsection (report request forms), 151
Table Name field (report request form Table Details section), 150
tables
actions on cell values, adding, 245-246
adding
cell hyperlinks, adding, 254, 255
header rows, repeating, 187-189
Table Title field (report request form Table Details section), 151
Table Will Provide More Details of Chart field (report request form Charts section), 150
body size width, 203
creating, 200-204, 236, 340-341
creating new reports from, 204-206
expanding width, monitoring, 202
headers/footers, 203
InteractiveSize property values, 201
online content, 356
RBA, creating, 303
report height size, 203
testing
uploaded reports on SSRS website, 271-274
textboxes (reports), 176
Text Box Properties dialog, 184
Ticket Number field (report request form Report Information section), 147
TIFF file format, 145
time. See date and time functions
tools. See utilities
Transact-SQL. See SQL
transforming stored database data, 111
converting data types, 112-113
returned data NULL values, checking, 114
translating data based on results, 111-112
troubleshooting
database server connections, 94-95
errors. See errors
multiple same colored wedges, 263
RBA
SrsResources not declared, 312-313
System.Security.Permissions .SecurityPermission permission request, 312
report sizes, 209
SSRS subscriptions, 277
T-SQL. See SQL
Type of Chart field (report request form Charts section), 150
types (data), converting, 112-113
U
Universal Coordinated Time (UTC), 110
updates
Microsoft Update errors, troubleshooting, 32
software
missing software updates query, 88-89
overview, 87
permissions, 57
point (SUP), 320
RBA functions with SQL views, 290
SQL views, listing of, 88
SSRS, checking for, 8
uploading reports to SSRS website, 268-274
data source, updating, 270-273
new folders, creating, 268-270
permissions, 268
UTC (Universal Coordinated Time), 110
Logo Changer
default images, restoring, 75
technical details, 72
SQL Server Management Studio
database server connections, 94-95
default database, configuring, 96
invalid object name error message, 96-97
overview, 94
queries, executing, 95-96, 98, 326
V
value fields (reports), 209
values
chart values as parameters, passing, 239
NULL
checking for, 114
replacing from v_R_System_Valid view, 130, 336
report parameters
default, 209
multi-value, 210
verification
RP installation, 51
versions (SSDT-BI), 165
views
collections, 93
discovery data, 82
hardware inventory
current, 84
history, 84
security
group permissions, 65
roles, 52
software
inventory, 86
metering, 89
updates, 88
SQL. See SQL views
SQL Server Management Studio, 98, 327-328
state messages, 91
status message data, 90
v_R_System_Valid view, 117-118
W
Wake on LAN (WOL), 321
WAN (wide area network), 320
web archive (MHTML) file output, 145
web service URL, configuring, 17-18
websites
CASE function resources, 112
CAST function resources, 113
ConfigMgr SQL schema, 82
CONVERT function resources, 113
date and time functions, listing of, 109
date parts, complete listing, 109
default SQL Server Management Studio database, 96
ISNULL function resources, 114
paper size comparisons, 138
SQL operators, listing of, 105
SSRS
automatic logon, 288
blank reports, troubleshooting, 313-315
Chrome automatic logon, 311
Firefox automatic logon, 311
Group Policy Object (GPO) automatic logon setting, 312
Internet Explorer automatic logon, 310
Opera automatic logon, 312
publishing projects to, 276
publishing SSDT-BI reports to, 273-275, 285, 347
subscription error, 277
subscriptions, creating, 277-278, 286, 348
uploading reports to, 285, 345-347
username and password prompts, 309
Windows FileShare subscriptions, 277-278
working with RBA, 288
Yandex automatic logon, 311
state message topic types, 92
System Center Configuration Manager Reporting Unleashed online content, 351
Wiki Glossary of Technology Acronyms website, 321
Word, exporting to, 146
WHERE statements, 102
wide area network (WAN), 320
width (reports), 202
Wiki Glossary of Technology Acronyms website, 321
wildcard (%) operator, 105
Windows
File Share subscriptions, creating, 277-278
Management Instrumentation (WMI), 320
Server Update Services (WSUS), 321
user account credentials (RPs), 49
WMI (Windows Management Instrumentation), 320
WOL (Wake on LAN), 321
Word, exporting reports to, 146
WQL (WMI Query Language), 321
writing reports. See creating, reports
WSUS (Windows Server Update Services), 321
X-Z
XML file output, 145
Yandex SSRS website automatic logon, 311
year date part, 109