Onteora Software

Sql Server 2012 hit the RTM milestone yesterday 3/6/2012.  You can download the express edition or a trial version from here.  Full editions Sql Server 2012 should available on April 1, 2012

 http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx

http://www.microsoft.com/sqlserver/en/us/editions/express.aspx

The new sql express management studio uses the Visual Studio 2010 shell.  In visual studio 2010 I have the enterprise application block 5 installed.  When right clicking on the databases in the object explorer I was getting an about not being able to load the enterprise application block addin.  To solve this I went to the tools menu and selected external tools.  I pressed the reset toolbox button and have not seen the error since.

I was working on a Sql Server Reporting Services Report and needed to display a table inside of a Table.  I was not able to find a blog entry on how to do it.  So this was goal to create a report that looks like this

Step one lets create the sql query to get the data from the Northwind database

In this query we will get the Orders and Order Details to display in a sub table

Now that we have a query to get the data lets create an sql server reporting project in visual studio 2008

Add a new report to reporting project and use a datasource to the Northwind database

Select
    o.orderid,
    p.ProductName,
    od.UnitPrice,
    od.Discount,
    od.Quantity,
    o.CustomerID,
    o.RequiredDate,
    o.ShipAddress,
    o.ShipName,
    o.ShipCity,
    o.ShipCountry,
    o.ShipPostalCode  from [Order Details] od
    inner join Orders o on od.OrderID = o.OrderID
    inner join products p on od.ProductID = p.ProductID

In the design the query part of the wizard use the query above.

Select Tabular for the report type.

In the Design the table part of the wizard put the orderid in the group by fields and the rest under details

In the Choose the Table screen make sure you enable drill down and sub totals

Finish the wizard and you should get something like this

Go ahead and change the report name to something like Northwind Orders

In the blue line with [orderid] go ahead and move the CustomerId, RequiredDate, ShipAddress, ShipName, ShipCity, ShipCountry, and ShipPostalCode to the same line.  Adjust title of the column above the fields so you have something like this

Right click on the row with ProductName and select add row outside group above.   In the bottom row give put the ProductName, UnitPrice, Discount, and Quantity fields.  Delete any other fields in that row.  You should have something that looks like this

In the row you added the Titles to right click and select row visibility.  When the report is initially run hide the row and have so the display can be toggled by this report item and select orderid

Now when you run the report you should see a table under the main row.  Hope this helps