RDLC Reports

RDLC REPORTING:

Standard Format:

FORMAT("Unit Price",0,'<Precision, 2:5><standard format,0>')

One record report Print:

SalesHeader.GET("No.");

SalesHeader.SETRECFILTER;

REPORT.RUN(ReportSelection."Invoice",TRUE,FALSE,SalesHeader);

Multirecord report Print.

SETSELECTION Print :

CurrPage.SETSELECTIONFILTER(g_recILE);
PrintRecords(g_recILE,TRUE);

PrintRecords(VAR precILE : Record "Item Ledger Entry";ShowRequestForm : Boolean)
WITH g_recILE DO BEGIN
  COPY(precILE);
  FIND('-');
  REPORT.RUNMODAL(REPORT::"ILE",ShowRequestForm,FALSE,g_recILE);
END;

Merge two expression in one 

=string.Format("{0} {1}",Code.BlankZero(Fields!Containers_Quantity_Value_.Value),Fields!Containers_UoM_Value_.Value)

Group wise serial number:

=RunningValue(Fields!LineNo_SalesLine.Value, CountDistinct, "DataSet_Result")


=RunningValue(IIf(Fields!No_SalesLine.Value <> "", Fields!No_SalesLine.Value, Nothing)
    , CountDistinct
    , "DataSet_Result")

Footer decimal formating:

CDec(Last(Fields!Amount_SalesCrMemoHeader.Value, "DataSet_Result")).ToString(String.Format("{0};({0}); ",
Last(Fields!Amount_SalesCrMemoHeaderFormat.Value, "DataSet_Result"))) + chr(177) +

·         Display fixed decimal places =FormatNumber(ROUND(SUM(Fields!Line_CBM.value),3),3)

·         DISPLAY ONLY DATE: =format(Fields!Date.Value,"dd/MM/yyyy")

·         Display data on header nav 2009 rdlc

·         Add text box in body =Fields!COMPINFO_GSTREGNO.Value and set to hidden

Then go to header add textbox =reportitems!companyGstRegNo.Value

·         If the report is going to multiple blank pages set the Top ,Left,right and bottom margin in report properties.

·         Print new line

·         Fields!customeridname.Value & vbcrlf & Fields!address1_line1.Value & vbcrlf & Fields!address1_line2.Value & vbcrlf & Fields!address1_line3.Value

Hide Dublicate values in

=iif(Fields!YourField.Value = Previous(Fields!YourField.Value), True, False)

01/01/2001 AS MONTH YEAR

=Format(CDate(Fields!A.Value), "MMMM yyyy")

For Dates, the Regional and Language Options in Windows is used to select the actual format that is used. The table below shows the Fields that will be used to build the various formats. This table, incidentally, corresponds to the Regional setting for a European country/region.
Date
Format
Example
<Closing><Day,2>-<Month,2>-<Year>
0
05-04-03
<Closing><Day,2>-<Month,2>-<Year>
1
05-04-03
<Day,2><Month,2><Year><Closing>D
2
050403D
<Closing><Year>-<Month,2>-<Day,2>
3
03-04-05
<Closing><Day>. <Month Text> <Year4>
4
5. April 2003
<Closing><Day,2><Month,2><Year>
5
050403
<Closing><Year><Month,2><Day,2>
6
030405
<Day,2><Filler Character, >. <Month Text,3> <Year4>
7
5. Apr 2003
XML format
9
2003-04-05
The following table shows how these formats will change if the Regional and Language Options is set to "United States."
US Date
Format
Example
<Closing><Month,2>/<Day,2>/<Year>
0
04/05/03
<Closing><Month,2>/<Day,2>/<Year>
1
04/05/03
<Month,2><Day,2><Year><Closing>D
2
040503D
<Closing><Year>/<Month,2>/<Day,2>
3
03/04/05
<Month Text> <Closing><Day>, <Year4>
4
April 5, 2003
<Closing><Month,2><Day,2><Year>
5
040503
<Closing><Year><Month,2><Day,2>
6
030405
<Day,2><Filler Character, >. <Month Text,3> <Year4>
7
5. Apr 2003

