How to sum field from two queries in concur Report

[Vrinda21]

I’ve created two queries, both of which include the field Report ID, but each is filtered differently. I'm now trying to aggregate the counts of Report ID from both queries, but I'm encountering the error below.

Could someone please help me understand this error and suggest how we can perform the summation?

Thanks!

[KevinD]

@Vrinda21 the cross joins error leads me to believe that you pulled data from two areas in the data warehouse that don't gel. You might need to create a Union or Join report to achieve what you are looking to do.

[Vrinda21]

Hi Kevin,

Thanks for the response

I’m working on aggregating the count of fields that appear in two different queries. Both queries include the Report IDfield, but it's filtered differently in each one, as shown in the screenshot below:

• AP Query:
  The Report ID is filtered based on the Step Action Date/Time from the Expense Report Workflow Trail.

Final Query

The Final Query is a union of the AP and SAP queries.

• SAP Query:
  Here, the Report ID is filtered based on the Audit End Date from the Concur Audit Service.

Hence, the field report ID is filtered basis the 'step action date/time' coming from 'expense report workflow trail' in AP query and basis the 'concur audit service' coming from 'concur audit service' in SAP query.

Given this, could you please confirm whether the Expense Report Workflow Trail and Concur Audit Service can be joined?

[KevinD]

@Vrinda21 follow-up question...what are you trying to accomplish with this aggregation? Are you looking for your report to show a report ID count? Is this just to get a total amount of Reports that have completed that workflow step? 

[Vrinda21]

hi Kevin

We are trying to aggregate the results we get from the two queries - AP Query and Final Query. 

As mentioned above, in the AP query, the Report ID is filtered based on the Step Action Date/Time from the Expense Report Workflow Trail. The Final Query is a union of the AP and SAP queries where in the SAP Query, the Report ID is filtered based on the Audit End Date from the Concur Audit Service.

Also, we need to take out the % of result we get from these queries and divide by the difference. I was able to compute the difference using the 'except' function.

However, when i try to calculate the %, i get the below error.

Please confirm if such calculations are possible in concur reporting.