SSRS Reports with Oracle DB--some one plz help me.
Hi Guys,
I am creating SSRS report using Oracle DB. When i run this query in TOAD, it's giving output but when i run this in SSRS to create new dataset then it is giving me ORA -01008:not all variables bound(Microsoft OLE DB provider for Oracle).
This is the query iam running.
SELECT ts.transaction_status_desc, COUNT (ut.transaction_id) AS transcount
FROM dsc_transaction_status_vl ts, dsc_user_transaction ut, billing_markets bm, retail_location rl, cash_register cr
WHERE ts.transaction_status = ut.transaction_status AND ut.cr_number = cr.cr_number AND cr.rl_number = rl.rl_number AND ut.RL_NUMBER = rl.RL_NUMBER AND rl.bm_code = bm.bm_code AND (ut.creation_date BETWEEN tartDate AND :EndDate) AND bm.bm_name IN (CASE (:Market) WHEN 'OKC' THEN ('OKLAHOMA CITY' || 'TULSA') WHEN 'NFL' THEN ( 'DAYTONA'|| 'JACKSONVILL' || 'MELBOURNE'|| 'NORTH FLORIDA'|| 'ORLANDO' || 'WEST FLORIDA' ) WHEN 'LA' THEN ('PAC Los Angeles') WHEN 'CAR' THEN ('Carolinas') END )
I am passing 3 parameters: StartDate, EndDate and Market.
we have 3 markets and each marketincludes some cities. Cities are in BM_NAME column.
My Question is that
1) How to pass parameters for Oracle using SSRS
2) How to resolve this error: ORA -01008:not all variables bound(Microsoft OLE DB provider for Oracle)
3)How can i write Stored Procedure for this query & How to execute SP in SSRS.
Please reply me. Thanks in advance.
Thanks
Santosh
November 21st, 2007 7:45am
hii
For Creating SSRS using Oracle DB you need to do as follows
1) First of all create a shared data source for oracle
Give the type of the shared data source as "Oracle" from the drop down and give the proper connection.
2) Then you need to use the oracle sp or query after converting the query by oracle converter .
3)To pass the parameters for oracle give the parameter names in dataset as ":from_timeframe" that means instead of @ symbol in sql you need to give ":" for the parameters.
4) How to resolve this error: ORA -01008:not all variables bound(Microsoft OLE DB provider for Oracle)
To resolve this i think you need to give the TNS provider number. You need to paste the script for TNS number in your drive where your oracle is there and the path shouldbe like" TNSNames.ora file in 'D:\Oracle\product\10.1.0\Client_1\NETWORK\ADMIN'
hope now you can get it solved .
as am not sure about the solution and me too new to oracle.
anyway please let me know if you get the solution
Thanks
Mahasweta
Free Windows Admin Tool Kit Click here and download it now
November 21st, 2007 12:19pm
Hi Mahaswetha,
Sorry for delay responce & thanks for your reply. Now it's working well with your duidence. Actually iam getting one more problem. I am passing Date parameter as mm/dd/yyyy but in the datefield(in Oracle DB), date field is in yyyy-mm-dd hh:mis. So, how to convert date format?
Thanks
Santosh
November 23rd, 2007 1:43am
Hii Santosh
sorry for not mentioning that earlier.
yes date format in oracle is different from the date format in sql.so what you need to do is that you have to convert the date parameter.
to convert that u need to write (for example)
where dateparameter = to_date(yourdateparametername,mm/dd/yyyy)
you can check the format also.
hope now it will work fine .
Anyway let me know if it works for you
Thanks
Mahasweta
Free Windows Admin Tool Kit Click here and download it now
November 23rd, 2007 10:08am
Hi Mahaswetha,
Thanks for your advice, now its working fine.
Now iam getting one more problem, iam passing parameter @Market, which has 3 values(okc,nfl,La). In BM_Markets table there is a column bm_name, it has cities.
Oklahoma City, tulsa cities comes underOKC Market,
'daytona','jacksonville','melbourne','north florida','orlando','west florida' cities comes under NFL Market and
'atlanta','dallas','houston cellular','pac los angeles' cities comes under LA Market.
By default,All Markets should Select. IF Client select only two markets, then report should display only two markets data.
I wrote query like this,
select transaction_status_desc,count(transaction_id)
from psc
where (creation_date between @startdate and @enddate) and @Market IN
(select distinct
case
when bm_name in ('oklahoma city','tulsa') then 'okc'
when bm_name in ('daytona','jacksonville','melbourne','north florida','orlando','west florida') then 'nfl'
when bm_name in ('atlanta','dallas','houston cellular','pac los angeles') then 'others'
end
from bm_markets
)
group by transaction_status_desc
In Report Parameters Properties window, i selected Multi-Value check box, but when i preview the report, it is giving following error
"An expression of non-boolean type specified in a context where a condition is expected,near','Incorrect syntax near the keyword 'group'."
Could you please give me some advice, how to write this query for multivalue parameters.
Thanks
Santosh
November 25th, 2007 4:22am
you should define groupby in ssrs itself not in your query
Free Windows Admin Tool Kit Click here and download it now
April 22nd, 2010 8:45pm
Hi Mahasweta/Santosh,
I was getting the same problem so i followed the above mentioned steps.
But some how parameters are not getting passed thru SSRS reports.
Query is
select distinct assembly.partnumber, assembly.revision, assembly.p4colorid, assembly.projectname, userdb1a.assembly.p4partdesc2,
userdb1a.assembly.p4partdesc1, userdb1a.assembly.p4partdesc3, userdb1a.assembly.p4partdesc4, userdb1a.assembly.p4partdesc5,
userdb1a.assembly.p4partdesc6, c2custpartnumber, c2custpartrev, c2custname, SUPPLIERID, B3MFGPARTNUMBER, userdb1a.assembly.frozenindicator,
userdb1a.assembly.lifecyclestate, userdb1a.assembly.sequence, userdb1a.assembly.p2isobsolete, userdb1a.c2ptcusr.c2custpartname,
ADMINDB1.B3PRGRM.B3PROGRAMNAME, ASSEMBLY.P4ISLATESTREV, assembly.p4istabbed, assembly.p4material, assembly.b3netweight,
assembly.b3unitofweight, assembly.p4grain
from (USERDB1A.ASSEMBLY LEFT JOIN USERDB1A.C2PTCUSR ON userdb1a.assembly.obid=userdb1a.c2ptcusr.left)
LEFT JOIN USERDB1A.B3USAGE ON USERDB1A.ASSEMBLY.OBID=USERDB1A.B3USAGE.RIGHT
LEFT JOIN ADMINDB1.B3PRGRM ON USERDB1A.ASSEMBLY.PROJECTNAME=ADMINDB1.B3PRGRM.B3PROGRAMID,
ACTUATE.SSD_PROGRAMS_BASED_ON_WO_V
WHERE SSD_PROGRAMS_BASED_ON_WO_V.PROJECTNAME=ASSEMBLY.PROJECTNAME
AND assembly.p4islatestrev='+' upto this part i can run the query in SSRS and get rows
but as soon as add parameters
to the query as shown below i don't get any rows but when i run the query with parameters in SQLDEVELOPER i get rows back.
select distinct assembly.partnumber, assembly.revision, assembly.p4colorid, assembly.projectname, userdb1a.assembly.p4partdesc2,
userdb1a.assembly.p4partdesc1, userdb1a.assembly.p4partdesc3, userdb1a.assembly.p4partdesc4, userdb1a.assembly.p4partdesc5,
userdb1a.assembly.p4partdesc6, c2custpartnumber, c2custpartrev, c2custname, SUPPLIERID, B3MFGPARTNUMBER, userdb1a.assembly.frozenindicator,
userdb1a.assembly.lifecyclestate, userdb1a.assembly.sequence, userdb1a.assembly.p2isobsolete, userdb1a.c2ptcusr.c2custpartname,
ADMINDB1.B3PRGRM.B3PROGRAMNAME, ASSEMBLY.P4ISLATESTREV, assembly.p4istabbed, assembly.p4material, assembly.b3netweight,
assembly.b3unitofweight, assembly.p4grain
from (USERDB1A.ASSEMBLY LEFT JOIN USERDB1A.C2PTCUSR ON userdb1a.assembly.obid=userdb1a.c2ptcusr.left)
LEFT JOIN USERDB1A.B3USAGE ON USERDB1A.ASSEMBLY.OBID=USERDB1A.B3USAGE.RIGHT
LEFT JOIN ADMINDB1.B3PRGRM ON USERDB1A.ASSEMBLY.PROJECTNAME=ADMINDB1.B3PRGRM.B3PROGRAMID,
ACTUATE.SSD_PROGRAMS_BASED_ON_WO_V
WHERE SSD_PROGRAMS_BASED_ON_WO_V.PROJECTNAME=ASSEMBLY.PROJECTNAME
AND assembly.p4islatestrev='+'
and assembly.projectname = :ProjectName
AND c2custname = :CUSTOMER
Pls. help me if you can.
Thank You,
Pratul.
May 6th, 2010 4:11pm
Hi Man, Which Edition of SQL Server do we have to buy the SQL Server in order to use the SSRS feature to use with Oracle DB as the backend ?
or it can be installed as freeware runtime components / services only ?/* Infrastructure Support Engineer */
Free Windows Admin Tool Kit Click here and download it now
November 16th, 2010 2:31am
Hello Albert,
I dont think so to use Oracle as back end you need to have some other edition.
Any edition of SQL server 2005 or 2008 (preferably enterprise edition) you can have.
This will help you to connect Oracle.
Hope that helps.
Regards,
MahaswetaMahasweta das
January 7th, 2011 8:26am