Home » Developer & Programmer » Reports & Discoverer » Query in Data model reports 10g
Query in Data model reports 10g [message #334876] Fri, 18 July 2008 08:52 Go to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
In report, i need to display data based on unigue combination of start date,end date and filter code.
-----------------------------------------------------------
START DATE  END DATE  FILTER   NO. of Units  Rate  Price
-----------------------------------------------------------
01-01-2008 31-01-2008 F1       100           10    1000
                               200           10    2000
                                10           20     200   

-----------------------------------------------------------
START DATE  END DATE  FILTER   NO. of Units  Rate  Price
-----------------------------------------------------------
01-01-2008 31-01-2008 F2       100           10    1000
                               200           10    2000
                                10           20     200    

Next i need to further sum up the units based on rate value.
Report output to be :
-----------------------------------------------------------
START DATE  END DATE  FILTER   NO. of Units  Rate  Price
-----------------------------------------------------------
01-01-2008 31-01-2008 F1       300           10    3000
                                10           20     200   

-----------------------------------------------------------
START DATE  END DATE  FILTER   NO. of Units  Rate  Price
-----------------------------------------------------------
01-01-2008 31-01-2008 F2       300           10    1000
                               10            20     200   

I am attaching the data model.I hv achieved the first filter condition .Please suggest something soon as i am very new to reports 10g.
[EDITED by LF: added [code] tags to improve readability]
  • Attachment: sample.rdf
    (Size: 52.00KB, Downloaded 132 times)

[Updated on: Fri, 18 July 2008 11:36] by Moderator

Report message to a moderator

Re: Query in Data model reports 10g [message #334921 is a reply to message #334876] Fri, 18 July 2008 11:44 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, read the OraFAQ Forum Guide to learn how to properly format your message. This time I did it for you (but don't expect me to do it again).

As of your question: it looks like a simple SELECT statement, use of the SUM aggregation function which requires the GROUP BY clause. Nothing special, actually. If you are capable of writing a query that will do the job, you'll easily transform it into a report.

However, how come does the result for the F2 filter / 10 rate equals 1000? Shouldn't it be 3000?
This input

START DATE  END DATE  FILTER   NO. of Units  Rate  Price
-----------------------------------------------------------
01-01-2008 31-01-2008 F2       100           10    1000
                               200           10    2000
                                10           20     200

produces this output in your example:

01-01-2008 31-01-2008 F2       300           10    1000  -> why not 3000 (similarly to F1/10)?
                               10            20     200
Re: Query in Data model reports 10g [message #335142 is a reply to message #334921] Mon, 21 July 2008 03:48 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
yes you are correct. it should indeed be 3000 for filter F2. I have created 2 break groups one on start date,end date,filter code & second on rate. Please help me with the query.
Re: Query in Data model reports 10g [message #335154 is a reply to message #335142] Mon, 21 July 2008 04:38 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
aditiC
Please help me with the query.

LF
It looks like a simple SELECT statement, use of the SUM aggregation function which requires the GROUP BY clause.

Re: Query in Data model reports 10g [message #335239 is a reply to message #335154] Mon, 21 July 2008 09:55 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
My report is based on 2 filter conditions so i have created 2 break groups. Based on the second filter condition, i need to sum up the units. But i have a single query like

select units,
rate,
price,
start date,
end date,
filter code
from account a, account line al
where a.id = al.id

my first group by is on start date,end date,filter code. Next i want to group by based on rate, and sum up the units for corresponding rates.

so i changed my query like

select sum(units),
rate,
price,
start date,
end date,
filter code
from account a, account line al
where a.id = al.id
group by rate,
start date,
end date,
filter code

but getting error as this is not a group by expression as i hv not included the price col. I don't require to group by price.

My data model has 1 query & 2 break grps. Please suggest what is needed to be done.

Thanks,
Aditi
Re: Query in Data model reports 10g [message #335296 is a reply to message #335239] Mon, 21 July 2008 14:39 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I don't require to group by price.

Right; according to your first message's example, you need to SUM the price column.
Re: Query in Data model reports 10g [message #335369 is a reply to message #335296] Tue, 22 July 2008 00:25 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Thanks a lot. Please suggest me some links/materail for reports10g.
Re: Query in Data model reports 10g [message #335411 is a reply to message #335369] Tue, 22 July 2008 02:14 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check documentation section of the Oracle Technology Network.
Previous Topic: improve the discovere standard view performance
Next Topic: How to change email subject
Goto Forum:
  


Current Time: Sat Dec 03 16:08:12 CST 2016

Total time taken to generate the page: 0.15886 seconds