Wrox Programmer Forums
|
BOOK: Professional Microsoft SQL Server 2008 Reporting Services ISBN: 978-0-470-24201-8
This is the forum to discuss the Wrox book Professional Microsoft SQL Server 2008 Reporting Services by Paul Turley, Thiago Silva, Bryan C. Smith, Ken Withee; ISBN: 9780470242018
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional Microsoft SQL Server 2008 Reporting Services ISBN: 978-0-470-24201-8 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 25th, 2009, 07:17 PM
Authorized User
 
Join Date: Sep 2009
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Chapter 6 Example

I'm following the example starting on P.135. I am using Report Builder 2.0 version 10.0.1600.60 and SQL Server 2008 Developer Edition.

I notice that the dialogs shown in the book are different from what I am seeing in actual practice. For example, rather than a Data Source Properties dialogue on P. 135, I see Data Set Properties.

If I cancel that and click on New Data Source, that dialog also has differences.

Despite the differences, I am able to complete this example based on a general understanding. However, for future reference, is this the result of my version of RB being either more or less current than that used by the authors? I would like to get a handle on this as I proceed through other examples.

Also, I do not get the same results if I run the report that is created using the above example. My results show individual amounts presumably at the order level. I can get the same results shown on p. 144 if I modify the query to generate the sum of the sales amount, as follows:

Code:
Select CalendarYear, SalesTerritoryRegion, Sum(SalesAmount) AS SalesAmount

From
	FactResellerSales AS F inner join DimDate as D
		on F.OrderDateKey = D.DateKey
	inner join DimSalesTerritory as ST
		on F.Sales.TerritoryKey = ST.SalesTerritoryKey
Group by CalendarYear, SalesTerritoryRegion
Order by CalendarYear, SalesTerritoryRegion
Am I missing something as to why this is necessary in my particular instance? My previous experience (a few years ago) is with Business Objects and I believe the aggregation may have been done automatically so I'm wondering if I've made a mistake somewhere.

Thanks for any input re: the above questions.
 
Old September 27th, 2009, 03:28 AM
Wrox Author
 
Join Date: May 2004
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default

There were errors in that chapter in the first run copy of the book, which were corrected in subsequent printings. If your copy of the book doesn't have the forward by Thierry D'Hers, you have a copy of the first run printing. I will work with the publisher to get you an updated copy of Chapter 6. I appologize for the confusion.
__________________
Paul Turley
Mentor | SQL Server MVP, SolidQ
Blog: sqlserverbiblog.com
Author: SQL Server Reporting Services Recipes for Designing Expert Reports; Professional SQL Server Reporting Services (2000, 2005, 2008 & 2012), Beginning T-SQL (2000, 2005 & 2008), SSIS, SSAS & Access
 
Old September 27th, 2009, 11:25 AM
Authorized User
 
Join Date: Sep 2009
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Re: Chapter 6 Example

Paul, thanks for the reply. My copy, which was purchased new earlier this month from Amazon, does include the forward by Thierry D'hers so that doesn't jibe with this being from the original printing. I don't find any information about the printing run on the copyright page. Below the Library of Congress catalog number is a code 005.75'85--dc22 plus 2009042930 on the line below that and further to the right, if those have any relevance.

Let me know what information is needed to receive an updated copy of Chapter 6. An electronic file can be sent to the e-mail address in my user profile for this site. Or perhaps I can be provided with a promotional code to purchase the chapter through this site without cost.

Thanks again.

Rob B.
 
Old September 27th, 2009, 01:26 PM
Wrox Author
 
Join Date: May 2004
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Rob

You do have a current copy of the book. This correction was not made to the current edition. Your suggestion to add a group by clause to the query addresses the problem. Another approach would be to modify the group expression for the detail row. I'll add this to the book errata report. Again, my appologies.
__________________
Paul Turley
Mentor | SQL Server MVP, SolidQ
Blog: sqlserverbiblog.com
Author: SQL Server Reporting Services Recipes for Designing Expert Reports; Professional SQL Server Reporting Services (2000, 2005, 2008 & 2012), Beginning T-SQL (2000, 2005 & 2008), SSIS, SSAS & Access
 
Old September 27th, 2009, 09:53 PM
Authorized User
 
Join Date: Sep 2009
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Paul,

Quote:
Your suggestion to add a group by clause to the query addresses the problem. Another approach would be to modify the group expression for the detail row.
Though the example in question is a very simple one, I can see where the best practice would be to perform the aggregation via the group expression since the report itself is less dependent on the data source/set. Not having yet fully mastered group expressions in Reporting Services, I have not been able to reproduce the example results in this manner. Any more details that can be provided would be appreciated. The methodolory is my primary interest.

Thanks.

Rob B.
 
