I am trying to learn Reporting Services using the title "MS SQL Server 2005 Reporting Services Step by Step" by Stacia Misner and Hitachi Consulting, published in 2006. I am experiencing problems with some of the exercises. I got as far as Chapter 4 when I followed directions to create a SQL statement to define a query string for a dataset. Pg 80: select * from vProductProfitability where year = 2003 and MonthNumberOfYear = 1 The view vProductProfitability does not exist in the tutorial database that came with the book, rs2005sbsDW. The result of this queryis the basis for the entire chapter on developing basic reports and I'm being denied a learning opportunity because the view does not exist. In short, I'm stuck. I have tried to findsomewhere at Microsoft to place this question and get some answers so I can continue thru the tutorial. To no avail. Does anyone have any suggestions? BTW, the solution that came in the CDis also wrong because the query noted above is also in the solution. This humble grasshopper seeks wisdom. Respectfully Submitted, Dave Matthews Atlanta, GA aka FlooseMan Dave

Hello, Just to be sure, your looking at the DB "rs2005sbsDW" and not the other one "rs2005sbs" right? Regarding official support,please take a loot at the readme.txt thats part of the samples CD, it contains contact\support information for the books materials. Hope that helps

Hi, Craig. Good question, and yes I was going after rs2005sbsDW and not the other. Regards, Dave Atlanta

Did you try Stacia's blog? http://blog.datainspirations.com/ cheers, Andrew

Hi, Andrew, I have just looked at Stacia's blog and she is so far ahead of me, I'm not so sure she would want to take time out to go back and help out a rookie who needs to have his hand held while he studies Repoprting Services from a book. Perhaps an alternative would be for someone who is familiar with the Adventure Works tutorial database could come up with something that roughly approximates the missing view it is using. This is a very important query because the project that first uses it is referenced starting all the way back in Chapter 3 and continues thru the end of Chapter 7. Without that view query, that is a lot of the book that has no tutorial project associated with it. For the hands-on types, learning without the learn-by-doing piece is more difficult. Thanks for the help! Dave from Atlanta

Don't you get a warranty when you buy a book? If someone bought my book I would answer any questions they would have about it. Write the publishers.. cheers, Andrew

Dave, I do not have the CD handy but I do know the readme on it contains contact infromation for support. Its the odd the query is just missing, perhaps a reinstall would help. The concern would be that if 1 query is missing for some reason, who is to say other needed things are not as well. I do not recall the install in detail but perhaps if you 1) detach the DB, move it/rename it and then 2) run the install from the disc again which should put a new copy of the files on your drive then you can run the file which I think essentially attached the DB for you. Again though, I would check the readme for support info as I know I have seen it there. good luck

I am trying to learn Reporting Services using the title "MS SQL Server 2005 Reporting Services Step by Step" by Stacia Misner and Hitachi Consulting, published in 2006. I am experiencing problems with sample files. I got as far asthe Introduction page xvistep 19. I get the following error: =================================== Errors in the encryption library: Failed to decrypt sensitive data. Possibly the encryption key does not match or is inaccessible because of improper service account change.(Microsoft.AnalysisServices) ------------------------------Program Location: at Microsoft.AnalysisServices.AnalysisServicesClient.SendExecuteAndReadResponse(ImpactDetailCollection impacts, Boolean expectEmptyResults, Boolean throwIfError) at Microsoft.AnalysisServices.AnalysisServicesClient.Restore(String file, String databaseName, Boolean allowOverwrite, ICollection locations, RestoreSecurity security, String password) at Microsoft.AnalysisServices.Server.Restore(String file, String databaseName, Boolean allowOverwrite, RestoreLocation[] locations, RestoreSecurity security, String password) at Microsoft.AnalysisServices.ManagementDialogs.RestoreDialog.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult) at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType) at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForOlap(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult) at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult) at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender) =================================== I'm using the SQL Server 2005 Enterprise "Evaluation" Edition, I have the service packs installed and I'm using windows authentication. Microsoft SQL Server Management Studio (expires in 180 days) 9.00.3042.00 Microsoft Analysis Services Client Tools 2005.090.3042.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 built by: (_sqlbld) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 6.0.2800.1106 Microsoft .NET Framework 2.0.50727.42 Operating System 5.0.2195 (Windows 2000, Service Pack 4) Respectfully Submitted, Jim Pollock London U.K.

Hi, Andrew, I'm with you -- if there are questions about what Ihave written, I would certainly insist on answering them. In this case, all I have to go on to communicate questions comes from the ReadMe on the accompanying CD: send all inquiries to mspinput@microsoft.com. Since I have already done so, and I am not certain I will ever get a response, the next best thing would be for me to ask a favor of whoever elsehas successfully used the book: I would like to see a copy of the SQL code that produces the view vProductProfitability. Loading it to my database would spare me no end of difficulties: I was directed to load the tutorial database to a version of SQL Server on a dev machine, not localhost. Perhaps I will yet get a resolution to this. Regards, Dave

