VB.Net or SSRS code for Ordinal Numbers

Easily found on web. See example in SRS.ReportingServices.Functions and also in my invoice report.
With acknowledgement to:
http://www.jigsawboys.com/2010/03/04/ordinal-number-in-reporting-services/

Public Function FormatOrdinal(day as Integer) As String
  SELECT CASE (day Mod 100)
    CASE 11,12
    CASE 13
      Return day.ToString() + “th”
  END SELECT

  SELECT CASE day Mod 10
    Case 1
      Return day.ToString() + “st”
    Case 2
      Return day.ToString() + “nd”
    Case 3
      Return day.ToString() + “rd”
    Case Else
      Return day.ToString() + “th”
  END SELECT
End Function

Good luck!

Advertisements

SSRS Adding Code and also custom assemblies to reports

How to add Code to SSRS reports

  1. Right click on report > Code > Type code as Visual Basic function
  2. In expressions within a data region use Code.<functionname> e.g. =Code.OrdinalFunction(4)

Code for Ordinal Number readily avialable on web. Example use in SRS.ReportingServices.Functions and also in my invoice report

How to add custom assemblies to SSRS reports

  1. Create a class library project in Visual Studio VB.Net or C#
  2. Use a meaningful assembly name e.g. <initials>.ReportingServices.Functions
  3. Add a class and methods to this class. Make the methods shared
  4. Compile to create a dll
  5. Copy the dll to the equivalent location of:
    C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
    You may not need the (x86) and you may be on a lower or higher version than 9.0
  6. In VS BIDS report right click > report properties > References > Add the reference
  7. In expressions in a report use full Namespace.ClassName.FunctionName(variables)

Next deploying the assembly to the report server

  1. Copy your DLL to the following location or equivalent:
    C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin

Good luck. Acknowledgments to:

http://www.devx.com/codemag/Article/33656/0/page/3

Some SQL Server 2005 services are not listed in SQL Server Configuration Manager – Also Red Cross shown in IIS – Server 2003

PROBLEM

  1. Some SQL Server 2005 services are not listed in SQL Server Configuration Manager
  2. Red Cross shown in IIS – Server 2003

SOLVED QUICK

BITS – Background Intelligent Transfer Service may not be registered and may have a startup type of Manual and be Stopped.

regsvr32.exe qmgr.dll
regsvr32.exe qmgrprxy.dll

Then reboot.

SOLVED DETAIL

by external support company. Asked them how they did it. Answer copied here:

It looks like a windows update was responsible for the failure, Steve correctly spotted the fundamental issue which was that the default web site was stopped in IIS.  By attempting to start IIS, this registered an event in the windows logs and I could diagnose the issue from there.

The earlier versions of SQL reporting prior to 2008 relied on IIS to operate and the failure of IIS to start resulted in the missing items in SQL.

Windows update installed a hot fix which altered a file responsible for BITS (the service that enables Windows Update to function), the update applied an updated version of a file for IIS and the file failed to register when it was installed.  IIS was effectively looking for a file which wasn’t present and stopped itself.

By re-registering the file and rebooting, IIS was able to recognise the updated file and the default web site started successfully.  As the default website hosts information referred to by SQL, this caused SQL reporting to re-appear.

An odd one, but then Microsoft updates are probably responsible for a lot of strange behaviour!

Incidentally, while I was on the server, I spotted a string of Windows Desktop Search errors in the log – Windows Desktop Search was given as an update by Microsoft and it is my personal view that the product is poorly designed and disables perfectly good search facilities included within Windows, it looks like it has been clogging your event log with errors for some time now and with both of these things in mind,  I have disabled the Windows Desktop Search.

Anyway, glad SQL & IIS are now working, please let me know if you have any further issues.

SSRS open url in a new window

With thanks to:

http://www.bidn.com/blogs/BrianKnight/ssis/972/ssrs-action-to-open-a-url-in-a-new-window

=”javascript:void(window.open(‘”+ Fields!ReferURL.Value + “‘,’_blank’))”

 

SSRS Data Cache in Development

After some effort I found that data in SSRS Development environment was being cached in a files called <ReportName>.rdl.data
So you may be expecting data to change and finding that it is not. To get around this you can do one of the following:

The following error may be due to this cache of data
[rsInvalidExpressionDataType] The Value expression used in textrun ‘InvoiceTitle.Paragraphs[0].TextRuns[0]’ returned a data type that is not valid.

Good luck!

Test Project Root Namespace disabling referenced project namespace

PROBLEM
Had a project SRS.Business.EntityModel
and so created TestProject.SRS.Business.

SOLUTION
I found that this project could not code against the first. However when I subtly changed the Test Project Root Namespace in this case to TestProject.SRSBusiness it worked.

Programmatic Web Search – Screen scraping – Web Page Input

To open a web page

Dim process3 As New Process
process3.StartInfo.FileName = Webpage
process3.Start()

To open a google search

use following plus search words separated by +

http://www.google.co.uk/search?q=

To read web page source – for screen scraping


Public Sub ReadWebPageSource()

Dim request As WebRequest = WebRequest.Create(Me.WebPage)
‘ If required by the server, set the credentials.
request.Credentials = CredentialCache.DefaultCredentials
‘request.Timeout = 3000

Try
‘ Get the response.
Dim response As HttpWebResponse = CType(request.GetResponse(), HttpWebResponse)
‘ Display the status.
‘Console.WriteLine(response.StatusDescription)
‘ Get the stream containing content returned by the server.
Dim dataStream As Stream = response.GetResponseStream()
‘ Open the stream using a StreamReader for easy access.
Dim reader As New StreamReader(dataStream)
‘ Read the content.
Me.Source = reader.ReadToEnd()
‘ Display the content.
‘Console.WriteLine(responseFromServer)

‘ Cleanup the streams and the response.
reader.Close()
dataStream.Close()
response.Close()

Catch ex As Exception
Me.Source = “”
MsgBox(ex.Message)
End Try

End Sub

WebBrowser and Set Inner Text

tag id=”lst-ib” is Googles search text box

WebBrowser1.Navigate(“http://www.google.co.uk“)
WebBrowser1.Document.GetElementById(“lst-ib”).InnerText = InnerTextTextBox.Text
WebBrowser1.Document.GetElementById(“lst-ib”).Focus()