Page Comparison

...

Quick

...

Background

...

Sometimes,
...
it
...
isn't
...
possible
...
to
...
be
...
able
...
to
...
pre-define
...
your
...
query
...
as
...
a
...
resource
...
or
...
an
...
input
...
to
...
report.
...
Sometimes,
...
it
...
becomes
...
necessary
...
to
...
generate
...
your
...
query
...
(whether
...
it's
...
SQL,
...
MDX,
...
or
...
whatever)
...
using
...
a
...
programming
...
language
...
(such
...
as
...
javascript).
...
Examples
...
of
...
this
...
requirement
...
may
...
involve:
...
Based
...
on
...
user
...
inputs,
...
a
...
query
...
may
...
require
...
a
...
join
...
to
...
one
...
or
...
more
...
tables
...
If
...
there
...
are
...
several
...
optional
...
inputs
...
that
...
effect
...
the
...
way
...
the
...
query
...
is
...
constructed.
...

What

...

This

...

Tech

...

Tip

...

Demonstrates

...

This
...
tech
...
tip
...
goes
...
beyond
...
simply
...
demonstrating
...
building
...
a
...
dynamic
...
SQL
...
query
...
in
...
a
...
javascript
...
rule.
...
Here
...
are
...
the
...
highlights:
...
Dynamic
...
SQL
...
generation
...
Iterating
...
over
...
a
...
result-set
...
in
...
a
...
javascript
...
rule
...
to
...
get
...
the
...
column
...
title
...
based
...
on
...
user
...
input
...
Defining
...
a
...
message-field
...
for
...
the
...
column
...
name
...
in
...
a
...
report
...
Defining
...
a
...
message-field
...
for
...
the
...
selected
...
region
...
in
...
a
...
report.
...

Sample

...

Use

...

Case

...

Instead
...
of
...
just
...
coding
...
up
...
a
...
sample,
...
I
...
wanted
...
to
...
make
...
sure
...
I
...
had
...
a
...
reasonable
...
use
...
case
...
for
...
the
...
technical
...
tip.
...
The
...
use
...
case
...
was:
...
Gather
...
user
...
input.
...
Based
...
on
...
the
...
users'
...
input,
...
run
...
a
...
query
...
that
...
either
...
returns
...
information
...
on
...
departments,
...
or
...
information
...
on
...
position
...
titles.
...
Do
...
this
...
using
...
only
...
one
...
report
...
specification
...
and
...
one
...
action
...
sequence.
...

Setup

...

and

...

Requirements

...

Before
...
beginning
...
this
...
tech
...
tip,
...
please
...
make
...
sure
...
you
...
have
...
downloaded
...
and
...
installed
...
the
...
following:
...
Report
...
Design
...
Wizard
...
(RDW)
...
version
...
1.1.6
...
or
...
later.
...
Pentaho
...
Design
...
Studio
...
(PDS)
...
version
...
1.1.6
...
or
...
later.
...
Pentaho
...
Pre-configured
...
Installation
...
(PCI)
...
running
...
locally,
...
and
...
the
...
hypersonic
...
database
...
running
...
locally
...
with
...
the
...
SampleData
...
database,
...
version
...
1.1.6
...
or
...
later.
...

Query

...

Analysis

...

This

...

action

...

sequence

...

will

...

compose

...

the

...

following

...

queries:

...

If

...

the

...

user

...

selects

...

the

...

department

...

report,

...

the

...

query

...

will

...

look

...

like

...

this:

Panel

Wiki Markup
select {color:red}department{color} as column1, sum(actual) from quadrant_actuals where region = '{region}' group by {color:red}department{color} order by actual desc

If

...

the

...

user

...

selects

...

the

...

position

...

report,

...

the

...

query

...

will

...

look

...

like

...

this:

Panel

Wiki Markup
select {color:red}positiontitle{color} as column1,

 sum

 sum(actual) from quadrant_actuals where region = '{region}' group by {color:red}positiontitle{color} order by actual desc

Note

...

that

...

in

...

both

...

cases,

...

the

...

first

...

column

...

is

...

selected

...

and

...

renamed

...

as

...

column1.

...

This

...

is

...

an

...

important

...

thing

...

to

...

note

...

because

...

when

...

the

...

report

...

is

...

built

...

using

...

the

...

Report

...

Design

...

Wizard,

...

column1

...

will

...

be

...

the

...

column

...

that's

...

used

...

for

...

getting

...

at

...

the

...

data.

...

Building

...

the

...

Report

...

Using

...

the

...

Report

...

Design

...

Wizard

...

For
...
the
...
purposes
...
of
...
laying
...
out
...
a
...
quick
...
report,
...
it
...
doesn't
...
matter
...
which
...
query
...
we
...
choose
...
-
...
the
...
result
...
will
...
be
...
the
...
same.
...
The
...
following
...
screenshots
...
walk
...
you
...
through
...
the
...
steps
...
and
...
options
...
to
...
choose
...
in
...
the
...
Report
...
Design
...
Wizard.
...

Run
...
the
...
Report
...
Design
...
Wizard
...
(
...
execute
...
the
...
reportwizard.bat
...
|.sh
...
file).
...
(If
...
you
...
are
...
not
...
familiar
...
with
...
the
...
Report
...
Design
...
Wizard,
...
review
...
the
...
Report
...
Design
...
Wizard
...
User's
...
Guide
...
before
...
going
...
forward.)
...
Info
title Important Note
In Step 5, "Describing Column1",

you'll

note

that

the

display

name

was

set

to

$(columnTitle).

In

Step

5,

"Describing

Actual",

you'll

see

the

display

name

is

set

to

Actual.

This

is

the

only

change

we

will

make

to

this

report

definition.

Otherwise,
...
the
...
report
...
defaults
...
were
...
used
...
to
...
generate
...
this
...
simple
...
report.
...
STEP
...
1:
...
Report
...
Title
...
and
...
Description

Versions Compared

Old Version 2

New Version 3

Key

Quick

Background

What

This

Tech

Tip

Demonstrates

Sample

Use

Case

Setup

and

Requirements

Query

Analysis

Building

the

Report

Using

the

Report

Design

Wizard