Prompting to Create Dyanamic Parameter Map for BIA Reporting

[cslangston]

Hi All,

I'm new to Cognos development and this is my first post.

We're trying to development a Cognos FM 10.2 model connected to an Oracle 11g database for the purpose of creating ad-hoc reporting in CWA (formally BIA).

Ours users use an application to generate output from a "study" that is then loaded to the database. Each "study" is unique (self-contained set of data) and stored in a set of 10 tables we call "slots". There are a total of 75 slots. So there are 750 tables (10 * 75) holding all 75 studies. The user defines the study name and the load process determines the "slot" the data is loaded into. There is one metadata table containing the study name and its assigned slot. As a new study gets loaded, an old study is removed so the study names and slot assignments are dynamic.

For each of the 10 sets of tables, the table name is suffixed with the "slot" number.

Example:
a_table_01, b_table_01, c_table_01 ... j_table_01
a_table_02, b_table_02, c_table_02 ... j_table_02
a_table_03, b_table_03, c_table_03 ... j_table_03
.
.
.
a_table_75, b_table_75, c_table_75 ... j_table_75

And one metadata table that specifies which study is in which set of tables.
Example:
META_TABLE:
STUDY_NAME, (string)
SLOT_NUMBER (string)

In Framework Manager, I'm trying to figure out how to prompt the user for the list of studies contained in the META_TABLE that can be used in a parameter map to then use the slot value assigned as part of the query subject SQL that gets appended to the database; otherwise, we would have to resort to modeling 750 tables.

Example Query Subject SQL syntax:
Select
   *
From
   [DATASOURCE].a_table_#$slot{'dynamically_mapped_study_name'}#


So when the user is in CWA (formally BIA) and drags a query item over to be reported on, they are prompted to select one - and only one - study name which is then used to pull the data from the appropriate data set table.

So far, everything I've read and experimented with regarding prompts, filters, expressions, calculations and parameter sets, all deal with working on the reporting data. In my case, I need a way to use the value in the database look-up table to determine which tables to pull the data from based on user input.

The FM documents suggest it can be done but I haven't been able to piece it together, yet. I've tried using a query subject to create a dynamic parameter map but haven't figured out how to get a value list prompt to pass the user's selection as a key to the parameter map so that value can be used in the subject query's SQL table name suffix.

Note that the study name and slot assignments are not part of the actual data set and therefore can't be filtered on. The meta table is the only way to map a study to the set of tables it's loaded into and it won't be available as one of the query items in CWA (BIA).

The closest I've run across for ideas was this thread: http://www.cognoise.com/index.php?topic=6343.0.

I'm willing to accept that it's not possible but I want to make sure I've exhausted all possible solutions before raising the white flag.

I've been racking my brains on this few for a few days so any help is greatly appreciated.

[cslangston]

Ok...I got most of it figured out.

Step 1: Create a Subject Query on the lookup table META_TABLE. Add one additional column - STUDY.
Select
   'STUDY' AS STUDY,
   META_TABLE.BUSINESS_STUDY_NAME AS STUDY_NAME,
   META_TABLE.SLOT_NUMBER AS SLOT
From
   [DATASOURCE].META_TABLE as META_TABLE

Step 2: Create a prompted filter
[DBLAYER].[STUDY_LOOKUP].[STUDY_NAME]  = #prompt('Study Name:','','','','[DBLAYER].[STUDY_LOOKUP].[STUDY_NAME] ')#

Step 3: Apply filter to STUDY_LOOKUP query subject. Now the lookup will only return one slot value to be used in a parameter map.

Step 4: Create parameter map based on STUDY_LOOKUP Query subject
Key: STUDY_LOOKUP.STUDY
Value: STUDY_LOOKUP.SLOT

Step 4: Create Query Subject a_table
Select   *
from  [DATASOURCE].a_table_#$slot{'STUDY'}#

Step 5: Change STUDY_LOOKUP prompt info property to SelectValue for STUDY_NAME

This works to prompt for a study name, however, the prompt isn't a dropdown list as the document states it should be. I did get it to work once but then had to make a change to a Query Subject in FM but after republishing, it no long presents a dropdown list but instead defaults to an edit text prompt. I've tried tweaking parameters but still can't get a dropdown list. This is critical because there are 75 studies and the user can't be expected to remember the names of all 75 - especially as new ones get loaded by other members.

Any ideas? What am I overlooking?

Thanks.

[Lynn]

I would think you want to prompt them for the slot rather than the study name, but on the slot number data item indicate a display reference as the study name. That should generate a prompt with a list of study names for them to choose from, but return the slot as what will be used by the parameter map.

[cslangston]

Thanks Lynn for your suggestion. Being new to Cognos development, I'm still trying to wrap my brain around how to code it. I'm not understanding how to get the slot number from the selection to the Query subject SQL. Where does the Query subject needing the slot number get it from? All the reading and searching I've done suggest I need to use a filter and parameter map and this was the only way I could figure out how to get it to work. Would you mind providing some pseudo code for me to develop against to see if that helps resolve the drop down list issue I'm having?

