Crystal Reports

Introduction

In this project, we are developing a robust reporting system using Crystal Reports to generate comprehensive insights across various operational datasets.

The reports are categorized into four key areas:

• Work In Progress (WIP)
• Packing
• Quality/7-0
• Efficiency/Miscellaneous

Each dataset feeds into specific reports to track and analyze different stages of the production lifecycle, from sewing order balances to quality summaries and worker strength evaluations. The overall structure supports seamless reporting and enhances data-driven decision-making for optimized factory performance.

Report Filter Procedures

Explanation of SplitString Function

The SplitString function is a user-defined function in SQL that allows you to split a string of values, separated by a specified delimiter (such as a comma), into individual entries and returns them as a table. This is useful when you need to pass a comma-separated list of values into a query or procedure and filter datasets based on those values.

The function takes two parameters:

• @Input: The input string containing values to be split.
• @Delimiter: The character used to separate the values (commonly a comma).

It works by iterating through the input string, finding each occurrence of the delimiter, and splitting the string into separate values. These values are then inserted into a table (@Output), which can be used in SQL queries to filter datasets, such as in WHERE clauses, to match any of the provided values.

Splitting Function

Create FUNCTION [dbo].[FormatForInClause] (@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN '''' + REPLACE(@input, ',', ''',''') + ''''
END

Filtering Stored Procedure

As an example, the GetWorkOrderStatus stored procedure is designed to dynamically generate and execute an SQL query to retrieve the status of work orders. It filters data based on parameters such as CompanyId, BrId, OrId, BuyMonth, PO, Color, and Size. If the parameters like PO, Color, or Size are passed as comma-separated values, the procedure utilizes a function (dbo.FormatForInClause) to format them for use in an IN clause, allowing flexible filtering. The query aggregates several fields (such as order quantities, synced quantities, etc.) and groups the results by key fields. It dynamically adjusts its filters based on the provided parameters, ensuring that only relevant data is retrieved.

A Complete Example of Where Clause Filtering is Shown in the Example Below:

ALTER PROCEDURE [dbo].[GetWorkOrderStatus]
    @CompanyId NVARCHAR(50),
    @BrId NVARCHAR(50),
    @OrId NVARCHAR(50),
    @BuyMonth NVARCHAR(50) = NULL,
    @PO NVARCHAR(MAX) = NULL,
    @Color NVARCHAR(MAX) = NULL,
    @Size NVARCHAR(MAX) = NULL
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)

    -- Build the dynamic SQL query
    SET @SQL = '
    SELECT
           [OrId]
          ,[CompanyId]
          ,[BuyMonth]
          ,[StyleNo]
          ,[PO]
          ,[ColorCode]
          ,[Color]
          ,[Size]
          ,[BrId]
          ,SizeId
          ,SUM(DISTINCT OrderQty) as OrderQtySum
          ,SUM(SyncedQty) as SyncedQtySum
          ,SUM(CardInitializedQty) as CardInitializedQtySum
          ,SUM(CuttingOutQty) as CuttingOutQtySum
          ,SUM(InductionQty) as InductionQtySum
          ,SUM(SewingLastOperationQty) as SewingLastOperationQtySum
          ,SUM(QualityPassedQty) as QualityPassedQtySum
          ,SUM(FinishingFirstOperationQty) as FinishingFirstOperationQtySum
          ,SUM(FinishingQualityPassedQty) as FinishingQualityPassedQtySum
          ,SUM(FinishingLastOperationQty) as FinishingLastOperationQtySum
          ,SUM(SendDownQty) as SendDownQtySum
          ,SUM(EndLineQualityRejectedQty) as EndLineQualityRejectedQtySum
          ,SUM(FinishingQualityRejectedQty) as FinishingQualityRejectedQtySum
          ,SUM(InductionSkipped) as InductionSkippedSum
          ,SUM(SewingLastOperationSkipped) as SewingLastOperationSkippedSum
          ,SUM(QualityPassedSkipped) as QualityPassedSkippedSum
          ,SUM(FinishingFirstOperationSkipped) as FinishingFirstOperationSkippedSum
          ,SUM(FinishingLastOperationSkipped) as FinishingLastOperationSkippedSum
          ,SUM(FinishingQualityPassedSkipped) as FinishingQualityPassedSkippedSum
    FROM
        [dbo].[DateWiseWorkOrderStatusNew]
    WHERE
        CompanyId = @CompanyId
        AND BrId = @BrId
        AND OrId = @OrId'

    -- Add conditions for BuyMonth
    IF @BuyMonth IS NOT NULL AND @BuyMonth <> 'NULL'
        SET @SQL += ' AND BuyMonth = @BuyMonth'

    -- Add conditions for PO
    IF @PO IS NOT NULL AND @PO <> 'NULL'
        SET @SQL += ' AND PO IN (' + dbo.FormatForInClause(@PO) + ')'
    ELSE
        SET @SQL += ' AND PO IN (SELECT DISTINCT PO FROM [dbo].[DateWiseWorkOrderStatusNew] WHERE OrId = @OrId)'

    -- Add conditions for Color
    IF @Color IS NOT NULL AND @Color <> 'NULL'
        SET @SQL += ' AND Color IN (' + dbo.FormatForInClause(@Color) + ')'
    ELSE
        SET @SQL += ' AND Color IN (SELECT DISTINCT Color FROM [dbo].[DateWiseWorkOrderStatusNew] WHERE OrId = @OrId)'

    -- Add conditions for Size
    IF @Size IS NOT NULL AND @Size <> 'NULL'
        SET @SQL += ' AND Size IN (' + dbo.FormatForInClause(@Size) + ')'
    ELSE
        SET @SQL += ' AND Size IN (SELECT DISTINCT Size FROM [dbo].[DateWiseWorkOrderStatusNew] WHERE OrId = @OrId)'

    -- Group by clause
    SET @SQL += '
    GROUP BY
        OrId
        ,CompanyId
        ,BuyMonth
        ,StyleNo
        ,PO
        ,ColorCode
        ,Color
        ,Size
        ,BrId
        ,SizeId'

    -- Execute the dynamic SQL
    EXEC sp_executesql @SQL,
        N'@CompanyId NVARCHAR(50), @BrId NVARCHAR(50), @OrId NVARCHAR(50), @BuyMonth NVARCHAR(50), @PO NVARCHAR(MAX), @Color NVARCHAR(MAX), @Size NVARCHAR(MAX)',
        @CompanyId, @BrId, @OrId, @BuyMonth, @PO, @Color, @Size


