Wednesday, July 22, 2009

Difference between NULL and DBNull

Difference between NULL and DBNull

Friday, July 17, 2009

External Stylesheets issue in Telerik Reporting

Situation:
I was able to create an stylesheet, exported into an xml file and then referenced it into another report and even though I could see the styles in the designer, when I run my project the styles simply disappeared.

Solution 1:
In the page where the report is called add the following code (replace the path properly):


Dim ess As New Telerik.Reporting.Drawing.ExternalStyleSheet("..\StyleSheets\TahomaSmall.xml")
myReport.ExternalStyleSheets.Add(ess)
Me.ReportViewer1.Report = myReport


Solution 2:
Replace the path properly in the rpt.designer.vb
Me.ExternalStyleSheets.Add(New Telerik.Reporting.Drawing.ExternalStyleSheet("..\StyleSheets\TahomaSmall.xml"))


Issue cause:
Even though the report has a reference to the external stylesheet in its vb code if your report is not in the same directory as your stylesheet it has to be estated like that in the reference.

Telerik Reporting Report

Issue when deploying a report. If the error shows sth like this inside, just add this into the web.config file



Wednesday, July 15, 2009

Issues with generated WebProject dll

When I try to build my solution I often get this error:
Unable to copy file "obj\Debug\MyProjectXXX.dll" to "bin\Debug\MyProjectXXX.dll". The process cannot access the file 'bin\Debug\MyProjectXXX.dll' because it is being used by another process.
The project it's trying to build is a class library referenced by a couple of other projects in the solution.

Solution

1. Delete whatever files exist in obj/Debug (just files)
2. Add these two lines to the "Pre-build event command line" in the MyProjectXXX properties:

if exist "$(TargetPath).locked" del "$(TargetPath).locked"
if exist "$(TargetPath)" if not exist "$(TargetPath).locked" move "$(TargetPath)" "$(TargetPath).locked"

1. Build the project again.


Reference:
http://social.msdn.microsoft.com/Forums/en-US/csharpide/thread/68648642-08d8-4cff-b0c3-c2782f010589

Wednesday, July 8, 2009

Telerik Reporting

This is up to the moment the best Alternative to SRS.

Advantages over SRS:
1. It doesn't need to work with SQL Server, thus it doesn't depend on any SQL upgrade.
2.Since it doesn't need SQL Server information, the program (VStudio) can perfectly send a dataset which will be directly read by the telerik reporting report control.
3.We can easily define user functions using vb sintaxis and everything we are used to use in VStudio.
4.Stylesheets are easy to implement.


Using stylesheets in Telerik Reporting Reports
To use stylesheets in a Telerik reporting report you just need to do this two steps:
1. Define your own styles for a report (template report)
2. Export your styles to a xml file in order to be called from other reports.
http://www.telerik.com/help/reporting/style-exportingand-reusing-style-sheets.html

How to send a Telerik Reporting Report by email?

I will explain it step by step following the architecture I have here, which is one complete project for all the application reports, so that all the reports will be stored in only one place and called from wherever is needed.

MyWebProject - Architecture

rptFilters.aspx.- It contains the report filters (user input to get the data). These pages are created by report.

ReportPreview.aspx.- It contains the ReportViewer control. It is only one ReportPreview page for all the report filters pages because the filter page from it gets the data is a parameter.

Report.vb.- This is the report.

 

Report.vb code extract

Getting the information from a dll method (the hatParams table has been filled in the rptFilters.aspx.vb page)

Public Sub New(ByVal strParams As String, ByVal strUser As String, ByVal strServer As String, ByVal strPort As String, ByVal intComp As Integer, ByVal strLang As String)
    Dim cSynergy As DLLSynReports.clsReports
    Dim hatParams As Hashtable = Nothing
    Dim dsData As System.Data.DataSet = Nothing
    Dim iResult As Integer
    cSynergy = New DLLSynReports.clsReports(strUser, strServer, strPort, intComp, strLang, strPath, strModName)
    hatParams = FillHashTableByReports(strUser, "rptOrderStatus", strParams)
    InitializeComponent()
    Try
        iResult = cSynergy.GetUnicSOReport(CType(hatParams("WhseFrom"), Integer), _
                            CType(hatParams("WhseTo"), Integer), CType(hatParams("ReqDateFrom"), Integer), _
                            CType(hatParams("ReqDateTo"), Integer), CType(hatParams("PlantDateFrom"), Integer), _
                            CType(hatParams("PlantDateTo"), Integer), CType(hatParams("ShipDateFrom"), Integer), _
                            CType(hatParams("ShipDateTo"), Integer), hatParams("ProductFrom").ToString, _
                            hatParams("ProductTo").ToString, CType(hatParams("GroupProdFrom"), Integer), _
                            CType(hatParams("GroupProdTo"), Integer), CType(hatParams("ShipToFrom"), Integer), _
                            CType(hatParams("ShipToTo"), Integer), CType(hatParams("Dept"), Integer), _
                            CType(hatParams("SalesManFrom"), Integer), CType(hatParams("SalesManTo"), Integer))
        Me.DataSource = dsData
    Catch ex As System.Exception
        'An error has occurred while filling the data set. Please check the exception for more information.
        System.Diagnostics.Debug.WriteLine(ex.Message)
    End Try
