Add code to rdlc report to calculate sub total where some criteria is not in the dataset

[Hugh Self Taught]

Hi VB.Net gurus,

I'm using VS2015 VB.Net & have designed a rdlc report using report designer (NOT Crystal Reports)

The report consists of nested lists & have a totals row on the lowest grouping wrapping the details to calculate the points per section. However my dilemma is that I cannot just sum based on the group.

Amongst the sections to total is a Novice section, a Pre section & a RS section. In some instances the Novice & Pre sections are combined in a RS section at a competition. Some competitors only compete in the RS based competitions but I still need to calculate whether they're actually Novice or Pre. When they promote from Novice to Pre they start again from 0 points. They require different amounts of points to progress from one level to the next & that data is in a table. The report dataset is from a table of all events history with several joins for names etc. In my old MS Access DB I used a recordset to loop with if's & lookup's then changed the points background color in the report to indicate Novice or Pre.

I've looked up how to add the code to the report & how to call it from the report textbox. Just don't know how I would lookup the criteria. Do I load a datatable to refer to if that's even possible? I include a segment of the VBA code from the access report for info. This is the last piece of functionality needed to complete this report then I can go live on my sql server & archive the Access version. Any guidance much appreciated.

If intMaxGrade > 5 Then ' if above Gold ie: Dress Codes
If intMaxGrade = 8 Then
intMaxGrade = Nz(DMax("Disp_Seq", "qryMaxGrade", "PtsMale =" & intMaleID & "And PtsFemale =" & intFemID & "And StylID =" & i & "And Disp_Seq between " & 6 & " And " & 7), 0) 'Get highest non RS grade achieved
End If

intGrade6Points = DLookup("PointsToProgress", "[Dance Section]", "Disp_Seq =" & 6)
intGrade7Points = DLookup("PointsToProgress", "[Dance Section]", "Disp_Seq =" & 7)

Set rsTempHistData = CurrentDb.OpenRecordset("qryTempHistData") 'Load dataset of all Male & Female partnerships
rsTempHistData.MoveLast
rsTempHistData.MoveFirst

intChkGrade1 = 0
intChkGrade2 = 0

Do Until rsTempHistData.EOF()
If rsTempHistData.Fields("Disp_Seq") > 5 And rsTempHistData.Fields("Style") = 1 Then
intCalcTotal1 = intCalcTotal1 + Nz(rsTempHistData.Fields("PtsPoints"), 0) + Nz(rsTempHistData.Fields("PtsPointsBonus"), 0)

If intMaxGrade = 0 And rsTempHistData.Fields("Disp_Seq") = 8 Then
If intChkGrade1 <> 7 Then
If intCalcTotal1 >= intGrade6Points Then
intChkGrade1 = 7
RowTotal1 = intCalcTotal1
intCalcTotal1 = 0
Else
RowTotal1 = intCalcTotal1
End If
Else
RowTotal1 = intCalcTotal1
End If

ElseIf intMaxGrade = 6 And rsTempHistData.Fields("Disp_Seq") = 6 Or intMaxGrade = 6 And rsTempHistData.Fields("Disp_Seq") = 8 Then
RowTotal1 = intCalcTotal1
If intChkGrade1 = 7 And rsTempHistData.Fields("Disp_Seq") = 6 Then
intCalcTotal1 = intCalcTotal1 + intPrevTotal1
RowTotal1 = intCalcTotal1
intChkGrade1 = 0
blnPromoted1 = False

ElseIf intChkGrade1 <> 7 Then
If intCalcTotal1 >= intGrade6Points Then
intChkGrade1 = 7
intPrevTotal1 = intCalcTotal1
intCalcTotal1 = 0
blnPromoted1 = True
End If
End If

ElseIf intMaxGrade = 7 And rsTempHistData.Fields("Disp_Seq") = 8 Then
RowTotal1 = 0

If intChkGrade1 = 7 Then
RowTotal1 = intCalcTotal1

