Getting started with Express Reporting Services 2005

Downloading and installation

  1. Download SQLEXPR_ADV.EXE from the site of Microsoft to this page after you selected the language. SQLEXPR_TOOLKIT.EXE from this page after you selected the language.
  2. Important! First install Sql Server Express 2005
  3. If you have problems of installation, uninstall Sql server express and its correlate softwares, and after install first SQLEXPR_TOOLKIT.EXE and next SQLEXPR_ADV.EXE
  4. Download and install ODBC Firebird driver from here.
  5. Start>Settings>Control panel>Administration services>Origin ODBC. You will find the item Firebird/Interbase (r) driver in the Driver tab. Clic on "customer DNS" tab . Clic on the Add button>Select Firebird driver>it will open the window Setting for ODBC Firebird. In the textbox Name of the source of data (DSN) write any name for the source of the data. In the Database textbox insert the complete path of the Firebird database on your PC. In the textbox Client DLL insert the path of the library fbclient.dll on your PC.
  6. After the SQLEXPR_ADV_ITA (EN) installation it is appeared in the menù start directory, between the others, the voice Microsoft Sql Server 2005> Sql Server Business Development Intelligence Studio. Clic on it.
  7. Will be opened “Visual Studio 2005” IDE . Up in the Standard bar of the instruments clic on New Project, or in the Initial Page clic on Create Project. Give a name to the new project.
  8. Will appear "New Project" window. In the left pane select "Business Intelligence Projects". In the right pane select "Server Report" wizard. Give e name tho the project. Ok. Now you'll see report wizard window. Clic next.
  9. Now you have to set Data origin. Give it a name. From the combo "Type" select ODBC. Clic "edit". It will appear Connection properties window. Select "use connection string". Clic on "Generate" button. Ok. Will appear the connection string window. Next. Will appear Query project window. Write a simple query: select*from YourTable.
  10. Next. In the window Select type of report select, for simplicity, Table. Next. In the window Planning Table the fields of the table will appear to you, on the left side. Leave all as it is. Next. In the window Chose table style you can choose between the types of style listed, but it is clear that if you will not follow the guided procedure you can personalize as you want the styles of the report. Next. In the window Completion wizard you may give a name to your report. Therefore the end.
  11. To add a new report, in explorer solutions select Report folder and right clic Add report. you'll see report wizard window. Clic next. Now follow the steps from 9 to 10.


Therefore you have created your first one report with the Reporting Services. In the next argument we will see how to use the report with the control reportviewer in VB 2005.

Reporting Services 2008 don't support reportviewer add in

I have realize that Reporting Services 2008 don't support reportviewer add in. Particularly isn't possible pass the report parameters to the reportviewer because arises an exception for the setparameters method.

using report parameters in SSRS 2005

In this post we'll use report parameters to center an image in a reportviewer control.

