Customising built in reports

andySOLTEC

I am attempting to customise the layout of the 'Sales - Invoice GB' (No 10572) to meet the existing stationery of my customer, in order to do this I need to display the Shipping Address in the Header. As expected I receive errors if I try to put these values in the header or footer sections, therefore I have added the ShipToAddr fields into the hidden text box utilised by GetData in te header to retrieve the customer address etc.

Even though I am able to see the shipping address in the body of the document during a preview and/or print, the fields in the header remain blank. When adding the fields to the hidden text box I have followed the existing formatting and incremented the GetData numbers accordingly in the header, to no end.

The new textboxes in the header have the value '=code.GetData(51)' using 51-58 according to the line required, having counted up from line 1 to ensure the correct data is displayed.

Thanks in advance for any assistance

Andy


For completeness the content of the hidden text box is included below: -

=First(Fields!STRSUBSTNO_Text004_CopyText_.Value) + Chr(177)
+ First(Fields!STRSUBSTNO_Text005____.Value) + Chr(177)
+ First(Fields!CustAddr_1_.Value) + Chr(177)
+ First(Fields!CompanyAddr_1_.Value) + Chr(177)
+ First(Fields!CustAddr_2_.Value) + Chr(177)
+ First(Fields!CompanyAddr_2_.Value) + Chr(177)
+ First(Fields!CustAddr_3_.Value) + Chr(177)
+ First(Fields!CompanyAddr_3_.Value) + Chr(177)
+ First(Fields!CustAddr_4_.Value) + Chr(177)
+ First(Fields!CompanyAddr_4_.Value) + Chr(177)
+ First(Fields!CustAddr_5_.Value) + Chr(177)
+ First(Fields!CompanyAddr_5_.Value) + Chr(177)
+ First(Fields!CustAddr_6_.Value) + Chr(177)
+ First(Fields!CompanyAddr_6_.Value) + Chr(177)
+ First(Fields!CustAddr_7_.Value) + Chr(177)
+ First(Fields!CompanyInfo__Phone_No__Caption.Value) + Chr(177)
+ First(Fields!CompanyInfo__Phone_No__.Value) + Chr(177)
+ First(Fields!CustAddr_8_.Value) + Chr(177)
+ First(Fields!CompanyInfo__Fax_No__Caption.Value) + Chr(177)
+ First(Fields!CompanyInfo__Fax_No__.Value) + Chr(177)
+ First(Fields!CompanyInfo__VAT_Registration_No__Caption.Value) + Chr(177)
+ First(Fields!CompanyInfo__VAT_Registration_No__.Value) + Chr(177)
+ First(Fields!CompanyInfo__Bank_Name_Caption.Value) + Chr(177)
+ First(Fields!CompanyInfo__Bank_Name_.Value) + Chr(177)
+ First(Fields!CompanyInfo__Bank_Branch_No__.Value) + Chr(177)
+ First(Fields!CompanyInfo__Bank_Branch_No__Caption.Value) + Chr(177)
+ First(Fields!CompanyInfo__Bank_Account_No__Caption.Value) + Chr(177)
+ First(Fields!CompanyInfo__Bank_Account_No__.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___Bill_to_Customer_No__Caption.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___Bill_to_Customer_No__.Value) + Chr(177)
+ First(Fields!FORMAT__Sales_Invoice_Header___Document_Date__0_4_.Value) + Chr(177)
+ First(Fields!VATNoText.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___VAT_Registration_No__.Value) + Chr(177)
+ First(Fields!Invoice_No_Caption.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___No__.Value) + Chr(177)
+ First(Fields!SalesPersonText.Value) + Chr(177)
+ First(Fields!SalesPurchPerson_Name.Value) + Chr(177)
+ First(Fields!OrderNoText.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___Order_No__.Value) + Chr(177)
+ First(Fields!ReferenceText.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___Your_Reference_.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___Posting_Date_Caption.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___Posting_Date_.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___Due_Date_Caption.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___Due_Date_.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___Prices_Including_VAT_Caption.Value) + Chr(177)
+ First(Fields!FormatPricesIncludingVAT.Value) + Chr(177)
+ First(Fields!Ship_to_AddressCaption.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___Sell_to_Customer_No__Caption.Value) + Chr(177)
+ First(Fields!Sales_Invoice_Header___Sell_to_Customer_No__.Value) + Chr(177)
+ First(Fields!ShipToAddr_1_.Value) + Chr(177)
+ First(Fields!ShipToAddr_2_.Value) + Chr(177)
+ First(Fields!ShipToAddr_3_.Value) + Chr(177)
+ First(Fields!ShipToAddr_4_.Value) + Chr(177)
+ First(Fields!ShipToAddr_5_.Value) + Chr(177)
+ First(Fields!ShipToAddr_6_.Value) + Chr(177)
+ First(Fields!ShipToAddr_7_.Value) + Chr(177)
+ First(Fields!ShipToAddr_8_.Value)

