Wednesday, December 8, 2010

Modeling Tools


Quick flash back on MS SQL Server Code Names

Code Name Final name
SQL95 -----------------SQL Server 6.0
Hydra -----------------SQL Server 6.5
Sphinx ----------------SQL Server 7.0
Shiloh ----------------SQL Server 2000 (32-bit)
Liberty ---------------SQL Server 2000 (64-bit)
Yukon -----------------SQL Server 2005
Katmai/Akadia ---------SQL Server 2008
Kilimanjaro -----------SQL Server 2008R2
Denali ----------------SQL Server 2011

Monday, December 6, 2010

What's New in Report Builder 3.0

Report Builder 3.0 offers a number of enhancements to the report design experience. For example, you can add maps, Sparkline’s, and indicators to your reports, rotate text 270 degrees, control page breaks, and create report parts and shared datasets and save them to a report server for others to use. You can include data from new data source types: SharePoint lists, Microsoft SQL Azure, and SQL Server Parallel Data Warehouse. You can do more with expressions, including calculate aggregates of aggregates. For exporting to Excel, you can now set names for worksheets, and you can produce data feeds from your reports.

Collaboration and Reuse

Report Parts
Report parts are report items that you store on a report server, or on a SharePoint site that is integrated with a report server. You can reuse report parts in multiple reports, and you can update them on the server.
The report part that you add to your report maintains a relationship to the instance of the report part on the site or server by means of a unique ID. After you add report parts from a site or server to a report, you can modify them, independent of the original report part on the site or server. You can accept updates that others have made to the report part on the site or server, and you can save the modified report part back to the site or server, either adding a new report part or writing over the original.
Report parts let work groups utilize the different strengths and roles of their team members. For example, one person can create charts and save them as report parts that others can reuse in their reports.

Shared Datasets
Shared datasets are a new type of report server item that can retrieve data from shared data sources that connect to external data sources. A shared dataset provides a way to share a query to provide a consistent set of data for multiple reports. The dataset query can include dataset parameters. You can configure a shared dataset to cache query results for specific parameter combinations on first use or by specifying a schedule. You can use shared dataset caching in combination with report caching and report data feeds to help manage access to a data source.
Like report parts, a shared dataset that is added to a report maintains a relationship to the definition of the dataset on the report server. Unlike report parts, when the definition is changed, you do not have to accept updates. All reports that have the relationship always use the shared dataset definition on the report server.

Data Sources
Reporting Services supports three new data source types: Microsoft SQL Azure, Microsoft SQL Server Parallel Data Warehouse, and Microsoft SharePoint List.

SharePoint List Data Extension
The SharePoint list data extension lets you specify a SharePoint list as a data source. In your report, you can include data from a SharePoint list for the following versions of SharePoint sites: SharePoint Foundation 2010, SharePoint Server 2010, Windows SharePoint Services 3.0, and Office SharePoint Server 2007. The associated SharePoint List query designer provides a graphical interface to explore the metadata for all lists that you have sufficient permission to view. In each dataset query, you can select multiple fields from one SharePoint list.

Microsoft SQL Azure
The Microsoft SQL Azure data source type connects to SQL Server databases in the cloud and enable reports to retrieve and render report data from SQL Azure databases.

Microsoft SQL Server Parallel Data Warehouse
The Microsoft SQL Server Parallel Data Warehouse data source type connects to a Microsoft SQL Server Parallel Data Warehouse and enable reports to retrieve and render report data from SQL Server databases.

Data Visualization
Report Builder 3.0 provides a Map Wizard and Map Layer Wizard to add maps and map layers to your report to help visualize data against a geographic background. A map layer displays map elements based on spatial data from a map in the Map Gallery, from a SQL Server query that returns SQL Server spatial data, or from an Environmental Systems Research Institute, Inc. (ESRI) shape file. Map elements can be polygons that represent shapes or areas, lines that represent paths or routes, or points that represent locations such as stores or cities. You can also add a background that displays Bing map tiles.
After you relate map elements with report data, you can specify rules for the map elements on each layer that control color, size, width, or marker type. For example, you can add a bubble map that varies bubble size for store locations based on sales or a color analytical map that displays customer demographics for geographic regions.
You can add labels, legends, titles, a color scale, and a distance scale to help your users interpret the map display. You can add interactive features such as tooltips and drill-through links, or provide parameters that enable a user to interactively control the visibility of each layer.

Configuring a Map Layer
In a map report, you can use the map wizard to configure an existing map layer. You can change the type of map, the data visualization rules, and change match field that specifies the relationship between spatial data and analytical data. The Specify the match fields for spatial and analytical data wizard page displays data from both the spatial data source and the analytical data source to help you choose the field on which to match.
By default, reports that display a Bing map as a background use an HTTP connection. You can also specify whether to use a Secure Sockets Layer (SSL) connection to the Bing Maps Web server.

