Use SSRS LookUp function with ASPOSE.CELLS

Charlie.R · January 23, 2018, 9:51pm

Hello,

I have an excel model with preset formats and non consecutive cells to fill in various places. Is it possible to insert a particular row of the dataset based on the content of another cell?

For example, if a cell has text: abcd I would like to insert in a nearby cell the results of a particular sql column where the key for the dataset row is abcd.

Thanks,

Charles

shakeel.faiz · January 24, 2018, 6:00am

@Charlie.R

Thanks for using Aspose APIs.

Please provide us your screenshots, your input Excel file(s) and your expected output Excel file. We will look into your issue and help you asap.

Charlie.R · January 24, 2018, 6:35am

Hi Shakeel,

I do not have any samples yet as I do not know how to code it. to simplify, imagine I am doing a VLOOKUP but based on data returned by the data set.

So data set is:
COL1 COL2
a 10
b 20
c 30
d 40
e 50
f 60

If cell A1 has “b” I would like my inserted data in B1 to be 20

hope it clarifies things.

thanks,

Charles

shakeel.faiz · January 24, 2018, 3:19pm

@Charlie.R

Aspose.Cells does support VLOOKUP function. I think, your requirements are best achieved using VLOOKUP function.

Please see the following sample code, its input Excel file, output Pdf file and screenshot for a reference.

Download Link:
Sample Excel and Output Pdf File.zip (17.4 KB)

C#

Workbook wb = new Workbook("sampleVlookup.xlsx");

Worksheet ws = wb.Worksheets[0];

Cell d1 = ws.Cells["D1"];
d1.PutValue("e");

wb.CalculateFormula();

wb.Save("output.pdf");

Screenshot:

Charlie.R · January 24, 2018, 3:39pm

Hi @shakeel.faiz

I am not trying to insert a Vlookup, I am trying to apply a match, or the equivalent of the SQL WHERE clause to one particular Excel cell.

I am actually evaluating purchasing ASPOSE.CELLS so that I can simply plug the numbers I need rather than have to download all the data within a tab and use a vlookup (in which case I don’t need aspose and can just embed the query in Excel)

Are you thus saying that ASPOSE.CELLS is not able to insert a specific row of data from a query that returns an array within an Excel cell?

thanks,

Charles

shakeel.faiz · January 24, 2018, 3:42pm

@Charlie.R

Aspose.Cells can find the row based on your data. And once you find your needed row, you can extract data from that row. You only need to find the row index.

Please see this article that explains how you can find/search data. You can also restrict that it should only search specific column or specific range.

Find or Search Data

Charlie.R · January 24, 2018, 3:57pm

@shakeel.faiz

The documentation is about finding row within an Excelsheet no? Or am I reading this wrong?

Please also note that I am not using .NET I am only using Excel and would like to insert data within a workbook.

so using the example above, my query returns 6 rows, what function do I need to insert in Excel so that Aspose will insert the value in COL2 where COL1=“e”

thanks,

Charles

shakeel.faiz · January 25, 2018, 3:40am

@Charlie.R

This looks like a Microsoft Excel question and it is not related to Aspose.Cells. You can post this query in Stackoverflow or any such website. However, Aspose.Cells supports most of the Excel formulas. If you are able to find the needed formula than most probably, it will also be supported by Aspose.Cells APIs.

Charlie.R · January 25, 2018, 6:30am

@shakeel.faiz

As Excel does not integrate in SSRS I believe this would definitely be an Aspose question.

After running more tests it would seem that I would need to use =FIRST with an embedded IIF function but I cannot get it to work with Aspose.

=First(IIF(Fields!name.Value=“something”,Fields!Data.Value,Nothing),“DATASET”)

trying this with Aspose in Excel I enter

&=IIF(BALACC_DATA.BALACC_KEY=“20.135”,BALACC_DATA.BALACC_VALUE,Nothing)

but it does not seem to recognize the true argument:

ASPOSE Alert @@  : Thursday, 25.January 2018 07:27:45 : The Value expression for the textrun ‘D01bal_0_3.Paragraphs[0].TextRuns[0]’ contains an error: [BC30455] Argument not specified for parameter 'TruePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Aspose.Cells.ReportingServices.Services.ReportingService2010.ReportingService2010.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, Warning[]& Warnings)
   at    .(String , String , Boolean , Byte[] ,     )
   at    .(String , String , Boolean , Byte[] ,     )
   at    .     ()

