Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved]Use Excel Data for SQL PassThru paramter

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Solved]Use Excel Data for SQL PassThru paramter
 Login/Join
 
Member
posted
I have a request from a user that is similar to the one listed here http://forums.informationbuild...647039336#2647039336

My user would like to use an Excel file that will contain a list of IDs that would be used to filter the report. Due to the nature of the report I am using a SQL passthru at the point where the filtering by this parameter occurs, so I need the parameter in a format that will work in SQL. Ex 'ID_1','ID_2','ID_3'...

Now the Excel file will not be static and as I understand it to use the Excel file as a data source it would require the file having a constant size or any additional rows would not be read in by the adapter. (I have never used an Excel adapter so I may be completely wrong on this.) Either way the user will need to be able to update the file whenever they make any changes.

I am trying to figure out some way that will pull the values from the Excel spreadsheet and put them into a parameter in the proper format for SQL.

I cannot seem to find anything on how to accomplish this. If anyone has done something similar or knows where I could find information that would point me in the right direction I would really appreciate it.

This message has been edited. Last edited by: Nathaniel,


7.7.03/8.1.04
Windows, All Outputs
 
Posts: 26 | Registered: December 10, 2012Report This Post
Expert
posted Hide Post
Hi Nathaniel,

I cannot remember whether Excel Direct Retrieval was available in 7.7.03 (might have been 7.7.05 when it became available?). If it is then use that via "upload datafile" from the server console or in Dev Studio.

Otherwise, if I remember correctly, you will need to set the MS Excel file up as a System DSN under ODBC on the reporting server.

The MS Excel data will need to be declared as a NAMEd range that has the bounds of the data required. If the user adds additional rows of data then they will need to resize the named range - I once had some VBA code that did this because you can never rely on a user to remember to do it.

The other thing is that the range should have column names that are unchanging because once you have created the synonym the column names will be those declared within the synonym.

My memory may be playing tricks and some of that may be incorrect.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Looks like direct retrieval is available for 7.7.03 - link to documentation

Once you have the data source setup then you can table it out as normal.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Member
posted Hide Post
Perfect! That is exactly what I needed to know. I guess I was searching with the wrong terms haha.

Thank you very much for the help Tony.


7.7.03/8.1.04
Windows, All Outputs
 
Posts: 26 | Registered: December 10, 2012Report This Post
Expert
posted Hide Post
You're welcome, glad it helped.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved]Use Excel Data for SQL PassThru paramter

Copyright © 1996-2020 Information Builders