Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 18, 2005

MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances - Page 6

By William Pearson

We return to the Values tab of the Edit Chart Value dialog.

The expression within the Value box, =Count(Fields!Store_Sales.Value), explains an occurrence we noted earlier: because this default expression is a Count, it produces a value of 1, in our case, for each of the Store Sales values (there is one Store Sales value per Store). This is the reason we get equally sized sections within our pie, and, while we will change this within the current context, it can be useful to leave it as it is, and to use another means of showing the actual values (such as the legend). If the "slices" are narrow / small, they may not afford us the room to add a lengthy label, such as the combination of Store Sales and Percent Total Sales values that we added to the Series label above.

We will select an alternative here, and present only the percentage value, but this is only one possibility. (A significantly larger chart region might make more data easily presentable, for instance).

21.  Click the Function (fx) button to the right of the Value box.

The Expression editor appears.

22.  Type (or cut and paste) the following into the Expression pane (the right half of the Edit Expression dialog):

=SUM(Fields!Store_Sales.Value)

The Expression pane of the dialog appears as depicted in Illustration 21.


Illustration 21: The Newly Completed Expression Pane

23.  Click OK to accept our input and close the Expression editor.

We return to the Values tab of the Edit Chart Value dialog.

24.  Click the Point Labels tab of the dialog.

25.  Place a check mark in the checkbox to the immediate left of Show point labels.

26.  Click the Function (fx) button to the right of the Data label box.

The Expression editor appears.

27.  Type (or cut and paste) the following into the Expression pane (the right half of the Edit Expression dialog):

=ROUND(((SUM(Fields!Store_Sales.Value)/ SUM(Fields!Store_Sales.Value, "ProductData")) * 100), 2)
& "%" &  ")" & vbcrlf &  ""

The Expression pane of the dialog appears as shown in Illustration 22.


Illustration 22: The Expression Pane with Our Input

Note that we have added an expression to generate a Percent of Total Sales (we would be challenged for room within the pie slices, as we shall see, for much more than the percentage). In this case, however, we are generating the percentage independently within the report - and not selecting the calculated member we created in the cube (in our last article) to generate this percentage.

We have once again encountered a scenario, similar to one that we came across in assembling our matrix in the last article, where we are confronted with a "layer" selection. While performance might be enhanced by selecting the calculated member in the cube, we are simply demonstrating that we have the option of generating it locally for purposes of our practice exercise here.

NOTE: For more information about the construction of the calculated member, as well as considerations surrounding the selection of the appropriate "layer" for placement of various components within an integrated business intelligence solution, see Mastering OLAP Reporting: Percent of Total - Two Perspectives.

28.  Click OK to accept our input and close the Expression editor.

We return to the Point Labels tab of the Edit Chart Value dialog.

29.  Click the Label Style button within the bottom half of the tab.

The Style Properties dialog opens.

30.  Select "8pt" in the Size selector on the upper right of the Font tab, as depicted in Illustration 23.


Illustration 23: Select "8pt" as Font Size ...

31.  Click OK to accept our input and close the Style Properties dialog, and to return to the Point Labels tab of the Edit Chart Value dialog.

The Point Labels tab appears as shown in Illustration 24.


Illustration 24: The Completed Point Labels Tab

32.  Click OK to return to the Data tab of the Chart Properties dialog.

33.  Click the single entry in the Category groups list, whose default title is chart1_CategoryGroup1, to select it.

34.  Click Edit to open the Grouping and Sorting Properties dialog.

35.  Click the Sorting tab.

36.  In the top line of the Expression selection list, select the following expression:

=Fields!Measures_Percent_Total_Sales.Value

37.  Select Descending in the corresponding Direction selector to the immediate right of the newly selected expression.

The Sorting tab appears, with our selections, as depicted in Illustration 25.


Illustration 25: The Completed Sorting Tab with Our Additions

38.  Click OK to accept changes and close the Sorting tab.

We return to the Data tab, where we will make a final cosmetic adjustment to our chart.

39.  Click the Legend tab.

40.  Ensuring that the checkbox to the left of Show Legend is checked, click the Legend Style button in the lower left corner of the Legend tab.

41.  In the Size selector of the Style Properties dialog that appears, select "8pt."

The Style Properties dialog appears as shown in Illustration 26.


Illustration 26: The Style Properties Dialog with Our Setting

42.  Click OK to accept changes and return to the Legend tab.

43.  Click the Data tab.

We return to the Data tab of the Chart Properties dialog, which now appears as depicted in Illustration 27.


Illustration 27: The Completed Data Tab

44.  Click OK to return to the Layout tab.

We are now ready to Preview the results of our handiwork.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM