Multiplying result by a cell

Aspose.Cells Product Family
1

Hello,

I have tried all possible combinations but cannot seem to get this simple calculation to work.

using Aspose for SSRS I am testing to see if templates can be built in Excel

I am returning data in cell D9 and I would like E9 to return a value through Aspose and multiply it by D9

I have tried the following without success:

&=&=Score.REV_SCORE1*D9
&==Score.REV_SCORE1*D9
&=&=Score.REV_SCORE1*D{r}
&==Score.REV_SCORE1*D{r}
&==First(Score.REV_SCORE1)*D{r}
&=&=SUM(Score.REV_SCORE1)*D{r}
&=&=First(Score.REV_SCORE1)*D{r}

Any help would be appreciated,

thanks,

Charlies
What would be the correct way to write the formula?

1 Like
2

@Charlie.R

Thanks for using Aspose APIs.

We have logged your issue (i.e. CELLSRS-523) in our database for investigation. We will evaluate it and help you in using proper ways or combinations of the formulas and its calculation. Once, we will have some news for you, we will update you asap.

3

@Charlie.R

Thanks for using Aspose APIs.

Please provide us your report definition file. We need it to continue looking into this issue further. Thanks for your cooperation in this regard and have a good day.

4

@shakeel.faiz please find a sample file and output attached.

Ultimately I would like to be able to decide on the Excel formula to insert programmatically, if I have a certain flag in the DB then add *D9 otherwise leave returned figure as is.

thanks,

CharlieCalc.zip (27.7 KB)

5

@Charlie.R

Thanks for your posting and using Aspose APIs.

If you want to ask some question related to programming in C# or Java with

  • Aspose.Cells for .NET
  • Aspose.Cells for Java

You may ask it in separate thread as this thread is relating to

  • Aspose.Cells for Reporting Services

We have recorded your comment and concerns for evaluation and resolution. Once, we will have some more news for you, we will share it with you asap.

6

@shakeel.faiz I can confirm I would like to do this with SSRS and ASpose.cells.

basically maybe use the iif function to do formula concatenation and insert the formula if needed?

7

@Charlie.R

You meant

  • Aspose.Cells for Reporting Services

Please spare us some time, we will update you tomorrow or in next few days. Thanks for your patience and have a good day.

8

@Charlie.R

We have checked your report definition file.

All forms are erroneous (containing errors)

&=&=Score.REV_SCORE1D9
&==Score.REV_SCORE1D9
&=&=Score.REV_SCORE1D{r}
&==Score.REV_SCORE1D{r}
&==First(Score.REV_SCORE1)*D{r}
&=&=SUM(Score.REV_SCORE1)*D{r}
&=&=First(Score.REV_SCORE1)*D{r}

D{r} is repeat dynamic formula. It must be in table report item and D is column index.

But there is not a table or tablix report item in your report.

Please refer to Sales Order Detail 2008.rdl about repeat dynamic formula.

We have uploaded a Main Features.zip. There are three report definition files in Main Features.zip.

Report definition files show these main features:

  • Kinds of Table
  • Matrix
  • Chart/Pivot Table
  • Repeat Dynamic Formula

Download Link:
Main Features.zip (107.4 KB)

9

@shakeel.faiz

thank you for your response, I had a look at the attached file but cannot find an example that answers my question above.

I am aware all the formulae are wrong, I was just putting them as a example of what I had already tried. I am not trying to return a tablix or repeating formula, I just tried all possible methods.

In you attached file you do not actually have a single cell with Excel formulae, you are only returning information straight from the server.

If my above post was not clear the expected result is as follow:

Step 1 (basic) return a value from DB (in the exemple I attached 0.5) display this value as an Excel formula and multiply by the cell next to it so =0.5 * D9 no tablix no recurrence nothing.

Which is why I tried the First(code) and a way to then concatenate text to return " *D9 "

The Step 2 (more advanced) is I only want to add the concatenation if a certain condition is met &iif(blah condition=true,"*D9","")

thanks,

Charlie

10

@Charlie.R

Please see if the following information is useful for you. Let us know your feedback.

You cannot directly find the flag of repeat dynamic formulas in report definition file.

The flag and some import information are wrote into custom property of report definition file.

So you need to use Aspose.Cells for Reporting Services Client to open report definition file in MS Excel.

Please see the following screenshot. It shows, how Aspose.Cells for Reporting Services Client opens Sales Order Detail 2008.rdl in MS Excel.


rdl.jpg (163.0 KB)

11

@shakeel.faiz I was able to open the templates in Excel, I was saying that they do not answer my question.

I am NOT trying to do a repeat formula.

I would just like to know the correct code to return =0.5*D9 where 0.5 comes from SQL and D9 is hardcoded. It is a static formula always in the same place.

even in your template example you do not have a repeat formula that answers the above question, I am not trying to return =C9*D9 like in your repeating formula, I am trying to mix the results of a query with an excel reference

thanks

12

@Charlie.R

We have recorded your comments and concerns. We will evaluate them and help you. Once, we will have some update for you, we will share it with you asap.

13

@Charlie.R

Thanks for using Aspose APIs.

We created a report definition file. Please check it. i.e. CELLSRS523.rdl

  1. Build Data Set.

  2. Add report element in MS-Excel.
    …
    The value of E4 cell is &=testDSet.amount. The value of F4 cell is 90.
    The value of G4 cell is =E4*F4, but the cell displays #VALUE!.

  3. Save and deploy report definition file.

  4. Check report definition file and find that there are only two textboxes in report definition file. G4 cell was not created in report definition file. But it was saved to the custom property of report definition file.

  5. Export report and find that there are three cells in exported Excel file.

Download Link:
CELLSRS523.zip (7.4 KB)

14

@shakeel.faiz

I took a look at your example but it is not exactly what I am after. I do not wish to have the source data sitting in a column and just using excel to do the math.

In the case of your example I would expect only 2 cells, one with 90 and one with &=testDSet.amount * F4

thanks,

Charlie

15

@Charlie.R

At present, Aspose.Cells for Reporting Services does not support &=testDSet.amount * F4.

16

@shakeel.faiz would you be able to add it as an enhancement?

17

@Charlie.R

We have recorded your comment in our database for its consideration/feasibility and implementation. Once, we will have some update for you, we will share it with you asap.

18

@Charlie.R

We will work on your suggested enhancement, however it is a bit difficult for us at the moment. We need to evaluate it thoroughly. We will update you in next couple of weeks.

19

@shakeel.faiz thank you

20

@Charlie.R

We have added New Feature for CELLSRS-523.

At present, Aspose.Cells for Reporting Services supports

&=testDSet.amount * F4

in static textbox.

And only supports following four operators (±*%).

&=testDSet.amount + F4 
&=testDSet.amount - F4 
&=testDSet.amount * F4 
&=testDSet.amount % F4

Please try the fixed version i.e.

The version supports 32 bit MS-Office.

If your Excel is 64 bit, you need to re-register Aspose.Cells for Reporting Service Designer after you have installed fixed version (i.e. 18.2.3).

Please refer to re-register command.

C:\Windows\Microsoft.NET\Framework64\v2.0.50727\RegAsm.exe "C:\Program Files (x86)\Aspose\Aspose.Cells for Reporting Services\Bin\Aspose.Cells.ReportingServices.Client.dll" /tlb Aspose.Cells.ReportingServices.Client.tlb /codebase