Are you sure you are using the databases that came with the book? If you are using the AdventureWorks sample databases from MSDN, they don't have the views that are specific to the book. The vProductProfitability is in the rs2005svsDW database. The databases that come with the book do not have LDF files so you must attach them using the script that also comes with the book. USE [rs2005sbsDW] GO /****** Object: View [dbo].[vProductProfitability] Script Date: 05/23/2007 12:56:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* from Microsoft SQL Server 2005 Reporting Services: Step by Step*/ /****** Object: View dbo.vProductProfitability Script Date: 9/8/2003 3:03:29 PM ******/ ALTER VIEW [dbo].[vProductProfitability] AS SELECT dbo.DimProduct.ProductName AS Product, dbo.DimProductSubCategory.ProductSubCategoryName AS SubCategory, dbo.DimProductCategory.ProductCategoryKey AS CategoryKey, dbo.DimProductCategory.ProductCategoryName AS Category, SUM(dbo.FactResellerSales.TotalProductCost) AS CostAmount, SUM(dbo.FactResellerSales.SalesAmount) AS SalesAmount, SUM(dbo.FactResellerSales.OrderQuantity) AS OrderQuantity, LEFT(dbo.DimTime.MonthName, 3) AS [Month], dbo.DimTime.MonthNumberOfYear, dbo.DimTime.CalendarYear AS [Year] FROM dbo.FactResellerSales INNER JOIN dbo.DimProduct ON dbo.FactResellerSales.ProductKey = dbo.DimProduct.ProductKey INNER JOIN dbo.DimProductSubCategory ON dbo.DimProduct.ProductSubCategoryKey = dbo.DimProductSubCategory.ProductSubCategoryKey INNER JOIN dbo.DimProductCategory ON dbo.DimProductSubCategory.ProductCategoryKey = dbo.DimProductCategory.ProductCategoryKey INNER JOIN dbo.DimTime ON dbo.FactResellerSales.OrderDateKey = dbo.DimTime.TimeKey GROUP BY dbo.DimProduct.ProductName, dbo.DimProductSubCategory.ProductSubCategoryName, dbo.DimProductCategory.ProductCategoryName, dbo.DimProductCategory.ProductCategoryKey, dbo.DimTime.MonthNumberOfYear, LEFT(dbo.DimTime.MonthName, 3), dbo.DimTime.CalendarYear

Miles, I aplogize that it has taken me a lifetime to respond -- I have been pulled aside into other things and only recently have I returned to this. This query you have provided is exactly what I was looking for. There were a few mods needed to tweak some of the column names, but by and large, it was dead on. I changed it slightly into a CREATE VIEW and then ran it in SQL Server. Once I got my permissions straightened out with the DBA (AdventureWorksDW is loaded on a server they support), the query ran perfectly. In addition, I ran a query in Reporting Services that uses the view and it worked perfectly as well. I would like to thank you for responding with the missing view. I am now able to resume my self-training with a view of having something meaningful to work toward -- actually being useful in the area of Reporting Services. Warmest Regards, Dave Matthews aka FlooseMan Dave Atlanta, GA

Dave, I'm in your shoes here (trying to learn RSS using the MP book), on chapter 5 and DataSource query is failing because of no view called vProductProfitability. I see that you found a solution by modifying a previous suggestion. Could you share your complete solution with me so I can continune on in my quest for knowledge? I would very much appreciate. Thanks! Kerry Mapes Claron, PA

If you've figured out the solution for this, I would love it if you could post it on here. I've been going through the book with sticky notes on all thecorrections, but this one has me stumped.

The solution is to use the database files that come with the book. The standard AdventureWorks database does not have the extra view.

create VIEW [dbo].[vProductProfitability] AS SELECT DimProduct.EnglishProductName AS Product, DimProductSubcategory.EnglishProductSubcategoryName AS SubCategory, DimProductCategory.ProductCategoryKey AS CategoryKey, DimProductCategory.EnglishProductCategoryName AS Category, SUM(FactResellerSales.TotalProductCost) AS CostAmount, SUM(FactResellerSales.SalesAmount) AS SalesAmount, SUM(FactResellerSales.OrderQuantity) AS OrderQuantity, LEFT(DimTime.EnglishMonthName, 3) AS [Month], DimTime.MonthNumberOfYear, DimTime.CalendarYear AS [Year] FROM FactResellerSales INNER JOIN DimProduct ON FactResellerSales.ProductKey = DimProduct.ProductKey INNER JOIN DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN DimTime ON FactResellerSales.OrderDateKey = DimTime.TimeKey GROUP BY DimProduct.EnglishProductName, DimProductSubcategory.EnglishProductSubcategoryName, DimProductCategory.EnglishProductCategoryName, DimProductCategory.ProductCategoryKey, DimTime.MonthNumberOfYear, LEFT(DimTime.EnglishMonthName, 3), DimTime.CalendarYear

