Table of Contents
- Getting Started
- SetData & GetData - the NAV way
- Improving SetData & GetData
open the report layout, go to the custom code section and copy/paste the code below:
' Source: https://github.com/AndreasRascher/RDLCReport_CustomCode
' Hidden Tablecell Property Hidden=Code.SetGlobalData(Fields!GlobalData.Value)
' =================
' Global variables
' =================
Shared GlobalDict As Microsoft.VisualBasic.Collection
' ==========================
' Get value by name or number
' ==========================
' Key = position number or name
Public Function GetVal(Key as Object)
Return GetVal2(GlobalDict,Key)
End Function
Public Function GetVal2(ByRef Data as Object,Key as Object)
'if Key As Number
If IsNumeric(Key) then
Dim i as Long
Integer.TryParse(Key,i)
if (i=0) then
return "Index starts at 1"
end if
if (Data.Count = 0) OR (i = 0) OR (i > Data.Count) then
Return "Invalid Index: '"+CStr(i)+"'! Collection Count = "+ CStr(Data.Count)
end if
Return Data.Item(i)
end if
'if Key As String
Key = CStr(Key).ToUpper() ' Key is Case Insensitive
Select Case True
Case IsNothing(Data)
Return "CollectionEmpty"
Case IsNothing(Key)
Return "KeyEmpty"
Case (not Data.Contains(Key))
Return "?"+CStr(Key)+"?" ' Not found
Case Data.Contains(Key)
Return Data.Item(Key)
Case else
Return "Something else failed"
End Select
End Function
' ===========================================
' Set global values from the body
' ===========================================
Public Function SetGlobalData(KeyValueList as Object)
SetDataAsKeyValueList(GlobalDict,KeyValueList)
Return True 'Set Control to Hidden=true
End Function
Public Function SetDataAsKeyValueList(ByRef SharedData as Object,NewData as Object)
Dim i as integer
Dim words As String() = Split(CStr(NewData),Chr(177))
Dim Key As String
Dim Value As String
For i = 1 To UBound(words)
if ((i mod 2) = 0) then
Key = Cstr(Choose(i-1, Split(Cstr(NewData),Chr(177))))
Value = Cstr(Choose(i, Split(Cstr(NewData),Chr(177))))
AddKeyValue(SharedData,Key,Value)
end if
' If last item in list only has a key
if (i = UBound(words)) and ((i mod 2) = 1) then
Key = Cstr(Choose(i, Split(Cstr(NewData),Chr(177))))
Value = ""
AddKeyValue(SharedData,Key,Value)
end if
Next
End Function
Public Function AddKeyValue(ByRef Data as Object, Key as Object,Value as Object)
if IsNothing(Data) then
Data = New Microsoft.VisualBasic.Collection
End if
Dim RealKey as String
if (CStr(Key) <> "") Then
RealKey = CStr(Key).ToUpper()
else
RealKey = CStr(Data.Count +1)
End if
' Replace value if it already exists
if Data.Contains(RealKey) then
Data.Remove(RealKey)
End if
Data.Add(Value,RealKey)
Return Data.Count
End Functionlocal procedure AddKeyValue(VAR KeyValueListAsText: Text; _Key: Text; _Value: Text)
var
Chr177: Text[1];
NewPair: Text;
begin
Chr177[1] := 177;
NewPair := _Key + Chr177 + _Value + Chr177;
KeyValueListAsText += NewPair;
end;
local procedure GetGlobalDataFields(SalesHeader : Record "Sales Header"; Addr : Array[8] of Text) KeyValueList : Text
begin
AddKeyValue(KeyValueList, 'Address1', Addr[1]);
AddKeyValue(KeyValueList, 'Address2', Addr[2]);
AddKeyValue(KeyValueList, 'Address3', Addr[3]);
AddKeyValue(KeyValueList, 'Address4', Addr[4]);
AddKeyValue(KeyValueList, 'Address5', Addr[5]);
AddKeyValue(KeyValueList, 'Address6', Addr[6]);
AddKeyValue(KeyValueList, 'Address7', Addr[7]);
AddKeyValue(KeyValueList, 'Address8', Addr[8]);
AddKeyValue(KeyValueList, 'CompanyAddress1', CompanyAddr[1]);
AddKeyValue(KeyValueList, 'CompanyAddress2', CompanyAddr[2]);
AddKeyValue(KeyValueList, 'CompanyAddress3', CompanyAddr[3]);
end;Example in AL:
dataset
{
dataitem("Purchase Header"; "Purchase Header")
{
[...]
column(GlobalData; GetGlobalDataFields("Purchase Header"))
{ }
[...]
- open the report.rdl file, search for "<ReportItems>" and paste the following text below
- move the tablix into your list tablix if necessary
<Tablix Name="SetGlobalDataTable">
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>0.3cm</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.3cm</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Textbox14">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value />
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox14</rd:DefaultName>
<Visibility>
<Hidden>=Code.SetGlobalData(Fields!GlobalData.Value)</Hidden>
</Visibility>
<Style>
<Border>
<Style>None</Style>
</Border>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember />
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<Group Name="Details" />
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<DataSetName>DataSet_Result</DataSetName>
<Left>15.04597cm</Left>
<Height>0.3cm</Height>
<Width>0.3cm</Width>
<ZIndex>12</ZIndex>
<Style>
<Border>
<Color>Red</Color>
<Style>Solid</Style>
</Border>
<TopBorder>
<Color>Red</Color>
<Style>Solid</Style>
<Width>1pt</Width>
</TopBorder>
<BottomBorder>
<Color>Red</Color>
<Style>Solid</Style>
<Width>1pt</Width>
</BottomBorder>
<LeftBorder>
<Color>Red</Color>
<Style>Solid</Style>
<Width>1pt</Width>
</LeftBorder>
<RightBorder>
<Color>Red</Color>
<Style>Solid</Style>
<Width>1pt</Width>
</RightBorder>
</Style>
</Tablix>
As shown in the example above getting the data into the layout works by calling
=Code.GetVal("YourIdentifier")'!Please end your Expressions with an apostrophe or else you will loose the arguments when copy & pasting textboxes from one instance of SQL Report Builder to another
The report layout is rendered in different steps. Header and footer are rendered after the body. So if we want to align header or footer contents with the current content in the page body we need to use tranfer data from the body via custom code functions.
Code.SetData- saves a list of values as text in a global variable. The values are seperated by the character ± . The code representation of that character isChr(177)Code.GetData- returns a value from one of the 3 lists at the requested position number
Shared Data1 as Object
Shared Data2 as Object
Shared Data3 as ObjectPublic Function GetData(Num as Integer, Group as integer) as Object
' Num - position of the string you want to have
' Group - select which of the 3 globals you want to use as source
' Object - return value
if Group = 1 then
Return Cstr(Choose(Num, Split(Cstr(Data1),Chr(177))))
End If
if Group = 2 then
Return Cstr(Choose(Num, Split(Cstr(Data2),Chr(177))))
End If
if Group = 3 then
Return Cstr(Choose(Num, Split(Cstr(Data3),Chr(177))))
End If
End FunctionPublic Function SetData(NewData as Object,Group as integer)
' NewData - string with char177 as seperator char
' Group - select which of the 3 globals you want to use as source
' Return True - Required to hide the blind table. The method is called within the hidden property of the tablix cell. The propery is processed before rendering other the values
If Group = 1 and NewData <> "" Then
Data1 = NewData
End If
If Group = 2 and NewData <> "" Then
Data2 = NewData
End If
If Group = 3 and NewData <> "" Then
Data3 = NewData
End If
Return True
End FunctionThe NAV approach has some drawbacks we would like to avoid
- after adding new fields to the list, the counting starts. You need to know the position of the item in a list to get the correct value.
- looking at
=Code.GetData(3,1)doesn't indicate which value we want to get - Having 2 arguments instead of 1 in the GetData function only adds to the complexity
- the list of values is maintained in RDLC instead of C/AL or AL which takes a lot of time and is hard to compare between versions
Target #1 Providing the possibility of named indexes to avoid counting and provide better readability
- Approach: Using the Microsoft.VisualBasic.Collection() Object as new global variable. The Class is already available without the need for enabling of external assemblies
Target #2 GetData should only need 1 argument
- Approach: Use the data type dictionary to support named values. While numbered indexes are supported it is much more convenient to use names.
Target #3 Make it easier to maintain the value list
- Approach: By creating a procedure (C/AL or AL) to create a list of values, adding and modifying our Field-List becomes a lot easier