To my supprise Reporting services does not seem to have support for an
alternating item color when displaying a table (well that I can find at
least).

After some playing I came up with a simple little iif statement.
Simply set the rows Background color in the properties pane to this:
=iif(RowNumber(Nothing) Mod 2, “LightGrey”, “White”)

I can not beleive how hard it is to deploy reports to production! Its
easy for development. Just tell visual studio where the server is and
bang! its done. I spent most of today trying to figure out how to
deploy to production without having to manually upload each report
through the web interface and then manually linking them to my shared
data source.

I tried writing a vb script to do it which I got
90% their but could not link to a shared datasource. Microsofts
documentation of the scripting commands you can do ARE GARBAGE! when
trying to set a property you need to send a name value pair (which is
fine) but there is no docs I could find that say what the strings are
that I can pass to it. But I digress.

In the end I found this tool:
http://www.sqldbatips.com/showarticle.asp?ID=62

This
person wrote a tool that connects to my development RS and generates a
folder of scripts along with a batch file to run them all. I opened the
batch file, changed the server to production and a couple of minutes
later I was …..happy? yes. that is the word.

For those of you
that want to deploy reports with a shared datasource via a manual
script (using the rs.exe program to execute it) here is a snip of one
of the scripts:

Public Sub Main() Dim name As String = "Allocation Report" Dim parent As String = "/MyProgram.ReportStaging" Dim location As String = "c:\tempreports\Allocation Report.rdl" Dim overwrite As Boolean = True Dim reportContents As Byte() = Nothing Dim warnings As Warning() = Nothing Dim fullpath As String = parent + "/" + name

 'Common CatalogItem properties Dim descprop As New [Property] descprop.Name = "Description" descprop.Value = "" Dim hiddenprop As New [Property] hiddenprop.Name = "Hidden" hiddenprop.Value = "False"

 Dim props(1) As [Property] props(0) = descprop props(1) = hiddenprop

 'Read RDL definition from disk Try  Dim stream As FileStream = File.OpenRead(location)  reportContents = New [Byte](stream.Length) {}  stream.Read(reportContents, 0, CInt(stream.Length))  stream.Close()

  warnings = RS.CreateReport(name, parent, overwrite, reportContents, props)

  If Not (warnings Is Nothing) Then   Dim warning As Warning   For Each warning In warnings    Console.WriteLine(Warning.Message)   Next warning  Else   Console.WriteLine("Report: {0} published successfully with no warnings", name)  End If

  'Set report DataSource references  Dim dataSources(0) As DataSource

  Dim dsr0 As New DataSourceReference  dsr0.Reference = "/MyProgram.ReportStaging/dsMyProgram"  Dim ds0 As New DataSource  ds0.Item = CType(dsr0, DataSourceDefinitionOrReference)  ds0.Name="dsMyProgram"  dataSources(0) = ds0

  RS.SetReportDataSources(fullpath, dataSources)

  Console.Writeline("Report DataSources set successfully")

 Catch e As IOException  Console.WriteLine(e.Message) Catch e As SoapException  Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText + " (" + e.Detail.Item("Message").InnerText + ")") End TryEnd Sub

Here is a little method I made to build the query string to pass to reporting services.
-showToolbar shows or hides the toolbar with paging, printing, exporting, etc.
-showParameters shows or hides the entry fields that allow a user to change what values the report is generated from
-properties
is a name value collection of parameters that you can supply to the
report to generate it instead of the user having to type them into the
boxes if ShowParameters is on.

Private
Function BuildPropertyString(showToolbar as boolean, showParameters as
boolean, properties as NameValueCollection) As String
If _properties Is Nothing Then Return “”
Dim sb As New System.Text.StringBuilder
if ShowToolbar then sb.Append(“&rc:Toolbar=true”)
if ShowParameters then sb.Append(“&rc:Parameters=false&”)
For Each key As String In properties.Keys
sb.Append(key)
sb.Append(“=”)
sb.Append(System.Web.HttpUtility.UrlEncode(properties(key)))
sb.Append(“&”)
Next
sb.Remove(sb.Length – 1, 1) ‘remove the trailing &
Return sb.ToString
End Function

NOTE: this function will exit if no properties are passed to it.
Also it would have been beter of me to rename properties to parameters now that I look at it

Working on more reports today and discovered that reports print in
standard format and I have several I need to print in landscape mode. I
finally figured out how

goto report -> report properties -> layout
set the width to be 29.7cm
set the height to be 21cm

I wish there was an option that just said landscape though!

One of the cool things I discovered today was that you can use .NET
formatting codes when formatting data for output with reporting services

1. Right Click on the field
2. goto properties
3. Check the Custom Radio box and type one in

you can type in ‘c’ for currency formatting that is locale specific, ‘d’ for decimal output, or ‘p’ for percentage.

I needed a custom one that had a number formatted by commas with no decimal places so I simply typed in ###,###,### and viola!

More info on format strings
Standard numeric formattings: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp

Custom numeric formattings: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp

Date time formatting: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemGlobalizationDateTimeFormatInfoClassTopic.asp

Beefs:

1. Report wizard comes back with weird errors when a
query is not right. When you run it in query analyzer the actuall issue
gets reported and is easy to correct.
2. Can not use a stored proc that has multiple result sets.
3. The report wizard will not work in some scenarios when using a temp table or a table variable.
4. MSDN documentation = the suck
5. The designer in visual studio does not remember the parameters entered when switching between layout and preview mode
6. I can not find a place to change my rules to metric.
7.
Does not seem to be any way to pass a dataset to it or an XML file. I
really liked this feature in crystal especially when the reporting
layer does not have permssions / access to the physical database
8.
Data designer does not like it when objects have different owners. i.e.
(I am calling dbo.sp_report which internally joins to a view not owned
by dbo… might work if I prefix the view with the owner though)
9. Expressions are VB based which is case insensitive but field names ARE case sensitive!
hence the expression below do not evaluate the same.
iif(Fields!Transaction_Type_code.Value = “BRM”, Fields!amount.Value, “”)
iif(Fields!Transaction_type_code.Value = “BRM”, Fields!amount.Value, “”)

Pros:
1. Easy to use
2. .NET formatting support
3. Easy to export reports
4. Easy to design simple reports
5. Integrated designer is awsome as I don’t have to publish to preview or reset any databindings
6. Many methods to use the system (url, soap, etc.)

EDIT: Ok you can connect datasets to the report but seems a little involved
http://msdn.microsoft.com/SQL/2000/learn/bi/reporting/default.aspx?pull=/library/en-us/dnsql2k/html/rsdsetex3.asp