# Percentage of Sub-total

Lawrence Brown shared this problem ago
Completed

Hi,

Is it possible to use the 'Percentage of Total' function but calculated for Sub-totals?

I have a report that calculates the % of the total number but I'd like it to do it for the Income and Expenditure respectively, I've included an additional column to show how I'd like the end result to look.

Regards,

Lawrence

Hi Daryle,

Since there have now been a couple of requests for this functionality, I have put together a custom advanced function to achieve this.

Upload the attached jar file through the plugin manager. The new function will be called "Sectioned Percentage of Total". Apply this to the column of choice, and select the column used for your sub-totals as the reference column. In the original example you would select MA2 as the reference column.

Let me know if you run into any problems with this.

Regards,

Nathan

## Replies (6)

1

Hi Lawrence,

Thanks for sending in the question. This might not be the cleanest way to express this (there might be other ways to accomplish this via Report from Report), but I think to accomplish what you are after you are going to need to consider using sub queries.

Using basic append sub queries I was able to accomplish the following:

In your case, the master query would contain your MA2, MA4, BUD, and % of Total columns. Then you could create an individual sub query for income and expenditure each (filtering the sub query by either income or expenditure to limit the sub query results) to produce an individual % of Total.

If you you haven't worked with sub queries before, our wiki has some great information on the subject:

Can you review this and let us know if this solution could work for you, or if you have any follow-up questions here? I look forward to hearing back!

Kind Regards,

Dustin

1

Is this still the best way to calculate the % of subtotal?

1

Hi Daryle,

Since there have now been a couple of requests for this functionality, I have put together a custom advanced function to achieve this.

Upload the attached jar file through the plugin manager. The new function will be called "Sectioned Percentage of Total". Apply this to the column of choice, and select the column used for your sub-totals as the reference column. In the original example you would select MA2 as the reference column.

Let me know if you run into any problems with this.

Regards,

Nathan

1

I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!

Regards,

Nathan

1

Hi Nathan

I wanted to use your plug in but in my YellowFin installation (7.4.7), there is no Plugin Management link in the Administration section on the right of the Admin console (as per https://wiki.yellowfinbi.com/display/USER74/Install+Plug-in)

Is this a bug or is there a setting needed to enable plugins? My user has the admin role.

Thanks

Greg

1

Hi Greg,

Chances are, your user is lacking the appropriate role permission for this. It should be "Plugin Management" under the administration section.

Nathan

1

Thanks Nathan. I finally found the Plugin Management setting under the Administration setting and enabled it for the admin role.

Greg

1

Hi Nathan,

Could you attach the source code for the custom advanced function?. It woul be helpful to understand how to create one which receives the name of a column as a parameter (eg. the reference column in this case).

Good bye,

Gabriel

1

Hi Gabriel,

I have attached the source code for this.

Nathan

1

Hi @Nathan

I hope you're doing well.

It's this function still working? I installed the plugin but i can't see the option on advanced function.

Looks like that doesn't work in crosstab, only in normal report. But if I do in normal report and after i change back is still working :D haha

Thanks

Evan

1

Hi Evan,

This is only intended to work with non-crosstab reports. I think the following Idea might be what you'd be looking for here as well: Add a Percent of Total Row Totals Option.

Can you take a look at that Idea item and let me know if this appears to be what you're looking for? If so, please feel free to throw in a vote there and continue to follow the Idea item for any potential future updates.

Regards,

Mike

1

Hi Mike,

Actually I used the plugin from Nathan in a report and I converted the report into crosstab and the function still works. I know that could be a bug but it worked for now.

I also already voted in this idea.

Thanks!

Best regards,

Evan Bessa

1

Hi Evan,

Thanks for your response. So what you're saying is it works, you just have to toggle back to a normal report first to select it, but once you make it a crosstab, it still works, but it's just not found in the Advanced Function list when the report is a crosstab report? Is my understanding correct?

Regards,

Mike

1

Hi Mike,

yes, this is really crazy, but the steps that i did were:

1. I converted the crosstab into normal table report

2. I used this advanced function into the column that I want and worked.

3. I converted back from normal report into crosstab

4. The advanced function still working, I can't edit this function but I can do any other change, so this is fine, at least while we have this bug :)

Thanks

Evan Bessa

1

Hi Evan,

Thanks, I'm able to replicate this. I've gone ahead and logged a defect for this. That said, this is a custom JAR file, and there's a rather simple workaround, so I do find it likely this will be addressed, but it is a defect nonetheless so I will keep you posted on any progress made here in this ticket.

Regards,

Mike

1

Hi Mike,

I understand, but it's not a problem for me since I can use in a crosstab, so if this stops to work. Please let me know and I can try to find another solution for that, as once the idea for the function is still in "idea ":)

Thanks

Evan

1

Hi Evan,

Sure sounds good. Please follow the referenced Idea item for future updates there and I will keep you posted on this one here.

Regards,

Mike