How do I tell Cognos Query or Report Studio to not aggregate line item details in Invoice

[csimon]

Hello, I am trying to figure how to tell Cognos Query or Report Studio not to aggregate values within line item details. For example, I have an Invoice Details report that provides all of the details within the request, including each allocation line and each of its properties. For like values - like GL account - it will summarize the values of that line instead of adding a separate line. I thought there was a way to do that at least in Report Studio but I can't figure it out.

So if account 740000 has two separate lines it will summarize that value - I want to break it out so that there is a line for each allocation line from the invoice. We have some transactions where 740000 would have a negative value and then another positive value - I want to have that broken out and not totalled.

I hope all that makes sense!

[KevinD · Accepted solution]

@csimon I know exactly what you are talking about. In fact, when I do our training classes on Cognos Reporting, I mention this very thing. So, you are in luck today. 🙂

I would recommend turning it off in Query Studio, it's easier to describe. So, build your report, then on the left-hand menu, click Manage File. Next, select My Preferences. You'll see the preferences appear on the lower half of the screen. One of the options is Advanced Run-time Options. Click this. When the advanced options appear, you'll see a some check boxes. One of them (it will be checked) says "Automatically summarize detail values, suppressing duplicates". Uncheck this box. Be sure to save the report. 

This should do it for you. I believe this will carry over if you then open the report in Report Studio. 

Give it a try and let me know how it goes. Oh, and because I'm feeling especially generous, I provided a couple screenshots for you. 

[csimon]

BAM! I knew it was something simple because I've done it before. I was overthinking. THANK YOU! You are a rockstar.

[KevinD]

@csimon Well, I wouldn't say rockstar...okay, maybe I would. 🙂

Glad I could help. 

[Anonymous User]

Hello Kevin and Simon!
This is literally the only thread where I found people discussing something at least similar to what I am facing at work.

I need to display an average number of comments on blog posts for each year.

Well, I am able to do all of those.

I insert a column blog posts which shows blog post IDs. I also add a sum at the bottom.

I insert a column # of comments which shows a number of comments on each individual blog post. I also add average at the bottom.

Then I group by year.

Results are ok - I get years as groups, and within those there is a list of of all blog posts and their comments and at the bottom of that list the last row shows: total number of blog posts and average number of comments.

However, as you can imagine - there are thousands of blog posts! And I am tasked to show data for the last 10 years.

Do you know of a way to hide the actual list of blog posts & number of comments on each blog post? This would save soo much space and blog post IDs are completely meaningless and out of context.

Ideally, I would like to have 10 groups for 10 years and in each group I would like to have a two row table:

Header row > Column 1: "Total blog posts"; Column 2: "Average number of comments"

Data row > Column 1: 15024; Column 2: 4.7

I think this is possible, because I am able to get these values and calculate accordingly.

You would really help me a lot! Thanks in advance!

PS
Happy to hop on a Zoom to screenshare if that makes things better.

Best from Serbia!

Strahinja

[KevinD]

@Anonymous User are you using Cognos reporting to build out this report or are you building it in some other tool? Blog Posts are not data from SAP Concur, so I am unclear as to what tool you are pulling blog posts from and what tool you are using to build out the report you mentioned.

[Anonymous User]

Hi Kevin!! Thanks for taking the time to respond, I really appreciate it.

Yes, I am using Cognos Query Studio 10.2.1 (it looks almost exactly like your printscreen). And you are correct, it's not backed by SAP. I think Cognos is just placed on top of some kind of a database, I am not 100% sure.

I hope you could still help me out with an advice how to accomplish what I described within the Cognos tool.

Thanks!

[KevinD]

@Anonymous User Query Studio is a bit limited in what it can do. From your description it sounds like you want to use some columns to do averages, but yet don't necessarily need those columns visible. Is that correct? Do you know if you have access to the Report Studio tool? It is another reporting building tool in Cognos. This tool is more robust and doesn't have the limitations of Query Studio. I'll await your reply.

[Anonymous User]

Hello Kevin,

Unfortunately the only tool available to me is the Query Studio (see print screen in new tab here).

Basically, what I am getting is can be seen on print screen here (opens in new tab).

And what I would like to have is can be seen in this (very, very poorly) photoshopped pic here (opens in new tab).

I hope these graphics help convey what I am after. Again, thanks for taking the time Kevin.

[KevinD]

@Anonymous User So, I hate to say it, but I don't think Query Studio will be able to do what you want. The tool has a lot of limitations on how it can display data. I don't believe it will allow you to hide the data that you used to create the average number of comments. 

Your best option would be to run your report as an Excel file. Once it runs, download it and open it with Excel. Then you can manually manipulate it to hide the date and just show the averages.