How to create Table Header constant in Body of RDLC and SSRS Reporting?

Hi ,

This is a simple steps guide in 1 report where I had created the Header Tab-lix common for all pages in body.  In this report I had used 3 tab-lix and Header I had made constant using the advanced mode.

This is for reference to remember to do complete the report without crushing your head in grinder. :)

I had defined 5 steps.

1. Click on Advanced mode.
2. Select Static Row. And Change the 2 properties mentioned in screenshot.

More than 30 Points you must think before designing any RDLC Reports in MS Dynamics NAV 2017

Hi,

This post is related with RDLC Report Defination Language in C/AL so before Designing you must

know the reason why and whom and How you will create Reports in MS Dynamics NAV 2017.

So my readers will be able to resolve many problems and concepts which anyone can use in their

daily development process.

25. We can pick Year , Month and Date Difference From Posting Date in NAV.

Using 

= Year(Fields!PostingDate.Value) Further you can also use this as grouping in RDLC.

24. We can use Format in TextBox & also Use HTML in TextBox in NAV

You can use HTML Format in Reports using Textbox If you need help in it you can comment below.

23. Use Of Exit Funtion in Source Expression

You may use  Exit Function also in Data Source of DataItem of RDLC Reporting

22. PrintOnlyIfDetail

Most of you might be knowing it can be used to save unwanted dataset in Report so can be used to improve the timing issue.

21. Use Matrix Report

20. Use conversion Function in RDLC Reporting to convert Format of Expression

19. What is the use of Dataset (Fields!Expression,"Dataset Result")?

It Defines the scope of Field Expression to complete the Result.

18. Use of Function in RDLC 

Check where Code.BlankZero Defination is Given in Report in Report Properties.

17. We can also use Date Function in RDLC Reports

16. We can use year for Grouping in RDLC Reports

15. INLine IF (IIF) and Switch Case can be used in expression of Row and textboxes

14. We can change Expression of Text Box on Behalf of conditions

13. Filling row with color even odd as per requirement.

You can use it in visibility expression

=Iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White")

You can change the Color of BackGround on Behalf of grouping in MS Dynamics NAV.

12. We can insert Row No. in Table using inbuilt function ROWNUBER in RDLC

11. We can repeat header in RDLC Report on every page using static property

10. We can use top n property present in Filters of tablix property to calculate top N customer Item Vendor Report.

9. You can put images in MS Dynamics NAV Report and according to conditions.

8. You can Dynamically break a Tablix and remove last Line from tablix easily using RowNumber Inbuilt Function in RDLC.

=Int((RowNumber(Nothing)-1)/25) 

So these are the points which are possible in MS Dynamics NAV if any any of the above point you need practical session you can comment me or directly message me from my blog I will update you with my blog.

Thanks to all my readers.

Happy Independence Day 2017 to all Indians.

Understand Grouping and applying Toggles in MS Dynamics NAV RDLC Layout

Hi,

Before going in detail you should check the details about controlling the Columns and Rows in NAV

RDLC Layout.

Understanding NAV RDLC Reporting

Today I got a requirement from my client to show all Items By Location Report in MS Dynamics 

NAV. The requirement was simple using Item and ILE Tables but he wants to see the total in one line 

and if he needs to show details then only details must appear.

This requirement can be completed by using SSRS or RDLC Toggle Functionality present in MS Dynamics NAV.

So lets Get Started with seeing the Final result in Demo DB Cronus.

You can click on + and see the details so it is dynamic report for your client.

How to achieve it?

This Hide Details is a boolean field and Integer has Data Item Table view property

Number = 1 as we do earlier.

Now I had created the Layout as follows

Location Code is Grouped as Parent Group here. And selecting the Details Row I had Set the expression as Follows:

NOT Last(Fields!HideDetails.Value, "DataSet_Result")

Grouping In Export To Excel Report Without Layout in MS Dynamics NAV 2017

Grouping In Report with Code in A/L

Hi All,

Today I Got a Requirement for Excel Buffer Report in Navision and with Grouping of Location 

Code in Item Ledger Entry.

Typically it was tough requirement , but thanks to MS Dynamics Community and Saurabh Dhyani Sir

for his valuable time which shared a most important thing and rarest blog of Navision.

Direct Link For Grouping of Reports via Coding in MS Dynamics NAV

