Using list of values from Excel file to filtering VA report

[Castiel]

I have to do the bankrupt report in VA. The bankrupt report will show the debts of people who have gone bankrupt. 
The client brings lists of bankrupts every month in Excel files.
I look for the ID of bankrupts in a general debt report and filter only bankrupts from lists that a client brings.
In building such a report in VA, it is necessary for a client to be able to filter specific IDs by himself and see their debts.
Filter them all at once and not one by on.

How can I do such a filter in VA? How can I see only IDs from Excel file?
Thank you.  

[JosvanderVelden]

There are many ways to do that. What exactly do you intend to do. What version of VA are you using? Will this be a VA report for yourself? For others to consume? Have you completed the VA Basics course?

[Castiel]

Version of VA: SAS® Visual Analytics Release: 8.5.2.

This report should be used by the client only, I will update the data of the depts, but the filtering and exporting of specific data will be done by the client.

Client will download/enter/type the bankrupt IDs and get their depts from VA report.

It's not something ordinary to do in VA report, or I'm wrong.

The client log in to VA report as a reading user. He can see all the people that have the depts. but the client interesting to see only bancrupt depts. The client has list of bancrupt IDs, so he download/enter/type them in VA in some way and get only their depts.

How can I do such a filter n VA? Filter is used by the reading user. Filter with list of IDs.

[JosvanderVelden]

You can use control objects to allow viewers to filter data. A control is an object that filters or narrows the scope of the data that you are currently viewing. See: https://documentation.sas.com/doc/en/vacdc/8.5/vaobj/n11d8xohg1cc49n1d3ovjmgvdeu3.htm

[Castiel]

In controls I can filter only one by one. text input - one id at a time, drop sown list - one id at a time, slider - not an option for category variables, button bar - one id at a time, list control - selection one by one.

I need to filter 100 IDs at the same time, for example.

[JosvanderVelden]

And how would the report viewer know which 100 IDs or 25 IDs? Just a hunch or something that has a pattern or repeats itself? Or a random selection based on whatever is needed at the moment?

NOTE:

• The list control has an option to allow multiple selection. Please read the documentation carefully.

[Castiel]

The report viewer has list of needed IDs, each month it changes.

List control has multiple selection, but I need to put vi sign to choose at least one of IDs. I can't choose all 100 in one click.

[EyalGonen]

Hi @JosvanderVelden 

I am helping out @Castiel with this question 🙂

Assume the list of filtering IDs are in an external Excel file and the user has this Excel file on their PC and wants to dynamically filter the VA report based on the list of IDs (which can be long) in the Excel file. What approach would you take for this requirement?

[HunterT_SAS]

One point I am not clear on here - is the Excel file with the list of bankrupt ID getting loaded into CAS at all? Or is the idea the client has this list, looks at it, and manually goes through the report to filter by the IDs they see inside of this Excel file - so we're trying to find the most efficient way to do that?

If the Excel file is not getting loaded to CAS at all, one possibility is to do something like this:

- Get the bankrupt IDs in a single comma separated line, like ID1,ID2,ID3, etc
- The report needs to have a List Control with a list of all IDs, and a Character List parameter needs to be assigned to the List Control

- Generate a URL to the report that includes this parameter. This can be done in the Copy Link dialog for the report:

- When ready execute the URL, paste the list of IDs at the end of this URL and execute.

One major downside to this approach though is that if the list of IDs is long enough then you may exceed URL length limits (i.e. if the list pushes this past 32K characters) and we cannot get past that. 

Otherwise, if the Excel file gets loaded into CAS, could you simply create another List Control where the source is this Excel file and map this datasource to your main datasource with this ID value? The client would then just need to open the report, the List Control would already have all of the bankrupt IDs displayed there, then the client would right-click, select all, and the report would be filtered for the bankrupt IDs?

Or - is it possible to do this work outside of Visual Analytics by taking the Excel file and running a job that joins it with the main datasource to create a new datasource that's just for the bankrupt IDs, and then build a report off of that? That way the client would not really need to do anything beyond provide you with the Excel file and then open this report. 

[EyalGonen]

Hi @JosvanderVelden ,

Thank you and Renato Luppi for calling me and providing a wonderful idea how to implement this!

The idea used VA's Web Content object to embed a SAS Job Execution job into the VA report and this SAS job allows the user to upload an Excel/CSV file and the SAS code loads this uploaded data to some CAS table (in CASUSER as an example) and then the VA report joins the original data with the new uploaded CAS table effectively creating a report on the VA report.

[Renato_sas]

For your reference, here are some links to additional information that might be helpful (each one has multiple parts):

https://communities.sas.com/t5/SAS-Communities-Library/Data-Driven-Content-leveraging-third-party-vi...

https://communities.sas.com/t5/SAS-Communities-Library/Introduction-to-Integration-of-SAS-Visual-Ana...