CSharp:Microsoft Reporting Services SSRS

Premessa

Getting started
Locate and start Reporting server Tools
Nell'IDE di Visual Studio per consentire di aggiungere nuovi item di tipo reporting leggi qui

Report RDLC

I reports sono files con estensione RDLC (che sta per Report Definition Language Client Side). Sono documenti (Report) a cui agganciare i dati provenienti da una fonte che può essere un DB SQL Server. Si possono collegare direttamente al DB o attraverso opportune classi da popolare prima di inviarle, con i relativi dati associati, al report.

I report da WPF

In questo scenario useremo un controllo XAML nel quale ci sarà l'oggetto che recepirà dinamicamente il file *.rdc vero e proprio e lo rappresenterà.

Lo XAML

Nel codice seguente, lo XAML ha l'oggetto ReportView nel quale confluirà il report da visualizzare:

<UserControl x:Class="WpfApp1.ReportControl"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
             xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 
             xmlns:local="clr-namespace:WpfApp1"
             xmlns:rv="clr-namespace:Microsoft.Reporting.WinForms;assembly=Microsoft.ReportViewer.WinForms"
             mc:Ignorable="d" 
             d:DesignHeight="300" d:DesignWidth="300"
             Loaded="UserControl_Loaded">
    <Grid HorizontalAlignment="Stretch" VerticalAlignment="Stretch">
        <WindowsFormsHost>
            <rv:ReportViewer x:Name="rptMain"></rv:ReportViewer>
        </WindowsFormsHost>
    </Grid>
</UserControl>

L'elemento <rv:ReportViewer> (riga 13) è il segnaposto del report da visualizzare.

Code Behind

L'evento che gestirà il popolamento dinamico del report da visualizzare è il TAG "Loaded="UserControl_Loaded" (riga 10) del primo elemento <UserControl> nello XAML. Lato C#, nel codeBehind:

public enum ReportType
{
	Customer,
	QuickSale,
	SubA1,
	MainA,
	SalesPrivateCustomer,
	ACCReferralLetter
	// TODO
}
//...
private void UserControl_Loaded(object sender, RoutedEventArgs e)
{
	//TestJson();
	rptMain.LocalReport.SubreportProcessing += LocalReport_SubreportProcessing;
	switch (CurrentReportType)
	{
		case ReportType.Customer:
			TestCustomerReport();
			break;
		case ReportType.QuickSale:
			TestQuickSaleReport();
			break;
		case ReportType.SubA1:
			TestSubA1Report();
			break;
		case ReportType.MainA:
			TestMainAReport();
			break;
		case ReportType.SalesPrivateCustomer:
			TestSalePrivateCustomerReport();
			break;
		case ReportType.ACCReferralLetter:
			Test_ClinetFile_ACCReferralLetter();
			break;
		default:
			throw new Exception("Report type not found");
	}
}
//...
 
// Assegnazione delle info per visualizzare il report voluto
private void TestQuickSaleReport()
{
	rptMain.LocalReport.ReportPath = string.Format("{0}\\Sale\\r_QuickSale.rdlc", m_reportsRootPath);

	object o = QuickSaleTest.GetTestReport(); // Classe che ha questo metodo per popolare la classe da mandare come dataset al report *rdlc

	ReportDataSource rds_h = new ReportDataSource();
	rds_h.Name = "QuickSale_Header";
	rds_h.Value = o;
	rptMain.LocalReport.DataSources.Add(rds_h);

	ReportDataSource rds_d = new ReportDataSource();
	rds_d.Name = "QuickSale_Details";
	rds_d.Value = ((List<r_QuickSaleModel_Header>)o).First().Details;
	rptMain.LocalReport.DataSources.Add(rds_d);

	ReportDataSource rds_p = new ReportDataSource();
	rds_p.Name = "QuickSale_PaymentMethods";
	rds_p.Value = ((List<r_QuickSaleModel_Header>)o).First().PaymentMethods;
	rptMain.LocalReport.DataSources.Add(rds_p);

	rptMain.RefreshReport();
}

Segue calse e metodo di test per popolare con dati tipo il report

