Archive

Archive for the ‘Crystal Reports’ Category

Vb6 software on Azure VM

There are still customers that uses old, aged software: for example, i have customers using my old program for truck transports, that uses a Microsoft Access file as database.
As i wrote in this post, this software in installed on a on-premise server, which was tipically Windows server 2003.
Now, these old servers (often recycled) are dying, the hardware today is more cheaper than in the past but an Windows Server 2012 license is still expensive for a small office.
So the idea: let’s try to migrate to Azure!
So i began an incredible amount of try & catch (errors..)
The application requires to send emails via CDO , with an attached pdf file generated from the Crystal Reports engine (used from the app for the printing activities).
In order to print via CDO is required a working email client; in the old Windows 2003 there was by default Outlook Express, instead from 2008 version in Windows Server there is no more an email client: this can be resolved installing Windows Mail, but it requires .NET 3.5
First error: using an Windows Server 2012 DataCenter NOT R2.
The main problem with the “normal” version is, IMHE (In My Humble Experience) that .NET 3.5 is not installable because you can’t use the downloadable 3.5 installer and this must be done from the Server Manager that complains about missing sources (for example see here, but i was not able to achieve the same result) : could be that attaching an ISO file (of Windows Server 2012) it works.
But i lose no more time and try creating instead an Windows Server 2012 R2 vm: same complain about the missing sources, but this time .NET 3.5 installed without issues.
The installation of Windows Mail was without problems and so for my app.
The only problem is the calendar for date input: my vb6 app uses mscal.ocx (perhaps a wrong choice) which is problematic.
For example in the setup.lst file generated from the vb6 package installer wizard the mscal is generated as

File10=@MSCAL.OCX,$(WinSysPath),$(DLLSelfRegisterEx),$(Shared),5/7/98 12:00:00 AM,90112,8.0.0.5007

But the DLLSelfRegisterEx must be changed in DLLSelfRegister otherwise the setup is not successful.
And in every case the problem is that the calendar was not displayed (interface in Italian):


The solution was to recreate from scratch the VM and create an vb6 installer WITHOUT mscal.ocx: it is already present in the Azure vm and trying to install another mscal.ocx causes big troubles in the registry; now the mscal.ocx is working ok.
Another curious thing was that in the vb6 code

With Flds
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = boolUseSsl
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = intSmtpAuth
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = strUser
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = strPwd
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSmtpSvr
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = intSendUsing
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = intSvrPort
    .Update
End With

“microsoft” was accidentally written with the initial “m” uppercase, so the email sending was not working in the new 2012 R2 server (instead in Windows Server 2003 yes!); after the correction, emails sent.

Advertisements

Strange behaviors in Crystal

I found today a strange behavior with Crystal Reports .NET , using Visual Studio 2010.
In a recordsource there are 2 numeric (integer) columns that were displayed in the preview window, inside Visual Studio.
At runtime for these 2 colums, nothing: no values displayed, even the headers.
This in a subreport; this one in origin had the same SQL of the main report.
Then the datasource of the subreport was changed, some columns previously used in the report was deleted, substituted with other columns, with different names.
I tried even to change the original subreport SQL (a JOIN between 2 CTE, inside every CTE a PIVOT) with a plain SQL (SELECT ‘value’ AS columnname) but without success: still no data displayed at runtime inside the subreport.
The solution is very strange, a Crystal formula (Vb syntax):

If IsNull({Command.TotalTSG}) then
    formula = 0
else
    formula = {Command.TotalTSG}
end if

If the fields are placed directly in the Crystal editor, data visible in preview but nothing at runtime; instead it works also at runtime with Crystal formulas: data displayed.
Note: all of the remaining fields (also integers) are displayed if placed directly on the report (but are fields in common as name between the main and the subreport), even without a formula.
Mysteries of Crystal Reports.

Categories: .NET, Crystal Reports, Vs2010

Old Crystal Reports Service Packs

I was searching the Service Pack 7 (the latest) for an ancient Crystal Reports 9: in the SAP (the current owner of Crystal Reports) site is not so immediate to find the things.. this is the link where to find this and others ancient SPs.

