OraFAQ Forum: Reports & Discoverer » Query in Data model reports 10g

Home » Developer & Programmer » Reports & Discoverer » Query in Data model reports 10g

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query in Data model reports 10g [message #334876]

Fri, 18 July 2008 08:52

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 1277 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

Littlefoot
Messages: 21823
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

Report message to a moderator

Re: Query in Data model reports 10g [message #335142 is a reply to message #334921]

Mon, 21 July 2008 03:48

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.

Report message to a moderator

Re: Query in Data model reports 10g [message #335154 is a reply to message #335142]

Mon, 21 July 2008 04:38

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

aditiC

Please help me with the query.

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

Report message to a moderator

Re: Query in Data model reports 10g [message #335239 is a reply to message #335154]

Mon, 21 July 2008 09:55

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

Report message to a moderator