Problem customising report object 113 Customer/Item Sales

fiona_twomey

Hi there,


I am having a problem customising this report for a customer and was wondering if someone could point out the error of my ways please?

I need the report to be grouped at 2 different levels :=-

Level 1 Gen. Product Posting group

Level 2 Customer

I have created a new key on the value entry table as follows :-

Gen. Product Posting group, Source Type, Source No., Item No. Posting Date

I have set the main group as Gen. Product Posting group table on the report and have set the new key as the table view for data item Value Entry. I am linking on Gen. Product Posting group from Value Entry table to Code on Gen. Product Posting group table. This works fine so far.

My problem is that I cannot group on Customer as this section on the report is reporting on a temporary table, i.e. Value Entry Buffer, to group on Item No.

Has anyone got any ideas?

I basically need the report to break and print a new header for each customer within the Gen. Product Posting grouping.


Thanks in advance.

Fiona

2tje

If I get you right you want a datastructure like:
Gen. Product Posting Group
Customer
Value Entry

Try the following:
- link the Value Entry to the Gen. Product Posting Group
- filter the Value Entry in the PreSection on Customer (with SETRANGE)
Add totals in the footers of DataItem 1 and 2.

This is no nice code, because you are going through every Customer as many times as you have Gen. Product Posting Groups, but it might work.

fiona_twomey

Thanks for the suggestion. I will try that and see how it works out

kriki

For starting, it is not a good idea to create a new key just for 1 report (especially in the Entry-tables).
It is better you read the Value entry with the filters you want and with a good key to find the records as fast as possible.
Then you save those records (or the values) in temptables. Also in temptables other then the ones you need.
You might re-use other tables for this.
Or create a completely new table to be used only as temptable. If you use a table as a temptable, it is not necessary to have your (or better:your customers) license changed.
So create a table with primary key "Gen. Product Posting group","Customer No." and add the fields you need.
Then you begin to fill the temptable up (by reading Value Entry and adding info in the correct temptable-record) and after that, you can start printing it using virtual table "Integer".

fiona_twomey

Hi Kriki,


I will implement as suggested and thank you for pointing me in the correct direction


Fiona

fiona_twomey

Does anybody know how you define a table as a temptable???

Miklos_Hollender

Just press Shift+F4 on the record variable in the Globals or Locals (wherever it is defined) and set this property.

Miklos_Hollender

... and then regard it similarly as a database table, so INSERT records an so on.

fiona_twomey

Thanks for that Miklos

Miklos_Hollender

... however, I'd disagree a little with Kriki on this, as yes, on one hand, creating a new key does slow down inserting records a little bit, and therefore it's good practice to avoid it for just one repord, but on the other hand, if one is not very experienced in Navision, handling temptables in reports is kind of hard and I think it's better to get the job done with creating a new key and later on removing it and redesigning the report with temptables only if the customer complains about the performance - which is not so very likely just for one key - it could be an easier approach.

I think if you create this key and instead of trying to customize this report, you just create a new one, on Value Entries, and just set the grouping in the Report Wizard, it can accomplish most of the task automatically.

fiona_twomey

Wow ! Thanks very much for that idea Miklos. It works perfectly and so simple at the same time. I can suspend battling with the temptable idea for a while thank god !

Miklos_Hollender

You are welcome. But please take a note that sometime later on to redesign it when you have the time and knowledge to.

P.S. I really like your name. I always admired Celtic culture and your name just sounds like something out of Leon Uris's Trinity book. Oh sorry, it's offtopic.

fiona_twomey

Why thank u for that Miklos ! It is a very Irish name alright.

I have just one more issue with this report if you have some time? I think the reason they requested to base this new report on the Customer/Item Sales report is that it groups the information on Item No.

i.e. you only see one line per Item No. per Customer. The Customer/Item Sales report used the temporary table ValueEntryBuffer to achieve this. Is there any other way to do this do you know?

Not sure if that's a clear enough description, but if not let me know?

Thanks,

Fiona

Miklos_Hollender

Well, I don't really have a running Navision here right now, but I think a key on Value Entries like G.P. Posting Group, Source Type, Source No., Item No. could do the trick. And then you would put all the fields in the GroupTotal of the Item No. I mean if you create it by the wizard, it creates several GroupTotal sections and if you press F9 on the section, it shows the code. And then the code says something like "IF CurrReport.TOTALSCAUSEDBY" and it means that if you have multi-level totalings, this built-in variable says whether the totaling that occured just right now has been caused by which field. So I think you would just put your visible fields in that section where the totals are caused by the Item No. Hope it helps.

P.S. check your private messages

fiona_twomey

Yeah thought it would be something like that alright Miklos Was just checking as I didn't want to waste time (that I don't have at this stage !) heading down the wrong track.

Haven't seen that CurrReport.TOTALSCAUSEDBY but sounds cool and exactly what I need in this situation I would imagine.

You've been a great help on this. Thanks very much !

P.S. Saw the message and will reply soon

Miklos_Hollender

You are welcome. As your time zone is only one hour earlier than mine, it means you are working at midnight, so it must be hard... Good luck!

fiona_twomey

May the force be with you and all that !! Ha ha

kriki

Miklos Hollender wrote:

... however, I'd disagree a little with Kriki on this, as yes, on one hand, creating a new key does slow down inserting records a little bit, and therefore it's good practice to avoid it for just one repord, but on the other hand, if one is not very experienced in Navision, handling temptables in reports is kind of hard and I think it's better to get the job done with creating a new key and later on removing it and redesigning the report with temptables only if the customer complains about the performance - which is not so very likely just for one key - it could be an easier approach.

I think if you create this key and instead of trying to customize this report, you just create a new one, on Value Entries, and just set the grouping in the Report Wizard, it can accomplish most of the task automatically.

True this is easier, but later the customer wants another report, and you create a new key, ... and so on.
I agree in that in the beginning working with temptables can be a little confusing, but it is best one learns it as fast as possible because in the end, it makes (the programmers') live a lot easier.
The problem is if later you have to redesign the report, you spend again time on it. And someone has to pay that time.
And remember to avoid a problem is always better then to cure it.