Cannot apply filters to charts with Year, Month fields (time dimension)

[reverie]

Whenever I'm creating filters on queries which involve time, this error shows up:

XQE-PLN-0298 -  Member already exists in hierarchy '[Speaker Fee Analysis].[Speaker Fee Start Date].[Speaker Fee Start Date]', with member unique name '[Speaker Fee Analysis].[Speaker Fee Start Date].[Speaker Fee Start Date].[Year]->[2012]' having caption: '2012'. Cannot add the same member with caption: '2012.0'. This is most likely caused by a DMR model where a given member unique name cannot be associated with a unique _memberCaption. Such can happen when a _businessKey is incorrectly chosen or when _businessKey and _memberCaption are defined using macro expressions that evaluate to different values for the same member unique name.

We created the model in such way that there's a time dimension for each date used in the tables.

Has anyone experienced this before with dates?

[MFGF]

Whenever I'm creating filters on queries which involve time, this error shows up:

XQE-PLN-0298 -  Member already exists in hierarchy '[Speaker Fee Analysis].[Speaker Fee Start Date].[Speaker Fee Start Date]', with member unique name '[Speaker Fee Analysis].[Speaker Fee Start Date].[Speaker Fee Start Date].[Year]->[2012]' having caption: '2012'. Cannot add the same member with caption: '2012.0'. This is most likely caused by a DMR model where a given member unique name cannot be associated with a unique _memberCaption. Such can happen when a _businessKey is incorrectly chosen or when _businessKey and _memberCaption are defined using macro expressions that evaluate to different values for the same member unique name.

We created the model in such way that there's a time dimension for each date used in the tables.

Has anyone experienced this before with dates?

Hi,

If I'm reading this correctly, you're trying to create filters using a dimensional package? Can you elaborate on exactly what you mean by "I'm creating filters"? Do you mean you are using slicers? Filter functions in dimensional expressions? Detail filters? Something else?

Can you share they types of expressions you are using?

Cheers!

MF.

[reverie]

Hi,

If I'm reading this correctly, you're trying to create filters using a dimensional package? Can you elaborate on exactly what you mean by "I'm creating filters"? Do you mean you are using slicers? Filter functions in dimensional expressions? Detail filters? Something else?

Can you share they types of expressions you are using?

Cheers!

MF.

Yes, I'm trying to create detail filters using a dimensional package on charts. Actually, I think I've figured out the error. It seems that I forgot to create a separate member caption that's string when creating the hierarchies for the dates.  :)

Thanks!

[MFGF]

Yes, I'm trying to create detail filters using a dimensional package on charts. Actually, I think I've figured out the error. It seems that I forgot to create a separate member caption that's string when creating the hierarchies for the dates.  :)

Thanks!

Hi,

Detail filters? With a dimensional package? No no no no no no no no no!!! Don't do that!! Take a look at FAQ#4:

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

Can you tell us the details of what you need to do? We can (hopefully) suggest a much more robust and efficient way to achieve your goal.

If you use detail filters with dimensional packages, you're letting yourself in for a world of pain in future...

MF.

[reverie]

Hi,

Detail filters? With a dimensional package? No no no no no no no no no!!! Don't do that!! Take a look at FAQ#4:

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

Can you tell us the details of what you need to do? We can (hopefully) suggest a much more robust and efficient way to achieve your goal.

If you use detail filters with dimensional packages, you're letting yourself in for a world of pain in future...

MF.

Hi MF,

Sure, I need to create a report that consists of multiple tabs and charts. The package that I'm using right now consists of multiple dimensional packages in it to set as a topic for each tab. For each tab, there will be a set of prompts and charts to discuss each topic. These prompts should be able to filter the charts based on member captions/data items found per tab, hence, we used detail filters. Also, the report tabs that I need to create only needs drill-through, not drill-down.

Honestly, I'm not quite sure if I'm doing the report correctly since I'm also having an issue in terms of performance when loading the reports. Would you have any suggestions?

Thanks a lot.

[AnalyticsWithJay]

I would suggest "sharpening the axe" before cutting down the tree :). Spend a couple of hours studying Dimensional Reporting - it will save you so much time moving forward:

http://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.1.0/com.ibm.swg.im.cognos.ug_cr_rptstd.10.1.0.doc/ug_cr_rptstd_id16866cr_rptstd_dimensional_reporting_sty.html#cr_rptstd_wrkdat_calculated_rollup_function_slve_ordr

[MFGF]

Hi MF,

Sure, I need to create a report that consists of multiple tabs and charts. The package that I'm using right now consists of multiple dimensional packages in it to set as a topic for each tab. For each tab, there will be a set of prompts and charts to discuss each topic. These prompts should be able to filter the charts based on member captions/data items found per tab, hence, we used detail filters. Also, the report tabs that I need to create only needs drill-through, not drill-down.

Honestly, I'm not quite sure if I'm doing the report correctly since I'm also having an issue in terms of performance when loading the reports. Would you have any suggestions?

Thanks a lot.

Hi,

If you're using multiple "cubes" in the same package (which is what I think you're describing above) you need to think about the MUNs (Member Unique Names) to be able to filter across all of them with a single prompt. If you look at the properties of a dimensional member in one "cube" (right click the member in the package tree and select Properties) you will see its MUN. Make a note of it and do the same for a corresponding dimensional member in another cube. Are the MUNs the same or are they different?

If they are the same, your job is easy - in each chart you'd bring in a query calculation with the expression [Your desired level of the hierarchy] -> ?YourPromptParameter?

If they are different, you'd need to get a bit more clever. You'd need to use a prompt macro function in an expression - this would construct each different MUN in each chart, using the same parameter for the ID at the end. Without knowing what your MUNS look like, it's tricky to give you an example, though. Here's an example from the sample Great Outdoors Sales (Analysis) package:

#'[Sales (analysis)].[Products].[Products].[Product line]->[Products].[' + prompt('ProdLineParam','token') + ']'#

vs

#'[Product forecast (analysis)].[Products].[Products].[Product line]->[Products].[' + prompt('ProdLineParam','token') + ']'#

Your prompt would use two calculated items - one for Use Value and one for Display Value. The Use Value would be a calculation:

roleValue ('_businessKey', [your desired level of the hierarchy])

The Display Value would be another calculation:

roleValue ('_memberCaption', [your desired level of the hierarchy])

Cheers!

MF.