Workspace Filters for Reports

[m shea]

Hi,
I've created a number of reports with filters.  The filters are standard across these reports, i.e. pYear, pPeriod, pName, etc.

Now I'm attempting to place these onto a new workspace and also place either a Select Value filter or Slider Filter onto the workspace too.

However, when I place the either filter widget onto the workspace the properties page to "Select a data item to filter on:", is empty.  So, I am unable to choose which report filter I would like to apply.

Does anyone have an idea why the list may be empty?

Thanks in advance,
Matt

[sakthivelmani]

Hi Matt,

I am facing the same issue. Did you by any chance find the solution. Please let me know


Thanks,
Sakthi

[m shea]

Sakthi,

I have not found a solution to this.  I have involved IBM on this, but they do not have an estimate of completion.

When it's resolved, I will post what they pass along to me.

Thanks
Matt

[MFGF]

Hi,
I've created a number of reports with filters.  The filters are standard across these reports, i.e. pYear, pPeriod, pName, etc.

Now I'm attempting to place these onto a new workspace and also place either a Select Value filter or Slider Filter onto the workspace too.

However, when I place the either filter widget onto the workspace the properties page to "Select a data item to filter on:", is empty.  So, I am unable to choose which report filter I would like to apply.

Does anyone have an idea why the list may be empty?

Thanks in advance,
Matt

Hi,

If you add a prompt into your source report, you are effectively controlling the filtering of your queries from within your report. In this case you would need to create another report containing the same prompts (but no other content on the page) and drag this report into Cognos Workspace to get the prompts for your hard-coded parameter filters.

The alternative is to delete the prompts and filters from your source reports, then either drag the relevant sets of members into the context area (for a dimensional report) or add the data items you want to be able to filter on into the query of your chart/crosstab/whatever in each report, then create a second query called _BusinessInsight_ and add the same items to this query (note the underscores and case of the letters in the name - they are important).

Cheers!

MF.

[sakthivelmani]

Hi MF,

What if am using the custom SQL ? I tried following your steps but still the fiscal year and fiscal period dont show up in the select value filter in workspace . Please help me


Thanks,
Sakthi

[MFGF]

Hi MF,

What if am using the custom SQL ?

If you choose to do this, you are totally responsible for all aspects of query generation. You need to add the items to the SQL and also into the query this feeds into. Then you need to add the _BusinessInsight_ query and populate this with the same items to filter on.

My advice is that if you choose to build reports using manually-coded SQL you need to accept the consequences, which can be time-consuming and make ongoing maintenance a challenging task. If you invest time in building an appropriate metadata model you make your life a lot easier. I'm sure there is a lesson to be learned here :)

MF.

[sakthivelmani]

Hi MF,

I did try all the steps you mentioned. Could you please explain in a elaborate way about creating the _BusinessInsight_ query ?

What i did was created a custom sql and attached to the query1 and then used those data items in the chart. And later when i saw your post, i created one more query and renamed it as _BusinessInsight_ and pulled those data items to the chart and replaced them with the query1's data items. Is that what you really meant ?
Or am i doing something stupid ?
Please help me

Thanks,
Sakthi

[MFGF]

Hi MF,

I did try all the steps you mentioned. Could you please explain in a elaborate way about creating the _BusinessInsight_ query ?

What i did was created a custom sql and attached to the query1 and then used those data items in the chart. And later when i saw your post, i created one more query and renamed it as _BusinessInsight_ and pulled those data items to the chart and replaced them with the query1's data items. Is that what you really meant ?
Or am i doing something stupid ?
Please help me

Thanks,
Sakthi

Hi,

No - not the right approach.

If your chart is built from a query fed by custom SQL, you first need to add the "extra" items you want to filter on into your SQL. Then you need to bring these items into the query the chart uses.

Next you add a second query to the report - called _BusinessInsight_

Assuming you have a package you can drag things in from, simply drag in the "extra" items you want to be able to filter on into this query. If you don't have a package, you're going to have to code another piece of custom SQL to retrieve those items, then feed them into this query.

Once you have this complete, you should be able to see and use these extra items in Cognos Workspace when you drag in the chart from this report.

MF.

[sakthivelmani]

Hi MF,

Thank you for the response. This is exactly what i am doing. But i dont see the numberic fields (fiscal year and fiscal period ) in the select value filter. I do see theose fields when i use slider filter.


Many Thanks,
Sakthi

[sakthivelmani]

MF,

If you could please send a test email to email address removed (if possible) , i will send you the sql, sample records and the xml that i used.

Thanks,
Sakthi

[MFGF]

Hi MF,

Thank you for the response. This is exactly what i am doing. But i dont see the numberic fields (fiscal year and fiscal period ) in the select value filter. I do see theose fields when i use slider filter.


Many Thanks,
Sakthi

Hi,

Select value filters are for use with character items, not numbers or dates. To be able to use select value filters you will need your items to be cast to char or varchar data types in your SQL queries.

Cheers!

MF.

PS. Please don't post your email address publicly in the forum. See paragraph 7 in the thread below:

http://www.cognoise.com/index.php/topic,24030.0.html

[sakthivelmani]

Am Sorry, MF. And i tried casting them to char which did not work. But i added some characters to the fiscal year values and made it work.

Thanks a lot, MF

I have one other question. Is there a way to show the values in the chart vertically instead of showing them horizontally. because when i have more data the values collapse against each other. Or it will be great if you can give me other solution


Thanks again,
Sakthi

[sakthivelmani]

Hi MF,

I have one more question. How can i link two different charts in the same workspace. For example, I have a countries in the chart A and Cities in the chart B and both the charts use the same table. When i click on a specific country in Chart A , can i show only the related cities in Chart B. I tried drill through which does not work. Please help me

Thanks,
Sakthi