How to create a "filter" against a CSV file?

[Tactical]

I'm looking for assistance in creating a "selection criteria" or filter of sorts that would limit the report results to names contained within a csv file. More specifically, using RS i'm generating a report with various selection criteria. This report is for the most part a standard report available to all users. The difference is that the client names each user might want to filter the report by are contained in a csv file and changes often.

Any ideas?

Thanks in advance.

Tac

 

[obiwaugh]

Is there any way that the names could be stored in an Excel file? That will make things really easy.....

 

[Tactical]

Sure - no problem.

 

[obiwaugh]

OK...adding tables from multiple data sources can be tricky, and perhaps not solvable in an online forum.

There is, however, a fairly straightforward solution to using values in a text file as selection criteria. We're going to create a Macro that reads a text file, and then passes the text along to a report variable.

Create a .txt file named "EMPL_LIST.txt" and put it in the C: directory.

Populate it with a couple of EMPLIDs. ( 107','108','109 ) You only put the ' around the commas. Do not use any carriage returns. Do not type the parentheses. You would only type the text from the 7 to the 9 in the example used here.

Build the report, and create a Report Variable called 'EMPLID_RV'. It will hold text values, as EMPLID is a text field.

Go to Selection Criteria and set EMPLID in list from report variable EMPLID_RV.

Then, go to Tools, Macro..., and then create a Report Macro named "GET_EMPL_LIST"

Here is the code:

Sub GET_EMPL_LIST()
Open "C:\EMPL_LIST.txt" for input as 1
Line Input #1, A$
SetRepVar "EMPLID_RV", A$
End Sub

You then need to link the Macro to "Before Report Open"


When you run the report, ReportSmith opens the text file, reads the data, sets the report variable to whatever is in the text file.

It works great!

 

[Tactical]

That may work but a typical comma-delimted text file isn't going to be formatted like that. What would you change for a CSV file in the more traditional format?

Tac