Charlie.R · January 25, 2018, 9:26am

After some more investigating it would seem that my first instinct was correct in saying I needed a Lookup function.

The following seems to give me what I want in SSRS

=LookUp(“20.105”,Fields!BALACC_KEY.Value,Fields!BALANCE.Value, “BALACC_DATA”)

I can’t seem to write it in such a way that it works in Excel though:

I tried: &=LookUp(“20.105”,BALACC_DATA.BALACC_KEY,BALACC_DATA.BALANCE, “BALACC_DATA”)

ASPOSE Alert @@  : Thursday, 25.January 2018 10:23:10 : The Value expression for the textrun ‘Sheet1_0_3.Paragraphs[0].TextRuns[0]’ has an incorrect number of parameters for the function ‘LookUp’.
   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Aspose.Cells.ReportingServices.Services.ReportingService2010.ReportingService2010.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, Warning[]& Warnings)
   at    .(String , String , Boolean , Byte[] ,     )
   at    .(String , String , Boolean , Byte[] ,     )
   at    .     ()

shakeel.faiz · January 25, 2018, 1:20pm

@Charlie.R

Thanks for using Aspose APIs.

There are actually two Aspose.Cells APIs.

Aspose.Cells for .NET
Aspose.Cells for Reporting Services

It seems, you are using the second one i.e. Aspose.Cells for Reporting Services. Please confirm, so that we log your issue for product team analysis and investigation. Thanks for your cooperation in this regard and have a good day.

Charlie.R · January 25, 2018, 1:33pm

@shakeel.faiz

This is correct, I am using the version for reporting services (SSRS)

thanks,

Charles

shakeel.faiz · January 26, 2018, 1:57am

@Charlie.R

Thanks for using Aspose APIs.

We have logged your issue in our database for product team consideration and investigation. We will look into it and update you. Once, there is some fix for you or any other news for you, we will update you asap.

This issue has been logged as

CELLSRS-521 - Use SSRS LookUp function with Aspose.Cells for Reporting Services

shakeel.faiz · January 30, 2018, 2:07am

@Charlie.R

We have tested the Lookup function. Please refer to Book1.rdl. We use Aspose.Cells for Reporting Services 17.8.8.

Download Link:
Sample RDL File.zip (8.2 KB)

Charlie.R · February 1, 2018, 6:37pm

@shakeel.faiz

I think we are halfway there.

I can now use the lookup function in a tablix but not in a textbox.

Now if I use a formula (any formula) outside a tablix it gets sent back as raw text (the formula without &=)

If I edit the file in Report Builder I am able to get the code to work in a textbox

   <Textbox Name="Textbox1">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>=Lookup("20.105" , Fields!BALACC_KEY.Value , Fields!LATEST.Value , "local")</Value>
                <Style />
              </TextRun>
            </TextRuns>
            <Style />
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>Textbox1</rd:DefaultName>
        <Top>0.63417in</Top>
        <Left>9.69667in</Left>
        <Height>0.66667in</Height>
        <Width>2.19792in</Width>
        <ZIndex>1</ZIndex>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>

Whereas in your version the formula is simply stripped:

      <Textbox Name="Sheet1_0_4">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>Lookup("20.105" , local.BALACC_KEY , local.BALACC_NAME , "local")</Value>
                <Style>
                  <FontFamily>Calibri</FontFamily>
                  <FontSize>11pt</FontSize>
                  <Color>#000000</Color>
                </Style>
              </TextRun>
            </TextRuns>
          </Paragraph>
        </Paragraphs>

I have attached all the examples and screenshots in the file below. Thanks!

lookup.zip (111.7 KB)

shakeel.faiz · February 2, 2018, 2:47am

@Charlie.R

We have logged your feedback and comment and provided files in our database against the logged issue. We will look into them and help you. Once, there is some update for you, we will let you know asap.

shakeel.faiz · March 8, 2018, 3:10pm

@Charlie.R

We have closed this thread as per your post in the other thread. CELLSRS-521 is marked as Resolved now. If you have any question, please feel free to let us know.

https://forum.aspose.com/t/publish-failed-when-template-contains-excel-formulae/170192/26?u=shakeel.faiz

aspose.notifier · November 19, 2018, 6:20pm

The issues you have found earlier (filed as CELLSRS-521) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi