Formula on Totals

gaparna · January 8, 2013, 6:23pm

Hi,

Sorry, to bother on and on. We would like to know if there is any update on this issue. We are going live in another week and as this is a critical functionality, we thought we will check again.

Please let us know if there any updates.

Thanks,

Aparna

shakeel.faiz · January 9, 2013, 7:20am

Hi,

Thanks for your posting and using Aspose.Cells for Reporting Services.

Sure, once there is some update for you, we will share it with you asap.

We have also logged your comments against this issue id: SSRS-40074.

Hopefully, your issue will be fixed soon.

gaparna · January 9, 2013, 10:58pm

Thank you ! As an interim solution , if we could make the &=&= SUMIF(...) can be shown as values on the VS and not as formula until we get a fix for the hide before and after the formula column would help. Although we would definitely need the hide and formula to work as that's a critical funcationlaity for us.

We appreciate all the help.

Thanks,

Aparna

shakeel.faiz · January 10, 2013, 1:41am

Hi,

Thanks for your posting and using Aspose.Cells for Reporting Services.

We have discussed your issue with the developments team. We will soon provide you a fix or interim solution.

We will get back to you asap.

amjad.sahi · January 10, 2013, 3:38am

Hi,

We have found the issues and fixed them accordingly.

Please try the fixed MSI version (attached): Aspose.Cells for Reporting Services V1.9.0.10.

Please let us know your run result.

By the way, we are developing new feature about showing image and text in a cell, so we might delay in replying your issues/queries.

Thank you.

shakeel.faiz · January 11, 2013, 12:40am

gaparna:
Thank you ! As an interim solution , if we could make the &=&= SUMIF(...) can be shown as values on the VS and not as formula until we get a fix for the hide before and after the formula column would help. Although we would definitely need the hide and formula to work as that's a critical funcationlaity for us.

We appreciate all the help.

Thanks,

Aparna

Hi,

Thanks for your posting and using Aspose.Cells for Reporting Services.

We have fixed sum question at table footer.

Please download and try the fixed version: Aspose.Cells for Reporting Services v1.9.0.12 (MSI).

Please let us know your run results.

gaparna · January 11, 2013, 10:27am

Hi,

Does the SUMIF only work on the footer and not on the dataset. We didn't test it on the footer but we tested it on the table , summing across column values and not across the row values like &=&=SUMIF($B$5:$CD$5,"Week Of",B{r}:CD{r}) and it didn't work . It still displays the text and not the values on the VS.

update : We tested it on the footer as well and it doesn't work.

Please let us know if the usage is acceptable by the tool.

Thanks,

Aparna

shakeel.faiz · January 11, 2013, 10:52am

Hi,

Thanks for your posting and using Aspose.Cells.

Could you please retest this issue? Because I have recently updated the link from 11 to 12.

So you might be using older Aspose.Cells for Reporting Services v1.9.0.11 (MSI).

Please use the latest one now: Aspose.Cells for Reporting Services v1.9.0.12 (MSI) and let us know your feedback again.

Thanks for your cooperation.

gaparna · January 11, 2013, 3:25pm

Hi,

I'm afraid it is still not working. The formula displays differently , partly in SSRS langauage on the VS/UI /Web , but works in Excel. SUMIF($B$5:$CD$5,"Week Of",Inspections.NAME:Inspections.WEEK60) instead of SUMIF($B$5:$CD$5,"Week Of",B{r}:CD{r}).

Thanks,

Aparna

shakeel.faiz · January 13, 2013, 2:22pm

Hi,

Thanks for your posting and testing your issue with the latest: Aspose.Cells for Reporting Services v1.9.0.12 (MSI).

We have logged your comments/feedback in our database against the issue id: SSRS-40074.

Please spare us sometime, we will soon get back to you.

shakeel.faiz · January 14, 2013, 3:40am

Hi,

Thanks for your posting and using Aspose.Cells for Reporting Services.

We cannot find the SUMIF function in your report define file, so the report define file could show the SUMIF formula on the VS/UI /Web.

We have checked your provided report define file.

If you want SUMIF function at the table footer, we advise you to try setting =SUM(K9:K9) directly.

gaparna · January 14, 2013, 7:08am

Hi,

Thank you for looking into the issue. We are attaching a snapshot of where the SUMIF is located at. There are 2 instances where the traditional SUM would not work.

In the example given here , we are trying to sum the values from left to right on the same data row and not the footer. We need this only because, when we hide the columns through Modify Attribute, the addition of column values through C{r}+D{r}+E[r}+....., does not calculate correctly because , after the hide , the columns are shifted left and it would lose some of the values that it would have summed up if not hidden. For example, if the column B is hidden, now C is shifted to B , and hence the value of C is lost as the value of D is now shifted to the column C.

