NavisionUser: August 2015

RDLC REPORTING:

· 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")

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)

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

RTC

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

RTC:

Add report in Page:

Add Cardpart in Card

Create a page as Cardpart and in designer after the Container add Group type indent the fields.

Make CardPart sourcetable primary key same as Card sourcetable

InCard source table on insert trigger add below code

IF NOT ins.GET("Document Type","No.") THEN

BEGIN

ins.INIT;

ins."Document Type":="Document Type";

ins."No.":="No.";

ins.INSERT;

END;

· Make relationship between two pages (part)

User ProviderID

http://plataan.typepad.com/microsoftdynamics/2010/04/linking-parts-on-a-page-using-the-providerid-property.html

Search for Salesperson is disabled.

Add a new secondary key (salesperson code) in the table

3) Nav 2009 RTC 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

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

05-04-03

05-04-03

<Day,2><Month,2><Year><Closing>D

050403D

03-04-05

5. April 2003

050403

030405

<Day,2><Filler Character, >. <Month Text,3> <Year4>

5. Apr 2003

XML format

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

04/05/03

04/05/03

<Month,2><Day,2><Year><Closing>D

040503D

03/04/05

April 5, 2003

040503

030405

<Day,2><Filler Character, >. <Month Text,3> <Year4>

5. Apr 2003

use dotnet class Regex.

variables:
Name DataType Subtype Length
Regex | DotNet | System.Text.RegularExpressions.Regex.'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
Match | DotNet |System.Text.RegularExpressions.Match.'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

the code:
Match := Regex.Match('h123xxx', '\d+'); // text value = h123xxx
MESSAGE(Match.Value); // gives you 123

How to Set View Mode on Pages as Defau

...
Declare WshShell as Automation Variable for 'Windows Script Host Object Model'.WshShell
In OnOpenPage Trigger:
...
IF ISCLEAR(WshShell) THEN
CREATE(WshShell,FALSE,TRUE);
WshShell.SendKeys('^+V');//Ctrl+Shift+V

here the command for the Edit in case of
WshShell.SendKeys('^+E') //Ctrl+Shift+E

Function key

WshShell.SendKeys('{F5}{F5}'); //Ctrl+Shift+E

Replace string function in Navision

ReplaceString(String:Text[250];FindWhat:Text[250];ReplaceWith:Text[250]) NewString:Text[250]

WHILE STRPOS(String,FindWhat) > 0 DO

String := DELSTR(String,STRPOS(String,FindWhat)) + ReplaceWith + COPYSTR(String,STRPOS(String,FindWhat) + STRLEN(FindWhat));

NewString := String;

Subform setselectionfilter:

Create a variable PurchaseLine

CurrPage . SETSELECTIONFILTER ( PurchaseLine );
// you can also test purposes here MESSAGE (FORMAT (PurchaseLine.COUNT)) settle.
IF PurchaseLine . FindSet THEN BEGIN
  REPEAT
    // whatever you intends test with the selected records
  UNTIL PurchaseLine . NEXT  =  0 ;
END ;

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

FILTER RECORDS ON PAGE

OnFindRecord(Which : Text[1024]) : Boolean
exit(find(which) and ShowThisRecord);

OnNextRecord(Steps : Integer) : Integer
create a local variable
Local Variables
Name | DataType
ResultSteps | Integer
then add the following code

REPEAT
ResultSteps := NEXT(Steps);
UNTIL (ResultSteps = 0) OR (ShowThisRecord);
EXIT(ResultSteps);

ShowThisRecord() : Boolean

g_recPH2.GET("Document Type","Document No.")

IF g_recPH2."Assigned User ID" = USERID THEN

EXIT(TRUE)

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

* NAV 2009 Run Report From RUN

Dynamicsnav:////runreport?report=50000

*Make relationship between two pages (part)

User ProviderID

http://plataan.typepad.com/microsoftdynamics/2010/04/linking-parts-on-a-page-using-the-providerid-property.html

RTC:

Attach report in Page:

g_recSH.GET("Document Type","No.");
CurrPage.SETSELECTIONFILTER(g_recSH);
REPORT.RUNMODAL(50000,TRUE,FALSE,g_recSH);

Or

CLEAR(g_recSIH);
g_recSIH.SETRECFILTER;

REPORT.RUNMODAL(REPORT::"Invoice",TRUE,FALSE,g_recSIH);

Add Cardpart in Card

Create a page as Cardpart and in designer after the Container add Group type indent the fields.

Make CardPart sourcetable primary key same as Card sourcetable

InCard source table on insert trigger add below code

IF NOT ins.GET("Document Type","No.") THEN

BEGIN

ins.INIT;

ins."Document Type":="Document Type";

ins."No.":="No.";

ins.INSERT;

END;

Search for Salesperson is disabled.

Add a new secondary key (salesperson code) in the table

use dotnet class Regex.

the code:
Match := Regex.Match('h123xxx', '\d+'); // text value = h123xxx
MESSAGE(Match.Value); // gives you 123

How to Set View Mode on Pages as Default

Function key

WshShell.SendKeys('{F5}{F5}'); //Ctrl+Shift+E

Replace string function in Navision

ReplaceString(String:Text[250];FindWhat:Text[250];ReplaceWith:Text[250]) NewString:Text[250]

WHILE STRPOS(String,FindWhat) > 0 DO

String := DELSTR(String,STRPOS(String,FindWhat)) + ReplaceWith + COPYSTR(String,STRPOS(String,FindWhat) + STRLEN(FindWhat));

NewString := String;

Subform setselectionfilter:

Create a variable PurchaseLine

CurrPage . SETSELECTIONFILTER ( PurchaseLine );// you can also test purposes here MESSAGE (FORMAT (PurchaseLine.COUNT)) settle.IF PurchaseLine . FindSet THEN BEGIN
REPEAT
// whatever you intends test with the selected records UNTIL PurchaseLine . NEXT = 0 ;END ;

FILTER RECORDS ON PAGE

OnFindRecord(Which : Text[1024]) : Boolean
exit(find(which) and ShowThisRecord);

OnNextRecord(Steps : Integer) : Integer
create a local variable
Local Variables
Name | DataType
ResultSteps | Integer
then add the following code

REPEAT
ResultSteps := NEXT(Steps);
UNTIL (ResultSteps = 0) OR (ShowThisRecord);
EXIT(ResultSteps);

ShowThisRecord() : Boolean

g_recPH2.GET("Document Type","Document No.")

IF g_recPH2."Assigned User ID" = USERID THEN

EXIT(TRUE)

Pages

Friday, 28 August 2015

SSRS - Serial number for the group column