Cognos Bar Chart

[srujan]

Hi everyone,

I have a requirement where, in a horizontal bar chart i need to show the aggregate value of a data item as one of the value on the y axis. Let me put this in another way - along with the bars based on the corresponding values of a data item i need to show a bar that indicates the aggregate value of that data item. Can anyone please advise.

Thanks!

[R Ambre]

Hi,

To add aggregate column in bar chart ,

Create data item which will be sum of all values
Expression: Total([Measure Value] for Report)

Add that data item on X Axis next to measure ,Please refer attached screenshot.

Sort chart on the basis of measure value.

Total column will be the last bar in chart

[MFGF]

Hi everyone,

I have a requirement where, in a horizontal bar chart i need to show the aggregate value of a data item as one of the value on the y axis. Let me put this in another way - along with the bars based on the corresponding values of a data item i need to show a bar that indicates the aggregate value of that data item. Can anyone please advise.

Thanks!

Reddy29886,

Please do NOT use the calendar as a method of creating new posts in the Cognoise forum. We have added step-by-step details of how to create posts in Cognoise in the FAQ section - see FAQ 3:

http://www.cognoise.com/index.php/topic,27563.0.html

I also see that you posted the same question twice - in two separate threads (the other one is here). Please do not do this - take a look at the Forum Etiquette post below, paying attention to point 4:

http://www.cognoise.com/index.php/topic,24030.0.html

Thanks,

MF.

[srujan]

Hi Rasika,

It did not work for me. Thanks for replying though.

I figured a way to do this. I am just mad at me that y i did not think of this yesterday. I am using a union to achieve this.


Thanks,
Srujan

[srujan]

Hi MF,

Sorry for that. I will make sure that i follow your instructions next time.


Thanks!

[R Ambre]

Hi Reddy,

Yes by union as well you can achieve this but I thought of above solution is simpler.
I am not sure why it is not working for you.

Please find attached screenshot It is working for me.

[srujan]

Hi Rasika,

Can you show me the screen shot of the chart in report design mode. Just wanted to see what are your measures,and what data items are you using.Can you tell me in brief how you achieved this.
i was able to display the total bar but i am not able to display a label for that bar. Please look at the screenshot to understand what i am trying to ask.



Thanks,
Reddy

[MFGF]

Hi Rasika,

Can you show me the screen shot of the chart in report design mode. Just wanted to see what are your measures,and what data items are you using.Can you tell me in brief how you achieved this.
i was able to display the total bar but i am not able to display a label for that bar. Please look at the screenshot to understand what i am trying to ask.



Thanks,
Reddy

Works fine for me too. Here's my chart:



The trick is to stack the "Total" query item to the right of the existing categories:



Here is the expression for the "Total" item:



Of course, this assumes you are using a relational package. If you are using a dimensional package a different approach is needed.

Cheers!

MF.

[srujan]

Thanks Rasika and MF for sharing your knowledge. It works for me now. But still i might have to go with Union. Because i need to sort the chart by the category x-axis labels in ascending order. Not sure if this is possible with the above approach.


Thanks!

[MFGF]

Thanks Rasika and MF for sharing your knowledge. It works for me now. But still i might have to go with Union. Because i need to sort the chart by the category x-axis labels in ascending order. Not sure if this is possible with the above approach.


Thanks!

Assuming you mean the labels before the Total column, yes - you can do that. You can see in my screenshot that I have sorted my categories - in my case by the measure value - but it's easy to sort on the category values themselves.

Cheers!

MF.

[srujan]

Hi M.F, I get that you are sorting on Product Type, and you are displaying the total at the very end.
I want to take the total as a value and sort it along with other product type values.

i.e If you look at your screenshot, i should have this order  binoculars, climbing accesories...tents, tools, total....Wood .

I don't see a way to achieve that.

Thanks,
Reddy.

[MFGF]

Hi M.F, I get that you are sorting on Product Type, and you are displaying the total at the very end.
I want to take the total as a value and sort it along with other product type values.

i.e If you look at your screenshot, i should have this order  binoculars, climbing accesories...tents, tools, total....Wood .

I don't see a way to achieve that.

Thanks,
Reddy.

From a technical standpoint, you'd need to use the Union approach to be able to do that. However, from a business requirement/common sense/sanity check standpoint I can't see any reason in any situation where a summary would be required in the middle of a set of details. How can that make any sense to anyone reading the chart? Are you sure you're fully understanding the requirements?

MF.

[srujan]

I am positive M.F. Its not that they want a summary to be displayed in the middle of a set of details. But the requirement we have here is that the chart should not be sorted by the measure, but by the descriptive values. Now here the problem arises if i do not use a union, the total bar displayed would be shown at the last like in the screenshot you posted.


Thanks,
R.

[BigChris]

As MF says, that's just going to look really weird. Before you break your neck making your union work, make sure you show them a demo version, perhaps using excel, with the total in the middle to make sure that that's what they want to see.

[MFGF]

I am positive M.F. Its not that they want a summary to be displayed in the middle of a set of details. But the requirement we have here is that the chart should not be sorted by the measure, but by the descriptive values. Now here the problem arises if i do not use a union, the total bar displayed would be shown at the last like in the screenshot you posted.


Thanks,
R.

Hi,

I think you need to check the requirements again - sorting by the descriptive values (within the details) is fine and you can do it without a Union. If you Union then sort the result of this alphabetically, the Total bar would appear in amongst the detail bars. Here's what I mean:


Above I have sorted the details alphabetically, and the Total that summarises them all is at the end. This seems to make a lot of sense, and this doesn't require a Union.


Above You can see the Total summary bar is sorted alphabetically too, and appears between the Tools and Watches detail bars - which makes no sense. This is what you would get if you used a Union.

My feeling here is that your requirement is the first option, not the second. If I were you I would check back with the client to make sure.

MF.

[srujan]

Thanks M.F and Chris.

Sure i will check back with the client on this.


-R