Problem customizing Sales by Item Summary report

[hmalib]

Hello,

I am trying to customize "Sales by Item Summary Report" so that the report has a item description column. Our item numbering is not descriptive so the report is useless for us without description.

When I create the report the Description field stays blank when I run the report and does not show anything.
This is how I created the report.

I went to create new report, selected Sales>SalesByItemSummary>
I left all the column selections just as they were selected by default and left Filters on next page default as well.

On "Additional Tables and Layout Type" screen I clicked on Add Table button and selected "Item" table.
When I click OK it takes me to screen to create "Link Expression"
Here is what I have selected as an entries
Parent > name of the report (I left what was in there by default)
Link Type > one to one (i tried other options as well)
Table > Item
One to One option > First match (tried others as well)
Link Expression on the left I selected "Text" and on the right I selected "Description"
Got error that Link Expression is to long and to change it to:
Link Expression on the left "left(Text,240)" and on the Link Expression on the right "left(Description,240)"
I select use "Report Genie" and click ok
On the next field when it asked me to select fields I selected from the Salesbyitemsummary table needed field label, qty, amount, and text.
Save the report.

I assume that the "text" field should show Item Description since it was linked to that field in Item table but the when I run report the column is blank.
I tried to also select Description from Item table in the last step but it also stays empty when I run the report.

Can anyone tell me what am I doing wrong?

Thank You,

Henryk

[Al Buchholz]

Re: Problem customizing Sales by Item Summary report

Link Expression on the left I selected "Text" and on the right I selected "Description"
Got error that Link Expression is to long and to change it to:
Link Expression on the left "left(Text,240)" and on the Link Expression on the right "left(Description,240)"

Henry

You are almost there... Just a couple of minor changes....

Instead of the field "Text" use the field "Label" -
and the link expression on the right should link to the Name field of Item

I assume that the "text" field should show Item Description since it was linked to that field in Item table but the when I run report the column is blank.

There are 2 ideas here. One is to link to the proper record in Item and then get the Description from Item to appear on the report.

Don't combine those 2 ideas together and confuse them.

The linkage job is to connect the records in each table together properly.
Then the report can display all of the data from either table.

Once the linkage is done - as described - you can then place the Description field from Item directly on the report.

Notice on the genie that the table is a choice on the left and then all of the fields are availabe in the next column...

Hope that helps.

[hmalib]

Re: Problem customizing Sales by Item Summary report

Al,

Thanks for very quick reply.
Your suggestions worked perfect. Thank you very much.

I linked the expressions just like you said and then when I was choosing the field to display I chose name and description from the item table and rest (the data columns) from the salesbyitemsummary table.

Thanks again.

Henryk

[Al Buchholz]

Re: Problem customizing Sales by Item Summary report

Henryk

Glad that it worked for you.

And I hope the process is understandable so you can apply it to the next report that is similar. (And that others who read this can make sense of it too)

The whole linkage concept is so powerful and is the backbone of a relational reporting system...