Solved

User-Friendly Reporting Tool

Posted on 2013-06-24
15
628 Views
Last Modified: 2013-07-03
Greetings EE friends and God bless.  This question is a spin off from a previous post that involved an HTA GUI that allows my users to search by multiple parameters.  The previous question is below:

Previous Question

What I am trying to ultimately achieve is to have the HTA, or some other user-friendly GUI that would allow my user to select from a list of Oracle Discoverer report names and then run the query.  If the selected report required any parameters, then it would ask the user to select the parameters from a drop-down list. Once selected the user would then hit a Run button to have the data populate on the HTA/GUI.  

I guess my first step is to have this HTA to allow the users to select a Manufacturer name from a drop-down list and then run a DOS/Batch report containing parameters in which the selected Manufacturer name from the HTA drop-down served as the parameter in the DOS/Batch script.  The DOS/Batch script opened a program named Oracle Discoverer Desktop version 10g.  Once it opens the program, it provides the username, password, and parameter to query the database with an existing report.  Then, upon completion of the query, the Discoverer program will export the data.  The program allows multiple formats for export such as HTML, XLS, CSV, TXT.  Currently, it is set to export to an Excel 1997/2003 workbook.  The HTA would then query that workbook itself to provide the user with the data.  

I realize that this sounds incredibly inefficient which is why I am open to suggestions.  Anything that anyone can provide to streamline this process to make it faster and to better protect the data, I am all ears.  I just wanna say that I am grateful for any help that is provided.  Thanks and God bless again!

HTML
<html> 
<head> 
<HTA:APPLICATION 
APPLICATIONNAME="Purchasing Portal"
ID="Purchasing Portal"
border="normal" 
borderStyle="normal" 
caption="yes" 
icon="I:\BI-CEW\favicon2.ico" 
maximizeButton="yes" 
minimizeButton="yes" 
showInTaskbar="no" 
windowState="normal"
innerBorder="yes"
navigable="yes"
scroll="auto"
SINGLEINSTANCE="yes"
scrollFlat="yes" />

<br>
<title>Purchasing Portal -- Presented by ADS Solutions</title>
<center>
<TABLE BORDER="1" WIDTH="1000"><TR><TD>
<center><u><H1>Inventory Stock Position</H1></u></center>
<hr>
</center>
</head>

<style>

BODY
{
   background-color: Gainsboro;
   font-family: Cambria;
   font-size: 12pt;
   margin-top: 30px;
   margin-left: 5px;
   margin-right: 5px;
   margin-bottom: 30px;
}
button
{
   background-color: Steelblue;
   font-family: Cambria;
   font-size: 12pt;
   width: 100px;
   margin-left: 0px;
}
textarea
{
   color: white;
   font-family: Cambria;
   font-size: 11pt;
}
select
{
   font-family: Cambria;
   font-size: 10pt;
   width: 300px;  
   margin-left: 0px;
}
td
{
   color: default;
   font-family: Cambria;
   font-size: 11pt;
}
</style>

<script language=VBScript>  
' Copyright 2013, Chris Wright, cwright at adsinc dot com 
 
sub Window_onload 

  LoadDropDown 
	
  Dim entry 
  set dMenus = createObject("Scripting.Dictionary") 
  for each entry in Split(sMenuItems, ",") 
    menu.innerHTML = menu.innerHTML & "&nbsp;<span id=" & entry _ 
                   & " style='padding-bottom:2px' onselectstart=cancelEvent>&nbsp;" _ 
                   & entry & "&nbsp;</span>&nbsp;&nbsp;" 
    dMenus.Add entry, Split(eval("s" & entry), ",") 
  next 
  sMenuOpen = "" 


  window.resizeTo 1150,600
    strComputer = "."
    Set objWMIService = GetObject("Winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * From Win32_DesktopMonitor")
    For Each objItem in colItems
        intHorizontal = objItem.ScreenWidth
        intVertical = objItem.ScreenHeight
    Next
    intLeft = (intHorizontal - 1150) / 2
    intTop = (intVertical - 600) / 2
    window.moveTo intLeft, intTop
end sub 
  
Sub RunScript
    Location.Reload(True)
End Sub

