A Problem with a dynamic list filter

[jks7]

Hi everybody,
i have a big problem with cognos. 
I want to create a list that can be filtered by a range Slider(years). So the user can choose if he wants to have a look on one year or more. The list should display just one index or content of the variable not one for every year.
I tried a lot but nothing worked.

A little scatch for a better understanding...

The Slider choose the year 2011 and 2012 and the list should look like this...

Produkt l Price l Revenue l
xy           2,00    1500
ab           3,00    5000

and if the User change the slider to the year 2011 the value should be changed
like...
Produkt l Price l Revenue l
xy           2,00    1000
ab           3,00    2500

I hope you could understand my problem...

Thank you for your help and sorry for my bad english...

Regards

[MFGF]

Hi,

You haven't made it clear where you're having problems with this. Are you struggling with a particular error, or are you just unsure of how to go about this in the first instance?

Assuming you have no specific errors (please post them up if you do), this seems like a very straightforward example of using a Data Deck.

The Data Deck will be driven by the Year item, and your List will be displayed on the cards within it. You will need to add the Year item to the query the list uses, then link the Data Deck to the List via a master/detail relationship based on the Year item.

Finally, you will need a Data Discrete Values Slider based on the Year item, and you will need to create a connection from this to your data deck via a new variable.

Regards,

MF.

[MFGF]

Here is a quick example I created using the GO Sales (query) sample package.  The rendered active report is here:

https://docs.google.com/open?id=0B6m-USVf1p1ncDN1TC1uY0VRY0dJdW0xS1pCejNRdw

and the XML report spec is here:

https://docs.google.com/open?id=0B6m-USVf1p1nbW1RaW9Ga0xUVmFUS2kwQXh2QXBvdw

I also created a dimensional version agains the GO Sales (analysis) sample package. This one has a "Time" entry for all years as well as the individual year selections. I achieved this by using a Query Calculation which defined a set of members from the Time Dimension - set ([All Time Member],[First Year],[Second Year],[Third Year]) - this needs to drive the Data Deck and the Data Discrete Values Slider, and needs to be in the query of the List.

The links for the MHT and the report spec for this are below:

https://docs.google.com/open?id=0B6m-USVf1p1nMXFhdXNNUzFSN3U3a2ZCcWtmN3RYQQ

https://docs.google.com/open?id=0B6m-USVf1p1nRG5aVXhkSXRUOW1EaXFldURIbk9fUQ

Regards,

MF.

[jks7]

Thanks for your fast and detailed reply but your Solution just works for one year but i wanted to choose one or more years.

Do you have a solution for this or is it generally possible??

I have change your Slider to a range slider but it will show me just one year and not two or three.
Hopefully you will understand my problem..

2012:
Produkt l Price l Revenue l
xy           2,00    1500
ab           3,00    5000

2011:
Produkt l Price l Revenue l
xy           2,00    1000
ab           3,00    2500

2011 and 2012
Produkt l Price l Revenue l
xy           2,00    2500
ab           3,00    7500

[MFGF]

My last example using a dimensional source provides this - you can see "Time" (which is all years) or you can see each year individually. Did you want something different?

MF.

[jks7]

hi,

i wanted something different.

In your solution you have three different years and the user can choose which year he wants to see but it should be possible to look at the year 2010 and 2011 without the year 2012. Or 2011 and 2012 without the year 2010.

Do you unterstand my problem?

regards

[MFGF]

I can't quite picture how your user would select this from a slider? You will have to give them individual items to select with a control like this, so can you be more specific about how you see this working?

MF.

[jks7]

ok, i will try it once more.

I want to habe a list with a project and some facts like revenue.

In the query of this list is also the year and the list should show the details of every project of the year that the user can be choose by a slider or something like that. The User should be able to choose not only one year but rather he can choose two or more years.

But every entry should be apper just one time.

Projekt a has in the year 2010 a revenue of 1000, 2011a rev. of 2000 and in the year 2012 a revenue of 2500.

When the user choose the year 2010 and 2011 the list should show a revenue of 3000.
The User can also select 2011 and 2012 an 4500 should be shown as revenue.

Summarizing the user can choose an intervall.

regards

[MFGF]

Ok. The practicalities are that a card of a deck can show one thing that has been selected per control. So in order to allow users to see a list representing data from two desired years, you will need to somehow come up with a way of representing those two years as a single, selectable item from your control. In the second example I sent, the three years rolled up into a summary member called "Time", so that could then be used as a single, selectable item from the control and show figures for all three years.  So I repeat my question... How exactly do you see this working, practically, given what you know now about the way decks and controls in active reports work? I understand your requirement, but not how you see it being practically implemented.

Regards,

MF.

[jks7]

I would say i am a beginner in cognos.
I know how the functionality of the standard tools but nothing more.

So that is why i created this thread.
I imagine that a active Report is able to handle my problem but i dont see a way of an implementaion because a deck can handle only on value.

In my mind, there must be a way to filter the query so that the list is dynamic calculated.

[MFGF]

Well, you now know how active reports work - based on selecting specific values from a control. They do not support traditional "multi-select" prompts. If you can engineer a way to present a collection of years as a single item (as I did with "Time" in the second example) you can include this as a selectable option in the report, but you need to decide whether this is a viable option in your case and how you would make it intuitive to the users of the report.


Sent from my iPad using Tapatalk HD

[MMcBride]

Write a SQL Statement to return the values you are looking for.

This will not help you wirte a better Cognos report but it will give you an idea of the challenge you are facing with the business rules you want to impliment.

When you write the SQL you will find you probably have exactly what the Data Deck is giving you (Assuming you write a seperate total)

Year1 Year2 Year3 Total

If you find a way to return
Year1 Year1+year2 Year1+Year2+year3 as 3 columns then you will have the abse logic and you will understand what you need to do within Cognos.

I personally think Active Reports are an Advanced topic in Cognos. If you can make Report Studio do what you want the next step is to convert it to an Active Report - but this is just my personal opinion.

Over the years I have supported many different Reporting Tools, when I am working with a tool I am new to, the first thing I personally do is figure out a way to write a SQL Statement to give me the results I need. If I have problems with the SQL I know I am going to have problems with the Reporting tools because it is nothing more than a SQL Generation tool with some Formating capabilities.

The more you know the tool the easier it is to figure out tricks around rough spots

The Business Rules you list above is a valid Business Rule (Not that being invalid has prevented users from making requests) - but it involves alot of heavy date manipulation.
I created something very similar to what you are describing in "Triangle Development" Reports for Actuary users.

How I resolved the issue you have here is I had to create a "Development Period" in the Fact table then mapped the Measure rollups to this development period.
For my reports they wanted to see quarterly rollups for 10 years. So from 3 to 120 Months in 3 month increments.

The main difference between my work and what you want is they wanted to see from 3 months to 120 months in Quarter intervals all at the same time - but once this "Development Period" is defined you could easily create a Data Deck to use this development period as your Slider value which would give you the results you are looking for.

I don't have an easy answer for your problem here, but I do believe the ultimate answer resides in creating a custom development periods and using that as your slider value