SSRS - Serial number for the group column

                We were developing a report which contain a group (grouped by Product) and the group contain a detail row in it. We wanted to show Serial Number for the Products, we trying to use RowNumber function in SSRS but it didn’t come up as expected.  Finally we found the solution from Web which was written by Bilal Hani- a Microsoft Community Contributor as how to “add serial number to grouping column in SSRS Tablix”.

1. Open the report properties (right click on blank area outside the layout and click properties) or (Menu-->Report-->Report Properties)

2. Click on the code pane on the left

3. In the code window enter below script

Dim Counter as integer=0

public function getGroupCounter() as Integer

 Counter=Counter+1

 return Counter

end function

4. Go to the properties of the group (based on which group you need the group serial number)

5. Click the variables tab on the left

6. Click on Add and set Name as GroupCountValue and set the expression (in the expression window) as =Code.getGroupCounter, click ok and close the window.

7. Now in the textbox where you want to display the group number, set the expression as =Variables!GroupCountValue.Value

Make 0 as blank

Iif(quantity=0,””,quantity)

Repeat Table Header on each page:

http://blogs.msdn.com/b/robertbruckner/archive/2008/10/13/repeat-header-and-visible-fixed-header-table.aspx

1)in the grouping pane, click on the small triangle and select "Advanced Mode" to show static members:

1.     verify that you have the properties grid turned on (in BI Development Studio use F4, in Report Builder go to the "View" tab and select "Properties")
 

2.     select the corresponding (static) item in the row group hierarchy
 

3.     in the properties grid:
- set KeepWithGroup to After
- set RepeatOnNewPage to true for repeating headers
- set FixedData to true for keeping headers visible

=======================================================================

Shared Data50000 as Object

               

Public Function GetData(Num as Integer, Group as integer) as Object

if Group = 50000 and Cstr(Choose(2, Split(Cstr(Data50000),Chr(177))))>"" then

   Return Cstr(Choose(Num, Split(Cstr(Data50000),Chr(177))))

end if

End Function

Public Function SetData(NewData as Object,Group as integer)

If Group = 50000 and NewData <> "" Then

      Data50000 = NewData

end if

End Function

In Body:(CustDet) and set to hidden

add expression

=Fields!BilltoCustomerNo_SalesHeader.Value+ chr(177) +

Fields!BilltoName_SalesHeader.Value+ chr(177)

In Header add expression

=code.SetData(reportitems!CustDet.Value, 50000)

Header Values

=code.GetData(1, 50000)

=code.GetData(2, 50000)

(Fields!GroupingField.Value = ReportItems!LastValueOfGroup.Value)

3) Nav 2009 RDLC Add field in Header or Footer

Add a field in Body and give name to text box and change it to hidden

In Header or Footer add field expression as reportitems!field.Value

Space Bewteen Textboxes
=string.Format("{0} {1}",Code.BlankZero(Fields!Containers_Quantity_Value_.Value),Fields!Containers_UoM_Value_.Value)


Show Decimals when necessary:
Set your format string on the text-box to custom
=iif(CDbl(Fields!val.Value)=Floor(CDbl(Fields!val.Value)),"0","0.00")


