BOXI rel 1 act as a DTS?

Steve95 · Apr 4, 2007

Hi All

Iam trying to create a LOV for a parameter for a report I have created in CRXI rel 1.

My data source is a Lotus Notes view, which gets updated on a daily basis with new entries. I have a destination table in sql 2000 which reflects this. Now what I want to do is automate this, ie. either via a DTS or if possible through BOXI.

So far I have experienced setting up via a DTS, the only problem being I am still figuring out how to overwrite the data rather than incrementing the record count of duplicates.

In essence Iam asking if this possible within BOXI rather me using SQL?

Many Thanks...

groggle · Apr 4, 2007

The only method I am aware of automatically "updating" a list of values is via Business Views. These are the database metalayers created by the Business View Manager. Inside of a Business View you can create a Dynamic parameter which can be scheduled to update the list of values.

The downside of this technique is that the report must use the Business View as its datasource. It is also not known what the future holds for Business Views. They will be supported until at least 2010 but probably replaced by a new metalayer tool which will take the place of both universes and business views.

Steve95 · Apr 4, 2007

Thanks for the above groggle, but do you know if BOXI can bring in data from another source or look at a view and update an field ie. LOV....

I hope I make sense.

Many Thanks

groggle · Apr 5, 2007

To the best of my knowledge, unfortunately the answer is no, an LOV can't be updated by anything other than a schedule via a Business View.

Turkbear · Apr 5, 2007

Hi,
You can, using custom code, provide a dynamic list of values at run time for users to select from and then pass the selected values ( using the URL to pass the data tothe viewer's page) to the published report's parameters for use when the report is displayed..
We use this page:

Code:

<HTML>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=charset=UTF-8">
<BODY>
<%
'===================================================================
' AUTHOR - SG, KA
' CREATED - March 31, 2004
' Modified by Turkbear June 18, 2006 to make it a csp page
' PURPOSE - This sample ASP application demonstrates how to view 
'           on demand a report with a multiple value parameter 
'           using the Crystal Enterprise XI COM SDK.
' DESCRIPTION
'	- Define CE Logon Variables
'   - Create the Enterprise Session Manager and iStore objects
'   - Query the CMS for a report to view
'	- Create and pass parameter for the report
'	- Create the viewer object and view the report
'==================================================================

'Declare the CMS Logon Variables
Dim CMS
Dim Username
Dim Password
Dim Authtype

'Set CMS logon credentials - change these to match your particular CE environment
CMS = "@BOEXI"
Username = "ReadOnly"
Password = "sample123"
Authtype = "secEnterprise"


'Declare variables for Enterprise Session
Dim oEnterpriseSessionMgr
Dim ceSession
Dim iStore
'Get the values
Rid = Request.QueryString("ReportID")
NbrParams = Request.QueryString("NumParens")
If NbrParams > 0 then
ValStr   = Request.QueryString ("Pvals")
NameStr = Request.QueryString ("Pnames")
arrVals = Split(ValStr,";")
arrNames = Split(NameStr,",")
End If


'Load the Enterprise Session Manager

Set oEnterpriseSessionMgr = Server.CreateObject("CrystalEnterprise.SessionMgr")

'Logon to the CMS and create iStore object
Set ceSession = oEnterpriseSessionMgr.Logon(Username, Password, CMS, Authtype)
Set iStore = ceSession.Service("","InfoStore")

'Declare InfoObject Variable
Dim Reports
Dim Report
'Dim Qry
'Query the CMS for the report you wish to view
Set Reports = iStore.Query("Select * from CI_InfoObjects Where SI_ProgID = 'CrystalEnterprise.Report' and SI_ID='" & Rid & "'")
Report = Reports.Item(1).Properties("SI_ID")
'Qry = Reports.SQLQueryString
'Declare the report parameter variables
Dim reportParameters
Dim fields
Dim tmpParameter
Dim value
Dim NewSpec()
Dim PType()
Dim inx 
Dim pcnt
pcnt = 0
inx = 0

'Get the report parameter(s) using the plugin interface
Set reportParameters = Reports.Item(1).PluginInterface("Report").ReportParameters
If NbrParams > 0 then
                       ' Build an array structure to determine the parameter type
For each parameter in reportParameters
	  pcnt = pcnt + 1
	  Next
	For each parameter in reportParameters
	 ReDim PType(pcnt)
	 PType(inx) = parameter.ValueType
	 inx = inx + 1
	 Next

	'Create a new Fields Collection Object
	Set fields = CreateObject("CrystalReports.Fields")
	
	for n = 0 to (NbrParams - 1)
	' Create a temporary parameter field 
		Set tmpParameter = CreateObject("CrystalReports.ParameterField")
		' Create list of values for this parameter
		arrPvals = Split(arrVals(n),",")
		Max = UBound(arrPvals)
		ReDim NewSpec(Max)
		' Create a parameter value oblect for each value in list 
		for v = 0 to UBound(arrPvals)
		set NewSpec(v)   =  createobject("CrystalReports.ParameterFieldDiscreteValue")  
		Next
		i = 0 ' initialize counter for value list array access
		 for each val in arrPvals  ' assign the value to the parameter
		    if PType(n) = 3 then
		       NewSpec(i).value = cDate(val)
		    else
		         NewSpec(i).value = Trim(val)
		   end if
		        tmpParameter.CurrentValues.Add NewSpec(i)
		              i = i + 1     ' increment counter
		              		 Next
			 
		tmpParameter.name = arrNames(n)
	Fields.Add tmpParameter
 Next ' parameter
  
End If

'Declare the Report App Factory and Report Document Objects
Dim rptAppFactor
Dim reportDocument

'Create the Report App Factory and Report Document Objects
Set rptAppFactory = iStore.EnterpriseSession.Service("","PSReportFactory")
Set reportDocument = rptAppFactory.OpenReportSource(Report)


'Declare the viewer object variable
Dim Viewer,dply

'Create a viewer object and view the report
Set Viewer = CreateObject("CrystalReports.CrystalReportInteractiveViewer")
With Viewer
  .reportSource = reportDocument
  .HasBooleanSearchButton = False
  .HasCrystalLogo = False
  If NbrParams > 0 then
  .ParameterFields = Fields
  End If
  .HasRefreshButton = True
  .IsDisplayGroupTree = False
  .HasPrintButton = true
  .PrintMode = 0 ' disable activeX Printing - set to 1 to enable (may require plug-in if set to 1)
  .IsOwnPage = true
End With
'
' Following redirect is a workaround for a printing issue..May not be needed in XI with DHTML, 
' but does not hurt to keep it
'Error handle
on error resume next
Viewer.ProcessHTTPRequest Request, Response, Session

if err.number <> 0 then
  response.write "Failed to view report" & "</BR>"
  response.write "error number: " & err.number & "</BR>"
  response.write "error description: " & err.description
end if 

%>
</BODY>
</HTML>

We provide a UI page for the users to select the values and pass the names and values in the URL that calls this page..

To Paraphrase:"The Help you get is proportional to the Help you give.."

Turkbear · Apr 5, 2007

Hi,
One additional note:
In the above code I forgot to add the newest revison info needed for XI.
It is an .asp page ( we call it report_viewer.asp) and not .csp and it uses VBScript as its language..

To Paraphrase:"The Help you get is proportional to the Help you give.."

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

BOXI rel 1 act as a DTS?

Steve95

MIS

groggle

IS-IT--Management

Steve95

MIS

groggle

IS-IT--Management

Turkbear

Technical User

Turkbear

Technical User

Similar threads

Part and Inventory Search

Sponsor