Skip to content

Getdata by name - custom code functions for Dynamics NAV and Business Central

Notifications You must be signed in to change notification settings

AndreasRascher/RDLCReport_CustomCode

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

67 Commits
 
 
 
 
 
 

Repository files navigation

Dynamics NAV/BC RDLC Custom Code - Improving SetData & GetData

Table of Contents


Getting Started

RDLC: Add new methods to the custom code section

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 Function

C/AL or AL: Add methods to create a key value list

local 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;

C/AL or AL: Add key value list text to the dataset

Example in AL:

    dataset
    {
        dataitem("Purchase Header"; "Purchase Header")
        {
	    [...]
            column(GlobalData; GetGlobalDataFields("Purchase Header"))
            { }
	    [...]

RDLC: Add a hidden control in the body section to set the data

  • 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>

Sample SetGlobalData

RDLC: Get data by name

Sample GetVal

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


SetData & GetData - the NAV way

SetData & GetData - Concept

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 is Chr(177)
  • Code.GetData - returns a value from one of the 3 lists at the requested position number

global Variables

Shared Data1 as Object
Shared Data2 as Object
Shared Data3 as Object

GetData

Public 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 Function

SetData

Public 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 Function

Improving SetData & GetData - The Concept

The 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

About

Getdata by name - custom code functions for Dynamics NAV and Business Central

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published