Cognos expense report excluding transaction where there is no data in children nodes

[jojeejoseph]

Hello Cognos experts,

Cognos beginner here 🙂

I am trying to create a Cognos report for Concur Expense transactions.
Some of the transaction in the report are domestic, and contains GST Code - [Expense].[Expense Tax].[Reclaim Code].
However, GST doesn't apply to some overseas transaction in the report.
My report runs fine, with most of the Data Items coming from [Expense].[Commonly Used Fields].xx and [Expense].[Entry Information].xx. Report at this stage shows all transactions irrespective of whether GST applies or not.
As soon as I add GST field [Expense].[Expense Tax].[Reclaim Code] to the Data Items, the report excludes overseas transactions where GST doesn't apply.
What I would like to see is report still displaying all transaction - showing the GST code where GST applies and showing 'None' for transactions where GST doesn't apply. Is this possbile? What are my options?

[KevinD · Accepted solution]

@jojeejoseph two other options are to create a Master Detail report or a repeater table. My Reporting contacts would suggest a repeater table. I am not quite sure how to do this. In my opinion, the Master Detail is a little easier to create.

Here is a link to our training manual: Cognos Reporting Training Manual. If you do a search for Master Detail, you will see an example of how to build this type of report. The example in the manual is not based on GST, but it should give you a pretty good idea of how to build the report.

You will also see an example of building a Join report. These are a bit more complicated, but will give the same result.

[rohanpatil]

Hi @jojeejoseph ,

I would create two queries. One for domestic transactions and another for overseas ones. In case of the domestic transactions, add the GST field [Expense].[Expense Tax].[Reclaim Code] to the Data Items. For the overseas transactions create a dummy field. Then join the two queries to a main query. This should give you the complete result.

Screenshot of how we configure dummy payment status (for report) in case of card transactions not added to any report.

Hope this helps.

[jojeejoseph]

Thank you @rohanpatil for this suggestion. Though “join” worked fine for our other use cases for expense reporting using cognos, for some reason “join” doesn’t seem to work in this particular case. Not sure why though!

[rohanpatil]

Sorry to hear that the suggestion did not work out for you. Hopefully the tips shared by @KevinD can help.

Best regards,

Rohan

[KevinD · Accepted solution]

@jojeejoseph two other options are to create a Master Detail report or a repeater table. My Reporting contacts would suggest a repeater table. I am not quite sure how to do this. In my opinion, the Master Detail is a little easier to create.

Here is a link to our training manual: Cognos Reporting Training Manual. If you do a search for Master Detail, you will see an example of how to build this type of report. The example in the manual is not based on GST, but it should give you a pretty good idea of how to build the report.

You will also see an example of building a Join report. These are a bit more complicated, but will give the same result.

[jojeejoseph · Accepted solution]

Thank you @KevinD, a Master-Detail report has done the trick for us. The training manual available at the link provided has been very useful.   

[KevinD]

@jojeejoseph YES!!!! I love it when my suggestion works out. I guess that's why they still let me moderate here on the Community. 🙂