MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart - Page 4
September 29, 2004
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.
Click for larger image
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 1: Column Selections by Table
Our selections appear in the Diagram pane as depicted in Illustration 17.
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.
20. Into the Alias box, type the following:
21. Type the following into the Alias box for CurrencyRate. ToCurrencyCode column:
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:
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:
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.
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.
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.