sub ReturnRows
	Dim cn
	Dim Rs
	Dim strData
	Dim strRow
	Dim fld
	Set rs = CreateObject("ADODB.Recordset")
	Set cn = CreateObject("ADODB.connection")
	cn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=I:\Chris Wright\Chris Wright\Daily Reports\Stock Position Reports\Daily Stocking Position.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"  'Persist Security Info=False;"
	cn.Open
	rs.Open "Select * from [VPN Position$] Where [Vendor Part Number] =""" & txtFilter.value & """", cn, 1, 3
	strData = "<table width=""100%"" border=""2"" cellpadding=""1""<caption>Results:</caption"">"
        if not rs.eof then
	  strData = strData & "<tr>"
          strRow = ""
          For each fld in rs.fields
            strRow = strRow & "<th>" & fld.name & "</th>"
          Next
          strData = strData & strRow & "</tr>"
        end if 
	do until rs.EOF
	  strRow = "<tr>"
	  For Each fld in Rs.Fields
		if fld.name = "MAX Ordered Qty" then
			strRow = strRow & "<td class=""mono"">" & fld.value & "</td>"
		else
			strRow = strRow & "<td>" & fld.value & "</td>"
		end if
	  Next
	  strData = strData & strRow & "</tr>"
	  rs.MoveNext
	Loop
	DataArea.InnerHTML = strData & "</table>"
end Sub

sub VendorName
	Dim cn
	Dim Rs
	Dim strData
	Dim strRow
	Dim fld
	Set Rs = CreateObject("ADODB.Recordset")
	Set cn = CreateObject("ADODB.connection")
	cn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=I:\Chris Wright\Chris Wright\Daily Reports\Stock Position Reports\Daily Stocking Position.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"  'Persist Security Info=False;"
	cn.Open
	Rs.Open "Select * from [VPN Position$] Where [Vendor Name] =""" & Optionchooser.value & """", cn, 1, 3
	strData = "<table width=""100%"" border=""2"" cellpadding=""1""<caption>Results:</caption"">"
        if not rs.eof then
	  strData = strData & "<tr>"
          strRow = ""
          For each fld in rs.fields
            strRow = strRow & "<th>" & fld.name & "</th>"
          Next
          strData = strData & strRow & "</tr>"
        end if 
	do until Rs.EOF
	  strRow = "<tr>"
	  For Each fld in Rs.Fields
		if fld.name = "MAX Ordered Qty" then
			strRow = strRow & "<td class=""mono"">" & fld.value & "</td>"
		else
			strRow = strRow & "<td>" & fld.value & "</td>"
		end if
	  Next
	  strData = strData & strRow & "</tr>"
	  Rs.MoveNext
	Loop
	DataArea.InnerHTML = strData & "</table>"
end sub

Sub LoadDropDown
Dim Rs, cn, objOption 
  ClearListbox
	Set Rs = CreateObject("ADODB.Recordset")
	Set cn = CreateObject("ADODB.connection")
	cn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=I:\Chris Wright\Chris Wright\Daily Reports\Stock Position Reports\Daily Stocking Position.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
	cn.Open
	Rs.Open "Select Distinct [Vendor Name] from [VPN Position$]", cn, 1, 3

    Do Until rs.EOF
	Set objOption = Document.createElement("OPTION")
	objOption.Text = rs.fields("Vendor Name")
	objOption.Value = rs.fields("Vendor Name")
	OptionChooser.Add(objOption)
	rs.MoveNext
    Loop 

End Sub

Sub ClearListbox
    For Each objOption in OptionChooser.Options
       objOption.RemoveNode
    Next
End Sub


</script> 

<!--Page layout follows--> 
<body 
<span id=dropmenu style="font:normal 10pt Cambria"></span>

<fieldset>
<b><legend>Search Criteria:</legend></b><p>
<b>Part Number: <input type="text" name="txtFilter" size="40"><br><br></b>
<b>Vendor Name: <select size="1" name="OptionChooser" onChange=""></select></b>

<br><br>
<input id=runbutton  class="button" type="button" value="Search" name="run_button"  onClick="VendorName" title="Click here to search specific values.">
<input id=runbutton  class="button" type="button" value="Refresh" name="run_button"  onClick="RunScript" title="Click here to refresh page.">
<br>
</fieldset>
<hr>
<span id=DataArea></span>
</hr>
</body> 
</html>

Open in new window


DOS BATCH
if not "%minimized%"=="" goto :minimized
   set minimized=true
   start /min cmd /C "%~dpnx0"
   goto :EOF
   :minimized
   rem runs the script in a minimized window


C:\oracle\discoverer\bin\dis51usr.exe /connect "crwright:ADS Parts Manager/PASSWORD@DB" /opendb "ADS Stocking Position By Vendor" /parameter "Supplier_Name" echo %1 /sheet ALL /export XLS "\\ads-fs1\ads\Chris Wright\Chris Wright\Daily Reports\Daily Stocking Position.xls" /batch


START "" "I:\Chris Wright\Chris Wright\Daily Projects\Miscellaneous\Daily Macros\Daily Position Macro-Working File.xlsm" /b /wait

tskill excel /a

Open in new window

0
Comment
Question by:Christopher Wright
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39273834
Recommendations for Oracle BI Discoverer customers
Existing Discoverer customers can continue to use Discoverer with the assurance of Oracle¿s Lifetime Support Policy, as well as with the continued planned enhancements and integration with future releases of Oracle Fusion Middleware and Oracle BI EE.

Existing customers are strongly encouraged to upgrade to Oracle BI Discoverer 10gR2 10.1.2.3 and to migrate to web deployment to take advantage of the many new exciting and powerful enhancements in this version and to get the best support benefits available.
[emphasis added] http://www.oracle.com/technetwork/developer-tools/discoverer/overview/discoverer-sod-jan2009-132849.pdf

My observation is - why persist with an 'installed client' (discoverer & hta on workstation)

Not sure what version of discoverer you have nor your infrastructure/budget etc. - but choosing a report can be done via server hosted (dynamic) html so I  would be heading in that direction.
0
 

Author Comment

by:Christopher Wright
ID: 39274498
Thank you so much for the suggestion Portlet Paul.  My organization is already using the web version of Discoverer; however, the users that will be accessing this are not the most tech savvy.  So, instead of sending them to some sort of Oracle orientation or even doing training internally, we decided it best to simply setup a basic GUI for them to use.  The reports will be limited to a handful and they will have no ability to modify or alter any conditions or filters, only select from a list of values for parameters.  We are currently using Discoverer for desktop and OracleBI Discoverer Viewer Version 10.1.2.48.18. Thank you so much again for helping.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39274539
ok, thanks for the added background - don't think I can offer much more.
0
 

Author Comment

by:Christopher Wright
ID: 39274543
I appreciate your support though and please accept my apology for the lack of background initially.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39274564
no apology needed I assure you - hope you get some useful suggestions. Cheers, Paul
0
 

Author Comment

by:Christopher Wright
ID: 39274593
Cheers, thanks again mate
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39274610
If you already have Discoverer, what would be the problem with creating "canned" reports that would only require the user to supply some parameters?

It has been some 7 years since I last used Discoverer, but as far as I can remember, you can select parameter values from drop-down lists, etc...; and also have the ability to choose the output format.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Christopher Wright
ID: 39275410
Greetings Mike!  Thank you so much for the suggestion.  We have made the attempt to created Canned reports in a Data Analyzer in the past but it proved to make it worse than better.  Report times were running significantly longer than the associated Discoverer Reports. I guess you can say that I am essentially trying to do the same thing.  I want to have "homemade" canned report for a selected list of existing reports.  Is this a possibility?  Thanks again!
0
 

Author Comment

by:Christopher Wright
ID: 39279311
Should I break this up at all?  I am desperate for help so if I need to modify or remove anything from this question, please let me know.  Thanks
0
 

Author Comment

by:Christopher Wright
ID: 39295457
I've requested that this question be deleted for the following reason:

No solution has been provided that can cover the needs of this question.  I really need help with how to ask questions in a way that can prompt more responses.  This is the third question I have had to delete because of ZERO or low responses.  :(
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39295458
No need to break this up yet but after what i propose, you might :)
0
 
LVL 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 total points
ID: 39295471
I haven't used discoverer before, nor Oracle but the principles in the design are the same to what i'm going to suggest.

The main underlying issue is creating a user friendly interface for your users.  Having extensive knowledge of BI tools, I know where you're coming from.  So I moved our users to a web based version because 99% of the time the users want the same report over and over again with minimal filtering, They just needed the report updated with the latest data.  Standardised reports work.  There's no confusion as no new elements are introduced, just the up to date data.  They are accurate as you've fully tested them.

So that said, I would go about it like this:

Set up a machine as a web server with a database for storing your report names and other options.  Install Discoverer on it.

Set up your database so that the options for the reports and linked with the correct reports.

Create a web page displaying the reports (and any related options)

When the user clicks run (and waits - animation "please wait" etc) then these parameters are passed back to your web server and it runs discoverer to generate the HTML report.  Your web server then passes the HTML back to the client for them to see.

I'm sure there'll be questions and maybe even obstacles but what do you think?
0
 

Author Closing Comment

by:Christopher Wright
ID: 39296417
I like the idea of that.  Many would argue to go with the Oracle Discoverer web version, but we have already tried that.  Users get bogged down and intimidated with buttons so I want to make an interface that is as simple as possible.  I go back to the K.I.S.S method (Keep It Simple Stupid!) Hahaha.  

I'm already ahead of you with having a machine setup with the reports and filters setup.  Now, I just need to create a website with the appropriate UI.  Thanks tagit!!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39296468
but choosing a report can be done via server hosted (dynamic) html so I  would be heading in that direction.
:) clearly that was insufficient information, but that's where I was hoping you would end-up.

Good luck with this xfitguru.  "KISSeR"?

(KISS enduser Reporting)
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39296470
Good to hear. I'll keep in touch as I'd like to know how you go with this project.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now