In the report project create the file Picture.rdl (SSRS 2008 don't support any reportviewer add in with reference to report parameters, this is a bug). To create a file rdl connected to firbird ODBC let see: Getting started with Express Reporting Services 2005
In the rdl file create two controls: image control and textbox control.
From the menu choose Report > Report Parameters command. Press Add to add a parameter.The value of a parameter can be used in expressions as Parameters!Foo.Value where Foo is the name of the parameter.
To center an image in the report we'll make use of the image control "padding" property, because it lets use expressions.
The "source" property have to be set: external
The "sizing" property have to be set: fitProportional
In order to select and center the image in the image control we'll make use of three parameters:
P (text type)
PadLeft (Integer type in points)
PadRight (Integer type in points)

As value of the left padding we have: =Parameters!PadLeft.value & "pt"
As value of right padding we have: =Parameters!PadRight.value & "pt"
As value of Value property we have: "file:///" & Parameters!P.Value

In order to write a caption we'll add to the report a large textbox control
In order to write a caption in the textbox control we'll make use of a parameter:
Caption
As value of Value property we have: =Parameters!Caption.Value
to show the report in Visual Basic we'll make use of a form with a reportviewer control in it.



In the Fprincipale form the code to show the image and caption in a reportviewer control is:
Imports FirebirdSql.Data.FirebirdClient
Imports Microsoft.Reporting.WinForms


Private Sub Report(ByVal filename As String)
ShowReport.ReportViewer1.LocalReport.EnableExternalImages = True
Dim strImage, strContesto As String, strPadleft, strPadRight As Integer

Dim params(3) As ReportParameter

strImage = Me.DataGridView1.CurrentRow.Cells("Percorso").Value
strContesto = Me.DataGridView1.CurrentRow.Cells("Contesto").Value

image1 = New Bitmap(strImage, True)
If (image1.Width / 72) * 2.54 > 17 Or (image1.Height / 72) * 2.54 > 12.25 Then

strPadleft = (17 / 2.54 - 12.25 / 2.54 * (image1.Width / image1.HorizontalResolution) / (image1.Height / image1.VerticalResolution)) * 72 / 2

in this case the left and right padding have to be set so that the new image.width is resized by the ratio
(12.25 / 2.54)/(image1.Height / image1.VerticalResolution).
strPadRight = strPadleft

Else

strPadleft = (17 / 2.54 - (image1.Width / 96)) * 72 / 2

in this case the image is resized with reference to screen resolution 96 DPI that is set by Windows OS. See Computer monitor DPI standards.
When th DPI image resolution is lower than the computer monitor DPI standard, the image appears smaller with reference to its printer sizes. Then, since the image control fits proportionally an image with a given DPI resolution, we, to see the real image size in reportviewer, need to refer to computer monitor DPI standard. Then say, 72*96/72=96, that is the number that apperars in the strPadLeft expression.

strPadRight = strPadleft

End If

params(0) = New ReportParameter("P", strImage, False)

params(1) = New ReportParameter("Didascalia", strContesto, False)

params(2) = New ReportParameter("PadLeft", strPadleft, False)

params(3) = New ReportParameter("PadRight", strPadRight, False)
ShowReport.ReportViewer1.LocalReport.ReportPath = filename ShowReport.ReportViewer1.LocalReport.SetParameters(params)
Dim RepDS As New ReportDataSource

Dim dv As New System.Data.DataView

dv = DataSet1.Tables("Immobili").DefaultView

RepDS.Name = "DataSource1"
RepDS.Value = dv
ShowReport.ReportViewer1.LocalReport.DataSources.Clear()
ShowReport.ReportViewer1.LocalReport.DataSources.Add(RepDS) ShowReport.ReportViewer1.LocalReport.ReportPath = filename
End Sub


Private Sub btnReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReport.Click

Try
Report(My.Application.Info.DirectoryPath & "\Picture.rdl")
Catch
MsgBox("Select a record")
Exit Sub
End Try

ShowReport.Show()

End Sub
The source code Picture.zip cans be downloaded here

IIS show error running ASP.net pages? Solved

Hello friends,
I noticed my 5.1 IIS on windows xp not showing aspx pages. After some troubling, I found the cause of the issue: I needed uninstall and reinstall the .net framework.
If you have many versions of framework installed on your machine you need a working unistalling freeware tool: dotnetfx cleanup. Here's the download page: http://www.filestube.com/b7b587c2f858fd0203e9,g/dotnetfx-cleanup-tool.html

Howto create a DSN

Follow the next procedure: close VWD. First install a Firebird ODBC build from Firebirdsql.org. Then Start>Settings>control Panel>Administrative tools>Data source OBDC>"User DSN" tab>Add button>Select Firebird/Interbase(r) driver>in the textbox "Data source name" insert a cutom name>In Database textbox browse your database>In client DLL browse fbclient.dll from your Firebird Application folder>In user database textbox insert database admin username>in "password" textbox insert admin password>In "caracter set" combobox select WIN1251. Now test the connession>Press the confirm button.
Goodbye.

Establishing a data connection for Asp.net page

With Visual Web Developer, from Microsoft:

  1. Create an ODBC data source for the database
  2. In default.aspx web form drag and drop a Gridview control
  3. Click on the top right of the Gridview control. Select "new data source". On the page will appear a window with various icons. Select "Database". Click OK.
  4. Noe 'll appear SqlDataSource1 control. Click on "New connection" button.
  5. It'll appear a window. Click on Edit button and select "Other". In the label It'll appear ".Net Framework Data Provider for ODBC"
  6. Below click the option button "Use connection string". Click on "Generate.." button, and select the tab "Computer data source". Then select your data source name. Then OK. Next.
  7. Click on the "Specify a stored procedure or a customized SQL statement" option button. Otherwise you may get a token error message because of the square brackets for a string without white spaces.
  8. You may use "select" statement. Write the select statement. Next. Click on "Test query" button.
  9. Now you should see your table.
  10. For further test start the debug.

Solved datagridview issue of new record editing

The issue, typical in Firebird, consist in the fact that, after you insert a new record with an autoincrement field in a datagridview, we have to exit from the application to be able to edit the new inserted record.

To solve this issue you have to proceed this way:
1) Proceeding for semplicity with SQL Manager 2005 lite software, Call
the autoincrement field "Counter"
2) In the database of interest create a new table called for example "Counter" with only a field named "memcont", type Integer.
3) To insert a record use this routine:
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim ObjConnection As New FbConnection()
Dim connection As FbConnection = New

