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 discussed your issue with the developments team. We will soon provide you a fix or interim solution.
We will get back to you asap.
Hi,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,
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
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.
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
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.
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.
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
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.
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,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,
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.
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
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
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
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.
Hi,
The sum on the group and the totals work. Thank you for all your support and help. We appreciate it.
Thanks,
Aparna
Hi,