SSRS Report in PDF - How to Include a Page Index

Epicor ERP 10
,
1

Hi All!
I have an old Access database that generates a production report with data exported from Epicor. Obviously I hate using Access alongside Epicor, so I want to get this functionality into Epicor only.

At the beginning of the production report there is a section that lists all of the part numbers available in the report, and then shows the page number that the part appears on. In Access this was handled easily with the [page] term. But as far as I can tell, the final rendered page number is not available at all in SSRS (report builder 3.0). At least, not in the body of the report. I could include the page number on the header and footer, but how can I reference that page number in the body?

The way I see it working is at the beginning of the report I insert a tablix listing every part number in the report. In the next column I list the first page number that the part number appears on. I don’t see any way to generate the page number from the BAQ, so I have to calculate it somehow from the SSRS. Right?

I have struggled with this for a while and keep coming back to it. This was so simple in Access, and seems impossible in Epicor. See my previous similar posts here:

Thanks for you time!
Nate

2

Kind of a hack, but how dynamic are the “sections” of your report?

If it was simply a table, and you knew that exactly 25 rows appeared on each page, you could calculate which page the item most likely appears on.

3

That is a plausible approach. But the report is extremely dynamic, showing only open jobs, and ops. Some pages have less than one part, and others have 20 parts. I suppose I could try to calculate where the pages might break, based on the number of lines for each part in the BAQ, and how much space a line takes up on a page. But, man… that is a lot of hackey work.

I really can’t believe that this very handy report is so difficult to accomplish.

1 Like
4

I think I might have a way. It’s not entirely automatic, it takes a bit of manual intervention.

  1. Add a DocumentMapLabel to fields that you’d want a TOC entry for.
  2. Print preview to Word
  3. When the Word doc opens, Insert a Custom TOC
    image
    image414Ă—649 23.5 KB
  4. Click the the Options button
    image
  5. Unselect the Outline Levels, and Select the Table entry fields checkboxes
    image
  6. OK on the TOC Options and TOC dialogs
  7. Select the newly inserted TOC, right click and select Update Field

You’ll now have a TOC with page #'s that match the page that the field you set the DocumentMapLable for.

Here’s a an example for a very basic report listing PartNum, Description, and IUM.

I set the DocumentMapLabel for the PartNum field:

image
image900Ă—440 6.45 KB

After exporting to Word, and inserting the TOC, I have:

Generated TOC:

image
image750Ă—507 14.6 KB

And scrolling down to that part (AMP-METER …) that is the first on page 5

image
image740Ă—259 12.3 KB

1 Like
5

Excellent! This solution is the closest I have come to something that actually works. However, Word does not like to handle the table that my report produces. Once the report finally opens in Word, the program lags terribly, making it almost unusable. I saved the Word doc to my desktop just to be safe. Even after reopening it, the lag continues. The file is only about 1.5M. I am not sure why it runs so slowly. Must be the large table structure.

Having said that, I did manage to insert a TOC, that included the part number and page. However, my report is sorted by parts that are due first. So the TOC lists all part numbers in page order, instead of alpha-part number order.

So, using Ctrl+Shift+F9, I unlinked the TOC from the document. This basically makes it a text block. Then, I converted the text to a table, breaking on paragraphs. This allowed me to resort the TOC into exactly what I needed.

The process is a bit too unwieldy to pass off to the end user. But the solution does work! Thank you for your time and dedication Calvin!

6

A ToC will be in page order. Try inserting an Index (in place of the ToC), using the same fields from the report.

That might make a table in order by reference, instead of page number.

1 Like
7

I couldn’t get the index to work. I think Word wants me to manually mark the items to be included in the index.

8

The TOC is made up of a label, a tab, and then the pagenum. You could copy the text created by the TOC, insert it as just unlinked text, convert to a table, and then sort it by the label.

Still lots of manual work. But it it is a must have …

9

I have been playing around with the table of contents and index references. I had a brilliant idea that I could use the SSRS report builder to manually insert the field code syntax. For example, In my part number field, I added this: (Chr(34) is the double quote char. UD03_Key4, is my part number.)

=Fields!UD03_Key4.Value &  "{ XE " & Chr(34) &Fields!UD03_Key4.Value & Chr(34) & " } "

The problem is that Word doesn’t identify this as a field by default. I am trying to figure out if there is a code or character that I can insert in my expression to tell Word to create a field.

This field creation happens automatically when using the document map for the TOC.

What do you think?
Thanks!
Nate

10

TOC is built by the process of Word gathering up all the “tags” that the document map created, then building a table using the pair of “tag ID” and page number it appears on.

The way Word builds index references is very different. You have to manually select them. I’m guessing that the thought is, that you wouldn’t apply a style to a specific word, just because you want to use it in an index later.

You might want to open the Word docx that SSRS makes, with a hexeditor, to see how the documentmap tags are created. Probably want to make a small word doc, create a single index reference, and then see how Word saves those (again with a hex editor)

11

Great idea. I opened the word docx in N++. But the text content is unreadable. I can’t identify the tags without somehow decoding the file first. In my sample I just created a file with three pages, three index links, and an index at the end. I have a hex editor in notepad++, but I can’t use it unless the content is in hex.

12

I just found out that a docx is really a zipped collection of xml files.
Heading back to the drawing board…

13

Which is kinda cool really! Since it’s XML, I believe there are some PowerShell scripts that lets one manipulate Docx files. Pretty sure Google will have ways to create a TOC.

Mark W.

14

Inside the zipped docx I found the document.xml file, and within that I found this bit of xml that marks “some stuff” as the first indexed value.

<w:r>
<w:t>Page 1 some stuff</w:t>
</w:r>
<w:r>
<w:fldChar w:fldCharType="begin"/>
</w:r>
<w:r>
<w:instrText xml:space="preserve"> XE "</w:instrText>
</w:r>
<w:r w:rsidRPr="00755E72">
<w:instrText>some stuff</w:instrText>
</w:r>
<w:r>
<w:instrText xml:space="preserve">" </w:instrText>
</w:r>
<w:r>
<w:fldChar w:fldCharType="end"/>
</w:r>

Let me simplify the question. Can I use SSRS (report builder 3) to add indexed values to my report before I render it? I don’t think I would be doing it with XML, since that is all generated by the rendering. I just want to tag each part number with an index field, so when I open Word, and add the index, the values will already be marked.

Thanks guys for diving deep into this craziness with me! :slight_smile:

15

Started up a thread over here to see if I can get any farther:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e6caad72-586e-4079-95a9-acf5e3df2dc7/use-ssrs-report-builder-30-to-add-word-index-fields?forum=sqlreportingservices