Apparently the selection of “Software product” does not work on Internet Explorer 10 , but it works on the latest Firefox.

Select Crystal Reports as “Software Product”, leave * as “Product Version”, select Service Pack on “Software Type”.

From the 4th page are listed the old legacy Service Packs.

Categories: Crystal Reports

Report object Subreports (minitutorial for seasoned “Crystallers”)

After a bad experience with ASP.NET Crystal Reports, i’m returned to use the Report Viewer 2010 for the printing needs of a .NET 4 web site that i’m developing.

Curiously in 12 years of web development is the first time that i have a request of to print something of complex from a web page, so there is a new range of problems.

The first lesson learned on the field is that the better thing to do for the data source is to provide an Object data source: if you try to provide directly a DataSet to the report at design time you can encounter various problems; the worst case is a stored procedure using a SQL Server temp table (the ones that begins with “#”, or “##” if you want that the temp table is recognized from another stored) , in my case with the Report object provided in Visual Studio 2010 i got an syntax error on the name of the temp table.

So i tried Crystal Reports because in my long experience (since 1998) with Crystal used from Visual basic 5/6 i never had problems with stored procedures (sometimes very complex) as data source, but this time with no luck.

In this case the stored is using DTE (Data Table Expressions) on temp tables, and returns a table (relative to Questions, “Domande” in Italian) ; in a class the datatable returned from the stored can be expressed as

public class PrintDomande
{
    public int IdDomanda { get; set; }
    public string TestoDomanda { get; set; }
    public bool RispostaInversa { get; set; }
    public string Dettagli { get; set; }
    public int NumOp { get; set; }
    public double MediaPerDomanda { get; set; }
    public double R1 { get; set; }
    public double R2 { get; set; }
    public double R3 { get; set; }
    public double R4 { get; set; }
    public double R5 { get; set; }
}

The Object data source is another class that reads from the stored and fills a List of the previous class:

public class PrintDomandeList
{
    public List<PrintDomande> GetData(string xmlDoc)
    {
        DataIfAdvices objData = new DataIfAdvices();
        DataTable rstPrintMain = objData.ExecuteStoredDomande(xmlDoc);
        List<PrintDomande> objLisT = new List<PrintDomande>();
        foreach (DataRow objRow in rstPrintMain.Rows)
        {
            objLisT.Add(
                new PrintDomande()
                {
                    IdDomanda = Convert.ToInt32(objRow["IdDomanda"].ToString()),
                    TestoDomanda = Convert.IsDBNull(objRow["TestoDomanda"]) ? String.Empty : objRow["TestoDomanda"].ToString(),
                    RispostaInversa = Convert.ToBoolean(objRow["RispostaInversa"]),
                    Dettagli = Convert.IsDBNull(objRow["Dettagli"]) ? String.Empty : objRow["Dettagli"].ToString(),
                    NumOp = Convert.ToInt32(objRow["NumOp"].ToString()),
                    MediaPerDomanda = Convert.IsDBNull(objRow["MediaPerDomanda"]) ? 0 : Convert.ToDouble(objRow["TestoDomanda"].ToString()),
                    R1 = Convert.IsDBNull(objRow["R1"]) ? 0 : Convert.ToDouble(objRow["R1"].ToString()),
                    R2 = Convert.IsDBNull(objRow["R2"]) ? 0 : Convert.ToDouble(objRow["R2"].ToString()),
                    R3 = Convert.IsDBNull(objRow["R3"]) ? 0 : Convert.ToDouble(objRow["R3"].ToString()),
                    R4 = Convert.IsDBNull(objRow["R4"]) ? 0 : Convert.ToDouble(objRow["R4"].ToString()),
                    R5 = Convert.IsDBNull(objRow["R5"]) ? 0 : Convert.ToDouble(objRow["R5"].ToString())
                }
            );
        }
        return objLisT;
    }
}

Adding a report with the Report wizard


(prepare yourself to a long wait) appears the window of the available sources (another wait time when you change Data source…and when you click Next)


The thing that i hate is that in the next screen i MUST choose a field to be used as grouping expression (but why ?..)

Otherwise you can’t click Next.


At a first sight i’m not understanding the utility of column groups, in every case by clicking next you see a preview


With subtotals


In my case i don’t need subtotals.

After is requested to choose a style


Our report after the last “click Next ” is ready


The columsn can de deleted , added, resized; is not as in Crystal Reports where you drop the field on the Details and eventually you use a Line object as separator: here you must create columns for every field: 2 fields in a column seems a problem.

As in Crystal Reports here you can add a new Detail row, this is done not from a Section Expert as in Crystal but you add a new row


While in Crystal Reports you can create a Formula that can be used more than once, here you apply an Expression to every field by right clicking the field


An expression must be written with the VB.NET syntax, for example we have a boolean field and we want to see a “X” for true values and nothing for false values is applied an expression as

=Iif(Fields!<boolfield>.Value, “X”, ” “):


Could be that there is a manner to create formulas valid for all the report (perhaps the Custom Code property of the Report object?)

The fields can be formatted as in Crystal Reports (as numeric with 2 decimals, for example).

In my case i have a stored procedure that gives me a summary DataTable for the main report , if in the same stored i pass a certain parameter value then is given an detailed dataset; the main ID is the link between the two identical dataset.

For the reporting with no details no problems , instead with a subreport the things went to complex.

I made a copy of the primary report and i have created the subreport:


Note that i have created a Header but in the final report this does not appear, but for now is not a problem.

Another at a first sight shocking discover was that apparently there isn’t a windows as in Crystal Reports where you can see all the report objects , in the toolbox ..nothing


The answer : from the View menu ofVisual Studio 2010 there is the item “Report Data”


By right clicking the datasource (in figure “DataSetDomandeSubRepo”) is possible to redefine the datasource.

The first thing to do for a subreport is to define a Parameter, in our case is the IdDomanda , the autoincrement Primary Key, so i defined


For Available Values and Default Values i leaved “No Default Value”; i have read in some blog that defining a Text data type is better to check “Allow blank value” and “Allow null value”.

In the main report i created a second detail row and here i inserted a subreport from the Toolbox.

The main problem is the free placement, by selecting cells is possible to join them with Merge cells in order to create a space


But is very difficult the objects positioning, while in Crystal Reports is more simple.

Anyway my final report is this :


After inserting the subreport i have linked the subreport (right clic on subreport) created before by simply writing his name … from this point of view Crystal Reports is very better.


The linking of the subreport parameter is done by create a parameter and writing the same name of the parameter defined in the subreport , the value is chosen from the current dataset (discover this simple thing was very difficult…):


At this point the big problem is that Crystal Reports has a preview, here …no preview , you must code a test page.

By placing a ReportViewer control in a page is automatically registered the assembly in the markup, and at runtime is requested an Microsoft Ajax toolkit ScriptManager ; in the page must be enabled the ViewState (sigh…)

<%@ Page Language=”C#” AutoEventWireup=”true” EnableViewState=”true”..

So a simple minimal page is this:

<%@ Page Title="" Language="C#" MasterPageFile="~/TemplateMaster.master" AutoEventWireup="true" CodeFile="TestStampa.aspx.cs" Inherits="Users_TestStampa" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<asp:Content ID="Content1" ContentPlaceHolderID="cphMain" Runat="Server">
    <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="true" EnablePageMethods="true" EnableScriptGlobalization="true" EnableScriptLocalization="true">
    </asp:ScriptManager>
    <br />
    <asp:UpdatePanel ID="UpdatePanel2" runat="server" UpdateMode="Conditional">
        <ContentTemplate>
            <asp:Button ID="cmdStampa" runat="server" Text="Stampa" onclick="cmdStampa_Click" />
            <br />
            <rsweb:ReportViewer ID="rvDomande" runat="server" Font-Names="Verdana" Font-Size="8pt" InteractiveDeviceInfos="(Collection)" WaitMessageFont-Names="Verdana" 
                WaitMessageFont-Size="14pt" Width="100%" Height="100%">
            </rsweb:ReportViewer>
        </ContentTemplate>
    </asp:UpdatePanel>
</asp:Content>

In this case we are enclosed in an asp:Content because the site is using a Master page.

The UpdatePanel is requested otherwise is done a postback at every page change/print/export with a complete page refresh , a possible problem for your page (that should do not make postback, is evil..)

If you use primarily JQuery (i use 1.7.2 version) and BlockUI the Report does not work : is much better a window.open and in this page use only , if possible, the Microsoft Ajax toolkit and no JQuery libraries; in every case no problems with the Dynatree JQuery plugin.

Ok , but how to pass a datasource a runtime ?

I have a button for to launch the print , with some textbox for the parameters, so my code is

protected void cmdStampa_Click(object sender, EventArgs e)
{
    DataIfAdvices objData = new DataIfAdvices();
    string strXmlMain = GetXmlFromParms(true);
    string strXmlPrint = GetXmlFromParms(false);
    rstPrintMain = objData.ExecuteStoredDomande(strXmlMain);
    rstPrint = objData.ExecuteStoredDomande(strXmlPrint);
    rvDomande.Visible = true;
    ReportDataSource rds = new ReportDataSource();
    rvDomande.Reset();
    rvDomande.ProcessingMode = ProcessingMode.Local;
    LocalReport rep = rvDomande.LocalReport;
    rep.Refresh();
    if(txtTIPODETTAGLIO.Text == "ND")
        rep.ReportPath = Server.MapPath("StampaDomandeND.rdlc");
    else
        rep.ReportPath = Server.MapPath("StampaDomande.rdlc");
    rep.DataSources.Clear();
    rds.Name = "DataSetDomande";
    rds.Value = rstPrintMain;
    rep.DataSources.Add(rds);
    if (txtTIPODETTAGLIO.Text != "ND")
        rep.SubreportProcessing += new SubreportProcessingEventHandler(rep_SubreportProcessing);
}

Note that if you don’t have technical problems or really complex data you can use the class as template for the report datasource , at runtime you can use a DataTable that has the same structure of the class (more fast).

I obtain two datatables rstPrintMain and rstPrint (for the subreport) by executing the stored with different XML parameters.

Note that i must supply the exact name for the datasource as defined in the report (“DataSetDomande”).

If the report is requesting details in a subreport then i must define an handler for the SubreportProcessing event.

Obviously i don’t want call the stored for every row of my report , but i called the stored for details once and in the subreport event i filter the dataset provided to the subreport.

The code:

void rep_SubreportProcessing(object sender, SubreportProcessingEventArgs e)
{
    if (rstPrint == null)
        return;
    DataRow[] objRows = rstPrint.Select("IdDomanda = " + e.Parameters[0].Values[0]);
    DataTable rstTemp = rstPrint.Clone();
    foreach (DataRow obJRow in objRows)
    {
        rstTemp.Rows.Add(obJRow.ItemArray);
    }
    e.DataSources.Add(new ReportDataSource("DataSetDomandeSubRepo", rstTemp));
}

Note the syntax for acquiring the parameter value (“.Parameters[0].Values[0]”).

An very interesting behavior that i discovered: in a first attempt i have written a fixed value ( i was not still discovering how to read the parameter) in order to verify the report , obviously the subreports were all equals, then i have tried

((System.Data.DataTable)(((Microsoft.Reporting.WebForms.LocalReport)(sender)).DataSources[0].Value)).DefaultView[intCounter][“IdDomanda”].ToString())

