Home » Developer & Programmer » Reports & Discoverer » The formula col refers a field in a Qry..pl help
icon8.gif  The formula col refers a field in a Qry..pl help [message #259978] Fri, 17 August 2007 01:29 Go to next message
suma_orkl
Messages: 16
Registered: July 2007
Junior Member
Hi all...

Im trying to refer to two columns in different queries inside a formula column. Its giving an error "the referd column is at a frequency below the group"

My requirement is like this
1) fst Query returns the sales during this week (total1)
2)second qry returns the sales last weel(total2)
3) A formula column to find the variance of sales (total1-total2/total2)%

Is it not possible in any way to handle this situation
please help me..its very urgent.......!

-Suma
Re: The formula col refers a field in a Qry..pl help [message #259995 is a reply to message #259978] Fri, 17 August 2007 02:09 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create two additional summary columns, both reset at report:
- "cs_1" will sum(total1),
- "cs_2" will sum(total2).
You can choose any other function, it doesn't have to be SUM (as it doesn't matter - this value has already been computed and now we only need to "extract" it out of its group).

Now use those two columns in a "variance" formula column.
Re: The formula col refers a field in a Qry..pl help [message #259997 is a reply to message #259995] Fri, 17 August 2007 02:14 Go to previous messageGo to next message
suma_orkl
Messages: 16
Registered: July 2007
Junior Member
thank yu little foot
I wil try this
Re: The formula col refers a field in a Qry..pl help [message #260001 is a reply to message #259978] Fri, 17 August 2007 02:20 Go to previous messageGo to next message
suma_orkl
Messages: 16
Registered: July 2007
Junior Member
I tried this but the values its giving are not correct since the sales values have to come aftr two filters..i.e
the sales hve to come like this


[U]GROUP DIVISION SALES [/U
marks --> glass ware --> 1852
--> aldo --> 1852

wen I used the summary col outside it gave result like this


[U]GROUP DIVISION SALES [/U]
marks -->glassware --> 3704
--> aldo --> 3704

but The value shud not get summed up.....

so in this case we cannot use the sumary cols outside as it wil give wrong results..
so any other way to do this????

--Suma

Re: The formula col refers a field in a Qry..pl help [message #260004 is a reply to message #260001] Fri, 17 August 2007 02:28 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, this situation is different from your explanation.

When you said "TOTAL", I read it as "OK, he used a SUM function to compute total". What do you call "total", then?

Are those "1852" values always the same? If so, use FIRST or LAST or AVG instead of SUM function. If not, well, say something more about the problem, possibly with an example. Also, enclose such things into the [code] tags to improve readability.
Re: The formula col refers a field in a Qry..pl help [message #260040 is a reply to message #259978] Fri, 17 August 2007 04:24 Go to previous messageGo to next message
suma_orkl
Messages: 16
Registered: July 2007
Junior Member
Sorry .. My explaination was wrong as yu said.
the thing is the Total sales has to come thru two filters..

there are several groups and inside groups there are many departments and we have to compute sales for wach and every dept within a group

heirarchy: GROUP1 ---> DEP1,DEP2,DEP3
GROUP2 ---> DEP2,DEP3
Group4---->DEP1,DEPP4

sales Lastweek sales Variance
GROUP1 ---> DEP1 1876
---> DEP2 200
----> DEP3 331.52

Report layout as explained above.

now ..my query is written such that it fetches the rows filtering the Group and department and it repeats itself.
Now If is use a summary col for the qry it gives the total sales belonging to GROUP1 and not GROUP1-->DEP1
I cannt use the figure to calulate variance with the last week same group same dept sales
Re: The formula col refers a field in a Qry..pl help [message #260046 is a reply to message #260040] Fri, 17 August 2007 04:40 Go to previous message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I told you the principle, the way I'd try to do that. As I don't have access to your data nor the report you've written, I'm afraid this is the best I can do. Unfortunately, I can not write a report for you based on nothing.

If you can, provide sample data (including CREATE TABLE and INSERT INTO statements, as well as desired output), attach a report and - hopefully - someone will take a look at it.
Previous Topic: report calling graph
Next Topic: Collapsing a Frame Vertically
Goto Forum:
  


Current Time: Mon Dec 05 23:53:48 CST 2016

Total time taken to generate the page: 0.06369 seconds