END

Controller Documentation

This document provides an overview of the Controller class, which is part of the ReportingProjectNCL. This controller handles requests for generating various Work in Progress (WIP) reports in different formats (PDF and Excel) using Crystal Reports.

Overview

The Controller is responsible for generating several production-related reports, such as Line Wise Balance, Sewing WIP, Work Order Balance, and Ex Factory reports. It interacts with Crystal Reports to render the output in either PDF or Excel formats, depending on the user’s request.

Namespace and Dependencies

The controller uses the following namespaces and dependencies:

using CrystalDecisions.CrystalReports.Engine;
using ReportingProjectNCL.PackingDatasetTableAdapters;
using System;
using System.Web.Mvc;
using System.Web.Http.Cors;
using ReportingProjectNCL.CrystalReports;
using ReportingProjectNCL.CrystalReports.WIPReports;

Report Wise Documentation

WIP Reports

Line Wise Line Balance Report

The code snippet initializes a dataset and uses a table adapter to fill the dataset with filtered data from the DateWiseWorkOrderStatusNew table. This report focuses on displaying the line-wise balance for each operation in the production process. The filters applied in the report allow you to customize the data retrieval based on the following parameters:

Filters

• CompanyId: The unique identifier for the company.
• BrId: Branch or factory ID to specify the location.
• OrId: The work order ID to track a specific production order.
• Buymonth: The month associated with the buyer’s order.
• StyleNo: The style number of the product being produced.
• PO: Production Order number, representing the buyer’s order.
• ColorCode: The specific color code for the product.
• Size: Size of the product to filter specific size-wise production data.
• LineCode: The code representing the production line for tracking.
• fromDateTime: The start date and time for filtering the report data.
• toDateTime: The end date and time for filtering the report data.

Code Snippet

The following C# code initializes the PackingDataset, creates a DateWiseWorkOrderStatusNewTableAdapter, and fills the dataset with data filtered by the specified parameters:

// Initialize the dataset and fill it using the table adapter
var ds = new PackingDataset();
var WIPDataAdapter = new DateWiseWorkOrderStatusNewTableAdapter();
WIPDataAdapter.Fill(ds.DateWiseWorkOrderStatusNew, CompanyId, BrId, OrId, Buymonth, StyleNo, PO, ColorCode, Size, LineCode, fromDateTime, toDateTime);

Sewing WIP Report

The code snippet initializes a dataset and uses a table adapter to fill the dataset with filtered data from the WIPSewingSendDown table. This report focuses on displaying the work-in-progress (WIP) for the sewing stage in the production process. The filters applied in the report allow you to customize the data retrieval based on the following parameters:

Filters

• CompanyId: The unique identifier for the company.
• BrId: Branch or factory ID to specify the location.
• OrId: The work order ID to track a specific production order.
• Buymonth: The month associated with the buyer’s order.
• StyleNo: The style number of the product being produced.
• PO: Production Order number, representing the buyer’s order.
• ColorCode: The specific color code for the product.
• Size: Size of the product to filter specific size-wise production data.
• LineCode: The code representing the production line for tracking.
• fromDateTime: The start date and time for filtering the report data.
• toDateTime: The end date and time for filtering the report data.

Code Snippet

The following C# code initializes the PackingDataset, creates a WIPSewingSendDownTableAdapter, and fills the dataset with data filtered by the specified parameters:

// Initialize the dataset and fill it using the table adapter
var ds = new PackingDataset();
var WIPDataAdapter = new WIPSewingSendDownTableAdapter();
WIPDataAdapter.Fill(ds.WIPSewingSendDown, CompanyId, BrId, OrId, Buymonth, StyleNo, PO, ColorCode, Size, LineCode, fromDateTime, toDateTime);

Daily Sewing Inline Balance Report

The code snippet initializes a dataset and uses a table adapter to fill the dataset with filtered data from the DailySewingInlineBalance table. This report focuses on displaying the daily inline balance for sewing operations in the production process. The filters applied in the report allow you to customize the data retrieval based on the following parameters:

Filters

• CompanyId: The unique identifier for the company.
• BrId: Branch or factory ID to specify the location.
• OrId: The work order ID to track a specific production order.
• Buymonth: The month associated with the buyer’s order.
• StyleNo: The style number of the product being produced.
• PO: Production Order number, representing the buyer’s order.
• ColorCode: The specific color code for the product.
• Size: Size of the product to filter specific size-wise production data.
• LineCode: The code representing the production line for tracking.
• DateTimeSewDate: The date for which the sewing inline balance is being retrieved.

Code Snippet

The following C# code initializes the PackingDataset, creates a DailySewingInlineBalanceTableAdapter, and fills the dataset with data filtered by the specified parameters:

// Initialize the dataset and fill it using the table adapter
var ds = new PackingDataset();
var WIPDataAdapter = new DailySewingInlineBalanceTableAdapter();
WIPDataAdapter.Fill(ds.DailySewingInlineBalance, CompanyId, BrId, OrId, Buymonth, StyleNo, PO, ColorCode, Size, LineCode, DateTimeSewDate);

Line Wise Output Report

The code snippet initializes a dataset and uses a table adapter to fill the dataset with filtered data from the SewingOutputReport table. This report focuses on displaying the output of sewing operations, organized by production line, within a specified time frame. The filters applied in the report allow you to customize the data retrieval based on the following parameters:

Filters

• CompanyId: The unique identifier for the company.
• BrId: Branch or factory ID to specify the location.
• OrId: The work order ID to track a specific production order.
• Buymonth: The month associated with the buyer’s order.
• StyleNo: The style number of the product being produced.
• PO: Production Order number, representing the buyer’s order.
• ColorCode: The specific color code for the product.
• Size: Size of the product to filter specific size-wise production data.
• LineCode: The code representing the production line for tracking.
• fromDateTime: The start date and time for filtering the report data.
• toDateTime: The end date and time for filtering the report data.

Code Snippet

The following C# code initializes the PackingDataset, creates a SewingOutputReportTableAdapter, and fills the dataset with data filtered by the specified parameters:

// Initialize the dataset and fill it using the table adapter
var ds = new PackingDataset();
var OutputReportAdapter = new SewingOutputReportTableAdapter();
OutputReportAdapter.Fill(ds.SewingOutputReport, CompanyId, BrId, OrId, Buymonth, StyleNo, PO, ColorCode, Size, LineCode, fromDateTime, toDateTime);

Line Wise Input Report

The code snippet initializes a dataset and uses a table adapter to fill the dataset with filtered data from the SewingInputDataset table. This report focuses on displaying the input of materials to the sewing lines, organized by production line, within a specified time frame. The filters applied in the report allow you to customize the data retrieval based on the following parameters:

Filters

• CompanyId: The unique identifier for the company.
• BrId: Branch or factory ID to specify the location.
• OrId: The work order ID to track a specific production order.
• Buymonth: The month associated with the buyer’s order.
• StyleNo: The style number of the product being produced.
• PO: Production Order number, representing the buyer’s order.
• ColorCode: The specific color code for the product.
• Size: Size of the product to filter specific size-wise production data.
• LineCode: The code representing the production line for tracking.
• fromDateTime: The start date and time for filtering the report data.
• toDateTime: The end date and time for filtering the report data.

