Telerik blogs
ReportingT Dark_1200x303

Telerik Report Server has been an important tool for JADER Ltd. in its Buku Project, aiding in the creation of the accountancy product j Count®. Let me show you around!

j Count® is the product we are writing and is part of the Buku project, or platform. Buku, for the uninitiated, means “book” in Malay. Essentially, the product started as an accountancy offering. It is intended for the UK (initially) market and SME-sized businesses. The product will be robust enough and functional enough for larger organizations as time passes. We have other ideas as the program structure is suited for many dashboard-type applications.

Typically, it can run in an iFrame and outside an iFrame, so it can sit within any website and be branded accordingly. Our wish is to make the program “belong” to the customer, while being a SaaS offering. (When in the real world is a rental yours?) We also want to keep up with enhancement requests, so that is an ethos of our business model, and any software company knows that is a hard ask.

If you go to https://www.buku.uk, you’ll notice an Alternate website on the menu. This is a simple HTML, CSS and jQuery/JavaScript website. If there is such a thing as a simple website. The program also runs in the WordPress Buku site. This illustrates the beauty of an iFrame. Any flavor of site can host it.

Telerik Report Server (TRS) is an offering from Telerik by Progress. The program can render reports within a viewer, behind the scenes and in a scheduled manner. Typically, it connects to a SQL Server database via a connection string but can use virtually any database that has a ADO.NET provider or an ODBC driver installed, i.e., the solution is database agnostic. The connection is exposed to the report to “see” the fields in the Data Source.

TRS helps to provide a structured output from the input of the j Count software. What we envisage is a mechanism by which we can schedule reports, have them emailed, or printed, or saved. The report data may be stored for re-rendering, and the stored data may be refreshed so that the report can include later transactions. This is an efficiency technique, as not all the preceding data needs to be recalculated.

We have a reporting area within our j Count® program which acts as “built-in hard-coded” reporting area. Eventually we are going to offer a user report designing facility—however, this is not in production yet.

Each report in the reporting area has parameters pertinent to the report to enable filtering and selection of the underlying data. We did not use the TRS parameter entry feature. The custom parameter editor is worth checking out for your use case, but we decided to use our own approach this time. We choose our own approach a lot as the product is flexible enough to give us room for this. Specifically because of the wish to have more granular control over the working of the parameters and the look and feel we wished to be in keeping of the current skin.

Note 1: If there was some way of “splitting” out the report parameters from the report viewer, and chaining them so there could be controller activity in obtaining the data in the parameter control area.

Note 2: This still may not work as some of the parameters act before the report and some act during the report so there would have to be a two-phase operation to fulfil the report which might not be ok, i.e., not what we want.

Reports screenshot

Transaction Report Filters and Parameters

This page is an ASP.NET Core C# Razor version of the Telerik UI. Specifically, a Tabstrip containing several panel bars, one panel bar per report.

Because I reuse the filters in different panel bars, I have a “clever” way of attaching them to the cshtml. The main problem was figuring out how to name them, as this property needs to be unique within the collection of controls on the screen.

So, the following is how I show a date range in any panel bar for any report. Several things to note: I pass the model (a more efficient way to do this exists, I am sure, however I used this method in the proof of concept and did not go back to optimize) to the Partial and it is rendered in place. The unique part is that within each panel bar every report is uniquely assigned an integer which is appended to the common part of the named control, thus making the name unique on the cshtml as a whole.

You shall notice a job for me in the future having all the controls lined up properly … This is on my list.

Code Snippet 1 – Partial Instantiation for Date Range

<tr class="trparameters@(this.Model._nReport.ToString())">
    <td>
        @(await this.Html.PartialAsync("_DateRange.cshtml", new Booking.Site.Models.Shared.ReportData()
    {
        _sReport = this.Model._sReport,
        _sBranding = this.Model._sBranding,
        _gId = this.Model._gId,
        _nReport = this.Model._nReport,
        _bPosted = this.Model._bPosted,
        _sHeading = this.Model._sHeading,
        _bAll = this.Model._bAll,
        _bContras = this.Model._bContras,
        _dEnd = this.Model._dEnd,
        _dStart = this.Model._dStart,
        _dAt = this.Model._dAt,
        _dTo = this.Model._dTo
    }))
    </td>
    <td>
    </td>
