PDA

View Full Version : Order report on a calculated field?


ABC123

Alastair
02-12-2004, 02:41 AM
I have been using Alpha Five for 2-3 weeks now and I am very impressed with what I have managed to accomplish with it in such a short time however, I am now stuck with a problem on report design.

I have developed a customised version of the Alphasports database and want to create a report to display bestselling items. I have a report based on the table invoice_items which contains the fields product_id and a calculated field which consists of:

total(Invoice_Items-"Quantity,GRP-"Product_Id)

I have grouped the report on product_id.

I would like to order the report on the calculated field so that the top selling products are printed at the top of the report. But I cannot find out how to do this. The report order won't accept a calculated field as an argument. Is there a better way of approaching this using Alpha 5?

I have a lot of experience with Access and used to do this sort of thing using the Access query forms does Alpha have an equivalent of these?

Stan Mathews
02-12-2004, 03:23 AM
"The report order won't accept a calculated field as an argument." - mine does, screenshot attached.

"Access query forms" - I believe these are result tables created by some sort of operation (summarization, intersection, crosstabulation). Check the documentation for examples.

Alastair
02-12-2004, 03:29 AM
Yes, I have tried this, but when I put the calculated field name in the order box it didn't alter the order the records were displayed in?

calc-"Tot_Quantity_For_Product_Id

Is it something I am doing wrong?

Alastair
02-12-2004, 03:38 AM
I have also tried group properties and under the group order - specify order field, I tried pasting in:

calc-"Tot_Quantity_For_Product_Id

Alpha wouldn't allow this; error message reported was "cannot allow function in this context"

Stan Mathews
02-12-2004, 03:39 AM
You might try substituting the calculation (total(Invoice_Items-"Quantity,GRP-"Product_Id)) for the name of the calculated field in the order expression box.

You are probably running into a timing issue. The total doesn't exist until the report is through grouping records, thus it is not available for ordering purposes.

If you summarized to a temporary table grouping rows by product id, totaling the Invoice_Items-"Quantity, ordering by that total, the result table would have the data in the form you want to use.

Alastair
02-12-2004, 03:58 AM
Yes, I agree it points to a timing issue.

In Access I would have got round this by using a query table which would have done the calculations first. I could then base the report on the query table instead of the orignal table - I haven't yet found an Alpha equivalent of this query table, is there one?

Using a temporary table sounds a complicated way of cracking this problem. I am not sure what you have in mind? Could you supply a step by step approach of doing this?

Stan Mathews
02-12-2004, 04:32 AM
" base the report on the query table instead of the orignal table" - exactly...

Got to the control panel, operations tab, new, summarize records, pick a table, use the genie, group rows by product id, select the Invoice_Items-"Quantity field, specify sum, ......, when you get to the point where you name the result table, select the advanced button and uncheck "delete pre-existing dictionary".

The result is the Alpha equivalent of a query form.

Alastair
02-12-2004, 04:47 AM
Stan,

Many thanks for all the help you have given me with this problem. I thought I must have been missing something. This operations tab seems a very powerful feature - I must look into it further.

Thanks again,

Alastair

tarlachmoorhouse
02-12-2004, 04:48 AM
OK I follow you, is this dynamic in that if I select this as the bais for a report when I run the report it will auto update the in the table, or will I have to get script it to run before I call the report.

(if it isn't dynamic I then have a follow on query which I add afterwards, I'll post then.)


ta


John

Stan Mathews
02-12-2004, 07:57 AM
You create the operation which summarizes data to the intermediate table, save the operation, create the report, run the report, re-run the operation when you need new data, run the report.

The trick is in keeping the dictionary from being over-written each time the table is re-created so the report is not wiped out.

Alastair
02-12-2004, 11:45 PM
When I run an operation before running the report, various warning messages are displayer and then a table of results is displayed on screen. This is a bit off putting is there a way of preventing this happening?

Alastair
02-13-2004, 12:28 AM
Sorry, I found out how to do this just after I asked the question!!

For anyone else with the same problem if you enter the script Genie for run saved operation theres an options tab if you click run silently everything is done in the background

tarlachmoorhouse
02-13-2004, 05:28 AM
Ok so this then raises my query, as it is not dynamic, how can you use this approach in a multiuser enviroment, as if I have a report open on the new table, then I would need to stop anyone else from using this operation until I've finished printing viewing the report.

Ta

John

Tom Cone Jr
02-13-2004, 05:34 AM
John,

I'm joining this thread late, so pardon me if this covers old ground. Your query lists are local to the current workstation. Unlike your permanent indexes they're not saved on the server. So, I don't think your concern is a problem.

-- tom

tarlachmoorhouse
02-13-2004, 08:42 AM
Thanks Tom, I think I see what you mean I'm just about to disapear off for a weeks holidays with the laptop, A5 and all the manuals / examples of the website, the only bit missing will be access to these message boards !!


Thanks

John