FbConnection("servertype=1;username=sysdba;password=masterkey;database=" _

& My.Application.Info.DirectoryPath & "\mydb.gdb")
connection.Open()

' Starts a local transaction.
Dim fbTran As FbTransaction = connection.BeginTransaction()

' Enlist the command in the current transaction.
Dim command As FbCommand = connection.CreateCommand()
command.Transaction = fbTran

Try
command.CommandText = _
"insert into new_table1" _
& " (Clienti, spesa) values ('" & Me.TextBox1.Text & "','"

_
& Me.TextBox2.Text & "')"
command.ExecuteNonQuery()
command.CommandText = _
"delete from counter"
command.ExecuteNonQuery()
'There we make use of the generator GEN_ID function of Firebird with no increment
command.CommandText = _
"INSERT INTO counter(memcont)
VALUES(GEN_ID(new_table1_COUNTER_GEN, 0))"
command.ExecuteNonQuery()
fbTran.Commit()
Catch ex As Exception
MsgBox("Insert a Integer number for field spesa")

fbTran.Rollback()
Finally
ObjConnection.Close()
End Try

'There we create an instance of the new row in the datatable
Dim persRow As DataRow = Principale.DataSet1.Tables("counter").NewRow
If IsDBNull(persRow("memcont")) Then
persRow("memcont") = Principale.DataSet1.Tables("New_table1").Rows.Count
End If
Principale.DataSet1.Tables("counter").Rows.Add(persRow)
'There we create an instance of the row 0 of the "counter" datatable


Dim CustomersRow As DataRow = Principale.DataSet1.Tables("counter").Rows(0)
'There we create an instance of the new row of the "New_table1" datatable
Dim newCustomersRow As DataRow = Principale.DataSet1.Tables("new_table1").NewRow()
' The autoincremnt field value musts reflect the "memcont" value +1
newCustomersRow("counter") = CustomersRow("MEMCONT") + 1
' there we assign to the unique record in the field "memcont" of the datatable "counter" the
new value of the autoincrement field

CustomersRow("MEMCONT") = newCustomersRow("counter")
newCustomersRow("CLIENTI") = Me.TextBox1.Text
newCustomersRow("spesa") = Me.TextBox2.Text


Principale.DataSet1.Tables("new_table1").Rows.Add(newCustomersRow)

Me.Close()

End Sub

Older Posts