Home > .NET, Crystal Reports, Vs2010 > Report object Subreports (minitutorial for seasoned “Crystallers”)

Report object Subreports (minitutorial for seasoned “Crystallers”)

2012/07/27

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.

Advertisements
Categories: .NET, Crystal Reports, Vs2010
%d bloggers like this: