I can´t find a direct solution withing SQL or RDLC so I had to make some more code and work with the data manualy. Here is code of the Form that represents the data in a rdlc report. The rest is just rdlc playing :)
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using ICSDB;
using Microsoft.Reporting.WinForms;
namespace ICS.EnterpriseResourcePlaning
{
public partial class WarehouseMovementsReportForm : Form
{
private DataTable DtWarehouseMovements = new DataTable();
int _WarehouseID = 0;
double _RuningSum = 0;
public WarehouseMovementsReportForm(int warehouseID=0)
{
_WarehouseID = warehouseID;
InitializeComponent();
AddColumnsToDataTable(DtWarehouseMovements);
}
private DataTable AddColumnsToDataTable(DataTable dt)
{
dt.Columns.Add("Datum", typeof(DateTime));
dt.Columns.Add("Bewegung", typeof(string));
dt.Columns.Add("Artikel", typeof(string));
dt.Columns.Add("ANummer", typeof(string));
dt.Columns.Add("LSNummer", typeof(string));
dt.Columns.Add("Paletten", typeof(double));
dt.Columns.Add("Faktor", typeof(double));
dt.Columns.Add("Handling", typeof(double));
dt.Columns.Add("Week", typeof(int));
dt.Columns.Add("RuningSum", typeof(double));
dt.Columns.Add("Lagergeld", typeof(double));
return dt;
}
public void FillDataTable()
{
DtWarehouseMovements.Rows.Clear();
_RuningSum = 0;
DateTime minDate = DateTime.Today;
DateTime maxDate = DateTime.Today;
minDate = dtpMinDate.Value;
maxDate = dtpMaxDate.Value;
WarehouseMovements tblWarehouseMovements = new WarehouseMovements();
WarehouseMovementTypes tblWarehouseMovementTypes = new WarehouseMovementTypes();
WarehouseItems tblWarehouseItems = new WarehouseItems();
tblWarehouseMovements.Orderer = "Date,ID";
tblWarehouseMovements.Parameters.Clear();
tblWarehouseMovements.SetParameter("Deleted", false);
tblWarehouseMovements.AddParameter("Date", minDate.AddDays(-1), "", SQLTable.Operator.Conditional.UND, SQLTable.Operator.Rational.größer);
tblWarehouseMovements.AddParameter("Date", maxDate.AddDays(1), "", SQLTable.Operator.Conditional.UND, SQLTable.Operator.Rational.kleiner);
tblWarehouseMovements.SetParameter("WarehouseID", _WarehouseID);
DataTable dtWM = tblWarehouseMovements.DataTable;
_RuningSum = SumToDate(minDate);
foreach (DataRow row in dtWM.Rows)
{
DataRow Nrow = DtWarehouseMovements.NewRow();
double Lagergeld = 0;
DateTime date = DateTime.MinValue;
DateTime.TryParse(row["Date"].ToString(), out date);
int movementID = 0;
int.TryParse(row["WarehouseMovementTypeID"].ToString(), out movementID);
string bewegung = tblWarehouseMovementTypes.MovementName(movementID);
int itemID = 0;
int.TryParse(row["WarehouseItemID"].ToString(), out itemID);
string artikel = string.Empty;
string aNummer = string.Empty;
double pFaktor = 0;
tblWarehouseItems.WarehouseItemData(itemID, out artikel, out aNummer, out pFaktor);
double palleten = 0;
double.TryParse(row["Pallets"].ToString(), out palleten);
int movementTypeFactor = tblWarehouseMovementTypes.MovementFactor(movementID);
_RuningSum += palleten * movementTypeFactor;
if (_RuningSum > Lagergeld)
Lagergeld = _RuningSum;
int week = 0;
week = DateAdapter.Week.WeekNumber(date);
Nrow["Datum"] = date;
Nrow["Bewegung"] = bewegung;
Nrow["Artikel"] = artikel;
Nrow["ANummer"] = aNummer;
Nrow["LSNummer"] = row["LSNumber"].ToString();
Nrow["Paletten"] = palleten;
Nrow["Faktor"] = pFaktor;
Nrow["Week"] = week;
Nrow["RuningSum"] = _RuningSum;
Nrow["Lagergeld"] = Lagergeld;
DtWarehouseMovements.Rows.Add(Nrow);
}
}
private double SumToDate(DateTime date)
{
double sum = 0;
string command = " SELECT SUM(dbo.WarehouseMovementTypes.Factor * dbo.WarehouseMovements.Pallets) AS Summe " +
" FROM dbo.WarehouseMovements INNER JOIN " +
" dbo.WarehouseMovementTypes ON dbo.WarehouseMovements.WarehouseMovementTypeID = dbo.WarehouseMovementTypes.ID " +
" WHERE (dbo.WarehouseMovements.WarehouseID = @WarehouseID) AND (dbo.WarehouseMovements.Date < @Date) AND (dbo.WarehouseMovements.Deleted = 0)";
SqlConnection conn = ICSConnections.ICSDB;
SqlCommand cmd = new SqlCommand(command, conn);
cmd.Parameters.Add("@Date", SqlDbType.NVarChar).Value = date;
cmd.Parameters.Add("@WarehouseID", SqlDbType.Int).Value = _WarehouseID;
double.TryParse(cmd.ExecuteScalar().ToString(), out sum);
return sum;
}
private void button1_Click(object sender, EventArgs e)
{
RefreshReport();
}
private void RefreshReport()
{
FillDataTable();
rptWarehouseMovements.ProcessingMode = ProcessingMode.Local;
ReportDataSource source = new ReportDataSource("WarehouseMovementsDS", DtWarehouseMovements);
rptWarehouseMovements.LocalReport.DataSources.Clear();
rptWarehouseMovements.LocalReport.DataSources.Add(source);
this.rptWarehouseMovements.RefreshReport();
}
}
}