none
[rsInvalidExpressionDataType] The Value expression used in field returned a data type that is not valid.

    Question

  • I got this error for a calculated field that calls a public function of a custom assembly based on the report parameter values.  The return type is an array of double.

    =code.ovalues.dGetValues(Parameters!Report_Start.Value, Parameters!Report_End.Value, Parameters!Tag.Value, Parameters!Agg.Value)

    Does SSRS 2005 supports the type double for a field?  Can the data source of a field in a dataset be an array? Is there a way to pass the array to a field by reference and display the values at runtime?

    I would appreciate any help since I could not find anything helpful on the internet and Microsoft document!

    Monday, January 15, 2007 9:19 PM

Answers

  • 1. No, you cann't do this from within the report.  The only way to attach a ADO.Net dataset (well, technically a datatable) to an RS dataset is when you're using the report viewer control (in local mode) instead of the server. 

    2.  Of course!  How silly of me... I completely forgot to mention writing your own data extension.  It's actually realtively straightforward since there are only a handful of interfaces you need to define.  In essense, it's a small subset of the ADO.Net interfaces - connection, command, data reader, parameter, transaction (which you can leave as a no-op if you don't need it).   Yes, if you're up for writing it, this would give you the greatest amount of flexibility, long term.

     

    Tuesday, January 30, 2007 8:10 PM

All replies

  • Double is supported, but arrays are not.  You'd need to return a scalar value from your function.

    As an alternative, you could consider calling your function to populate the default value of a multivalue parameter (which does support arrays) rather than as a calculated field.

     

     

    Tuesday, January 16, 2007 10:28 PM
  • Thanks for your reply!

    I added a multivalue and internal parameter of type float (there is no choice of double) and called the function to populate the default value.  Then, I made up a dataset with query string (select 'something' from dual') and assigned this dataset to a new table with one column displaying the multivalued parameter value.  The following error occurred during preview:

    The property of 'Default Value' of report parameter 'ArrayTest' doesn't have the expected type.

    I thought SSRS should be smart enough to implicitly convert double to float or vice versa.  Again, I couldn't find any help from online documentation!  Can you help again?

     

    Wednesday, January 17, 2007 5:32 PM
  • "Float" in RS corresponds to CLR's single, double and decimal, so picking Float is fine.

    There's something else going wrong with multivalue defaults for multivalue parameters... I can't even get strings to work at the moment.  I'll track down someone to assist and let you know what I find out.

     

     

    Friday, January 19, 2007 12:02 AM
  • Yes, it turns out there's a bug in the handling of multivalue defaults for multivalue parameters in the case where you return a single array as the default value.

    Two possible workarounds:

    1.  Don't return an array.  Just have a separate default value item for each value you want.

    2.  Return an array of object rather than an array of doubles.  Depending on how you're getting your original array of doubles, this might require a bit of custom code in the report, so the default value would end up being =Code.GetMyDefaults(), which returns an object array (where each item in the array happens to be a float).

    Keep in mind that you can't just stick a multi-value parameter into a textbox, since a textbox cannot display an array.  To quickly see if your multivalue parameter default is behaving as desired, try putting this into a textbox: =Join(Parameters!MyParameter.Value,", ")

    Monday, January 22, 2007 7:35 PM
  • I appreciate your help!

    What I have been trying to do was getting historical data from an OPCHDA-compliant server for plotting on a chart in SSRS report.  The return types of the values and timestamps are arrays of double and datetime. 

    For for testing purpose, I put an array with hard-coded numbers and return an object array (using Cobj) from a function in a custom VB.Net assembly using your workaround #2.  I was able to return an object array and display the joined default values of the MV parameter in a textbox from the above custom assembly. However, I still couldn't use those values for a field, which would serve as a data source of a line chart in the report. Apparently, an object array is not a valid type for a field.

    How should I use the object array (default values of the MV parameter) or is there any other way to define the data source of a chart besides fields from a dataset? Any related help is welcomed.

    Thanks!

    Monday, January 29, 2007 8:13 PM
  • Oh, I didn't realize you were trying to bind it to a chart...

    I'm afraid charts (and tables and matrixes and lists) can only be bound to datasets and each row in the dataset must correspond to a single data instance.   There are a few other approaches you could explore, however:

    1.  Use the report viewer control instead of the report server.  The report viewer control uses a very different form of data binding than is used in reports published to the report server.  Rather than the server executing queries (via a data provider) on your behalf, you instead populate a .Net DataTable in your application and bind it to the report's DataSet.  Since you populate the DataTable via your own code, you have complete flexibility as to data retrieval methods.

    2.  Use the XML data provider.  If the "OPCHDA-compliant server" exposes a webservice, you could retrieve the data via our XML data provider.

    3.  Custom stored procedure in SQL Server.  I'm unfamiliar with the details of this one, but I believe you could add a stored procedure to your database which calls out to the server to retrieve the data then hands it back as a standard rowset.  This would allow Reporting Services to be blissfully unaware of the fact that there's a non-SQL database providing the data.

     

     

    Monday, January 29, 2007 9:17 PM
  • These are two other possibilities I just found out:

    1) I can populate an ADO.Net dataset with theOPCHDA data in a VB.Net application. Is it possible to reference the reporting services report dataset using something like Reports!Myreport.Dataset!MyDataSet and assign the ADO.Net dataset to it?

    2) By searching some more on the Internet, I found out something about Data Processing Extensions for SSRS.  It seems that I can implement and deploy such an extension for the OPCHDA data, which populates an ADO.Net dataset.  Then, the extension can be selected in the Report Designer as a data source.  Would this method work?

    Thanks!

    Tuesday, January 30, 2007 7:50 PM
  • 1. No, you cann't do this from within the report.  The only way to attach a ADO.Net dataset (well, technically a datatable) to an RS dataset is when you're using the report viewer control (in local mode) instead of the server. 

    2.  Of course!  How silly of me... I completely forgot to mention writing your own data extension.  It's actually realtively straightforward since there are only a handful of interfaces you need to define.  In essense, it's a small subset of the ADO.Net interfaces - connection, command, data reader, parameter, transaction (which you can leave as a no-op if you don't need it).   Yes, if you're up for writing it, this would give you the greatest amount of flexibility, long term.

     

    Tuesday, January 30, 2007 8:10 PM
  • Hi! I am back with more questions. With the help of MSDN and DevX sites, I tried to implement and deploy a data processing extension. However, after I added my extension info to these files: rsreportdesigner.config, rsreportserver.config, rspreviewpolicy.config, and rssrvpolicy.config, I got this error when I was creating a new report using the new extension: "object reference not set to an instance of an object"

    Did I mess up the files or did the error come from the extension code?  For reference, I put something like this in the config files:

    <Extension Name="OPCHDA" Type="myCompany.myProduct.DataProcExtension.DPConnection,DataProcExtension"/>

    something like this in the policy files:

    <CodeGroup class="UnionCodeGroup"

    version="1"

    PermissionSetName="FullTrust"

    Name="DataProcExtensionGroup"

    Description="This code group grants data extensions full trust.">

    <IMembershipCondition

    class="UrlMembershipCondition"

    version="1"

    Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\DataProcExtension.dll"

    />

    </CodeGroup>

    Thanks!
    Tuesday, February 06, 2007 7:31 PM
  • Nothing leaps out at me as wrong with your config entries.  Most likely the error is coming from the extension code.  If the problem were in either of the config files, I believe you'd be getting errors about being unable to load the assembly.

     

    Tuesday, February 06, 2007 7:40 PM
  • I did get loading error in a slightly different scenario: created a shared data source with the extension, and then tried to add a new report using the wizard. The following error showed twice after I selected the new data source and click next without typing in any connection string since there was none: The designer extension XX could not be loaded. Check the configuration file RSReportDesigner.config. Then, the object reference error occurred after I typed in the query string, which was a set of strings separated by comma (as the command text).  When I clicked next, the loading error came up twice again.

    Since the datareader instantiates the business logic object, which returns a dataset, connection is not needed.  When I did not provide a connection string, would it cause error in the connection class? I do not have experience with writing data provider, so please advise.

    Thanks!

     

    Wednesday, February 07, 2007 5:30 PM
  • It is mandatory to implement the connection class, even if you don't use it for anything.  It has a set of mandatory properties and methods which must be implemented, including CreateCommand, which is used to get a command object.  While you're free to have the Open method do nothing other than return success, it must be implemented.  You should verify you have fully implemented all of the mandatory classes, methods and properties.

    I recommend testing this in the report design tool.  That will make it easier for you to attach a debugger to the process (visual studio) to first verify your constructors are being called and then determine if you're running into an exception in your data extension code.

     

    Wednesday, February 07, 2007 7:23 PM
  • The data processing extension finally works. The loading error was due to the failed attempt to load the generic designer for my extension. I found an answer in another thread in this forum, putting an entry of my extension with type of generic designer to the <Designer> section of RSReportDesigner.config as follows:

    <Extension Name="MyExtension" Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/>

    After solving the loading error, I was able to proceed and found a bug in the extension code.

    Thanks for all your help!

    Tuesday, February 13, 2007 8:34 PM