Answer My Searches

The labels on a pie chart in SQL Server 2005 Reporting Services (SSRS) are very ugly. They just get thrown over the chart by default and they all overlap. I hate it!

This MSDN page tells us how to fix it http://msdn.microsoft.com/en-us/library/ms156263(SQL.90).aspx

To change the position of point labels in a pie chart
Using Report Designer, create a pie chart. For more information, see Working with Chart Data Regions.

In Layout view, right-click the chart, and then click Properties.

On the Data tab, in Values, and then click Add or Edit to open the Edit Chart Value dialog box.

On the Point Labels tab, clear Auto, and then select a button other than the center button.

To set options on this tab, Show point labels must be selected.

I was completely stumped on this error that I was getting on the report manager web page after I uploaded an RDL file and set it use a shared connection I had already set up:

An error has occurred during report processing.
Cannot create a connection to data source 'TWO'.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

It turns out I had simply mistyped my connection string when I was setting up the shared connection. So that’s something you should check first.

I got this error when starting the report server:


System.ServiceProcess.TimeoutException: Time out has expired and the operation has not been completed. at System.ServiceProcess.ServiceController.WaitForStatus(ServiceControllerStatus desiredStatus, TimeSpan timeout) at ReportServicesConfigUI.Panels.ServerInformationPanel.StartStopServiceTask(Boolean start).

To fix I opened up a command prompt and typed:

C:\> cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regiis -i
Start installing ASP.NET (2.0.50727).
.............................................
Finished installing ASP.NET (2.0.50727).


Apparently I had some issue with my ASPNET account

How to treat a stored procedure that ends with a select as a select in another query:

You have to create a schema-compatible temp table, but then you can do filtering and grouping when you select out of the temp table at the end.


create table #sp_who (
spid smallint,
ecid smallint,
status nchar(30),
loginame nchar(128),
hostname nchar(128),
blk char(5),
dbname nchar(128),
cmd nchar(16))

insert into #sp_who execute sp_who
select * from #sp_who
drop table #sp_who


The function group_concat in MySQL will let you combine the values from multiple rows together in an aggregate function.

It’s a little hard to picture, so here’s a hypothetical aggregate query from the Netflix prize dataset:

select movie_id, customer_id, max(date) as latest, group_concat(rating) as all_ratings, count(*) as total_ratings
from ratings
group by movie_id,customer_id
having count(*)>1;

And we get back* **:

+----------+-------------+------------+-------------+---------------+
| MOVIE_ID | CUSTOMER_ID | LATEST     | ALL_RATINGS | TOTAL_RATINGS |
+----------+-------------+------------+-------------+---------------+
| 1        | 5           | 2007-12-02 | 3,2,4,5     | 4             |
| 54       | 3           | 2007-12-04 | 1,3,1       | 3             |
| 23       | 34          | 2007-12-07 | 5,5         | 2             |
+----------+-------------+------------+-------------+---------------+

As you can see for each set of rows grouped by the same movie_id and customer_id, it took each individual rating value and concatenated them all together in the new aggregate row.

* My actual query returned 0 rows so I thought I’d make up a quick example to illustrate the point.
** I made that pretty table*** with this ASCII table generating utility. Yay!
*** Pretty table may not be pretty in RSS readers or Blackberries.