I had used the same method  in MS Dynamics NAV 2017. I am Sharing my FOB with you all to 

download and understand the issue better.

Download by clicking the Here..

Useful Points about this Report

1. We can use grouping via C/AL Code.

2. We can arrange the Function via boolean Field.

3. Use of Exit Function in MS Dynamics NAV.

How to do dynamic implementation of decimal place in RDLC Reports?

Dear All,

Today  I had requirement from report in RDLC to implement dynamics Decimal Places. Initially it was to realise but I followed report 206 and once again I got my solution done soon.

For those who still not understand check the red screen there we have dynamic decimal place Value.

Solution :-

To implement this we have to use custom decimal formatting in RDLC reports and in that we had to define Format Value.

For Reference see the screen Shot.

This post also explain to juniors when you need to use Format Value in RDLC Reporting.

Thanks for your time to read my blog post.

Report Summary For RDLC

Hi ,

Today while going through GetData and Setdata functionality I got to know about a book which is the best one i can suggest to all my NAV individual developers.

Please go through this book although all pages are not available to read but you can buy this book from below link on Amazon.

You can also have look and bookmark this link for any guidance regarding reports in MS Dynamics NAV.

I liked the feature explained in it about Repeat Header on every page of Body using Advanced Mode.

Direct Link to read Book for Reporting Tools

Go to Buy this Book.

Click This  Book to Buy

Page N Of M and HTML Formatting in RDlC Reports

Hi ,

Today while going through my requirement in reporting i made a book mark which i think is the most important

bookmark for alll NAV and RDLC/SSRS developer.

How to develop page N of M in RDLC Reporting?

This post also includes how can we use hTML formatting in our RDLC Reports.

however I had not get into such type of requirement but i will be glad if some day i had handson for this requirement.

i hope you will surely look into this reporting methodology and also bookmark this link as i did.

https://community.dynamics.com/nav/b/dynamics101trainingcenternav/archive/2014/07/07/nav-2013-r2-advanced-report-layout

Report Details click here

How to print a next line in RDLC report using AL Code in MS Dynamics Navision?

There is requirement from my client to add all dimensions in RDLC layout with next line to be printed with new line. So I had to handle all things using Cal code.

Procedure :-

Define 2 CHAR variables.

Initialise these 2 variables.

Assign with format in CAL and add this copy dimension in dataitem and put it in RDLC layout your result will be shown as follows

How to remove space between Body and Footer on last page in RDLC reports?

Requirement :-

To remove Gap between footer and body on Last page in RDLC report.

Steps :-

I am sharing my steps if any other developer knows other methods then please do comment below.

Step 1.

Understand the concept of  ReportItems .

https://docs.microsoft.com/en-us/sql/reporting-services/report-design/built-in-collections-reportitems-collection-references-report-builder

Step 2.

I had created the whole footer part in my tablix and then setup visibility condition on behalf of ReportItems in RDLC. I am sharing the screen reference here.

Step 3.

Right click on Footer properties and untick repeat on last page in navision.

I hope this method has helped a lot to my junior developers.

How to Print footer on last page in PDF as well as preview

Print footer on last page in PDF as well as preview

Steps:

1. Design your footer in your visual basic

     (While designing use text box in place of line and use Format TAB near SQL TAB)

Example

2. On each text box, in hidden properties Put this condition.

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

                (if first page = last page then true)

3. Save and exit

Problem : -If you want that your report page should be broken after specified number on Line

Problem : -If you want that your report page should be broken after specified number on line

