Calculating age of CC transactions in cognos report 'Unsubmitted or unassigned CC transaction'

[Saikat1]

Hi Team,

I am trying to modify standard report 'Unsubmitted or unassigned CC transaction'  .  I want to calculate how much aged that transaction is (i.e. days between current date and posted date). I applied this condition add added a data item : 

_days_between (current_date,[Expense].[Credit Card Transactions].[Posted Date])

This one not getting added in sub queries unsubmitted or unassigned. So I added in main query, it is added without error. But when I am trying to add in in main page its giving an error like below :

Cross joins (between query subjects: Union1, [Credit Card Transaction Tables].[FCT_CCARD_TRANS Posted Dim_Date]) are not permitted for the user who has the identity '*'.

Can you please suggest any solution to this how I can calculate aging of transaction here and add in this report(modified unsubmitted or unassigned cc report). 

[TammyK]

This is a great question.  I struggle with creating the exact type of report you mentioned.  Currently I just use google to calculate over 60 days/over 90 days, then I manually add comments to my excel file of which cardholders are extremely delinquent.  I feel old school doing this aging manually.

[KevinD]

@Saikat1 I don't think you can add it to the main query. No error when adding it to the main query is not an indication that it should work. I've reached out to a colleague on the Reporting team. I'm waiting to hear back.

I tried going into your site to look at the report, but when I went in it showed you had the Analysis offering and not Business Intelligence. I also tried to see your Cognos permissions, but nothing came up. Your reporting offering and roles must be administered some other way. 

[KevinD]

@Saikat1 the expression you have is correct. You must add it to the Unassigned and Unsubmitted queries separately. Once added to each of these queries, you can then the data item to the Main Query, by selecting the main query, then use the source icon. See screenshot. 

We found that the assigned ranking caused the report to error after adding the expression, so if you don't care about the top 10, you can remove the rank expression. You will see this under Summary Filters when you are viewing the Main Query data items.

I hope this helps. Good luck. 🙂 

[Saikat1]

Hi Kevin @KevinD ,

We continue to get same error if I add in unsubmitted and unassigned. The expression I am using is this: 

_days_between (current_date,[Expense].[Credit Card Transactions].[Posted Date]) both in unsubmitted as well unassigned. Posted date field I used in both sub-queries.  

[KevinD · Accepted solution]

@Saikat1 did you remove the ranking, as I mentioned?

[Saikat1]

Hi @KevinD yes I did. I removed but still same issue.  

Sorry by mistake accepted your reply as solution but issue still same adding to unassigned or unsubmitted .

[KevinD]

@Saikat1 hmm...my colleague and I did actually walked through this yesterday and didn't have an issue. I'd have to guess that possibly you didn't add the expression correctly. It is difficult to provide help without seeing the actual report. Could you possible take some screenshots for me of your queries, please?

[Saikat1]

Sure please find screenshots.

unsubmitted before adding the field .

unsubmitted after adding  

Unassigned before adding

Unassigned post adding. 

So this is the problem I am facing while adding fields to subqueries. If I add this to main query without subqueries no issue.

[Saikat1]

Hi @KevinDKevinD did you able to find anything on this. Also I wanted to know if there is any field to capture aged transactions, or this also we need to get using queries. 

[KevinD]

@Saikat1 I haven't inquired any further as when my colleague and I tested it, it worked for us. There must be a step or something that you might not be doing correctly. 

There are two ways to see aged transactions. First, if you have an audit rule activated that flags transactions over a certain number of days old, you can run a report that filters for the exception code that is tied to the audit rule. This will show all transactions that have that exception. 

Second, you can build apply a filter to a query that would look at the current date go back as many days as you specify to see how many transactions fall into that range. Here is an expression that would look at today's date and go back 120 days. You would just replace 120 with however many days you want to go back.

[Transaction Date] between _add_days(current_date,-120) and current_date