</tr>

The _DateRange.cshtml file is as below:

The >name< of a date picker control is as here "EndDatePicker" + this.Model._nReport.ToString()

Note that we append the report number onto any controls which may be in more than one panel bar on the page. If the controls do not have a unique name, then a JavaScript console error would occur.

Code Snippet 2 – Displaying Start and End Dates

@model Booking.Site.Models.Shared.ReportData
@using Microsoft.Extensions.Caching.Memory
@inject Booking.Data.DB.Heron28.Heron28Context _oHeron28Context
@inject IMemoryCache _oIMemoryCache
<script>
    if (typeof (AllDatesCheckBox) == 'undefined') {
        function AllDatesCheckBox(e) {
            if (e.checked) {
                $("#StartDatePicker" + e.sender.element.attr("data-report")).data("kendoDatePicker").value('01/01/2000');
                $("#EndDatePicker" + e.sender.element.attr("data-report")).data("kendoDatePicker").value('31/12/2099');
                $("#StartDatePicker" + e.sender.element.attr("data-report")).data("kendoDatePicker").enable(false);
                $("#EndDatePicker" + e.sender.element.attr("data-report")).data("kendoDatePicker").enable(false);
            }
            else {
                $("#StartDatePicker" + e.sender.element.attr("data-report")).data("kendoDatePicker").enable(true);
                $("#EndDatePicker" + e.sender.element.attr("data-report")).data("kendoDatePicker").enable(true);
            }
        }
    }
</script>
<label class="k-label">
    @Booking.Site.Classes.Helper.Text(this.Context, this._oIMemoryCache, this._oHeron28Context, this.User.Identity.Name, "Click to choose all date data?", "Click to choose all date data?", false)
</label>
&nbsp;
<input id="AllDatesCheckBox@(this.Model._nReport.ToString())" data-report="@(this.Model._nReport.ToString())" />
<span class="daterange@(this.Model._nReport.ToString())">
    <br />
    <label class="k-label">
        Start
    </label>
    <br/>
    @Html.Kendo().DatePicker().Name("StartDatePicker" + this.Model._nReport.ToString()).HtmlAttributes(new { style = "width: 200px;" })
    <br/>
    <label class="k-label">
        End
    </label>
    <br />
    @Html.Kendo().DatePicker().Name("EndDatePicker" + this.Model._nReport.ToString()).HtmlAttributes(new { style = "width: 200px;" })
</span>
<script type="text/javascript">
    $(document).ready(function () {
        $("#AllDatesCheckBox@(this.Model._nReport.ToString())").kendoSwitch({
            change: AllDatesCheckBox
        });
    });
</script>
<style>
</style>

Code snippet 2 Line 10 only allows the JavaScript functions to be initialized if not initialized in a previous declaration. You are not allowed to declare a JavaScript function in the DOM more than once.

e.sender.element.attr("data-report")—this piece of JavaScript reads data off an attribute of an HTML tag element. So, data may be embedded in the template that is used to render the information.

Code Snippet 3 – Run Routine for Displaying a Report