internal class QuickSaleTest
    {
        private static Random random = new Random();

        private static string RandomString() {
            const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789 abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz";
            return new string(Enumerable.Repeat(chars, random.Next(10, 101)).Select(s => s[random.Next(s.Length)]).ToArray());
        }

        public static IEnumerable< r_QuickSaleModel_Header> GetTestReport()
        {
            r_QuickSaleModel_Header r = new r_QuickSaleModel_Header()
            {

                // clinic info
                ClinicName = "Clinic Name",
                ClinicAddress1 = "23, Test road",
                ClinicAddress2 = "20153 Castellanza (VA)",
                ClinicPhone = "0123-456789",
                ClinicGSTNumber = "IT0123456789",

                // doc info
                DocType = "Quick sale",
                DocDate = "2018-05-09",
                DocNumber = "QS-18/0001A3",

                // invoice to
                CustomerFirstName = "Andrea", CustomerLastName = "Colombo",
                CustomerAddress1 = "via C. De Simoni, 15", CustomerAddress2 = "21049 Tradate (VA)",
                CustomerCode = "DUMMY001",

                // clinician
                ClinicianFirstName = "Mario", ClinicianLastName = "Rossi",

                // amount info
                GSTPercentage = 15m,
                RoundingAmount = 0.74m,
                CompanyCode = "071"
            };
            if (r.CompanyCode == "071")
                r.CompanyName = "Bay Audiology";
            else if (r.CompanyCode == "072")
                r.CompanyName = "Dilworth Hearing";
            else
                r.CompanyName = "";

            // Dettagli
            r.Details = new List<r_QuickSaleModel_Detail>();
            for (int i = 0; i < random.Next(1,200); i++)
            {
                r_QuickSaleModel_Detail d = new r_QuickSaleModel_Detail()
                {
                    Code = random.Next(100000).ToString().PadLeft(8, '0'),
                    Description = RandomString(),
                    Quantity = random.Next(15),
                    UnitPrice = (decimal)random.Next(100000) / 100m
                };
                d.Amount = random.Next(100000) % 5 == 0 ? d.Quantity * d.UnitPrice - (decimal)random.Next((int)(d.Quantity * d.UnitPrice / 2 * 100)) / 100m : d.Quantity * d.UnitPrice;
                r.Details.Add(d);
            }

            // Metodi di pagamento
            r.PaymentMethods = new List<r_QuickSaleModel_Payment>();
            r.PaymentMethods.Add(new r_QuickSaleModel_Payment() { MethodDescr = "Method 1", MethodAmount = 123.45m });
            r.PaymentMethods.Add(new r_QuickSaleModel_Payment() { MethodDescr = "Method 2", MethodAmount = 555.0m });

            return new List<r_QuickSaleModel_Header>() { r };
        }
    }

Classe dataset

Conterrà i dati, opportunamente recuperati, da inviare al report *.rdlc

public class r_QuickSaleModel_Header
{
	public string ClinicName { get; set; }
	public string ClinicAddress1 { get; set; }
	public string ClinicAddress2 { get; set; }
	public string ClinicPhone { get; set; }
	public string ClinicGSTNumber { get; set; }

	public string DocType { get; set; }
	public string DocNumber { get; set; }
	public string DocDate { get; set; }

	public string CustomerFirstName { get; set; }
	public string CustomerLastName { get; set; }
	public string CustomerAddress1 { get; set; }
	public string CustomerAddress2 { get; set; }
	public string CustomerCode { get; set; }
	public string CustomerName { get { return string.Join(", ", new string[] { this.CustomerFirstName, this.CustomerLastName }); } }

	public string ClinicianFirstName { get; set; }
	public string ClinicianLastName { get; set; }
	public string ClinicianName { get { return string.Join(", ", new string[] { this.ClinicianFirstName, this.ClinicianLastName }); } }

	public List<r_QuickSaleModel_Detail> Details { get; set; }

	public decimal GSTPercentage { get; set; }
	public decimal GSTAmount { get { return this.InvoiceAmount * (this.GSTPercentage / 100m); } }
	public string GSTString { get { return string.Format("Includes {0:N2}% GST {1:N2}", this.GSTPercentage, this.GSTAmount); } }
	public decimal InvoiceAmount { get { return this.Details.Sum(s => s.Amount); } }
	public decimal RoundingAmount { get; set; }

	public List<r_QuickSaleModel_Payment> PaymentMethods { get; set; }

	public decimal BalanceDue { get { return -(this.InvoiceAmount - this.RoundingAmount - this.PaymentMethods.Sum(s => s.MethodAmount)); } }

	public string CompanyCode { get; set; }
	public string CompanyName { get; set; }
}

public class r_QuickSaleModel_Detail
{
	public string Code { get; set; }
	public string Description { get; set; }
	public decimal Quantity { get; set; }
	public decimal UnitPrice { get; set; }
	public decimal Amount { get; set; }
	public decimal Discount { get { return this.Amount - (this.UnitPrice * this.Quantity); } }
}

public class r_QuickSaleModel_Payment
{
	public string MethodDescr { get; set; }
	public decimal MethodAmount { get; set; }
}

Es di Report rdlc

Il contenuto vero e proprio è un XML, ma dall'editor dell'IDE si presenta così:

Aggiungere un dataset

Per aggiungere un dataset, il seguente esempio si basa sui dati come dal paragrafo precedente 'I report da WPF':

Mappa e Link

---

C# | SQL Server Sistemi di Reporting

---

Visual Studio | MS SQL | SSIS

---

Parole chiave: Report | RDLC | .RDLC