hi, i’m new to navision so pardon me if i misunderstood how navision process data. i’m currently working on a report based on the sales line table. -Objective of the report is to print by [item category code] per page. -Each [item category code] group should be further grouped by [shipment date], [document number],[description]. -Total on [quantity] for each [document no.] -Print [shipment date],[document no.],[description],[quantity]. -user can select [item category code] to print. i already generated the report using the following : [integer] itemcategory (hard coded to an array for selection) [date] user input date range. [sales line] generates a temporary file. [integer] iteration pointing to the temporary file. For each itemCategory that the user selected, and within the date range, the sales line table process the records filtered on the itemCategory and generate the temporary file for each documentNo and Description. The report would go through the date set and salesLine set for each itemCategory. If I include an option for the number of copies, the report would iterate through the salesLine again for each copy. What i am hoping in Navision is i could generate the result set (like a sql select) manipulate the result query and print the report based on the result query. Does anyone developed something like this? Or am i looking at this the wrong way in navision? Thanks for your time.
Hi, Based on what you said, you might want to go to the Sales Line dataitem property, and on the ReqFilterFields property, go ahead and select the fields that you want to do query on. After you saved the report and then run it, the request form of the Sales Line dataitem appears, and you can enter the criteria you want on the filter fields. I hope this will help.
thanks for replying. i did that too but that would select only a set at a time. our aim is to allow the user to select several categories or all and enter the number of copies for each in one report and query. jane
Jane, Hm … I have been doing programming in Navision for over 2 years. I have never done something like what you asked for. As far as I know, they have a sql server thing, and I don’t know how it can be used on the reports if installed. I have an idea here, but I am not very sure if it will work: since you would like to have the users to be able to select more than one set of criteria at a time, you can create several boolean fields on the option request form, something like - Criteria 1 field, Criteria 2 field, … and also a All field, etc. If the users put a check in certain boolean field or all boolean fields, then when the report runs, all these selected queries will be printed. Obviously, some codes are required in order to make this way work. I don’t know which trigger(s) you will implement these codes on, but I kind of think this might work, as I never actually tried it before. Do what I said make any sense to you?
actually that is what i did and the report works fine, it’s just that it doesn’t seem to be efficient because of the number of times it loops on the table. i’m just expecting some elegance in the reporting just like most report generator programs jane
Jane, Okay, let me make sure what you want: You are looking for multiple filters in each category, ex. Customer No.- 1|2|3|4 … etc. Is that what you meant?
yummi, let’s say i have the following record order1 category1 date1 … order1 category2 date1 … order2 category1 date1 … order3 category1 date2 … order4 category3 date1 … if user chose to print category1 and category2 then then the print out should look -----page1 category1 date1 order1 [some totals per order/category] order2 ‘’ date2 order3 ‘’ totals for category1 ------page2 category2 date1 order1 [some totals per order/category] totals for category2 ------ my data items integer (category- filter set for sales line table) …date (user can enter date range) …sales line (processing where it writes to temp file) …integer (page loop, number=1) …integer (temp file, prints depending of # of records) categories are loaded on array OnInitReport. request form contains array selection and date selection. from this, the report will loop on the [sales line] per category. also it loops for every date in the date range. it doesn’t seem efficient especially if the table is large. is there a better way? thanks again for giving some thoughts on my question.
Jane, According to my Navision experience, I really don’t think you can have the report generate the results of each category on each page; before you replied, I talked to my colleage about your situation, and we might have misunderstand your intention as we thought we wanted to do SQL query. I know what you say when comes to how efficient the report works, I don’t think so. Worse comes to worse, you might have to run the report number of times in order to get what you want. It is my pleasure to help you out. I can try to see if I can think of something else for this situation.
Hi, again Jane. I have some suggestions for you on the Sales Line dataitem, in order to make the report works the way you want. I want you to take a look at the following properties: NewPagePerGroup, NewPagePerRecord, GroupTotalFields. More likely you will have to set NewPagePerRecord to Yes, since you want each category to be printed on a separate pages. When talking about the GroupTotalFields, you have to figure out which field you want to use for grouping on the report. You may evaluate my ideas, and tell me what you think.
Hi, again Jane. I have some suggestions for you on the Sales Line dataitem, in order to make the report works the way you want. I want you to take a look at the following properties: NewPagePerGroup, NewPagePerRecord, GroupTotalFields. More likely you will have to set NewPagePerRecord to Yes, since you want each category to be printed on a separate pages. When talking about the GroupTotalFields, you have to figure out which field you want to use for grouping on the report. You may evaluate my ideas, and tell me what you think.
hi yummi, i cant do a NewPagePerGroup nor the GroupTotalField because the fields i’m working on are not part of any key and i don’t want to create a new key only for this report. i did use the printNewRecord to print on every page because i have the integer table for the category where the evaluation is Category[Number]. it would be nice if a sql type query result can be generated and manipulate the result set rather than manipulating the original table for each category. but i guess that how navision works. i’m working on another report and what i did is defined a generic table of numbers,text and dates to hold my query and work off the report from the generic table. i will revise the report in question and hope it is more efficient. i could e-mail it to you if you are interested thanks a lot for your all input. jane
Hey, Jane! I am really sorry to hear all that. I wish Navision is more opened when comes to sql query … since that is the way it works, let’s face it. Well you are very welcome about my inputs. I really would like to help you out, and of course, I like to learn as much new things when comes to creating reports in Navision. Okay, if you can tell me in details about your another report (project), or you can email me the objects, so I can check them out and work on it some. By the way, which version are you currently using?
hi yummi, we purchased ours in March 2002, (u.s. version 3.10). we have not implemented it yet, we just got it last august i think. the first couple of month, i’ve been working on some forms for data entry. i’ve been working on reports a couple of weeks now. again thanks for everything.