Reports & Discoverer :: How To Fetch Data Based On Given Criteria
Sep 1, 2012
I need to design a report out of the below data:
1. bprf_no will be my primary field
2. report parameters will be the bill_month & no_of_months
Based on above 2 parameters I need to scan through the data for BILL_MONTH <= '20-Jun-2012' and NO_OF_MONTHS <= 6 the other criteria being the AVG_IND in (1,2).
In brief the criteria will be to pick all BPRF_NO having AVG_ID in (1, 2) consecutively till a break (AVG_IND not in (1, 2) starting from the given BILL_MONTH and going below this period, that is BILL_MONTH <= '30-Jun-2012'.
For the below data, if my parameters are: BILL_MONTH <= '30-Jun-2012' and NO_OF_MONTHS <= 3, only the underscore added data should be picked (as they fulfill the criteria) and the report output will be like:
Here the NO_OF_TIMES is the count, that is no of times the BPRF_NO falls into the above mentioned criteria consecutively starting from the provided BILL_MONTH.
I have a report based on one table with three columns , one column will be updated upon user intervention that if approves the status flag of that column will be 3 and if he amends it will be 1 , by default it will be null.I want to display digital signature or image if the status is approved.I want to store the image/digital signature in Database.Do i need to create a table with BLOB column and store.
create table ot_rq_head ( r_date date, r_no number , r_status number );
I have a doubt that can we make a report on sum of the salary record wise and grouped by dept number?
like as shown BELOW
Dept No Dept Name 10 Accounting Employee ID Name Salary Sum Salary 7782 CLARK 2450 2450 7934 MILLER 1300 3750 7839 KING 5000 8750 Dept No Dept Name 20 Research Employee ID Name Salary Sum Salary 7369 SMITH 800 800 7788 SCOTT 3000 3800 7902 FORD 3000 6800 Dept No Dept Name 30 Sales Employee ID Name Salary Sum Salary 7844 TUNER 1500 1500 7499 ALLEN 1600 3100 7521 WARD 1250 4350 7900 JAMES 950 5300
emp table Name Null? Type ----------------------------------------- -------- ------------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
dept table
Name Null? Type ----------------------------------------- -------- -------------------------- DEPTNO NOT NULL NUMBER DNAME VARCHAR2(15) LOC VARCHAR2(15)
I am using: Desktop / Discoverer 4.1 / Windows XP.
I am attempting to add a new calculated column and have had some success with the CASE function but need to add additional criteria.
What I have that works is:
SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' THEN Total Spend Plus Commit ELSE 0 END)
What I need to add are a few additional criteria. I attempted and failed with a few variants of this:
SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' AND Capitalizable = 'Y' AND Task Owing Company = '534' OR '915' THEN Total Spend Plus Commit ELSE 0 END)
The three criteria points that I am looking to includea are:
•Expenditure Type = 'Supplier Rebates' •Capitalizable = 'Y' •Task Owing Company = '534' OR '915'
I have a complex requirement to be resolved, i have one table of quantities from where i want to show or select the data based on 2 criteria.
1) if the ps_qty is greater than 1000 then there should be two lines , like the qty should not be displayed greater than 1000, instead it should be lesser than 1000. 2) The weight should not be more than 50,000, it should be less than 50000 if its more it should be displayed as 2 lines.
The following is the test case.
CREATE TABLE OW_STAG_SHIP (PS_CODE VARCHAR2(12),PS_DESC VARCHAR2(30),PS_QTY NUMBER,PS_WT NUMBER); INSERT INTO OW_STAG_SHIP VALUES ('A','AAA',400,30000); -- this will be displayed as its because qty and wt are ok INSERT INTO OW_STAG_SHIP VALUES ('B','BBB',1100,4000); --Need to be displaed in two lines as qty is more than 1000 INSERT INTO OW_STAG_SHIP VALUES ('C','CCC',2500,6000); --Need to be displayed in three lines as qty is more INSERT INTO OW_STAG_SHIP VALUES ('D','DDD',600,60000); --Need to be displaed in two lines as wt is more. select * from ow_stag_ship
how can i print the stored images in reports 6i from database, based on the condition,that suppose if i have one field approval status whose flag is either 3 or 1 , based on this flag ,if the status is 3 then image should be displayed otherwise no. the rest of the process.
I need to filter records in a datablock based on Yes or No criteria.So i created a listitem(INCLUDE_ZERO_QTY_ITEMS) for that and i am using following code in WHEN-LIST-CHANGED trigger.
BEGIN IF :XGEC_SW_ITEM_LOAD_ITEM_DETS_V.INCLUDE_ZERO_QTY_ITEMS = 'Y' THEN GO_BLOCK ('XGEC_SW_ITEM_LOAD_ITEM_DETS_V'); SET_BLOCK_PROPERTY ('XGEC_SW_ITEM_LOAD_ITEM_DETS_V', default_where, '1=1'); [code]....
I am able to filter the records but the list item is not displaying initial value and the value that i am selecting from poplist.
In Report it is possible to set the maximum rows to fetch to be a number e.g. 1+.Is it possible to use a number that is selected from another query? When I try to do this i.e. I put in the field num_lines or :num_lines I just get invalid number
I have some transactions in my table with date and time.
i want to pass from date, to date and from time , to time as parameter.
when i pass one date and two time parameters, it works fine. but when i try to pass from date and to date (two date parameters) and two time parameters then it does not work accurately.
e.g. i want to pass 05-Aug-2010 and 06-Aug-2010 and time from 08:00:00 and 14:00:00 then it only retrieves data of both dates having only this time range. however i need to get transaction of 05-aug-2010 from 08:00:00 to 06-aug-2010 14:00:00.
i am looking to write a query that will count all NOT NULL queue values in a table that belongs to a certain group, but not sure how to write it. The following is what I have wrote, but just wanted to clarify how to implement count case based on 2 criterias.
select * count(case when queue is not null then 1 end) over(partition by group) as queue_orders from table_a;
For example if I have 10 jobs that belong to group 1, but I would like to count all group 1 jobs that have a queue value.
I have no data at the mo, just something that I am trying to start off.
when parameter is YES, column having values when Parameter is NO, column having no values
In this scenario, how can i skip the column with space when parameter is NO because the column exist in middle of columns in report and Present with space when parameter is YES.
I have one view which displays the information of all the receipts with date now i want to make ageing report based on this date. the columns will be 0-30 , 30-60,60-180 and > 180 , i want to distribute the receipts qty based on number of days.
I have the following query with analytic function but wrong results on the last column COUNT.
1)I am getting the output order by b.sequence_no column . This is a must. 2)COUNT Column :
I don't want the total count based on thor column hence there is no point in grouping by that column. The actual requirement to achieve COUNT is:
2a -If in the next row, if either the THOR and LOC combination changes to a new value, then COUNT=1 (In other words, if it is different from the following row)
2b-If the values of THOR and LOC repeats in the following row, then the count should be the total of all those same value rows until the rows become different. (In this case 2b-WHERE THE ROWS ARE SAME- also I only want to show these same rows only once. This is shown in the "MY REQUIRED OUTPUT) .
My present query: select r.name REGION , p.name PT, do.name DELOFF, ro.name ROUTE,
[code]...
My incorrect output[PART OF DATA]:Quote: REGIONPT DELOFF ROUTE THOR LOC SEQ COUNT NAASNAAS MAYNOOTHMAYNOOTHR010 DUBLINRD CEL 1 1 NAASNAAS MAYNOOTHMAYNOOTHR010 NEWTOWNRD CEL 2 1
[code]...
My required output[PART OF DATA]-:Quote: REGIONPT DELOFF ROUTE THOR LOC COUNT NAASNAAS MAYNOOTHMAYNOOTHR010 DUBLINRD CEL 1 NAASNAAS MAYNOOTHMAYNOOTHR010 NEWTOWNRD CEL 1 NAASNAAS MAYNOOTHMAYNOOTHR010 PRIMHILL CEL 1
[code]...
NOTE :Count as 1 is correctly coming.But where there is same rows and I want to take the total count on them, I am not getting.
I've a 6 column report where col5 is set to expand vertically (based upon comments) and works wonderfully. Col6 is a single character column, therefore does not expand so the report looks rather messy.
I would like col6 to expand in accordance to col5. Is it possible?
When running my query in sql developer, I have to execute the command apps.fnd_global.apps_initialize(user_id, resp_id, resp_appl_id) in order to show the result of my query. Without this, the query returns nothing. The problem is I am using this query as a custom query for my Discoverer report and my report does not show any record.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
Now my problem is we should fetch the data based on the below rules
If an OID contains 2 IOIDs for which there is a NEW and DISCO status attached, then fetch the 2 records If an OID has only 1 of these status, then ignore the same If an OID has none of the 2 status, then ignore the same.
I have one table storing all the information about employye, i have to develop three different reports based on the same table with different groupings , is there a easy way to call each format differently using single interface ,something like ref cursor.I dont want to create two or 3 different reports , instead choose the format.
db11gxe , apex 4.0 , firefox 24. I want to do automatic row fetch when the value of a select list changes ?but first i should ofcourse create a tabular form to fetch the data into it , but what i want is ,if i fetch 2 rows then the report contains only two rows , if i fetch 3 , the report contains only 3, if i fetch nothing , the report has nothing ?
If I am using the DESFORMAT=DELIMITED then I am getting unformated data and some time I am getting less data as per PDF output.
There are about 25 queries with interrelated way.
How can I get the output in excel from a RDF.
In Reports 6i Functionality was working while we were running reports because of reports were running in Previewer mode but in 10g reports output coming in PDF format.
I am running a report thru form....While i individually run my rdf file it will run successfully with data.When i tried same rdf by using form , it will run but without data..thats why i check PARAFORM ='YES' but all parameter goes perfectly...
We have a view that shows the all the data that we expect, so we created a report to show that data, the report worked, then all of a sudden the report stopped showing all the data. (we did have a server move)I checked the view and the all the data is there....
I have created a new report based on the view but it does the same thing, it doesn't bring all the data up (i get very old data), there are no conditions or anything.
other tables and views seem fine. i have recreated the view and i still have the same problem.
I have a report for this report i want to insert a data into one table. when am inserting the data then two time its inserted. if i run the report its inserted the data and i have taken the pdf file then also inserted 2 time for this.
Note: This report having only one group. not necessary below method give any other solution if destype=pdf then no need to insert otherwise insert the data.
I have made a travel booking system which comprises of 3 forms
1)Travel Booking form 2)Reservation Form 3)Cancellation Form
Under one booking number i can add multiple users in which they can have there multiple travels.
Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.
What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.
For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.
I have one report consist of two user parameters like FROMDATAE and TODATE and two queries in data model..
The 1st query is..
SELECT WONO,MCV_DATE,QTY FROM MCSHOP1 WHERE MCV_DATE BETWEEN :FROMDATE AND :TODATE;
It created two user parameters.ie FROMDATE and TODATE.
And 2nd query like this
SELECT MCVN FROM MCSHOP1 WHERE WONO=:WONO OR WONO LIKE 'RW%'||:WONO;
I dont know how to make 2nd query in data model. becoz the WONO will come from 1st query and LIKE command is there..But I tried in formula Column....but it returns more than one row...
Jow can i sort field data getting from formula column. The filed that i want to sort have source of that formula column. When i use order by clause with :abc ---(formula column) then it doesn't not work.