Code Snippet

The following C# code initializes the PackingDataset, creates a SewingInputDatasetTableAdapter, and fills the dataset with data filtered by the specified parameters:

// Initialize the dataset and fill it using the table adapter
var ds = new PackingDataset();
var OutputReportAdapter = new SewingInputDatasetTableAdapter();
OutputReportAdapter.Fill(ds.SewingInputDataset, CompanyId, BrId, OrId, Buymonth, StyleNo, PO, ColorCode, Size, LineCode, fromDateTime, toDateTime);

Work Order Balance Report

The code snippet initializes a dataset and uses a table adapter to fill the dataset with filtered data from the OrderStatusReport table. This report focuses on displaying the balance of work orders, showing the current status of each work order in the production process. The filters applied in the report allow you to customize the data retrieval based on the following parameters:

Filters

• CompanyId: The unique identifier for the company.
• BrId: Branch or factory ID to specify the location.
• OrId: The work order ID to track a specific production order.
• Buymonth: The month associated with the buyer’s order.
• PO: Production Order number, representing the buyer’s order.
• ColorCode: The specific color code for the product.
• Size: Size of the product to filter specific size-wise production data.
• LineCode: The code representing the production line for tracking.
• StyleNo: The style number of the product being produced.

Code Snippet

The following C# code initializes the PackingDataset, creates a OrderStatusReportTableAdapter, and fills the dataset with data filtered by the specified parameters:

// Initialize the dataset and fill it using the table adapter
var ds = new PackingDataset();
var WorkOrderAdapter = new OrderStatusReportTableAdapter();
WorkOrderAdapter.Fill(ds.OrderStatusReport, CompanyId, BrId, OrId, Buymonth, PO, ColorCode, Size, LineCode, StyleNo);

Ex Factory Audit Report

The code snippet initializes a dataset and uses a table adapter to fill the dataset with filtered data from the GetPOSplitDataSetExfactoryWiseWithFilters table. This report focuses on displaying audit details for products that have exited the factory, organized by specific parameters such as order, style, and customer. The filters applied in the report allow you to customize the data retrieval based on the following parameters:

Filters

• CompanyId: The unique identifier for the company.
• BrId: Branch or factory ID to specify the location.
• OrId: The work order ID to track a specific production order.
• Buymonth: The month associated with the buyer’s order.
• StyleNo: The style number of the product being produced.
• PO: Production Order number, representing the buyer’s order.
• ColorCode: The specific color code for the product.
• Customer: The customer or buyer for whom the product is being produced.
• fromDateTime: The start date and time for filtering the report data.
• toDateTime: The end date and time for filtering the report data.

Code Snippet

The following C# code initializes the PackingDataset, creates a GetPOSplitDataSetExfactoryWiseWithFiltersTableAdapter, and fills the dataset with data filtered by the specified parameters:

// Initialize the dataset and fill it using the table adapter
var ds = new PackingDataset();
var ExFactoryAdapter = new GetPOSplitDataSetExfactoryWiseWithFiltersTableAdapter();
ExFactoryAdapter.Fill(ds.GetPOSplitDataSetExfactoryWiseWithFilters, CompanyId, BrId, OrId, Buymonth, StyleNo, PO, ColorCode, Customer, fromDateTime, toDateTime);

Ex Factory Cross Tab Report

The code snippet initializes a dataset and uses a table adapter to fill the dataset with filtered data from the GetPOSplitDataSetExfactoryWiseWithFilters table. This report focuses on displaying a cross-tabulation of ex-factory data, allowing for a comparative view of production information based on various parameters such as order, style, and customer. The filters applied in the report allow you to customize the data retrieval based on the following parameters:

Filters

• CompanyId: The unique identifier for the company.
• BrId: Branch or factory ID to specify the location.
• OrId: The work order ID to track a specific production order.
• Buymonth: The month associated with the buyer’s order.
• StyleNo: The style number of the product being produced.
• PO: Production Order number, representing the buyer’s order.
• ColorCode: The specific color code for the product.
• Customer: The customer or buyer for whom the product is being produced.
• fromDateTime: The start date and time for filtering the report data.
• toDateTime: The end date and time for filtering the report data.

Code Snippet

The following C# code initializes the PackingDataset, creates a GetPOSplitDataSetExfactoryWiseWithFiltersTableAdapter, and fills the dataset with data filtered by the specified parameters:

