Question

SSRS Report - Invoice Number To Header

  • 22 March 2021
  • 7 replies
  • 457 views
G
  • garyb
  • Known Participant
  • 59 replies

I have been fighting an ongoing battle since we updated to E10 in January.

For the AR Invoice Form, using the standard E10 ARForm SSRS report as a base, I created a new custom form for our format.  The ARForm uses code in the report to add the invoice number to the page header.  (code.SetVarInvoiceNum function in the Invoice number group header).

For invoices that have only 1 page, all is good.  What we encounter is on some that have multiple pages per invoice, the values in the header (invoice number) jump ahead one invoice so things don’t match up.  We get page 3 of the invoice with the correct detail, but the invoice number and invoice date are pulling in the values from the next invoice.  We have to print each invoice individually to get it to print correctly, rather than all the invoices as a batch.  That can take more time if there’s a lot of invoices in the batch/group.

Here’s an example.  The 2 values highlighted in yellow are the current invoice (551924).  The values in orange are values for the next invoice (551925):  The orange highlighted values are retrieved using the Code.GetValue() function.  Page number is the Globals!PageNumber SSRS function.

 

All would be fine except that we have the page number in the report header section since it’s not possible to get a page number in the report detail section in SSRS, rather than the invoice header section in the report Matrix.  If not for the page number, I could format everything to the group header of the invoice number group and print correctly.

If I print the batch of invoices to the standard E10 AR Form, it all prints correctly.  It just seems to be our custom AR invoice form.  We didn’t have this issue in E9 since we were using Crystal Reports for everything.

Is there a better method to accomplish this?

Gary Browning Tube Forgings of America Inc / The Zidell Companies garyb@tubeforgings.com

7 replies

I

Hi  Garyb

My suggestion is make the Parent Group in the report body to Group On “invoice number.”

Show your Invoice Number in a cell in this group

Set the >Group Properties >PageBreak to “between each instance of a group”

 

Put only the page number in the header or footer select the formula "     page  "  & Globals!PageNumber & "  of  " & Globals!TotalPages  as the formula to show it 

 

 

G

Greetings Ken,

I have the report body setup exactly that way -- group on invoice number, breaking between each instance.  The problem is SSRS does not allow the Global!PageNumber to be in the body of the report.  It returns an error indicating such when I try to run the report to view data.

Gary Browning Tube Forgings of America Inc / The Zidell Companies garyb@tubeforgings.com
D

Maybe try “=ReportItems!Textbox15.Value” (textbox15 would be the name of the the textbox you want to copy the page number from) as the expression in the text box in the body of the report. I know this works the other way by copying data from the body to the header or footer I am not sure if it will work the other way.

G

Looking at the base ARForm more closely, there is a grouping “pageNumber” which uses the following to group on:

=IIF(Fields!Calc_NumRecordPerPage.Value<>0,Ceiling(Fields!InvoiceLine.Value / Fields!Calc_NumRecordPerPage.Value),1)

I have the Calc_NumRecordPerPage field in my output table, but it has a value of 0 for each invoice.  Anyone know how this value is calculated?

Gary Browning Tube Forgings of America Inc / The Zidell Companies garyb@tubeforgings.com
K
Userlevel 2

We are seeing the same issue - were you able to find a work around?

Kristine Fierce Business Systems Analyst Ag Leader Technology
M

If I want to display a field in Page Header (or footer), much like dmartinelli mentioned, I use the =ReportItems!_____.Value.  For example, I define invoice number in a Textbox down in the InvoiceNum grouping (for example this textbox could be named myInvoiceNum ) then up in the Page Header in the Textbox I put =ReportItems!myInvoiceNum.Value to reference the value from down below.

Many times I will make these very small textboxs with small font and white color font, if I don’t really want to display the invoice number down in the InvoiceNum grouping. It’s just for referencing the true value of the field. 

Marta Hoffman Programmer Tacony Corporation FENTON MO 6363493000
G

@kfierce - I basically created a new ARForm SSRS report file. 

I eliminated the page header and how it was formatted and the way it was trying to grab the values from the report data.

Instead, the form does not have a page header section.  In the SSRS tablix/matrix, I basically put all the necessary info in the group header at the top level group (invoice number), and then formatted the rows for the group header to print on each page.  I have the page breaks set on the top level group to be between each instance, and then have the ResetPageNumber set to true.  I then moved the page number to the page footer.  This made more sense to me than they way the original ARForm was working.

This seems to be working out as since I made these adjustments we’ve only had one instance of an invoice not formatting correctly recently, which I need to figure out, but it doesn’t have to do with the data in the ‘header’ section.

Hope this helps answer your question.

Gary Browning Tube Forgings of America Inc / The Zidell Companies garyb@tubeforgings.com

Reply


Terms & Conditions