In order to read the primary value.

DefaultView[intCounter] : i have used a counter, starting from 0, and in this routine i wrote a intCounter++ ; in the debugger i have verified that the subreports records were ok for every subreport.

But in the report preview, though in the debugger the datasource for every report was correct, i was seeing for every subreport the first subreport dataset ! (i began to pull my hair…)

Instead using the correct syntax the report is ok: it seems that the reading of the parameter value causes something…


Charting : i inserted a simple diagram for every row without any problem; this feature in the Report Viewer 2010 is complete, there is a lot of possibilities.


In this dialog there is the section Legend where to change the legend text and many other things.

A note : since i’m using the Report object in my .NET 4 project i have random compilation errors as:

Object reference not set to an instance of an object.

And sometimes the compiler is not able to rewrite some dll :you must retry the compilation and then is ok.

Another little trick is requested for the Report Viewer nationalization : at the address http://www.microsoft.com/it-it/download/details.aspx?id=20884 select your language in the dropdown, the page is changed and you can download a file named ReportViewerLP.Exe , installing this on your pc and obvious on the production server the ReportViewer language is changed depending from the browser language (or the language you set from code); on the production server (tipically an Windows 2008 r2) is required to download and install the Microsoft ReportViewer 2010 Redistributable (you can easily find the page with Google).