ElseIf intChkGrade1 <> 7 Then
If intCalcTotal1 >= intGrade6Points Then
intChkGrade1 = 7
intCalcTotal1 = 0
End If
End If

ElseIf intMaxGrade = 7 And rsTempHistData.Fields("Disp_Seq") = 7 Then
If RowTotal1 = 0 And intChkGrade1 = 6 Then
intCalcTotal1 = Nz(rsTempHistData.Fields("PtsPoints"), 0) + Nz(rsTempHistData.Fields("PtsPointsBonus"), 0)
End If

RowTotal1 = intCalcTotal1
intChkGrade1 = 7

ElseIf intMaxGrade = 7 And rsTempHistData.Fields("Disp_Seq") = 6 Then
RowTotal1 = 0
If intChkGrade1 = 7 Then
intCalcTotal1 = 0

ElseIf intChkGrade1 <> 7 Then
intChkGrade1 = 6
If intCalcTotal1 >= intGrade6Points Then
intChkGrade1 = 7
intCalcTotal1 = 0
End If
End If
End If

ElseIf rsTempHistData.Fields("Disp_Seq") > 5 And rsTempHistData.Fields("Style") = 2 Then
intCalcTotal2 = intCalcTotal2 + Nz(rsTempHistData.Fields("PtsPoints"), 0) + Nz(rsTempHistData.Fields("PtsPointsBonus"), 0)

If intMaxGrade = 0 And rsTempHistData.Fields("Disp_Seq") = 8 Then
If intChkGrade2 <> 7 Then
If intCalcTotal2 >= intGrade6Points Then
intChkGrade2 = 7
RowTotal2 = intCalcTotal2
intCalcTotal2 = 0
Else
RowTotal2 = intCalcTotal2
End If
Else
RowTotal2 = intCalcTotal2
End If

ElseIf intMaxGrade = 6 And rsTempHistData.Fields("Disp_Seq") = 6 Or intMaxGrade = 6 And rsTempHistData.Fields("Disp_Seq") = 8 Then
RowTotal2 = intCalcTotal2
If intChkGrade2 = 7 And rsTempHistData.Fields("Disp_Seq") = 6 Then
intCalcTotal2 = intCalcTotal2 + intPrevTotal2
RowTotal2 = intCalcTotal2
intChkGrade2 = 0
blnPromoted2 = False

ElseIf intChkGrade2 <> 7 Then
If intCalcTotal2 >= intGrade6Points Then
intChkGrade2 = 7
intPrevTotal2 = intCalcTotal2
intCalcTotal2 = 0
blnPromoted2 = True
End If
End If

ElseIf intMaxGrade = 7 And rsTempHistData.Fields("Disp_Seq") = 8 Then
RowTotal2 = 0

If intChkGrade2 = 7 Then
RowTotal2 = intCalcTotal2

ElseIf intChkGrade2 <> 7 Then
If intCalcTotal2 >= intGrade6Points Then
intChkGrade2 = 7
intCalcTotal2 = 0
End If
End If

ElseIf intMaxGrade = 7 And rsTempHistData.Fields("Disp_Seq") = 7 Then
If RowTotal2 = 0 And intChkGrade2 = 6 Then
intCalcTotal2 = Nz(rsTempHistData.Fields("PtsPoints"), 0) + Nz(rsTempHistData.Fields("PtsPointsBonus"), 0)
End If

RowTotal2 = intCalcTotal2
intChkGrade2 = 7

ElseIf intMaxGrade = 7 And rsTempHistData.Fields("Disp_Seq") = 6 Then
RowTotal2 = 0
If intChkGrade2 = 7 Then
intCalcTotal2 = 0

ElseIf intChkGrade2 <> 7 Then
intChkGrade2 = 6
If intCalcTotal2 >= intGrade6Points Then
intChkGrade2 = 7
intCalcTotal2 = 0
End If
End If
End If
End If

rsTempHistData.MoveNext
Loop
intCalcTotal1 = 0
intCalcTotal2 = 0

In this code Novice = 6, Pre = 7 & RS = 8

Continue reading...