Had the same issue, since there is no ldf, use the script provided with the book found in the install folder (i 've copied mine to c:\ even if you have tried with the restore folder try this) so open mangement studio new query window paste this : USE master GO EXEC SP_ATTACH_SINGLE_FILE_DB @DBNAME = 'rs2005sbs', @PHYSNAME = 'c:\Microsoft Press\rs2005sbs\Setup\Database\rs2005sbs.mdf' GO change the path according to your mdf file, if you don't know the path, make a search for rs2005sbs.mdf Now you should see all your objects appear. NOw do the same with the other db: rs2005sbsDW and you will get the following File activation failure. The physical file name "c:\rs2005sbs\Setup\Database\rs2005sbsDW_log.LDF" may be incorrect. New log file 'c:\microsoft press\rs2005sbs\Setup\Database\rs2005sbsDW_log.LDF' was created. Converting database 'rs2005sbsDW' from version 611 to the current version 655. Database 'rs2005sbsDW' running the upgrade step from version 611 to version 621. Database 'rs2005sbsDW' running the upgrade step from version 621 to version 622. Database 'rs2005sbsDW' running the upgrade step from version 622 to version 625. Database 'rs2005sbsDW' running the upgrade step from version 625 to version 626. Database 'rs2005sbsDW' running the upgrade step from version 626 to version 627. Database 'rs2005sbsDW' running the upgrade step from version 627 to version 628. Database 'rs2005sbsDW' running the upgrade step from version 628 to version 629. Database 'rs2005sbsDW' running the upgrade step from version 629 to version 630. Database 'rs2005sbsDW' running the upgrade step from version 630 to version 631. Database 'rs2005sbsDW' running the upgrade step from version 631 to version 632. Database 'rs2005sbsDW' running the upgrade step from version 632 to version 633. Database 'rs2005sbsDW' running the upgrade step from version 633 to version 634. Database 'rs2005sbsDW' running the upgrade step from version 634 to version 635. Database 'rs2005sbsDW' running the upgrade step from version 635 to version 636. Database 'rs2005sbsDW' running the upgrade step from version 636 to version 637. Database 'rs2005sbsDW' running the upgrade step from version 637 to version 638. Database 'rs2005sbsDW' running the upgrade step from version 638 to version 639. Database 'rs2005sbsDW' running the upgrade step from version 639 to version 640. Database 'rs2005sbsDW' running the upgrade step from version 640 to version 641. Database 'rs2005sbsDW' running the upgrade step from version 641 to version 642. Database 'rs2005sbsDW' running the upgrade step from version 642 to version 643. Database 'rs2005sbsDW' running the upgrade step from version 643 to version 644. Database 'rs2005sbsDW' running the upgrade step from version 644 to version 645. Database 'rs2005sbsDW' running the upgrade step from version 645 to version 646. Database 'rs2005sbsDW' running the upgrade step from version 646 to version 647. Database 'rs2005sbsDW' running the upgrade step from version 647 to version 648. Database 'rs2005sbsDW' running the upgrade step from version 648 to version 649. Database 'rs2005sbsDW' running the upgrade step from version 649 to version 650. Database 'rs2005sbsDW' running the upgrade step from version 650 to version 651. Database 'rs2005sbsDW' running the upgrade step from version 651 to version 652. Database 'rs2005sbsDW' running the upgrade step from version 652 to version 653. Database 'rs2005sbsDW' running the upgrade step from version 653 to version 654. Database 'rs2005sbsDW' running the upgrade step from version 654 to version 655. It worked for me and then you can carry on using all objects from the books. Hope that helps.

For anyone who needs the SQL 2008 version; Create VIEW [dbo].[vProductProfitability] AS SELECT dbo.DimProduct.EnglishProductName AS Product, dbo.DimProductSubCategory.EnglishProductSubCategoryName AS SubCategory, dbo.DimProductCategory.ProductCategoryKey AS CategoryKey, dbo.DimProductCategory.EnglishProductCategoryName AS Category, SUM (dbo.FactResellerSales.TotalProductCost) AS CostAmount, SUM (dbo.FactResellerSales.SalesAmount) AS SalesAmount, SUM (dbo.FactResellerSales.OrderQuantity) AS OrderQuantity, LEFT( dbo.DimDate.EnglishMonthName, 3) AS [Month], dbo.DimDate.MonthNumberOfYear, dbo.DimDate.CalendarYear AS [Year] FROM dbo.FactResellerSales INNER JOIN dbo.DimProduct ON dbo.FactResellerSales.ProductKey = dbo.DimProduct.ProductKey INNER JOIN dbo.DimProductSubCategory ON dbo.DimProduct.ProductSubCategoryKey = dbo.DimProductSubCategory.ProductSubCategoryKey INNER JOIN dbo.DimProductCategory ON dbo.DimProductSubCategory.ProductCategoryKey = dbo.DimProductCategory.ProductCategoryKey INNER JOIN dbo.DimDate ON dbo.FactResellerSales.OrderDateKey = dbo.DimDate.DateKey GROUP BY dbo.DimProduct.EnglishProductName, dbo.DimProductSubCategory.EnglishProductSubCategoryName, dbo.DimProductCategory.EnglishProductCategoryName, dbo.DimProductCategory.ProductCategoryKey, dbo.DimDate.MonthNumberOfYear, LEFT( dbo.DimDate.EnglishMonthName, 3), dbo.DimDate.CalendarYear