Remove Time
=Format(Fields!PostingDate_SalesInvoiceHeader.Value,"short date”)
=Format(Fields!PostingDate_SalesInvoiceHeader.Value,"dd-MMM-yyyy")


SSRS Time format

format(Fields!TimeofRemoval_SalesInvoiceHeader.Value,"hh:mm tt")

Transheader and Transfooter 

Define two Textcontants

Name	ConstValue
ContinuefromPgCaptionLbl	Continue from page
ContinueOnPgCaptionLbl	Continue on page

Go to the Layout 

Table Body insert a new row and set the row visibility to HIDE

Trans Header 

Add Expression in Textbox and Name It as TransHeaderSum

=RunningValue(Fields!SalesInvoiceLine_LineAmount_.Value,Sum,"Sales_Invoice_Header_No_") - Fields!SalesInvoiceLine_LineAmount_.Value

Trans Footer

Add Expression in Textbox and Name it as TransFooterSum

=RunningValue(Fields!SalesInvoiceLine_LineAmount_.Value,SUM,"Sales_Invoice_Header_No_")

Go to the Report Header and add one Textbox for Trans Header and set expression as
=First(Fields!ContinuefromPgCaptionLbl.Value, "DataSet_Result")
=CInt(Globals!PageNumber)-1
=First(ReportItems!TransHeaderSum.Value)

and Row Visibility to
=IIF(Globals!PageNumber=1,true,false)

Go to the Report Footer and add one textbox for TransFooter and set expression as
=First(ContinueOnPgCaptionLbl.Value, "DataSet_Result")
=CInt(Globals!PageNumber)+1
=Last(ReportItems!TransFooterSum.Value) 

and Row Visibility to 

=IIF(Globals!PageNumber=Globals!TotalPages,true,false)

Screen Shot:

NAV 2013 Reports :(New Report)

Add Functions:

Go to report properties -> CODE ->

Shared arrData() as object
Shared arrDataFindBold() as String
Dim intLeft as Integer

Public Function GetDataInfo(Num as Integer, Group as integer) as Object
   Return Cstr(Choose(Num, Split(Cstr(arrData(Group-1)),Chr(177))))
End Function

Public Function GetDataInfo2(Pos as string,Num as Integer,Group as integer) as Object
   Dim obj as Object
   obj = Cstr(Choose(Num, Split(Cstr(arrData(Group-1)),Chr(177))))
   If (obj Is Nothing) Then
        Return obj
   End If
 
   If (Pos.ToUpper()="L") Then
        If IsLeftBold(Num,Group) Then
          obj = Left(obj,intLeft)
        End If
   ElseIf (Pos.ToUpper()="R") Then
        If IsLeftBold(Num,Group) Then
          obj = Mid(obj,intLeft+1)
        Else
          Obj = ""
        End If
   End If
 
   Return obj
End Function

Public Function SetFindBoldData(NewData as Object,Delimiter as String)
        arrDataFindBold = Split(NewData,Delimiter)
        Return true  
End Function

Public Function IsLeftBold(Num as Integer,Group as integer) as Boolean
   Dim obj as Object
   Dim blnFound as Boolean
   
   obj = Cstr(Choose(Num, Split(Cstr(ArrData(Group-1)),Chr(177))))
   If (obj Is Nothing) Then
        Return blnFound
   End If
 
   If blnFound Then
        Return blnFound
   End If
 
   For i As Integer = 0 To arrDataFindBold.Length - 1
        If blnFound Then
         Exit For
        End If
        intLeft = Len(Trim(arrDataFindBold(i)))
        blnFound =(InStr(Left(obj, intLeft), Trim(arrDataFindBold(i))) <> 0)
   Next
 
   Return blnFound
End Function

Public Function SetDataInfo(NewData as Object,Group as integer)
        If IsNothing(arrData) Then
                ReDim Preserve arrData(0)
        End If
        If (arrData.Length() < Group) Then
                For intindex As Integer = arrData.Length To (Group - 1)
                        ReDim Preserve arrData(arrData.Length())
                Next
        End If

        If (NewData > "") Then
                arrData(Group-1) = NewData
        End If

        Return True
End Function

Body row visibility:

=(code.SetDataInfo(
Cstr(Fields!CustAddr1.Value) + Chr(177) +
Cstr(Fields!CustAddr2.Value) + Chr(177) +
Cstr(Fields!CustAddr3.Value) + Chr(177) +
Cstr(Fields!CustAddr4.Value) + Chr(177) +
Cstr(Fields!CustAddr5.Value) + Chr(177) +
Cstr(Fields!CustAddr6.Value) + Chr(177) +
Cstr(Fields!CustAddr7.Value) + Chr(177) +
Cstr(Fields!CustAddr8.Value) + Chr(177) +
Cstr(Fields!No_SalesHeader.Value) + Chr(177) +
Cstr(Fields!DocDate_SalesHeader.Value) + Chr(177) +
Cstr(Fields!g_txtPaymentTerm.Value) + Chr(177) +
Cstr(Fields!g_cdeCurrency.Value)+ Chr(177)
+Cstr(Fields!BilltoCustNo_SalesHeader.Value)
,1))

Textbox expression

=Code.GetDataInfo(1,6)

Reset Page Number On Group

Go to Group Properties( Press F4 key)
PageBreak -> ResetPageNumber -> TRUE

Serial Number based on Row Visibility :

http://stackoverflow.com/questions/18953301/ssrs-row-number-within-table-excluding-hidden-rows

Serial No: 

=RunningValue(IIf((Fields!BaseOfCharge.Value = "PAX") AND (Fields!GrpFtr_SvcCode.Value <> "") AND (Fields!g_blnRFShowDetail.Value = true),Fields!g_txtGroupOn.Value, Nothing)
, CountDistinct
, "Tablix3")

set Row visibility 
IIf((Fields!BaseOfCharge.Value = "PAX") AND (Fields!GrpFtr_SvcCode.Value <> "") AND (Fields!g_blnRFShowDetail.Value = true)

create a variable and set the tablix group in nav code
Fields!g_txtGroupOn.Value

Give Tablix Name
"Tablix3"

Report Print from page:

CLEAR(g_cdePrevDocNo);
CLEAR(g_cdeDocNo);
CurrPage.SETSELECTIONFILTER(g_recGLEntry);
g_recGLEntry.SETCURRENTKEY("Document No.","Posting Date");
IF g_recGLEntry.FINDFIRST THEN 
REPEAT
IF (g_recGLEntry."Document No." <> g_cdePrevDocNo) THEN
IF (g_cdeDocNo <> '') THEN
g_cdeDocNo += '|'+g_recGLEntry."Document No."
ELSE
g_cdeDocNo := g_recGLEntry."Document No.";
g_cdePrevDocNo := g_recGLEntry."Document No.";
UNTIL g_recGLEntry.NEXT = 0;

g_recGLEntry2.SETFILTER("Document No.",g_cdeDocNo);
REPORT.RUNMODAL(50023,TRUE,FALSE,g_recGLEntry2)

Set Footer Decimal formating

=(code.SetDataInfo(
CDec(Last(Fields!g_arrdecAmount_1_Value_.Value, "Grp_Footer")).ToString(String.Format("{0};({0});0.00",Last(Fields!g_arrdecAmount_1_Value_Format.Value, "Grp_Footer"))) + chr(177) +//show 0.00
CDec(Last(Fields!g_arrdecAmount_1_Value_.Value, "Grp_Footer")).ToString(String.Format("{0};({0}); ",Last(Fields!g_arrdecAmount_1_Value_Format.Value, "Grp_Footer"))) //hide if 0.00
,6))

set expression
=Code.GetDataInfo(1,6)

Show Header or footer only on the Last page:

set Visibility property to 
=IIF(Globals!PageNumber=Globals!TotalPages,false,true)

Show page number on Header:

set Textbox expression
=Globals!PageNumber & " of " & Globals!TotalPages

Main Group Property 
Pagebreak -> ResetPageNumber to True

RDLC Handle Fixed spacing in Footer.

Create a text constant:

Name                   ConstValue
BlankCaptionLbl

=IIF(Globals!PageNumber=Globals!TotalPages,Fields!BlankCaptionLbl.Value,Parameters!LblContinue.Value)

The Blank text box is reserved now.