Dynamic Formula and Table Footers

I’m in the process of evaluating Aspose.Cells for Reporting Services as a replacement for a bunch of reports that are currently designed using numerous 3rd party macros. One of my requirements is offering the ability to export an SSRS to Excel and to retain the formulas. I see that the product does indicate this works by using DynamicFormulas.


Here are my questions:
1) I need to ensure SUM functions are retained in the report. I have been trying to this by modifying your “Sales Order Detail” sample report (Total Discount and Total boxes). So far, I have been unsuccessful. Is this possible and can you explain how?
2) The Dynamic Formulas are great but don’t work when viewing the report on the SSRS report site. Is there any way to have the report display one value on the site and another when rendered in Excel?
3) Is it possible to apply a data filter on a group?

Thanks,
Mike

Hi,

Thanks for your question.

Can you please provide us your sample RDL file and PDF export to look into this issue? Some screenshots will be helpful, if you encircle with red lines the problematic areas inside your report.

I’m using the “Sales Order Detail 2008.rdl” file located in your samples folder. My primary concern is the the totals at the bottom of the report. I tried converting them to dynamic formulas (&==SUM(U22:U{-1})) but this is not working (I highlighted the cells in the attached spreadsheet).


The PDF and interactive rendered reports all have the subtotal dynamic formulas without calculation. I understand if this is a limitation but was hoping for a workaround. My user’s primary concern is with Excel and retaining the formulas.

Hi,

Thanks for your input and files. I have logged this issue in our database. We will update you asap.

This issue has been logged as CELSSREP-27795.

Hi,

We have checked your issue.

At first, please read User Guide online.

If you want to modify report which is created by use Aspose.Cells.Report.Designer, you must use Aspose.Cells.Report.Designer to modify it.

Secondly, please post your modified Sales Order Detail report define file.

Please see:

1. SUM function is integrated with Table report item (Tablix). So the SUM field must be inside table and use in conjunction with footer flag.

2. Now, it does not have the Dynamic Formulas convert function form ACRS report to SSRS report. Do you need it?

3. Now it does not have the function. We will consider that how to support the function .

Thanks for the quick replies. Now I feel stupid… as I was recreating the report to provide you with an example, I suddenly got the sum to work correctly. I figured I was doing something wrong… just wish I knew what. I’ll make sure I read the user guide more thoroughly on this subject.


Now that the critical issue is out of the way, can you set a filter on a dataset/table? In other words, I have a dataset that returns totals for a bunch of account numbers. I would like to create a table in the spreadsheet that only shows values from a specific account group. I need to try and do this without relying on additional grouping in the table due to design layout. This would be similar to the filter tab in the SSRS group options. I know I could create multiple datasets but then performance becomes an issue.

With regard to the data displayed in the report site/pdf, I know it’s a difficult function to include. It would be great if the formula would convert to a report function so the data could be displayed correctly in both renderers. Perhaps a function that allows specifying two formulas to use… one for Excel and one for SSRS? I’ll leave that for you to decide. Like I said, this is more of a “nice to have”. My boss wasn’t concerned about that (it bothered me more than him… I’m a perfectionist).

Hi,

Thanks for your input.

We have logged your requirements and will update you asap.

Hi,

We have fixed the issues for Dynamic Formulas.

Please download Aspose.Cells for Reporting Services v1.7.0.28 (MSI).

We plan to spend designing and developing group filter function on one and half months .

Hi,

We had add the feature for filter.

Please try the fixed version Aspose.Cells for Reporting Services v1.7.0.30 (MSI).

Please read the attached file how to configure filter manually.

The fixed version is a demo version which we need to improve continuously.

So if you find any question, please inform us, we will check and fix it ASAP.

Hi,

Please find attached the .docx file on how to configure filters manually.
I think Shakeel Faiz has missed it.

Thank you.

Thanks for the quick updates. I will give them a try.

The issues you have found earlier ( filed as SSRS-xxxxx ) have been fixed in this update.