daily production report with opeing and closing fields with grouping

Question

I have to create a production report in SQL Server 2008 for my system, which includes datewise and employee wise/process wise grouping and issue/receive with it's opening and closing quantity/pcs.

Here is my SQL Server table structure:

CREATE TABLE [dbo].[PROD_SUM](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TRANS_DATE] [date] NOT NULL,
    [TRANS_FLAG] [varchar](1) NULL,   // contains 'I'->Issue Or 'R' ->Receive
    [EMP_ID] [smallint] NULL,
    [PROC_ID] [smallint] NULL,
    [DEPT_ID] [smallint] NULL,
    [PCS] [smallint] NULL,
    [WEIGHT] [decimal](9, 3) NULL,
    [VALUE] [decimal](15, 2) NULL,
 CONSTRAINT [PK_PROD_SUM] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Here is some dummy data.

INSERT INTO [PROD_SUM]
           ([TRANS_DATE]
           ,[TRANS_FLAG]
           ,[EMP_ID]
           ,[PROC_ID]
           ,[DEPT_ID]
           ,[PCS]
           ,[WEIGHT]
           ,[VALUE])
     VALUES
           ( '2014-May-01', 'R', 1, 1, 1, 10, 12.50, 100.00),
           ( '2014-May-01', 'I', 1, 1, 1, 05, 4.75, 24.00),
           ( '2014-May-02', 'R', 1, 1, 1, 14, 14.50, 140.00),
           ( '2014-May-02', 'I', 1, 1, 1, 02, 2.47, 12.00),
           ( '2014-May-03', 'R', 1, 1, 1, 01, 1.22, 10.00)
GO

Now with above sample data, I need report for the period between '02-May-2014' and '03-May-2014' with opening and closing for the day list as follows.

                                     02-05-2014                        03-05-2014   
                         ----------------------------------   -------------------------------
Emp_Id | Proc_id | Unit | Opening  Receive  Issue  Closing | Opening  Receive  Issue  Closing    
---------------------------------------------------------------------------------------------
   1       1        Pcs       05       14      2      17        17        1      0      18
                    Wgt.    7.75     14.5   2.47   19.78     19.78     1.22      0   21.00
                    Val.   76.00   140.00  12.00  204.00    204.00    10.00      0  214.00

Here you can see that I have added one new column in report as "Unit", which includes Pcs, Weight and value. and very first date's opening column should be calculate opening value (eg. receive minus issue of previous dates (before 02-May-2014)). closing field should be Opening + receive - Issue, and this closing value should be carried forward in next date as opening value.

This tables has about 10k rows, so, performance must be consider while generating report. I am using SQL Server 2008 as database and right now I am not using it's reporting service. So, If some better option available in reporting service, let me know. because I had never used reporting service before.

Thank you.

what have you tried so far ? You only have I and R flags in TRANS_FLAG, but have the columns Opening, Receive, Closing and Issue in output. — Erran Morad, May 10 '14 at 04:24
@BoratSagdiyev, I want sum of Pcs,Weight and value grouped by TRANS_FLAG field, and opening and closing should be calculate. Opening will be previous days closing (except very first date, first date should be sum of fields before report start date) and closing will be opening+receive-issue. — Manish Sapkal, May 10 '14 at 07:29

score 1 · Answer 1 · answered May 12 '14 at 03:07

For Closing I would use the SSRS RunningValue function, e.g. (assuming your lowest level Row group is named "Proc_Id")

= RunningValue ( Fields!receive.Value - Fields!issue.Value , Sum , "Proc_Id" )

Then I would calculate Opening by reversing your logic e.g.

= ReportItems!Closing.Value - ( Fields!receive.Value - Fields!issue.Value )

daily production report with opeing and closing fields with grouping

1 Answers1