Cross tab is not working for prompt value choosen other than SELECT ALL

[crazy_tech]

Hi,
I have 2 prompt parameters ( AccountingDate and TypeofExpense).. The AccountingDate is a string of format 'yyyy-mm' . If i choose a AccountingDate for eg: 2024-02 and Type0fExpense= 'show all' ..it gives me correct data.. but if  choose the AccountingDate and choose a TypeofExpense eg : 'Rent' then it gives nothing though when i run the tabular data i have the data..But cross tab is showing zero result for choosen type.. I have checked if there is difference between 2 prompts properties .. nothing ..Could you please help me with where could be the issue?

[MFGF]

Hi,
I have 2 prompt parameters ( AccountingDate and TypeofExpense).. The AccountingDate is a string of format 'yyyy-mm' . If i choose a AccountingDate for eg: 2024-02 and Type0fExpense= 'show all' ..it gives me correct data.. but if  choose the AccountingDate and choose a TypeofExpense eg : 'Rent' then it gives nothing though when i run the tabular data i have the data..But cross tab is showing zero result for choosen type.. I have checked if there is difference between 2 prompts properties .. nothing ..Could you please help me with where could be the issue?

Hi,

I'm assuming your package is a relational package rather than a dimensional package? Can you confirm?

Can you get to the query associated with the crosstab and look at the filters driving these two prompts? What are the filter expressions?

Cheers!

MF.

[crazy_tech]

Its a dimensional package.. The data is coming from the fact table joined with a dimension (D_TypeofExpense).
The filters in the query the cross tab used is ExpenseType=?pExpenseType?   and AccountingDate=?pAccountingDate? and there is no difference in parameters

[MFGF]

Its a dimensional package.. The data is coming from the fact table joined with a dimension (D_TypeofExpense).
The filters in the query the cross tab used is ExpenseType=?pExpenseType?   and AccountingDate=?pAccountingDate? and there is no difference in parameters

Hi,

I think we're not quite on the same page here - dimensional packages don't have tables and joins. Take a look at FAQ#5 in the post below to confirm what sort of package you are using:

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

If you really are using a dimensional package, then detail filters are not the correct approach. You would instead use dimensional expressions in calculated items for Expensetype and Accountingdate within your crosstab, eg

[Your Expense Type level] -> ?pExpenseType?

This is why it's critical to understand the type of package you are using here.

Cheers!

MF.

[crazy_tech]

Thank you sir for your help and the link of FAQ. Well its Dimensional package.. I am more into SQL then cognos (first time learning cognos)  so i used that language of INNER and etc.. I confirmed its dimensional and then i checked the filters.. I was using calculated than dimensional expressions..I changed them and it worked..
Thank you so much

[MFGF]

Thank you sir for your help and the link of FAQ. Well its Dimensional package.. I am more into SQL then cognos (first time learning cognos)  so i used that language of INNER and etc.. I confirmed its dimensional and then i checked the filters.. I was using calculated than dimensional expressions..I changed them and it worked..
Thank you so much

Great news! Glad you managed to get things working properly. Using detail filters with dimensional packages can give inconsistent and incorrect results - CognosPaul wrote a great article on why - see FAQ#4 in the FAQs post I linked to previously.

Cheers!

MF.

[crazy_tech]

Hi , i have one more question if ?TypeofExpense? is 'ALL' then choose TypeofExpense='rent' else  ?TypeofExpense?..
but this should be applied to only other dataitem expenseCost  ..so it should be if the ?TypeofExpense? is all then give the expenseCost for rent else give the expensecost for the type selected