Passing an DataTable as source the refresh button must be disabled, otherwise there are errors.

<rsweb:ReportViewer ID="rvDomande" runat="server" ... " ShowRefreshButton="false Width="100%" Height="100%">
</rsweb:ReportViewer>

Normally the Report Viewer 2010 works very well , i have tried with Internet Explorer 9, 10, Firefox (Windows, Mac, Ubuntu) , Chrome (Window, Ubuntu) , Safari (Windows, Mac), Opera , Camino (Mac).

Bad surprises instead with Internet Explorer 7 and 8: it seems all ok by previewing the first page , but going to another page of the report the report body is reduced to a thin band.

I have pulled my hair another time… discovering also that in a localized site with Ie 7 in some windows the labels are showed in the base language, in my case English, even if the operating system and the browser are in Italian: you must force by code the localization (the classic flag image..).

In the Webkit based browsers (Chrome, Safari) there were 2 scrollbars (one for the window, one for the report).

Finally after 2 days of experimentations and analisys of the produced html code in the final page i have discovered that with these lines of code in the Page_Load you address the problems of WebKit and Ie7/8:

HttpBrowserCapabilities brObject = Request.Browser;
string strBrowser = ((System.Web.Configuration.HttpCapabilitiesBase)(brObject)).Browser;
string strType = ((System.Web.Configuration.HttpCapabilitiesBase)(brObject)).Type;
if (String.Compare(strBrowser, "chrome", StringComparison.OrdinalIgnoreCase) == 0 || String.Compare(strBrowser, "safari", StringComparison.OrdinalIgnoreCase) == 0)
{
    rvDomande.AsyncRendering = false;
    rvDomande.SizeToReportContent = true;
}
if (String.Compare(strType, "IE7", StringComparison.OrdinalIgnoreCase) == 0 || String.Compare(strType, "IE8", StringComparison.OrdinalIgnoreCase) == 0)
{
    rvDomande.AsyncRendering = false;
    rvDomande.SizeToReportContent = true;
}

