I have an SSRS report with multiple datasets (Orderhed, RptParameter, Company).
When I am in Order Entry, I enter an SO# and select **Actions/Print Sales Order Acknowledgement. To print multiple SO Acks, I select Filter tab on the “Sales Order Acknowledgement Report” screen; and enter 3 order #s in the filter grid. I select the SOFORM2 report style which is customized.
All 3 SO acks have the same SO# for the field. I suspect the FIRST() in the expression “=“Sales Order:” & ChrW(160) & First(Fields!OrderNum.Value,“OrderHed”)” causes this behavior.
The Standard SSRS version of the report has the expression as ‘=Fields!RptLiteralsLORNUM.Value & ChrW(160) & Fields!OrderNum.Value”. Without the First(), the correct SO# prints on each ack.
I thought I could just remove the FIRST() function in the expression, but it errors on save because I am not telling it which dataset for OrderNum. Why does it work in the standard version? Any suggestions? What additional information can I provide to assist with the solution.
Thanks
Tony
The FIRST() function returns the first row of the given dataset. In your example, it would return the first OrderNum found in the OrderHed dataset of the report. In SSRS each tablix is bound to a dataset, the standard version of the report likely has a tablix bound to the OrderHed dataset allowing it to print without a FIRST() or LOOKUP() function.
Aside from having the data in whatever dataset is bound to your tablix, one workaround I have used in the past if you want all the data to be printed would be to use a LOOKUP() which always is true. Something like LOOKUP(1,1,Fields!OrderNum.Value,“OrderHed”). The first two parameters of the lookup are compared to one another to determine whether the lookup should return anything or not, in this case we want everything to be returned, so you can make sure it is always true by setting 1 = 1. Then you have the field you want to return from the lookup and the dataset to take it from, in this case the OrderNum from OrderHed. It’s a bit of a cheat but it should do what you are looking for.
1 Like
Hi Ross, I entered the LOOKUP function as the expression in the text box, but it generated an error on save:
System.Web.Services.Protocols.SoapException: The Value expression for the textrun ‘LblOrderNum3.Paragraphs[0].TextRuns[0]’ has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset.
at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateReport(String Report, String Parent, Boolean Overwrite, Byte Definition, Property Properties, ItemType ItemType, CatalogItem& ItemInfo, Warning& Warnings)
at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte Definition, Property Properties, CatalogItem& ItemInfo, Warning& Warnings)
at Microsoft.ReportingServices.WebServer.ReportingService2010.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte Definition, Property Properties, CatalogItem& ItemInfo, Warning& Warnings)
Not sure if the above helps. What is a tablix?
A tablix is a fancy word for the grid that holds the data. Typically SSRS reports have a header and footer and then the “body” is comprised of one or more tablixes which are used to organize the data of the report.
“Scope parameter” makes me think it doesn’t like your dataset name, maybe the dataset has been changed to be called something other than OrderHed? Maybe you should be pulling the OrderNum from a different dataset? Hard to know without seeing the actual report.
Thanks Ross. I will check with my development partner that built the report.