// Initialize the dataset and fill it using the table adapter
var ds = new PackingDataset();
var ExFactoryAdapter = new GetPOSplitDataSetExfactoryWiseWithFiltersTableAdapter();
ExFactoryAdapter.Fill(ds.GetPOSplitDataSetExfactoryWiseWithFilters, CompanyId, BrId, OrId, Buymonth, StyleNo, PO, ColorCode, Customer, fromDateTime, toDateTime);

Quality Reports

Inline Quality Report

The code snippet initializes a dataset and uses a table adapter to fill the dataset with filtered data from the QualityDataSet table. This report focuses on displaying quality control data for products during the inline production process, allowing for real-time monitoring and analysis of quality issues and performance. The filters applied in the report allow you to customize the data retrieval based on the following parameters:

Filters

• fromDateTime: The start date and time for filtering the report data.
• toDateTime: The end date and time for filtering the report data.
• LineCode: The code representing the production line for tracking.
• OrId: The work order ID to track a specific production order.
• PO: Production Order number, representing the buyer’s order.
• ColorCode: The specific color code for the product.
• BuyMonth: The month associated with the buyer’s order.
• StyleNo: The style number of the product being produced.
• CompanyId: The unique identifier for the company.
• BrId: Branch or factory ID to specify the location.

Code Snippet

The following C# code initializes the PackingDataset, creates a QualityDataSetTableAdapter, and fills the dataset with data filtered by the specified parameters:

// Initialize the dataset and fill it using the table adapter
var ds = new PackingDataset();
var InlineDataAdapter = new QualityDataSetTableAdapter();
InlineDataAdapter.Fill(ds.QualityDataSet, fromDateTime, toDateTime, LineCode, OrId, PO, ColorCode, BuyMonth, StyleNo, CompanyId, BrId);

Endline Quality Report

The code snippet initializes a dataset and uses a table adapter to fill the dataset with filtered data from the QualityDataSet table. This report focuses on displaying quality control data for products at the end of the production line, allowing for detailed analysis of quality checks and the detection of defects before the products are finalized. The filters applied in the report allow you to customize the data retrieval based on the following parameters:

Filters

• fromDateTime: The start date and time for filtering the report data.
• toDateTime: The end date and time for filtering the report data.
• LineCode: The code representing the production line for tracking.
• OrId: The work order ID to track a specific production order.
• PO: Production Order number, representing the specific production order.
• ColorCode: The specific color code for the product.
• BuyMonth: The month associated with the buyer’s order.
• StyleNo: The style number of the product being produced.
• CompanyId: The unique identifier for the company.
• BrId: Branch or factory ID to specify the location.

Code Snippet

The following C# code initializes the PackingDataset, creates a QualityDataSetTableAdapter, and fills the dataset with data filtered by the specified parameters:

// Initialize the dataset and fill it using the table adapter FillBy
var ds = new PackingDataset();
var InlineDataAdapter = new QualityDataSetTableAdapter();
InlineDataAdapter.FillBy(ds.QualityDataSet, fromDateTime, toDateTime, LineCode, OrId, PO, ColorCode, BuyMonth, StyleNo, CompanyId, BrId);

Quality Summary Report

The code snippet initializes a dataset and uses a table adapter to fill the dataset with filtered data from the QualityDataSet table. This report provides a comprehensive summary of quality control data across various stages of production, allowing for a high-level view of the overall quality performance. The filters applied in the report allow you to customize the data retrieval based on the following parameters:

Filters

• fromDateTime: The start date and time for filtering the report data.
• toDateTime: The end date and time for filtering the report data.
• LineCode: The code representing the production line for tracking.
• OrId: The work order ID to track a specific production order.
• PO: Production Order number, representing the specific production order.
• ColorCode: The specific color code for the product.
• BuyMonth: The month associated with the buyer’s order.
• StyleNo: The style number of the product being produced.
• CompanyId: The unique identifier for the company.
• BrId: Branch or factory ID to specify the location.

Code Snippet

The following C# code initializes the PackingDataset, creates a QualityDataSetTableAdapter, and fills the dataset with data filtered by the specified parameters:

// Initialize the dataset and fill it using the table adapter FillBy1
var ds = new PackingDataset();
var InlineDataAdapter = new QualityDataSetTableAdapter();
InlineDataAdapter.FillBy1(ds.QualityDataSet, fromDateTime, toDateTime, LineCode, OrId, PO, ColorCode, BuyMonth, StyleNo, CompanyId, BrId);