But it is needed a final touch : in Ie7/8 the Report was now working in every page but reduced as width also in the first page, even if in the Report markup is specified Width=”100%”; examining with F12 the Html i have noticed that there is a table named ctl00_cphMain_<your report id>_fixedTable wrapping the report: trying to add a style width:100% et voilà , fixed, so i added a css as this:

#ctl00_cphMain_rvDomande_fixedTable{
    width:100%;
}

Only in Internet Explorer browsers are automatically generated buttons for Zoom and Print; but curiously the only browser where the Zoom above 100 is working ok (In ie9 the page is cutted at the top, In ie8 to the bottom) is Ie7! : so in my Report control markup i have added ShowZoomControl=”false” (in every case the customer can export to pdf, Word,Excel: here you can zoom at your will) .

I’m not the best html&css expert and i believe that the things can be done better, but now all is working in every browser i tried.

Final considerations : the Report object has interesting features , for example the Tablix concept , Variables, or Custom code that can be inserted in the report :


But is very hard to find well written documentation with examples about how to use these things.

I hope that in Visual Studio 2012 there is more documentation and the Report object has improvements.

Categories: .NET, Crystal Reports, Vs2010

Old Crystal Tricks

Many years ago i developed a software for transports by truck (that i should rewrite, i’m thinking to a Metro App) , in this blog i already wrote a post about.