function RunReport(nReport, sId) {
            h28_confirm("@Booking.Site.Classes.Helper.Text(this.Context, this._oIMemoryCache, this._oHeron28Context, this.User.Identity.Name, "Are you sure you want to run this report?", "Are you sure you want to run this report?", false)", function() {
                if (sId == null) {
                    sId = sGuidEmpty;
                }
                else {
                    $("#SelectPreRenderedWindow").data("kendoWindow").close();
                }
                switch (nReport) {
                    case @((int)Booking.Library.Classes.Enums.Reports.TimeBatches):
                        {
                            if ($("#AllCheckBox" + nReport.toString()).is(":checked") || ($("#StartDatePicker" + nReport.toString()).data("kendoDatePicker").value() != null && $("#EndDatePicker" + nReport.toString()).data("kendoDatePicker").value() != null)) {
                                var dStart = $("#StartDatePicker" + nReport.toString()).data("kendoDatePicker").value();
                                var dEnd = $("#EndDatePicker" + nReport.toString()).data("kendoDatePicker").value();
                                var bAll = $("#AllCheckBox" + nReport.toString()).is(":checked");
                                h28_CreateWindow(true, "ReportWindow", "Batch Report", function() {
                                    $("#ReportWindow").data("kendoWindow").refresh({
                                        url: '@Url.Content("~/Shared/_ReportViewer")',
                                        data: { lnReport: nReport, lsData: "[sReport]=[Time/Time Batches 1][sJob]=[Job][dStart]=[" + kendo.format("{0:dd-MMM-yyyy}", dStart) + "][dEnd]=[" + kendo.format("{0:dd-MMM-yyyy}", dEnd) + "][bPosted]=[false][bAll]=[" + ((bAll) ? "true" : "false") + "][gInstanceId]=[" + sId + "][sCulture]=[" + sCompanyCulture + "]*" }
                                    }).center().open();
                                }, null);
                            } else {
                                h28_ShowMessage("Please enter a date range or select all data...", sMessageWindow, nShowMessageLevel > 0, function() { }, null);
                            }
                        }
                        break;

The snippet above shows the RunReport function of the program it takes two parameters—nReport and sId. The nReport variable contains a number uniquely identifying the report type. The sId is the instance of the report or null, depending upon whether the data is to be gathered at run time or whether the data to be used has already been collated.

The reason for the sId is that the speed at which data is gathered can be slow depending upon the size of the collection and hardware specification. So we can reuse the data instance if the data set has not been changed or added to. In the future we are going to add a refresh where a data instance can be added to for those transactions that have occurred after the run that created the data in the first case.

Points to note:

  1. The _ReportViewer cshtml file is called for every report.
  2. The tokenized lsData string contains the variable parameter data for deconstructing within the controller.
  3. h28_CreateWindow is a “homebrew” routine which creates and displays a popup Telerik div window.

Code Snippet 4 – Controller for _ReportViewer

public PartialViewResult _ReportViewer(Booking.Site.Models.Shared.ReportData loReportData, int lnReport, string lsData)
{
    Microsoft.EntityFrameworkCore.Storage.IDbContextTransaction loIDbContextTransaction = this._oHeron28Context.Database.BeginTransaction();
    try
    {
        object loData2 = null;
        if (this._oIMemoryCache.TryGetValue("Buku_sData2" + Booking.Data.Classes.Configuration.SessionId(this.HttpContext.Session.GetString("Buku_sCompanyId"), Booking.Site.Classes.Helper.GetUserName(this.HttpContext), Booking.Site.Classes.Helper.GetIPAddress(this.HttpContext), this.HttpContext.Session.Id), out loData2))
        {
            string lsData2 = (string)loData2;
            Booking.Data.DB.Extensions.IdentityExtend.User loUser = this.SelectedUser(lsData2);
            if (loUser != null)
            {
                Guid lgCompanyId = Guid.Parse(Booking.Library.Classes.Utility.DecodeSerialisedData(lsData2, "gCompanyId"));
                Booking.Data.DB.Heron28.Entity loEntity = this._oHeron28Context.Entities.Where(E => E.EGId == lgCompanyId).FirstOrDefault();
                if (loEntity != null)
                {
                    loReportData._nReport = lnReport;
                    loReportData._sData = lsData;
                    object loImage = null;
                    Booking.Data.DB.Heron28.Entity loImagesEntity = this._oHeron28Context.Entities.AsEnumerable().Where(E => E.ECompanyEGId == lgCompanyId && E.ENType == (short)Booking.Library.Classes.Enums.Entities.Images && !E.Deleted).FirstOrDefault();
                    if (loImagesEntity != null)
                    {
                        if (!loEntity.GetDataSetDataset.Company[0].IsCO_nBrandingId1Null() && loEntity.GetDataSetDataset.Company[0].CO_nBrandingId1 != 0 && loImagesEntity.GetDataSetDataset.Images.FindByIMG_nId(loEntity.GetDataSetDataset.Company[0].CO_nBrandingId1) != null)
                        {
                            loImage = loImagesEntity.GetDataSetDataset.Images.FindByIMG_nId(loEntity.GetDataSetDataset.Company[0].CO_nBrandingId1).IMG_oImage;
                        }
                        else
                        {
                            loImage = Booking.Library.Classes.Utility.StreamToBytes(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("Booking.Site.Resources.Blank.jpg"));
                        }
                    }
                    else
                    {
                        loImage = Booking.Library.Classes.Utility.StreamToBytes(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("Booking.Site.Resources.Blank.jpg"));
                    }
                    if (loImage != null) // We should always have an image because if none has been set the blank is used.
                    {
                        switch ((Booking.Library.Classes.Enums.Reports)lnReport)
                        {
                            case Booking.Library.Classes.Enums.Reports.AdHocSLInvoice1:
                            case Booking.Library.Classes.Enums.Reports.AdHocSLInvoice2:
                            case Booking.Library.Classes.Enums.Reports.AdHocSLCreditNote1:
                            case Booking.Library.Classes.Enums.Reports.AdHocSLCreditNote2:
                            case Booking.Library.Classes.Enums.Reports.AdHocPLInvoice1:
                            case Booking.Library.Classes.Enums.Reports.AdHocPLInvoice2:
                            case Booking.Library.Classes.Enums.Reports.AdHocPLCreditNote1:
                            case Booking.Library.Classes.Enums.Reports.AdHocPLCreditNote2:
                                {
                                    loReportData._sReport = Booking.Library.Classes.Utility.StringValueOf((Booking.Library.Classes.Enums.Reports)lnReport);
                                    loReportData._gId =
                                        Guid.Parse(Booking.Library.Classes.Utility.DecodeSerialisedData(lsData, "gTransactionId"));
                                }
                                break;

This code snippet partially shows the _ReportViewer controller method. DecodeSerialisedData shows how the tokens are extracted from the lsData parameter.

What is put into the ReportData class is passed to the _ReportViewer.cshtml report viewer engine. And the report gets rendered.

The idea, or problem, being solved here is the “chaining” end to end of multiple report types in a consistent way which means that new reports are “dropped” in and the chain completed for them to start working. I suppose more work could be applied to the “chain” making it more automated in the way it works.

Code Snippet 5 – The Report Viewer Itself
(Partial representation, as the whole is long.)

@model Booking.Site.Models.Shared.ReportData
@using Microsoft.AspNetCore.Identity;
@using Microsoft.Extensions.Caching.Memory
@inject Booking.Data.DB.Heron28.Heron28Context _oHeron28Context
@inject IMemoryCache _oIMemoryCache
@{
}
<script>
    function StoreReport(e)
    {
        h28_confirm("@Booking.Site.Classes.Helper.Text(this.Context, this._oIMemoryCache, this._oHeron28Context, this.User.Identity.Name, "Store Report for User?", "Store Report for User?", false)", function () {
            var oModel =
            {
                _gId: '@this.Model._gInstanceId.ToString()',
                _nReport: @this.Model._nReport.ToString(),
                _sData: '@this.Model._sData'
            };
            $.ajax({
                url: '@Url.Content("~/Shared/_StoreReport")',
                type: 'POST',
                contentType: 'application/json; charset=utf-8;',
                data: kendo.stringify(oModel),
                success: function (oData) {
                    if (oData.bResult) {
                        h28_ShowMessage(oData.sMessage, sMessageWindow, nShowMessageLevel > 1, function () {
                            if ($("#StoredReportsGrid").data("kendoGrid") != null)
                            {
                                $("#StoredReportsGrid").data("kendoGrid").dataSource.read();
                            }
                        }, @Booking.Library.Classes.Constants._nMaxWindowTimeout);
                    }
                    else {
                        h28_ShowMessage(oData.sMessage, sMessageWindow, nShowMessageLevel > 0, function () {
                        }, null);
                    }
                }
            });
        });
    }
     
</script>
@(Html.Kendo().ToolBar()
.Name("AccountSaveAndActionsToolbar")
.Events(E => E.Click("h28_ToolbarClick"))
.Items(items =>
{
    if (string.Format("[{0}][{1}][{2}][{3}]", (int)Booking.Library.Classes.Enums.Reports.AdHocSLInvoice1, (int)Booking.Library.Classes.Enums.Reports.AdHocSLInvoice2, (int)Booking.Library.Classes.Enums.Reports.AdHocSLCreditNote1, (int)Booking.Library.Classes.Enums.Reports.AdHocSLCreditNote2).Contains("[" + this.Model._nReport.ToString() +"]"))
    {
        items.Add().Type(CommandType.Button).Text(Booking.Site.Classes.Helper.Text(this.Context, this._oIMemoryCache, this._oHeron28Context, this.User.Identity.Name, "Issue A New PDF Invoice", "Issue A New PDF Invoice", false)).Icon("plus").HtmlAttributes(new { onclick = "IssueInvoice('" + this.Model._gId.ToString() + "', " + this.Model._nReport.ToString() + ", '" + Booking.Site.Classes.Helper.Text(this.Context, this._oIMemoryCache, this._oHeron28Context, this.User.Identity.Name, "Would you like to issue the Invoice now", "Would you like to issue the Invoice now?", false) + "')" });
    }
    items.Add().Type(CommandType.Button).Text(Booking.Site.Classes.Helper.Text(this.Context, this._oIMemoryCache, this._oHeron28Context, this.User.Identity.Name, "Store Report", "Store Report", false)).Icon("save").Click("StoreReport");
}))
<div id="rvMain" class="k-widget">
    @Booking.Site.Classes.Helper.Text(this.Context, this._oIMemoryCache, this._oHeron28Context, this.User.Identity.Name, "Data is loading, please wait...", "Data is loading, please wait...", false)
</div>
<script type="text/javascript">
    $(document).ready(function () {
        $("#rvMain").telerik_ReportViewer({
            reportServer: {
                url: "@Booking.Data.Classes.Helper.GetCurrentReportServer()",
                username: null,
                password: null
            },
            reportSource: {
                report: '@this.Model._sReport',
                parameters: {
                    @switch ((Booking.Library.Classes.Enums.Reports)this.Model._nReport)
                    {
                        case Booking.Library.Classes.Enums.Reports.AdHocSLInvoice1:
                        case Booking.Library.Classes.Enums.Reports.AdHocSLInvoice2:
                        case Booking.Library.Classes.Enums.Reports.AdHocSLCreditNote1:
                        case Booking.Library.Classes.Enums.Reports.AdHocSLCreditNote2:
                        case Booking.Library.Classes.Enums.Reports.AdHocPLInvoice1:
                        case Booking.Library.Classes.Enums.Reports.AdHocPLInvoice2:
                        case Booking.Library.Classes.Enums.Reports.AdHocPLCreditNote1:
                        case Booking.Library.Classes.Enums.Reports.AdHocPLCreditNote2:
                        {
                            <text>
                                nServerInstance: @Booking.Data.Classes.Configuration._nServerInstance,
                                gTransactionId: '@this.Model._gId.ToString()',
                                gCompanyId: gCompanyId,
                                sCulture: sCompanyCulture
                            </text>
                        }
                            break;

This displays a report in the Telerik Report Viewer within a tabstrip.

One can see the parameters that are passed to the TRS to give the report something to do. Other report types shall have different parameters.

So steps:

  1. The panel bar one per report containing the parameters that are send to the report via the ASP.NET controller.
  2. Create run report method which activates the _ReportViewer that in turn gather’s the data.
  3. Create a report viewer method with generic parameter method so the same cshtml can be used for all reports.

In conclusion, this is not a complete example … However, I have provided enough for the idea to be picked up and propagated. Please do get in contact with any questions so I might help you understanding.

There is more code in GitHub so you can see the bigger picture.


About the Author

David Robertson

David Robertson is a software developer/computer scientist, with degrees and many years’ experience working and writing in the field. He also enjoys ethos and philosophical discussions about computing—where he sees it going and the journey so far. He believes in balance within the tech industry and supports it at every juncture, supporting and advocating for good mental health as a way forward for reasonable and reasoned programming.

 

Related Posts

Comments

Comments are disabled in preview mode.