Thanks.

[MPritchard55]

cslangston, did you ever resolve this?  I too have similar need...my user creates a "population selection" in Query Studio which they save to the Cognos Server.  I am trying to create a prompted query subject in which they would select (from a list) the name of their "pop sel" (we expect to create and save many that will be reused).   I have created the prompted query subject but can't get it to display as a list.  I have tried defining the 'Prompt Info' in the Properties pane for the appropriate query subject but it is not working.  Wondering if you found solution?

[cslangston]

I can't even get a prompted dropdown list to work using just one query subject and with one query item. I don't know how I got it to work that one time but nothing I try now brings up the dropdown list. We have opened a ticket with IBM for assistance.

Has anyone out there gotten a dropdown list to work from Framework Manager?

[MFGF]

I can't even get a prompted dropdown list to work using just one query subject and with one query item. I don't know how I got it to work that one time but nothing I try now brings up the dropdown list. We have opened a ticket with IBM for assistance.

Has anyone out there gotten a dropdown list to work from Framework Manager?

Hi,

When you say "nothing... brings up the dropdown list" do you mean you don't get a prompt when you test your query subject from within Framework Manager, or do you mean you don't see a prompt when you publish the data to a package and use the package from a reporting studio?

If it's the former, it probably means you have a parameter value set as the default for testing within FM. From the Test dialog, use the Options link in the bottom right corner and press the "Prompts" button. From here you can set/clear default parameter values for use in testing in FM.

Cheers!

MF.

[cslangston]

The issue is in getting the drop-down list from a reporting studio (BIA). When I choose "selectValue" as the prompt type and publish the package, I get a text edit box. If I enter a correct study name, it does retrieve data from the correct table but this is inadequate for the user as an incorrect name generates exception errors.

From within FM, I do get prompted to enter a [string] value - I'm not using a default value.

Sorry about the confusion.

[MFGF]

The issue is in getting the drop-down list from a reporting studio (BIA). When I choose "selectValue" as the prompt type and publish the package, I get a text edit box. If I enter a correct study name, it does retrieve data from the correct table but this is inadequate for the user as an incorrect name generates exception errors.

From within FM, I do get prompted to enter a [string] value - I'm not using a default value.

Sorry about the confusion.

Hmmm - intriguing! What are you setting the "selectValue" property for? Just to double-check - have you set the Prompt Type property for STUDY_NAME or for STUDY?

MF.

[cslangston]

I've tried setting it for each one of them. I've set each one, one-at-a-time, to selectValue and tested. Same result. Then set all three to selectValue. Same result. I've tried using the display reference and use reference but still get the same result. I've tried using different prompt types but it keeps displaying the edit text box.

Somehow, I got it to work *once* but for the life of me, can't figure out how I did it. I just remember setting the prompt type and it worked.

[cslangston]

OK...I finally got this to work but someone more skilled in FM will have to explain it. Here's a simple setup if anyone wants to play around with it.

DATABASE SETUP:
1) Create 10 identical tables named as follows: study_a_01, study_a_02, study_a_03...study_a_10
2) Create a meta data table META_TABLE with ten entries.
   column1: STUDY_NAME (unique string value)
        column2: SLOT_NUMBER (unique string values: 01-10)
3) Populate study tables with data but do not include any reference to the study name or slot number.
     Data should be unique for each table

FRAMEWORK MANAGER
1) Create (data source) query subject for META_TABLE.
     a) edit SQL: add 'SLOT' AS SLOT to the select clause. I chose to add it as the last column.
2) Create a (data source) query subject (call it study_a) for just the one study_a_01 table.
3) Create a (model) query subject called STUDY_LOOKUP using the columns from the META_TABLE.
    a) Create a filter, "filter_prompt": #prompt('Study', 'String', '', '', '[Study View].[META_TABLE].[STUDY_NAME]' )# = [Study View].[STUDY_LOOKUP].[STUDY_NAME]')
4) Create a parameter map, "slotMap," "based on existing Query item"
   KEY = STUDY_LOOKUP.SLOT
   VALUE = STUDY_LOOKUP.SLOT_NUMBER
5) Modify query subject study_a.
        Edit SQL "from" clause to replace the "01" with a macro: [datasource].study_a_#$slotMap{'SLOT'}# as study_a

Publish package. Incude but hide META_TABLE and STUDY_LOOKUP.

Note that I did not change the prompt info for any of the query items but left them as "server determined." Interestingly enough, CWA (BIA) was able to figure out it needed to be a dropdown menu option and presented as such as soon as I dragged an item over to be queried.

There are some other oddities that I don't really have time to dig into - like how the prompt heading doesn't use the value in the prompt macro but instead uses the META_TABLE column name. Rather than spend too much time on it, I just aliased it in the query subject SQL so that it displays something more meaningful and not a geeky column name.

Hope this helps someone out there having the same issue.

Cheers