The other scenario which we had described earlier in the thread was for the footer. We are able to use SUM(N9:N9) for a group footer successfuly. We have a second footer which is a sum of all the groups and if we use the same formula, it would only sum the last group and not all of the groups. So , we had used a SUMIF to find a string TOTAL and get the corresponding SUM totals to get the Grand Total.

If there are better and working ways, we would definitely use that. It was just a work aorund. Please let us know if there are other ways to accomplish it.

Thanks,

Aparna

shakeel.faiz · January 14, 2013, 8:10am

Hi,

Thanks for your feedback and providing us further information.

We have logged your comments in our database for investigation. We will look into your issue and get back to you asap.

Once, the fix is available or we will have some update for you, we will share it with you asap.

gaparna · January 14, 2013, 10:04pm

Hi,

Thank you for looking into it. We purchased Aspose for the usage of formula's and it's becoming very critical we deliver the product soon. We would appreciate if we could get this working as early as possible atleast the interim solution of SUMIF. But we would need the original issue being solved where we would have a scenario of hide column between 2 formula's and hence the circular reference error. We truly appreciate your timely help.

Thanks,

Aparna

shakeel.faiz · January 14, 2013, 11:56pm

gaparna:

Hi,

Thank you for looking into it. We purchased Aspose for the usage of formula's and it's becoming very critical we deliver the product soon. We would appreciate if we could get this working as early as possible atleast the interim solution of SUMIF. But we would need the original issue being solved where we would have a scenario of hide column between 2 formula's and hence the circular reference error. We truly appreciate your timely help.

Thanks,

Aparna

Hi,

Thanks for your comments and feedback. We appreciate that you have purchased Aspose.Cells for Reporting Services.

We have also logged your comments in our database. We will look into your rest of issue and help you asap.

amjad.sahi · January 17, 2013, 2:21am

Hi,

Please try the latest version: Aspose.Cells for Reporting Services v1.9.0.15.

We have fixed that dynamic formulas calculation error when hiding column(s).
We have fixed that the SUM of footer only sums the last group and not all groups. But the sum of footer sums all details.

Please let us know run result.

If you still face the issues, please post your report file here.

We will check it ASAP.

gaparna · January 17, 2013, 4:54pm

Hi,

Thank you for looking into it, we are still under the process of testing. On the initial note , the dynamic calculation of the formula and hide column issue seems to be working.

However, the totals on the group and the grand total does not seem to function right. We have two footer rows , one to sum up the group totals and the grand totals to sum up all the group totals. These formulae worked right on the excel and it was just displaying the formula on the UI, but now it seems to not work on both the places. We had used &=&=SUM(N9:N9) for the group totals and for the grand totals , we had used SUMIF function to find the string Totals in B9 and add N9's. We did not change any of the formula here.

We were not sure , if we had to change this to something else after the fix or the same formula would work as such. Please let us know , if we should be treating it differently or how it would work ?

Thanks,

Aparna

amjad.sahi · January 18, 2013, 1:08am

Hi,

The flag(&=&=) is the flag of dynamic formula. e.g. &=&=N{r}+M{r}+L{r}

The flag is the flag of expression. e.g. &=Inspections.Total_Time_Hoursa &=SUM(Inspections.WEEK1)

So, you may use &=SUM(Inspections.WEEK1) for the group totals and for the grand totals.

If you want to use Excel SUM function in exported Excel report, you can directly set =sum(K9:K9) for the totals

(see the screen shot below)

We post here our updated report define file and Exported Excel file in the attached archive. Please refer to it.

Thank you

gaparna · January 18, 2013, 5:25pm

Hi,

We did some more testing , as we wanted the excel formula for the Group and Grand totals on the exported excel, we used =Sum(k9:k9) for both the columns. It does work displaying data on the UI and displaying the formula on Excel. However, the SUM RANGE on the formula for the groups don't seem to be accurate. We had a few groups, and the groups which had fewer rows like 10 or so, did get the range correctly. For the groups that had larger row counts, the range is off. It's off , for both the group totals and the Grand totals. Is there any range limit on the groups ?

May you please assist us here.

Pleae find attached screenshot. In the example, the start range is correct , but the end range is off. Instead of 3514, it is 3507.

Thanks,

Aparna

amjad.sahi · January 22, 2013, 2:24am

Hi,

We have found the issues for total SUM rows error and fixed them.

Please try the fixed version : Aspose.Cells for Reporting Services v1.9.0.16.

Please let us know your run result.