12.
Select Add
Table from the context menu.
The Add
Table dialog appears.
13.
Using the SHIFT
key for multiple, non-contiguous selections, after the standard Windows
manner, highlight the following tables:
The
tables appear selected in the Add Table dialog as shown in Illustration
16.
14.
Click Add to
add the tables to the Diagram pane.
The Add
Tables dialog remains open, while the tables we have selected appear on the
Diagram pane, allowing us to make further choices. We will close it for
now.
15.
Click Close
to dismiss the Add Tables dialog.
The Add
Table dialog disappears, leaving the view of the Diagram pane with
the two added tables. One of the automatic joins (there are two in place)
needs to be deleted, before selecting fields for the dataset.
16.
Click the join
between the Currency.CurrencyCode and CurrencyRate.FromCurrencyCode fields,
to select it.
17.
Press the
[Delete] button to remove the join.
18.
Select the
columns listed in Table 1, by clicking the checkboxes in the associated
tables on the Diagram pane, in the order presented.
|
Table
|
|
Column
|
|
|
|
|
|
CurrencyRate
|
|
CurrencyRateDate
|
|
CurrencyRate
|
|
FromCurrencyCode
|
|
CurrencyRate
|
|
ToCurrencyCode
|
|
CurrencyRate
|
|
EndOfDayRate
|
|
Currency
|
|
Name
|
|
|
|
|
Table 1: Column Selections by Table
Our
selections appear in the Diagram pane as depicted in Illustration 17.
Illustration 17: Our Column
Selections (Compacted Table View)
We see
the column names appear in the Grid pane. We will next modify a couple
of the field names we have selected to make them a bit more useful.
19.
Click the Alias
box to the right of FromCurrencyCode (from the CurrencyRate
table) in the Grid Pane, to place the cursor there, as depicted in Illustration
18.
Illustration 18: Select
the Alias Box to Rename the Column ...
20.
Into the Alias
box, type the following:
Home Currency
21.
Type the
following into the Alias box for CurrencyRate. ToCurrencyCode column:
Foreign Currency
22.
Click the Criteria
box on the ToCurrencyCode row in the Grid Pane, just as we
did earlier for the Alias box, to place the cursor there.
23.
Into the Criteria
box, type the following:
GBP
24.
Type the
following into the Or... box, to the immediate right of, and on the same
row as, the Criteria box within which we placed the GBP filter in
the immediately preceding step, again on the ToCurrencyCode row:
EUR
NOTE: Ignore the notation changes that the grid makes for the present.
The relevant
portion of the Grid pane, with our Alias and Criteria additions,
appears as shown in Illustration 19.
Illustration 19: Alias and
Criteria Information, Grid Pane (Partial View)
25.
Execute the
query to test its operation, by clicking the Run ("!")
button.
The data
populates the Results pane, appearing similar to that depicted in Illustration
20. We will discuss the rate dates, circled in red, momentarily.
Illustration 20: Results
Dataset in the Results Pane (Partial View)
We can
see the SQL we have just created, in the SQL pane, beneath the Grid
pane. We often
simply input SQL in my articles, as this allows us to reach the intended main
subjects more rapidly, but, as we can see, the Query Builder makes it
easier for those of us who are not familiar with SQL to create queries without
having to learn the language first. The Query Builder is more than
adequate to support the creation of many business reports, so if you come
across any assertions that "Reporting Services requires report authors to
know SQL," you can discount the statement out of hand.
The more
SQL we have under our belts, the more likely we will be more efficient authors,
particularly in advanced query generation (some queries cannot be created through the Query
Builder, due to complexity or other complications. However, being fluent in SQL is certainly
not a prerequisite to creating robust and useful reports, as many of my
articles will demonstrate.
We now
have a final refinement to make to the query. The information consumers
specified that they need exchange rates as of the last day of each month.
The dates that we see in the results dataset, circled in red in Illustration
20 above, reflect numerous dates within the months whose data resides in
the database. We need to generate the exchange rates for the last day of the
months in the results set only. We will manage this with an addition to the WHERE
clause in the SQL, initially created when we specified the GBP and EUR
currencies above. To do this, we will use the AND Keyword, as we shall
see in the next step.
26.
In the SQL
pane, append the following expression to the existing SQL:
AND (CurrencyRate.CurrencyRateDate = CONVERT(CHAR, DATEADD(ss, - 1,
DATEADD(mm, DATEDIFF(mm, 0, CurrencyRate.CurrencyRateDate) + 1, 0)), 101))
The
complete query, with our adjustments, should appear as follows:
SELECT CurrencyRate.CurrencyRateDate, CurrencyRate.FromCurrencyCode AS [Home
Currency], CurrencyRate.ToCurrencyCode AS [Foreign Currency],
CurrencyRate.EndOfDayRate, Currency.Name
FROM Currency INNER JOIN
CurrencyRate ON Currency.CurrencyCode = CurrencyRate.ToCurrencyCode
WHERE (CurrencyRate.ToCurrencyCode = 'GBP') OR(CurrencyRate.ToCurrencyCode = 'EUR')
AND (CurrencyRate.CurrencyRateDate = CONVERT(CHAR, DATEADD(ss, - 1,
DATEADD(mm, DATEDIFF(mm, 0, CurrencyRate.CurrencyRateDate) + 1, 0)), 101))
The
purpose of the addition is to filter the results, once more, for the
data that is associated with the dates that reflect end-of-month dates.
The DATEADD function is useful for many such scenarios when working with
relational data in T-SQL, as are other components of the expression we have
added. Its purpose here is to generate the last day of the month for
any month that has dates appearing in the data (contained in the CurrencyRateDate
field).
The
function generates the last day of the month for a given CurrencyRateDate by
1) using DATEDIFF to compare the CurrencyRateDate with
01/01/1900, and then 2) returning the number of intervals that result, 3)
adding one month. It thus 4) delivers the first day of the month following the
CurrencyRateDate, whereupon it 5) subtracts one second to "roll
back the date to the day immediately preceding. The final result is the last
day of the month. This approach works regardless of the number of days
that are contained in the month of the CurrencyRateDate under
consideration.
27.
Execute the
query to see the modified results, by clicking the Run ("!")
button once again.
The dataset
we have generated now contains the ingredients for a report that meets the
expressed need of the information consumers. We see the currency rates
for the currencies for which the consumers have manifested interest, for
the end-of month dates for any month with rate data in the data
source. We are ready to begin constructing the report that will present this
data in a comparative line chart.
TIP:
When designing a report in Reporting Services, or any other
enterprise reporting application, always focus on the complete dataset first.
Making sure you have all the data that the report will require - before
beginning the physical construction of the report, and especially before
investing time in formatting and the like - can save the hours of rework that
is due upon those who have the "I'll come back to that later" mentality.
» 
Comment and Contribute