End Sub

ReportPreview.aspx.vb

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim datInfo As Data.DataSet = Nothing
    Dim strParams As String
    Dim strReports As String
    Dim strEmailReports As String
    Dim hatParams As Hashtable
    Dim strFormat As String
    Try
        If Not Page.IsPostBack Then
            datInfo = cUtil.GetSpecificTable("Synergy_session", "", "SYSE_SEQ = '" & strSYSE_SEQ & "' ", "")
            strReports = datInfo.Tables(0).Rows(0).Item("SYSE_PARAM1").ToString
            strEmailReports = datInfo.Tables(0).Rows(0).Item("SYSE_PARAM2").ToString
            strParams = datInfo.Tables(0).Rows(0).Item("SYSE_PARAM3").ToString
            strReportName = "RPT" & strSYSE_SEQ
            hatParams = ConfigReports(strReports, strParams)
            If hatParams.Item("Reports").ToString = "0" Then
                lblError.Text = GetCommonMessage(strGlobalUser, "NoRecord", "en")
            Else
                lblError.Text = ""
                strFormat = Request.QueryString("EmailFormat")
                If strFormat = "" Then
                    strFormat = "PDF"
                End If
                If strEmailReports = "True" Then
                    SendReportTelerik(strGlobalUser, strFormat, CType(ReportViewer1.Report, Telerik.Reporting.Report), hatParams, strReportPath, strReportName, strLang)
                End If
            End If
        End If
    Catch ex As Exception
        Call ManageError(strGlobalUser, strModName, strModName, "Page_Load", intGlobalMain, Err, strGlobalPathError, ApplicationType.Web)
    Finally
        If Not datInfo Is Nothing Then
            datInfo.Dispose()
            datInfo = Nothing
        End If
        cUtil = Nothing
    End Try
End Sub
Private Function ConfigReports(ByVal strReports As String, ByVal strParams As String) As Hashtable
    Dim cSynergy As DLLSynReports.clsReports
    Dim hatParams As Hashtable = Nothing
    'Dim intUnicId As Integer
    Dim strRpt As String
    Try
        hatParams = FillHashTableByReports(strGlobalUser, strReportName, strParams)
        hatParams.Add("User", strGlobalUser)
        hatParams.Add("Reports", 999)
        strRpt = ""
        Select Case strReports
            Case "rptGlobalInv.aspx"
                Dim myReport As New GlobalInv(strParams, strGlobalUser, strGlobalServer, strGlobalPort, intGlobalComp, strLang)
                myReport.ReportParameters(0).Value = "Danstar"
                myReport.ReportParameters(1).Value = hatParams.Item("User").ToString()
                myReport.ReportParameters(2).Value = hatParams.Item("DateFrom").ToString()
                myReport.ReportParameters(3).Value = hatParams.Item("WhseFrom").ToString()
                myReport.ReportParameters(4).Value = hatParams.Item("WhseTo").ToString()
                myReport.ReportParameters(5).Value = hatParams.Item("Whse1").ToString()
                myReport.ReportParameters(6).Value = hatParams.Item("Whse2").ToString()
                 Me.ReportViewer1.Report = myReport
            Case "rptOrderStatus.aspx"
                Dim myReport As New OrderPrice(strParams, strGlobalUser, strGlobalServer, strGlobalPort, intGlobalComp, strLang)
                myReport.ReportParameters(0).Value = "Danstar"
                myReport.ReportParameters(1).Value = hatParams.Item("User").ToString()
                myReport.ReportParameters(2).Value = hatParams.Item("WhseFrom").ToString()
                myReport.ReportParameters(3).Value = hatParams.Item("WhseTo").ToString()
                myReport.ReportParameters(4).Value = hatParams.Item("ReqDateFrom").ToString()
                myReport.ReportParameters(5).Value = hatParams.Item("ReqDateTo").ToString()
                myReport.ReportParameters(6).Value = hatParams.Item("PlantDateFrom").ToString()
                myReport.ReportParameters(7).Value = hatParams.Item("PlantDateTo").ToString()
                myReport.ReportParameters(8).Value = hatParams.Item("ShipDateFrom").ToString()
                myReport.ReportParameters(9).Value = hatParams.Item("ShipDateTo").ToString()
                myReport.ReportParameters(10).Value = hatParams.Item("ProductFrom").ToString()
                myReport.ReportParameters(11).Value = hatParams.Item("ProductTo").ToString()
                myReport.ReportParameters(12).Value = hatParams.Item("GroupProdFrom").ToString()
                myReport.ReportParameters(13).Value = hatParams.Item("GroupProdTo").ToString()
                myReport.ReportParameters(14).Value = hatParams.Item("ShipToFrom").ToString()
                myReport.ReportParameters(15).Value = hatParams.Item("ShipToTo").ToString()
                myReport.ReportParameters(16).Value = hatParams.Item("Dept").ToString()
                myReport.ReportParameters(17).Value = hatParams.Item("SalesManFrom").ToString()
                myReport.ReportParameters(18).Value = hatParams.Item("SalesManTo").ToString()
                myReport.ReportParameters(19).Value = hatParams.Item("PriceType").ToString()
                Me.ReportViewer1.Report = myReport
            Case Else
        End Select
        If strRpt <> "" Then
            hatParams.Add("rpt", strRpt)
        End If
        ConfigReports = hatParams
    Catch ex As Exception
        hatParams.Add("User", strGlobalUser)
        hatParams.Add("Reports", 0)
        ConfigReports = hatParams
        Call ManageError(strGlobalUser, strModName, strModName, "ConfigReports (" & strReports & ")", intGlobalMain, Err, strGlobalPathError, ApplicationType.Web)
    Finally
        cSynergy = Nothing
    End Try
