cancel
Showing results for 
Search instead for 
Did you mean: 
jojeejoseph
Occasional Member - Level 2

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

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?

2 Solutions
Solution
KevinD
Community Manager
Community Manager

@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.


Thank you,
Kevin Dorsey
SAP Concur Community Manager
Did this response answer your question? Be sure to select “Accept as Solution” so your fellow community members can be helped by it as well.

View solution in original post

Solution
jojeejoseph
Occasional Member - Level 2

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.   

View solution in original post

6 REPLIES 6
rohanpatil
Super User
Super User

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.

rohanpatil_0-1692870978548.png

 

Hope this helps.

 

Best regards / Mit freundlichen Grüssen,

Rohan Patil
SAP Concur Community enthusiast
Did this response answer your question? Be sure to select “Accept as Solution” so your fellow community members can be helped by it as well.
jojeejoseph
Occasional Member - Level 2

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!

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

 

Best regards,

Rohan

Best regards / Mit freundlichen Grüssen,

Rohan Patil
SAP Concur Community enthusiast
Did this response answer your question? Be sure to select “Accept as Solution” so your fellow community members can be helped by it as well.
Solution
KevinD
Community Manager
Community Manager

@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.


Thank you,
Kevin Dorsey
SAP Concur Community Manager
Did this response answer your question? Be sure to select “Accept as Solution” so your fellow community members can be helped by it as well.
Solution
jojeejoseph
Occasional Member - Level 2

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.   

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


Thank you,
Kevin Dorsey
SAP Concur Community Manager
Did this response answer your question? Be sure to select “Accept as Solution” so your fellow community members can be helped by it as well.