ara3n

if you look at the reports in RDL Designer, you will notice that in the upper left page Header there are hidden/red text boxes.

If you look at Value You'll find the following values

=code.SetInfo(ReportItems!Header__CompanyAddress_Info.value,1)

SetInfo passes values from the global variable of the report. How do you get to those global variables?

Click on a report in blank area and you'll see Report Menu beside Tools. From Report select Report Properties.

In there you should find Code Tab. Inside you will find all the variables and GetCode function and SetInfo functions.

You will need to create variable for shiptoAddress as object and add code to both functions with a new interger 5000 for example

The idea is that MS will in future add new ones and so that your doesn't interfere with theirs.

Next Add or copy the hidden/red text boxes and change the Value to

=code.SetInfo(ReportItems!Header__CompanyAddress_Info.value,50000)

Then add your own text boxes with
Value =Code.GetData(1,50000)
Value =Code.GetData(2,50000)

ara3n

Just to add some more info.

The info above is for NA version which uses similar technique to get to print info into the page header section, in this case they use the function with two parameters, in your localization they are using with one parameter.

petevans

In that case shouldn't he create a new column in the hidden table as well? If the point is to separate additions from future MS changes to the report I mean.

I am having the same problems with the shipping address. I have added header and footer info in different ways in different reports. For some I created a new column in the hidden table, and for some I just added fields to an existing column, like andySOLTEC outlined, and they all work. (Although I agree a new column and new global variable seems like the best design).

But it doesn't work for shipping address for some reason ](*,)

A workaround is to access the data directly with:

=ReportItems!ShipToAddr_1__1.Value

in the header, but this will only show on the last page if the report has multiple pages, and that's why I am trying to move it to Get/SetData (with no luck so far).

If anyone has an idea of why the shipping address is "special", input is appreciated

petevans

Ok so, this is strange - to me at least.

I placed a copy of the shipping address in the classic client header (hidden) - shows in RTC.
I place it in the body together with the existing address in the classic client - doesn't show in RTC.

Apparently it matters where you put textboxes in the classic client? I thought it was only supposed to send a dataset to RDL.

Even stranger is that I can't replicate that behavior with the existing shipping address fields in the classic.

I dunno, it's probably because I haven't got enough experience with it yet, but I feel like I am bumping into strange things like this all the time in RDL report designing for NAV. It's pretty frustrating.

Anyway, if you want your shipping address to show in the header and also have support for multiple pages, what I outlined above works:

- Make a copy of the shippingaddress in your own variable. I put mine in OnAfterGetRecord(). Might need to CLEAR it in OnPostDataItem.
- Stuff it somewhere in the (classic) header. Fill in DataSetFieldNames.
- Get it in RTC using Get/SetData

andySOLTEC

Hi Pete

I'm glad it's not just me finding these issues all the time, I managed to solve mine by moving a table I had added into the Report Designer data model.

I'll bare your method in mind for future issues, which are inevitable!

Do you also find that when you only have 1 value expected, the role tailored designer outputs blank rows? This plays havock when trying to use First() or Last() to get a single value. In case it helps I've found that using Max() enables me to get the single value.

Regards
Andy

petevans

You mean moving a table in the RTC designer fixed your problem?

Not sure what you meant by that last bit with First/Last/Max. I still don't understand what those do tbh. I have a manual that says this about First: "Returns the first value from the specified expression". But that doesn't make much sense to me, as I see it used, for example in the table in Sales Credit Memo:

=First(Fields!Sales_Cr_Memo_Line_Description.Value)

The first value of this? It's a field - it has only one value per record.

Hard to find good (searchable) documentation on this.

andySOLTEC

Oh you're right there, it's a nightmare trying to find out anything on this thing!

Here's a scenario I've come across...

Modify an existing report that returns sales lines, add is the sales header table to get the customer information. You know there is only a single record that matches the order number, I've even monitored the query using SQL Profiler and seen the query used to confirm only 1 value is returned.

Having added the value into the classic report designer, now add it in the visua studio designer within a table. This should only output a single value right? Wrong! In my scenario it returned about 6 rows, and the value I wanted was in row 5.

If I use First() I get blank, if I use Last() I get blank! The only way to get the value was to use Max()

I wish I could find out why this is happening!


Andy

petevans

Ok I replicated the problem and it does matter where you put the textboxes in the classic client. If you want the info in the header (with Get/SetData) you can't have the classic textbox in the body.

To replicate:
- add a set text label in the classic body somewhere.
- add the get/set info in RTC layout.
- run the report: it won't show.
- move the label to the header (classic), save, compile, refresh VS, upload changes to classic and save/compile again.
- run the report again: it shows.

Maybe this is something everyone knows, I don't have a link to anything that says otherwise, but I had a very strong impression that it should not matter where you put the textboxes in classic client. Not sure what my sources are, maybe I was misinformed at a course or something.