Old September 29th, 2009, 12:00 PM
Authorized User
 
Join Date: Sep 2009
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Re: Chapter 6 Example

I'm continuing to have difficulty with the example which begins on page 135. As mentioned in my original post, use of sum(SalesAmount) and "group by" CalendarYear and SalesTerritoryRegion in the Data Set query does produce the results of Figure 6-18 on page 144.

I've also found that if I use the original query shown in Figure 6-7 on page 137 but change the data cell for SalesAmount to Sum(SalesAmount) and change the Detail row group to include a group on SalesTerritoryRegion, I also get the same results as above.

However, when I try to apply the SalesAmount column total as described on pages 144-5, I do not get the results shown in Figure 6-21. Rather, an extra row appears after each SalesTerritoryRegion row (vs. after all regions for each calendar year) that shows blanks in the year and region columns and the sales amount from the prior row repeated in the 3rd column. No CalendarYear total row appears. I've repeated the process and played around with it but haven't discovered the "trick" to make it work as shown.

If anyone who has successfully completed this example through adding totals can describe how they did it, it would be much appreciated.

Thank you.
 
Old September 29th, 2009, 04:13 PM
Authorized User
 
Join Date: Sep 2009
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Re: Chapter 6 Example (Update)

I have discovered that, where Figure 6-19 on page 144 shows inserting a row "Inside Group - Below", if the row is inserted "Outside Group - Below", the total for all regions for each calendar year are added correctly, i.e., the output looks the same as Figure 6-21 on page 145.

If someone can confirm that this is the correct methodology that would be helpful.

Thanks.
 
Old October 4th, 2009, 12:48 AM
Wrox Author
 
Join Date: May 2004
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I will be posting and errata document to the book downloads for chapter 6 containing the following information. Following these directions, it would not be necessary to add a Group By clause the query.

Chapter 6 Errata and Corrections


In order to reproduce the results shown in chapter 6, one correction and additional step is necessary:
Page 141 – Group the details on the SalesTerritoryRegion field
After adding the CalendarYear and SalesTerritoryRegion groups shown on page 140, it is necessary to add a details row group expression for the SalesTerritoryRegion field. To do this, click the down arrow on the (DetailsGroup) in the Row Groups list and choose Group Properties. In the Group Properties dialog, click the Add button under Group expressions. Choose the [SalesTerritoryRegion] from the field list and then click OK to close the dialog.
Page 144 correction – Add total row for CalendarYear
Instead of inserting the total row for the SalesTerritoryRegion field (shown in Figure 6-19,) right-click the CalendarYear textbox and then follow the same instructions to insert a new row for the total.
__________________
Paul Turley
Mentor | SQL Server MVP, SolidQ
Blog: sqlserverbiblog.com
Author: SQL Server Reporting Services Recipes for Designing Expert Reports; Professional SQL Server Reporting Services (2000, 2005, 2008 & 2012), Beginning T-SQL (2000, 2005 & 2008), SSIS, SSAS & Access
 
Old October 5th, 2009, 11:10 AM
Authorized User
 
Join Date: Sep 2009
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Re: Chapter 6 Errata

Paul,

Thanks for the errata.

In addition to adding the details row group as you describe in your previous post for Page 141, I found I needed to change the [Sales Amount] cell to [Sum(SalesAmount)]. I did this by left clicking this cell (highlighting [Sales Amount]), right clicking and selecting Summarize By.... and Sum. The cell could alternately by manually edited.

Without doing so, I found that the detail amounts shown when the report is run are are left at the order level and don't match Figure 6-18 on Page 144. This is before adding the Calendar Year total which you also correct in your errata.

Did I miss something or is there another way to accomplish this?

Thanks.

Rob B.
 
Old October 5th, 2009, 11:22 AM
Authorized User
 
Join Date: Sep 2009
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Re: Chapter 6 Example - Page 151

Re: the same example referred to above, at the bottom of Page 151, reference is made to repeating header information by selecting the related option in Tablix Properties. This did not work for me and my research found this to be known problem in Reporting Services -- it works sometimes but not always. I didn't find any pattern for when it does or doesn't. However I did find a link to a helpful MS Technet article at:

http://technet.microsoft.com/en-us/l.../cc627600.aspx

This involves using the Properties window for the outermost static row group which is accessed using the Advanced button for Groups.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Chapter 6 - Code Download Missing for this Chapter dbaechtel BOOK: Professional SharePoint 2007 Development ISBN: 978-0-470-11756-9 0 August 11th, 2009 11:02 AM
Chapter 2 - End of chapter exercises whizzkid1892 BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 1 July 30th, 2008 12:02 PM
Generics chapter 12 difficult chapter i found ...? Larryz C# 2005 1 July 4th, 2007 09:40 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.