Solution :  declare two  variables (:-  PageVar and LineVar  as integer

                     Write code on after get record section

                                IF (LineVar MOD 20) = 0 THEN

                                NewPageVar := NewPageVar + 1;             

LineVar := LineVar + 1;

In VB assign group of NewPageVar

How to Repeat table header/Footer on multiple pages.

Repeat table header on multiple pages.

How to resolve If header is repeating on blank page

• If header is repeating on  blank page
• Cause :- report height and width is more than specified in report properties
• Solution:     
• • • Go to Report properties in Visual basic
    • Click on centimeter of page specified and click ok
    • Again go to Report TAB
    • Click on View-à Ruler
    • Reduce your report length and width within the size

Ex: for A4 Page Report should be with  in 21cm and height 29 cm

How to fix number of lines in RDLC report?

Problem :- If you want to fix number of lines in your report

Solution :-  declare integer table and variables

Datatype             Data Source        Name

DataItem             Integer                                 No

  Column                 K                                          No

  Column                 SHOWBOOl                     No

Step 2

Step 3

Step 4

New page per record for Grouping

Hi All ,

This is reference screen for creating new page per record of group in RDLC Reporting.

Code.BlankZero use in MS Dynamics Navision 2017 RDLC Reports

Code.BlankZero

Hi ,

Today I went to one of my report built in NAV 2016 for my client wher I faced this issue. And I got a 

new thing in base report. I am sharing it to my juniors in NAV for easy understanding.

Why should we use Code.BlankZero?

Sometimes we need to generate value in textboxes and if its of datatype integer or decimal then it will

be display Zero as output. Few client regret to see that.

Where can you find this used in Base Reports of NAV?

Report 206.

Impact of using Code.Zero

It will only display the value if the record is > 0.

For Reference see the screenshot.

How to use Getdata and Setdata in RDLC Reports of NAV 2017?

Hi All,

Today I am going to post how to use GET DATA and SET DATA function in MS Dynamics NAV 2017 RDLC Reporting.

Why we need GETDATA and SETDATA?

We need this functionality in RDLC Reporting so that we will be able to modify the header part with Body part in MS Dynamics NAV.

How RDLC Reports Works?

In RDLC we have 3 Sections on which we have to work with our defied datasets.

Header :- Runs only ones at the start of the reporting.

Body :- Stores all the data and run multiple times untill dataset has records to show.

Footer :- Runs only ones at the last page on reporting.

Examples of Getdata Setdat Reports in MS Dynamics Nav :-

Report 206 and many more.

Steps to follow how to use Getdata and Setdata :-

1. To implement Getdata Setdata in any report you need to copy all code from Report 206 present in RDLC report properties. For Reference have a look.

2. Now you need to find out a textbox which has properties of invisible and copy all code present on its visiblity condition and copy paste it in excel as its my preference to understand better ahead.

3. Now in Excel File we have number of rows and this row number is used in textboxes of Header you can have a look in expression of textboxes present in Header of Report section.

4. To add a Field in existing report you need to add a expression in the last line of this excel and it will be same as the expressions already present and get the number where it is coming. Then you need to insert the textbox and write in expression of Textbox.

Code.GETDATA(No. on Excel Line , 1)

I think it will be enough to explain in theory i will post a video for this topic if my readers want the video to explain.  I hope it will be better for all my NAV developers to design reports in more specific way.

Please comment and subscribe for more posts like this.

FORMAT Date in NAV 2017

How to Format Date in MS Dynamics NAV 2017 using C/AL Code?

We generally use Format for two reasons in MS Dynamics NAV :-

1. To convert Integer Type Data in String Format.

FORMAT(Integer Type DATA);

2. To Format Date Type Variable in Navision to convert it into desired date Form.

FORMAT(TODAY,0,4)

Today I got a requirement from my client to change the Indian Date format in DDMMYYYY only

for Example 02052017. So to get this done I had used these formulas and format available in Navision.

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

I hope this post will help in major development of NAVISION.

For more information kindly refer to :-

Format Date Suggestion on Dynamics User

 

The History Of Use Of Format Address Codeunit in RDLC Reports MS Dynamics NAV

Requirement :
To add company address and Sales Address and purchase Address on NAV report of RDLC.


How I Did it?
We can do it in various format but here i am showing it from Array in Navision.


Advantage:
It is the best Possible way to implement address and in a very neat and clean format.


Procedure :
Step1 - We need to define a variable of company address with its dimension 8,Formataddr Codeunit , CompanyInfo record of 79 in global variables.

Step2: Define this in data item with array Index.

Step3. Now Goto Code using F9 Shortcut.

Just use the Code:


CLEAR(CompanyAddr);
FormatAddr.Company(CompanyAddr,CompanyInfo);


Step4. Now run the RDLC Layout and put 8 Textboxes and assign them the value of Company Address.
Step5. Similarly you can put Company Picture also and your report by using Image and Then DATABASE selecting the Company info picture from dataitem which
you had taken and then selecting img/bmp.

--

Enjoy your Stuff with reporting. If you find this post useful then share it and subscribe to our blog.