Sparklines and Data Bars
Sparklines and data bars are simple charts that convey a lot of information in a little space, often in-line with text. Sparklines and data bars are often used in tables and matrices. Their impact comes from viewing many of them together and being able to quickly compare them, instead of viewing them singly. This makes it easy to see the outliers. Each sparkline often represents multiple data points over time. Data bars can represent multiple data points, but typically illustrate only one. Each sparkline typically presents a single series. You cannot add a sparkline to a detail group in a table. Because sparklines display aggregated data, they must go in a cell that is associated with a group.
Sparklines and data bars have the same basic chart elements of categories, series, and values, but they have no legend, axis lines, labels, or tick marks.

Indicators are minimal gauges that convey the state of a single data value at a glance. The icons that represent indicators and their states are visually effective, even when they are used in small sizes. Indicators can be used by themselves in dashboards or free-form reports, but they are most commonly used in tables or matrices to visualize data in rows or columns.
Indicators can show trends by using directional images such as arrows; ratings by using incremental icons such as stars; and states by using images such as traffic lights or check marks. Indicators are available in Report Builder 3.0 and Report Designer.

Aggregates and Expressions

Calculating Aggregates of Aggregates
You can create expressions that calculate an aggregate of an aggregate. For example, in a cell in the row group header that is associated with a group based on year, you can calculate the average monthly sales for year by using the expression =Avg(Sum(Fields!Sales.Value,"Month"),"Year").
By using this feature for charts and gauges that are nested in a table, you can align horizontal and vertical axes for charts and scales for gauges. You do this by calculating the maximum and minimum of aggregated values so the nested items use the same ranges.

Enhanced Support for Expressions
New global and a new property for report variables are introduced in this release.

The following additional items have been added to the built-in globals collection.
• OverallPageNumber and OverallTotalPages Support for page numbering for the entire rendered report. New properties that are related to page breaks control how the page numbering occurs over page breaks that are set on report items.
• PageName Support for naming pages.
• RenderFormat Support for information that is specified for the renderer.
Report variables now have a read-only property that is set by default. Advanced expression writers can clear this option to preserve the value of a report variable throughout a report session. This is the equivalent of setting the Writable property for a report variable in RDL. Report variables are still calculated once, and then recalculated every time the report is reprocessed. This property provides a way to persist data across report rendering, page changes, and some types of user interaction. The value of the variable is set when the report is reprocessed, but is maintained in the current session. In custom code, you can specify the variable value by using the SetValue function.

Report Layout and Rendering
Naming Excel Worksheet Tabs
Properties of reports and page breaks let you generate the names of worksheet tabs when you export reports to Excel. You can provide an initial page name of a report that can be exported as the default name of worksheet tabs, or use page breaks and page names to provide different names for each worksheet tab.

Rendering Reports to Data Feeds
The Atom rendering extension renders reports data as Atom-compliant data feeds. Depending on the layout of your report, the Atom rendering extension creates one or more data feeds. The data feeds can be exchanged and read by applications that can consume Atom-compliant data feeds. For example the PowerPivot client can use Atom-compliant data feeds that are generated from reports.
Using report data as data feeds gives you an additional way to provide data to applications when the data is not easily accessible through client data providers, or you prefer to hide the complexity of the data source and make it simpler to use the data. You can generate as Atom-compliant data feeds from Report Manager or a SharePoint site that is integrated with Reporting Services.

Report Pagination
Page breaks on tablix data regions (table, matrix, and list), groups, and rectangles give you better control of report pagination. Report pages, delimited by page breaks, can have different page names and reset page numbering. By using expressions, the page names and page numbers can be dynamically updated when the report is run. You can also disable page breaks entirely, or based on expression values.

Report Authoring Tools
Making it Easier to Design and Edit Reports
The run-time credentials, specified in the data source properties of a report, might not work for design time tasks such as creating queries and previewing reports. Report Builder 3.0 provides a user interface for changing credentials when it is unable to connect to the data source.
Rotating Text 270 Degrees
Text boxes can now be rotated 270 degrees. Standalone text boxes in a report header or footer, the report body, or text boxes in the cells of tables and matrices can display text written vertically and bottom to top. This rotation helps you create more readable reports, fit more columns on a printed report with fixed page size, and create reports with more graphical appeal.

Add image in the SQL Table

Create table ImageDB (description varchar(20), img image)
insert into ImageDB (description, img)
Select 'description1234', *
from OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\Desert.jpg', SINGLE_BLOB) as tempImg

Select * from ImageDB