Reports & Discoverer :: Retrieving Time Based Transactions?
Aug 6, 2010
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 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)
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.
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.
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 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.
2) Heat Thermal Star-TSP700II (For customer B grade only)
Problem: Each printer works independtly (stand alone) in a perfect way but when i try to use both the problem begins.
Situation no 1: When printer no 1 is set as default printer.
I saved the data and has given the command to print while calling run_product and giving it parameter "DESNAME", "DESTYPE". NOw the following thing will happen
Grade A Customer printing (on canon) = PERFECT GRADE B Customer printing (on star) = worst as it gives 0.3 inches blank strip as output
Situation no 2: When printer no 2 is set as default printer and report background engine is closed to get refresh.
I saved the data and has given the command to print while calling run_product and giving it parameter "DESNAME", "DESTYPE". NOw the following thing will happen
Grade A Customer printing (on canon) = WORST as no paper is pprinted / (side note: HP P1005 prints blank page) GRADE B Customer printing (on star) = Perfect
My anaylsis: I have noticed that the only "defualt" printer works the best. Can we close background engine automatically and then change defualt printer?
Further note: In situaion no 2, if I run the report and see the preview and try to print it on canon then it gices 70% of the print out and 30% is missing as comapred to nothing is missed in ssituation no 2.
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?
MY problem is in my reports i wish to have filter number of records on my requirement , when i call reports from form then ask me that how many number of records you want..
What i want is I want the data which account is having more than 5 subaccounts within the last one month. Also need data with other date criteria like last 100 days if more than10 sub accounts. need single query.
Having more than 8lacks in my database. i wrote the query but it is taking much time and didnt give the data even after 14 hours.
My query is : select * from table a where account in (select account from table b where b.subaccount=a.subaccount and b.date>=sysdate-35 group by b.account having count(b.subaccount)>5) union select * from table a where account in (select account from table b where b.subaccount=a.subaccount and b.date>=sysdate-100 group by b.account having count(b.subaccount)>10)
how to retrieve the Subaccounts which satisfies my requirements.
I studied a document about lexical parameter in that it says "Lexical parameters are used to substitute multiple values at run time and are identified by a preceding '&'. Lexical s can consist of as little a one line where clause to an entire select statement"
Select * from emp, deptno &where.
and i know about substitution variables using & is this are same (lexical and substitution) or different.
I have a requirement while A4 printing..When printing each time user have to select file -> page setup -> size(A4)..THEN printing format become A4 Size. How to achieve this one without user have to go on setting.
(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.
Is it possible to link to Discoverer Viewer from Forms without requiring the user to log in again. If so, can I then link to a specific Workbook within Discoverer?
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.
I need to copy my discoverer report from dev to prod. How should I do this? Are the items created using calculation in the report can also be copied? These items are not present in the folder in discoverer admin.
My user would like to have a way to attach files to database records. For simplicity, we'll say that each employee in the database could have 0-many file attachments associated with them. I know how to make this part work using Oracle Forms.
So that's the easy part for me. The tough part is that they want to be able to run an Oracle Report on our application server (displays as a PDF, also downloadable as a PDF) and have links on this report to the attachments that they uploaded using the forms process above.
For the idea of storing the files on a folder somewhere, I simply created a text object on the report that had it's hyperlink property pointing to the file location. Done.. opens fine.
However, I want to be able to have the option of storing these files in the database instead, just in case we can't go with the shared folder idea. I'm not sure how to make this work. I can store a blob in the database.. but how do I link to that blob on the report for them to be able to download it? Is this even possible?
how to create a download link on the Oracle Report that let's them download a file out of the database.
I have a doubt; can we format a particular word in oracle reports at runtime..?
Ex:-
A text filed contains data Quote:We request #BANK# to finance the same Bill as per agreed interest rate and other terms and conditions agreed on the above addendum and immediately remit the proceeds to our account A/c no. #ACCOUNTNO#, held with your Bank.
We need to make #BANK#, #ACCOUNTNO# are bold at runtime, Is there any method to format characters at runtime..?
Currently some jobs created in WBT scripting need to converted into oracle,plsql.There is one job in WBT scripting, which will invoke the oracle reports inside and generate the PDF files in the destination path as follows:
a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?USERID=%LOGONINFO%+server=rep_pscm9722+destype=file+desname=D:ORACLE10G\%CCALLRptName%+desformat=PDF+PARAMFORM=no+report=PCCALL.RDF") a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?
[code]...
Now, i want to convert this into oracle,plsql? Is it possible or not?
[Message #1] how to get a unapplied receipts for ageing report? and RMA(credit and debit note)columns ?
Which tables should be refered for Ageing reports.
[Message #2] How to create an ageing report in discoverer?
Which tables should be refered for a Ageing reports.
Ageing report colums customer name, customer id,location,segment, credit note and debit note in different column,unapplied amount and applied amount in a different colum.Already i got a due remaning date and total amount I want a linked table names.
I migrated a Oracle 6i Report to Oracle Report 9i so I'm trying to execute a this report in the Run Web Layout, but this doesn't bring me any information, how can i run this reports in the Web layout?
When i run this report en the Paper Layout it's bring me data. When a run the report just bring me a blank web page.
We have a reports proces which we want to produce a report where the browser windows doest not display, or if it does, quickly closes after the report is completed.We are using Forms Version 10.1.2.0.2, Oracle Database 10g Release 10.2.0.4.0, OS is Windows XP Pro SP3
We have tried the below, but the primary application window closes, not the report server window. - http - removed due to form message:You cannot use links until you have posted more than 5 messages.
We have modified the forms trigger to produce the report as shown below, but get the error: FRM-41213: Unable to connect to the Report Server
Current theory is the connection to the database is lacking the user id and password since the cmdkey is not specified. How can we specify the cmdkey when using the run_report_object?
I am creating a report which generates the data to a PDF/CSV based on users Input.When I create the report using Report Builder and execute the report,Delimited data appears in a PDF layout.
But if I add the below code in After Parameter Form trigger,the column headers keeps repeating with the data.
function AfterPForm return boolean is begin IF :P_DESTYPE = 'CSV' THEN :MODE :='DEFAULT'; :DESFORMAT:='DELIMITED'; Elsif :P_DESTYPE = 'PDF' THEN :DESTYPE := 'CACHE'; :DESFORMAT:='PDF'; END IF; return (TRUE); end; [code]....
But the PDF output seems fine where the header is shown only once.I could see many posts regarding this in Oracle reports 6i wherein delimited_hdr=no is used in Command Line but I would like to know the work around in Oracle reports 10g.
select count(*) from invoice where inv_id in (SELECT inv_id FROM invoice_hist WHERE to_char(timestamp) between TO_char('05/12/2010 18:22:00', 'MM/DD/YYYY HH24:MM:SS') and TO_char('05/21/2010 18:22:00', 'MM/DD/YYYY HH24:MM:SS'));.
Here timestamp is of type DATE in the invlice_hist table.
