Traditional web-based reporting with SAS BI tools

Filtering data multiple ways in a prompt created in IMS

Reply
Contributor
Posts: 53

Filtering data multiple ways in a prompt created in IMS

I have developed a report in WRS 3.1 that uses a map created in IMS 3.1. The report prompts users to select one or more locations for the report. We also needed to give them the option to select All Locations so I followed SAS Sample # 30924 to create an optional prompt in IMS. This works beautifully and I am happy with it.

Example of value list for the prompt:
All Locations
Daytona
Cocoa
Deland
. . . and so on.

Now the users in a particular region want to be able to pick one value from the list - Regional Locations - that filters the data on all of the individual locations that are in that region.

We still want to offer them the prompt the way it is with All Locations as one value and each individual location as the other values but we want to add Regional Locations to this list.

So now the list above will be:
All Locations
Regional Locations
Daytona
Cocoa
Deland
. . . and so on

Thus, if the user selects Regional Locations it will return the data for Daytona and Cocoa only, for example. There are actually 15 different regional locations and certain users don't like having to individually select all of these. Others do not have a problem with it.

Does anyone know of a way to add this functionality to my existing All Locations filter such that it is another OR condition?
SAS Employee
Posts: 110

Re: Filtering data multiple ways in a prompt created in IMS

UCF Angel,
Let me ask around about this today. Hopefully someone will be able to help with this one.
Contributor
Posts: 53

Re: Filtering data multiple ways in a prompt created in IMS

Thank you Jenn2. I'll look forward to any response.
SAS Employee
Posts: 110

Re: Filtering data multiple ways in a prompt created in IMS

Okay,
So I am still trying to hunt down an answer but one thing that comes to mind is that you are trying to create a prompt "Regional locations" that is a collection of locations to be used in the report? This is because you don't want to manually select a bunch of prompts because that is inefficient. If that is the case, can you set default prompts in v3.1? More importantly, can you set mulitple default prompts in v3.1 such that instead of using "regional locations," you have those locations selected automatically by default? I just want to make sure I understand your needs.
Contributor
Posts: 53

Re: Filtering data multiple ways in a prompt created in IMS

We have different sets of users so the prompt/filter needs to serve three different needs. If this cannot be done then we would have to create separate reports for each set of user with the prompts they need.

1. On the university level, a user wants to select 'All Locations' for the report.

2. On the college level, a user wants the option to select one or more locations from a list of all available locations, including 'All Locations'.

3. On the regional campus level, a user wants the option to select from either choice above but also to select 'All Regional Locations'. The 'All Regional Locations' would contain a predefined subset of all available locations.

So, the prompt/filter has to accommodate all three situations. We don't want to have default prompts and have created a blank as the selected default.

Thanks!
SAS Employee
Posts: 110

Re: Filtering data multiple ways in a prompt created in IMS

UCF Angel,
We are SO stuck on finding a solution to this problem. Your solution about creating a separate report for each user was the only solution my colleagues and I came up with for v3.1. Part of the problem is that many of us are not as familiar with v3.1 as we are for later versions. I'm still going to ask around because it brings up a need that perhaps other customers have as well. Plus, I may not be talking to the right people.


I'm sorry.
Contributor
Posts: 53

Re: Filtering data multiple ways in a prompt created in IMS

Thank you so much for trying to find a solution to this. Is this something that we will be able to accomplish with the 9.2 BI tools - version 4.2? We are in the process of migrating and hope to have it up and running in the next three months. I could hold off my users, if necessary, if I can promise them this functionality will be available in the future.

If it is available in the later version, is it easy to do or is there a paper/sample that outlines how to set multiple OR conditions in a filter/prompt on the map?
SAS Employee
Posts: 110

Re: Filtering data multiple ways in a prompt created in IMS

Hello UCF Angel,
I was speaking with Angela Hall about this. She gave this recommendation below for v4.2. She will probably chime in here soon and explain more about it but I wanted to at least get you thinking about her idea.

"I'd create a custom prompt in SAS Stored Process system and create a custom query in SAS Stored Process code.
Include the STP in the IMAP (drop all the IMAP created prompts in preference for the SAS Stored Process prompts).
Then use this."

Sorry I couldn't embellish more.

I don't think that this idea has surfaced for later versions yet. That's why I'm curious about it.
Contributor
Posts: 53

Re: Filtering data multiple ways in a prompt created in IMS

Jenn2, thanks again for trying to find a solution to this problem. Thank you Angela for your suggestion of a stored process; I will look into this possibility.
SAS Employee
Posts: 238

Re: Filtering data multiple ways in a prompt created in IMS

Included are some links that might be helpful in joining the Stored process prompt and resulting table into the Information Map.

Making Stored Processes available to Web Report Studio: http://support.sas.com/kb/15/585.html

How to Create a SAS® Stored Process and Then Associate It with a SAS® Information Map: http://support.sas.com/kb/26/175.html

How to Dynamically Select a Table for a SAS® Information Map Using a SAS® Stored Process with Prompts: http://support.sas.com/kb/33/146.html

Creating the stored process itself might be a little tricky. You'll need to translate the each custom prompt selection (all, area 1, area 2, individual loc, etc) into a valid query in the sql/data where clause.

~ Angela Hall
http://sas-bi.blogspot.com
Contributor
Posts: 53

Re: Filtering data multiple ways in a prompt created in IMS

Angela, thank you for all the included resources. I will see if I can work something out. I have experience with PROC SQL and creating stored processes but haven't tried using a stored process in an information map. The resources you've supplied seem very helpful.

Thanks everyone!
Ask a Question
Discussion stats
  • 10 replies
  • 388 views
  • 0 likes
  • 3 in conversation