Date field not available in widget filter

[hansi]

I have a workspace containing 1 report which has Date(x-axis),  Volume of Users by Service - When I drag in a filter widget, the only filter option i have is to filter on Service - I want also to filter on date, but it does not appear as an option ?

Why is this?

(Is it something to do with how the date is set up in the Project/Package via Framework Manager?)

- What determines the date being allowed to appear within the axis of analysis i.e. appears in the filter widget?

[MFGF]

Hi,

Assuming you are using a Select Value filter, normally only character items appear in the list. There is a way to fool it, though:

1. Add a new query to your report and call it _BusinessInsight_  (note - the underscores before and after, and the specific case of the name are very important)
2. Drag the same date item from the package into your new query.
3. Save your report.

You may find that the format of the date is not ideal when presented in the prompt. If so, you can take things a step further:

4. Add a second data item to your new query (called DateFilter or something similarly intuitive) - set the expression to be cast([Your date item], varchar(10))
5. Add an identical data item to the query of your list/crosstab with exactly the same name.
6. Save your report.

If you now use DateFilter in your Select Value filter, it should hopefully be a more intuitive format.

Cheers!

MF.

[hansi]

Excellent! It works - thank you very much!
.... My question after this is ....
Why do we have to implement a "workaround" solution to be able to filter on the date?
Surely it is a fundamental requirement of BI tool to be able to do this easily without intervention - I see lots & lots of Dashboards who filter on date/month-year/Q1, etc - Do they all have to perform this kind of "workaround" to achieve it ?

[MFGF]

As far as I can tell, the issue you are seeing here is just for specific 'datetime' datatypes. I think the assumption is that most dashboards will show summarised data - eg year or quarter or perhaps month, and these items generally appear as expected in your filter dialogs. Often datetime fields are found only in detailed target reports.

The _BusinessInsight_ query is provided by IBM to allow you to include items to filter on that are not displayed in the widgets you are including in your dashboard. Here I've used it to force the date item to be available as a filter, but I don't think that was IBM's original intent for it.

Cheers!

MF.

[sakthivelmani]

Hi MFGF,

I too have the same problem. There are several fields that don't appear in the select value filter.Some of them are numeric fileds. And these reports are created using the custom sql. Could you please elaborate on how to do the workaround in this case ?

Thanks,
Sakthi

[sakthivelmani]

Hi MFGF,


I tried your workaround steps by creating a query named _BusinessInsight_ and used the data items from that query in the chart. All the fields are showing in the workspace widghet filter except for Fiscal_year and Fiscal_period which ahs the numeric values like (1,2,3,...14). Please let me know if i am missing something

Thanks,
Sakthi

[MFGF]

You need to have the items in both the _BusinessInsight_ query and the query of the relevant widget object (ie chart/crosstab etc)

MF.

[sakthivelmani]

Hi MFGF,

Thanks for the response

I do have them in both the query and _BusinessInsight_ . But am using the data items for the chart from _BusinessInsight_.

This is what am doing

I have a chart for Query1. Then i created a new query and named _BusinessInsight_ and pull the data items from _BusinessInsight_ query and adding them to the chart.
The fields are
fiscal year (0,1...15)
 fiscal_period (0,1,..14) ,
atype
budget


I have the title, fiscal year, fiscal period on the X-axes and Budget on the Series axes

When i try to put this chart in workspace, the select value filter picks up fiscal year, fiscal period, atype(Which is not used in the chart) but its not picking the fiscal year and fiscal period. But the slider filter picks all of them. Am i missing something


Thanks,
Sakthi

[urh]

Hello MFGF,

All I want is to put column CHANGEDATE into slider, that we could slide between differenet dates and data in table automatically refresh. (please take a look at attachment screen.png)



If you explain me how to do that without problems would be very good. Because I faced that when I am trying to put slider on Cognos workspace I cant see CHANGEDATE data item on list and also not values..

I am trying to do that od DB2 database.

Thanks.