End Function
Public Sub SendReportTelerik(ByVal strUser As String, ByVal strFormat As String, _
                      ByVal report As Telerik.Reporting.Report, ByVal hatParams As Hashtable, ByVal strReportPath As String, _
                      ByVal strReportName As String, ByVal strLang As String)
    Dim strReports As String
    Dim strResult As String
    Try
        strReports = CreateTelerikReports(strUser, strFormat, report, strReportName, strReportPath, hatParams)
        strResult = EmailReports(strUser, strReports, "", strReportName)
    Catch ex As Exception
        Call ManageError(strUser, strAppName, strModName, "SendReport", intGlobalMain, Err, strGlobalPathError, ApplicationType.Web)
    End Try
End Sub
Public Function CreateTelerikReports(ByVal strUser As String, ByVal strFormat As String, ByVal report As Telerik.Reporting.Report, ByVal strReportName As String, ByVal strReportPath As String, _
                          ByVal hatParams As Hashtable) As String
    Dim bytResult As Byte() = Nothing
    Dim strOutputPath As String
    Dim strDevInfo As String
    Dim strEncoding As Text.Encoding = Nothing
    Dim strMimeType As String = ""
    Dim strExtension As String = ""
    Try
        strOutputPath = "\Reports\" & Mid(strUser, 5)
        strDevInfo = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
        Try
            bytResult = Telerik.Reporting.Processing.ReportProcessor.Render("PDF", report, Nothing, strMimeType, strExtension, strEncoding)
        Catch exSoap As SoapException
            Call ManageError(strUser, strAppName, strModName, "CreateTelerikReports", intGlobalMain, Err, strGlobalPathError, ApplicationType.Web)
        End Try
        Select Case strFormat
            Case "PDF"
                strExtension = ".pdf"
            Case "MHTML"
                strExtension = ".mht"
            Case "EXCEL"
                strExtension = ".xls"
        End Select
        Call CreateStreamFile(strUser, bytResult, strReportName & strExtension, strOutputPath)
        CreateTelerikReports = strOutputPath & "\" & strReportName & strExtension
    Catch ex As Exception
        CreateTelerikReports = ""
        Call ManageError(strUser, strAppName, strModName, "CreateTelerikReports", intGlobalMain, Err, strGlobalPathError, ApplicationType.Web)
    End Try
End Function
Public Function EmailReports(ByVal strUser As String, ByVal strReports As String, _
                        ByVal strCC As String, ByVal strSubject As String) As String
    Dim cMail As New DLLLallemand.cBIZExchange(strUser, DLLLallemand.DataConnection.Lallemand, ApplicationType.Web)
    Dim cUdtMail As New DLLLallemand.clsUDTMail
    Try
        cUdtMail.strMAIL_FROM = "BatchReports@aaa.com"
        cUdtMail.strMAIL_SUBJECT = strSubject
        cUdtMail.strMAIL_MESSAGE = "Look at the atttachement for your requested Reports !"
        cUdtMail.strMAIL_URL = ""
        cUdtMail.colMAIL_TO.Add(strUser & "@aaa.com")
        cUdtMail.colMAIL_CC.Add(strCC)
        cUdtMail.colMAIL_ATTACHEMENT.Add(System.Web.HttpContext.Current.Request.MapPath("~") & strReports)
        EmailReports = cMail.SendMail(strUser, cUdtMail)
    Catch ex As Exception
        EmailReports = "error"
        Call ManageError(strUser, strAppName, strModName, "EmailReports", intGlobalMain, Err, strGlobalPathError, ApplicationType.Web)
    End Try
End Function

Wednesday, July 1, 2009

SRS

Making operations with agrupated fields:

http://msdn.microsoft.com/en-us/library/ms157274(SQL.90).aspx