Creating Multi-Query Report in Intelligence

[lgaydos]

I've recently upgraded from Analytics to Intelligence, and am trying to recreate my Expense Accrual report with the formatting I previously had. I've been editing the Standard Report and am getting close, but I keep running in to a cross join error when attempting to add our Branch (code) custom field at the entry level. From another post I saw here on the community, it sounds like I'm needing a multi-query report, but when I tried to just start from scratch, it's telling me there's nothing in the Data Warehouse. Any tips? Is there a way to add an additional query to a standard report? (This is the last field that I'm needing to add.)

[KevinD]

@lgaydos you said you are trying to recreate your Expense Accrual. Why aren't you still using this report? I'm not sure why you are needing to recreate it instead of continuing to use it. 

As for the data warehouse being empty. This is just a place holder and is referred to as the data package, it is not where you pull data items from to add to a report. When you start a new report, it asks to choose a data source, you simply just click on Concur Data Warehouse to point to the source. 

I can tell you the Expense Accrual Standard Report is a multi-query report. There is a recording of how to modify the Expense Accrual Standard Report that you might find helpful: Modifying the Expense Accrual Standard Report. Give that a watch as I think you'll find it helpful.

[lgaydos]

@KevinD "Recreate" may not have been the most appropriate word. I was using the Expense Accrual report in Analytics, but now that I'm on Intelligence, I'm trying to get the Expense Accrual report formatted the way I had it in Analytics (for example, having it include that custom field I mentioned). That information and video were extremely helpful, thank you! The only difference I'm noticing is that the accrual report in Analytics included unassigned transactions, whereas in Intelligence it only includes unpaid and unsubmitted.

[KevinD]

@lgaydos actually, the name of the query is called Unsubmitted CC Trans. However, if you look at the Query filters, there is a filter that says to only show Unassigned Transactions. So, when you run the report, you should get all transactions that are either unassigned to a report or are assigned to a report, but not yet paid. 

[lgaydos]

@KevinD Good morning, 

There are not any transactions showing on the report with an Unassigned status. Using myself as an example, I currently have an unassigned credit card transaction, but I am not showing up on the Expense Accrual report at all, and just quickly spot-checking I found three other employees with unassigned CC transactions as well. 

These transactions are days/weeks old, so it shouldn't be a matter of pending an overnight update. There is also a filter on the Unsubmitted CC Trans query for the transaction date to be less than or equal to the accrual date, and I'm using today's date as the accrual date, so that shouldn't be the issue. 

[KevinD]

@lgaydos question for you...when you run the Expense Accrual report, are you running it from the Standard Reports folder?

If not, is the report in your My Content folder or somewhere else? I'd like to go in and run the exact same report you are to see if I can see Unassigned transactions.

[lgaydos]

@KevinD I am running it from My Content. My suspicion is that it's potentially because I added in our custom field of Branch. A transaction wouldn't have a branch associated with it until it is assigned to a report. I assumed that Unassigned transactions would just be blank in that field, which is how it operated under Analytics, but perhaps that is an incorrect assumption and it's just excluding them from the report if there is no branch?

I've re-copied the report (the one named "Expense Accrual (1)" in My Content) and started over, leaving off the addition of Branch, and it is pulling in Unassigned transactions appropriately. The only other thing I noticed was that my original report was only showing transactions dated March, April, and May, and I'm not sure how any of my edits would've made that happen. 

[KevinD]

@lgaydos well done sleuthing the issue. Definitely the custom field caused the unassigned to be left out. That's why Cognos can be tricky is if someone didn't explain how the data warehouse works, you are left wondering what is happening and why data has disappeared.

Are you still only seeing the three months?

[lgaydos]

@KevinD When I re-copied and started over I am now seeing older dates. 

In regard to the custom field, is there a trick to getting it to return a blank field there if there is no Branch as opposed to excluding the transaction all together?

[KevinD]

@lgaydos you would likely need to create a separate query for the custom field through a Union or Join. I've also seen people use a repeater table or a master detail report. If you download the training manual we have, it does discuss Joins and Master Detail reports. If you go to this link: https://www.concurtraining.com/cnqr/cognos-11-registration you can download the training manual.

[lgaydos]

@KevinD Thanks so much for all your help!

[laffertyme]

Hi Kevin,

I have started to watch the video but I do not get the Edit Report selection after I click the 3 dots.  What could I be doing wrong?  I have copied the report to a new folder.

[KevinD]

@laffertyme which browser are you using? Could you take a screenshot of what you see when you click the three dots, please? 

Your Cognos permission looks fine, so it could be a browser issue. Maybe try a different browser and see if that works. 

[laffertyme]

I am using Chrome.  Someone with my company changed my permission this morning and said that it will take up to 24 hours to take affect.  Maybe once that updates, I will have access to the edit choice??