CHAPTER 12 DELIVERING BUSINESS INTELLIGENCE WITH SSRS
Figure 12-7. Output from graphical query builderLike the multidimensional view of the data, as you saw when you queried the same cube in the
Patient Referral cube browser, SSRS sees the data two-dimensionally, with rows and columns. An SSRS
report can access data from a cube; however, it is necessary to arrange the data two-dimensionally
because this is how the report will aggregate the data when it sums and totals the measured values, in
this case Time to Admission and Fact Referral Count. We have also applied a filter to the query so that
only the years from 2010 through 2012 will be selected.
As you may have noticed in the bottom left corner of Figure 12-7, you can use several buttons on the
toolbar to modify the properties of the query, such as showing empty cells or adding calculated
members. A calculated member is part of the MDX query that is created by combining one or more
elements into a value that can be used independently as a new element. You could, for example, create
calculated members to not only show the average time to admission for each dimensional element such
as BranchName and Year, but also show the minimum time to admission, using the MIN function, or the
maximum time to admission, using the MAX function. The calculated members would become part of
the overall query that could be used as new measures with data values returned at each dimensional
level. In other words, if you had to create a calculation at the report layer that wasn’t created as a
calculation in the cube itself, you could create a Calculated Member at the dataset layer of the report.
For those not skilled in writing MDX calculations, this is an easy way to create report level calculations.
With the graphical query built and working, let’s now look at the MDX that was created behind the
scenes. You can do this by clicking the Design Mode toolbar button. Figure 12-8 shows the MDX query
that returns the selected dimensional elements and measures for the report.