In these days the problem: for another customer i have prepared a near identical pc, with the same Windows 2003 standard 32 bit , and surprise , the reports using UFL functions are not working : trying to launch them = crash , the program exits without errors ; in the event viewer only a generic message that my program crashed.

What is a UFL function ? In old Crystal Reports (i don’t know for the latest release, my program uses reports that was developed with Crystal 9 , Service pack 7) you can use custom function implemented in Visual Basic DLL; these functions are visible in Crystal if the name begins with “CRUFL” , so i have developed a dll named CRUFLWtk


That gives the cities reached from a truck in a trip : my program has a Trips archives where for every Trip there is a list of Cities reached in the Trip .

Compiling the DLL you can see in Crystal the new functions available for a formula, in my case


These functions does nothing else to give a list of cities separated from a comma, for example “Torino, Genova,Milano”

After some attempt (also the install of Crystal Reports on the server…) there were 2 options:

  1. Make a image of the working server done with Vmware vCenter Converter, then launch on the other customer server a VmWare Player using this vm
  2. Search an alternative to UFL functions, because these functions in this case does nothing of very special

The UFL reports (launched from the vb6 program) are perfectly working on every Windows XP on the earth i have tried, so i assume that there are problems with Windows 2003, and the fact that for a customer all is working under Windows 2003 is a strange miracle that i have no time to investigate; finally for this software there are no future developments (as vb6 version).

I have decided for the second option thinking to the old good WhilePrintingRecords that i have used a lot in the past.

The first step was to insert a subreport , with a ADO source, but i was having the problem that also in Crystal there was authentication troubles, the database password was not recognized.

Here the strange discovery : in order to pass the database password of a Microsoft Access file to a subreport (the Access file is password protected, ok is a weak protection but best than nothing) there is a curious procedure (discovered after a last desperate attempt………):

  1. Insert a subreport in Crystal


  1. Choose every time (do not reuse previous connections: apparently does not work !) to create a new ADODB connection


  1. Choose the OLE DB 4 provider, BUT click on “use a connection link file” and select a udl file prepared first (sorry for the Italian language..the XP vm is in Italian because the customers are tipically Italians)


  1. For to create a udl file right clic somewhere (better if in the program folder where are the reports) and create a file with .udl extension, open with double click
  2. Compile for a connection to a Access file (in my case) and make sure to write the db password, and save the udl with the password enclosed


 
 


Doing this , finally when i have defined the link between the main and the subreport no password problem when i was trying the report preview.


At this point how to obtain all the cities touched from our truck drivers, or the start and last cities ?

In my program we have an archive of steps, with a start and a end for every step:


Well, we can define our subreport with these formulas in order to obtain a list of all cities

INIT

WhilePrintingRecords;
StringVar ConcatenatedID := "";

RUNNING

WhilePrintingRecords;
StringVar ConcatenatedID := ConcatenatedID + {GetSintesiPercorsoViaggioCrystal.SiglaPartenza} + "," + {GetSintesiPercorsoViaggioCrystal.SiglaArrivo} + ",";

SHOW

WhilePrintingRecords;
StringVar ConcatenatedID;

GetSintesiPercorsoViaggioCrystal in my sample is a view without parameters that gives a table as


With the subreport defined as


We have a result as (not the illustrated case)


 
 

The subreport in enclosed without borders, with a height equal to the one of the other normal fields.

For the other case where we need the start city of first trip and the end city of the last trip we must change our formulas:

INIT

>WhilePrintingRecords;
StringVar strStart := {GetSintesiPercorsoViaggioCrystal.SiglaPartenza};
StringVar strEnd := "";

RUNNING

WhilePrintingRecords;

SHOW

WhilePrintingRecords;
StringVar strStart ;
StringVar strEnd := {GetSintesiPercorsoViaggioCrystal.SiglaArrivo};
strStart + " --- " + strEnd

The result:


The only problem is that specifying”Can grow” in the subreport and in the main report the textbox does not grow if the text field is larger than the subreport: so beware of this.

For now is fine, i’m out of the Windows 2003 UFL reports nightmare.