Data Truncation in SSRS from Stored Proc.
HI, I am calling SP into SSRS, The SP has one field, it generates XML code. I need to shred the XML data into rows but somehow my SP pulls only little amount of data. When I executed same SP in Management Studio, I am able to get all XML code. Is there any way to shred XML data in SSRS coming from Stored Proc. Please let me know if any body had similar issue before. Thank you.
August 31st, 2011 3:31pm

Hi Neo_Deep, I had replied to your thread you posted. To assist you to solve your issue, I would demonstrate you step by step, I have tested it in my testing environment, and it works fine. Please refer to the steps below: Create procedure in your database. CREATE PROCEDURE [dbo].[Usp_Personaddress] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here Select* From Person.Address for xml raw,type,root('Rows') END Create Dataset to retrieve Xml code. 1. Right-click the datasouce, select Add Dataset . 2. Name it as ds_Xml, specify the Query Type as Stored procedure and Procedure name as Usp_Personaddress. 3. Click Ok. Create xml data source. 1. In the Report Data, right-clickData Sources. 2. Then click Add Data Source. 3. In the Shared Data Source Properties dialog, name it as dataSource_Xml, select type XML, and leave the connection string blank. 4. Go to Credentials tab, check Use Windows Authentication (Integrated Security). Create xml Parameter. 1. In the Report Data, right-click Parameters, select Add Parameter. 2. Name it as para_Xml, check the Internal item below Select parameter visibility category. 3. Switch to Default Values tab, Check the Get values from a query item, specify the dataset to ds_Xml, value to the Xml Code filed (such as ID). 4. Click ok. Create dataset to show on report body. 1. Right-click the dataSource_Xml, select Add Dataset. 2. Name it as ds_Data, keep the Query type to Text. 3. Click the fx button right adjacent to the Query textbox, type in the expression below: ="<Query><XmlData>"+Parameters!para_Xml.Value+"</XmlData></Query>" 4. Switch to Fields, Click Add to add the fileds you want to query, and then click ok. Field Name Field Source AddressID AddressID AddressLine1 AddressLine1 City City … … I hope this can help you, if you have anything unclear, please feel free to let me know. Thanks, Bill Lu Please remember to mark the replies as answers if they help and unmark them if they provide no help.If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2011 4:55am

Hi Bill Lu, Thanks a lot for reply. It worked. I am able to bring XML Data from SP to SSRS. But I am loosing some data in some columns. I am getting waring: [rsMissingFieldInDataSet] The dataset ‘XMLFields’ contains a definition for the Field ‘City’. This field is missing from the returned result set from the data source. [rsErrorReadingDataSetField] The dataset ‘XMLFields’ contains a definition for the Field ‘City’. The data extension returned an error during reading the field. There is no data for the field at position 8. To avoid above warning I used following function: Function NullAsZero(ByRef F As Field) As Double If F.IsMissing Then Return 0 Else If F.Value Is Nothing Then Return 0 Else Return F.Value End If End If End Function Then I got bellow warning: [rsRuntimeErrorInExpression] The Value expression for the textrun ‘City.Paragraphs[0].TextRuns[0]’ contains an error: Unable to cast object of type 'System.String' to type 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Field'.
September 6th, 2011 10:36am

Hi Bill Lu This is my actual data: <Materials> <item id="88" name="I88"> <Materialtype id="09" name="Rubber" /> <MaterialStatus id="21" name="In complete" /> <StateGroup id="31" name="cvs" /> <TotalAmount>0.0000</TotalAmount> <MaterialCost>0.0000</MaterialCost> <LaunchDate>2013-06-01T00:00:00</LaunchDate> <CostPerUnit>0.0000</CostPerUnit> <MCost>0.0000</MCost> <NumOfUnits>0.0000</NumOfUnits> <NumOfProducts>0</NumOfProducts> <MDCost>0.0000</MDCost> <OverHeadCost>0.0000</OverHeadCost> <RegulatoryCost>0.0000</RegulatoryCost> <OtherCost>0.0000</OtherCost> <CostComplete>0</CostComplete> <Brand>Reebok, Nike (USA)</Brand> <Attachment>0</Attachment> </item> <item id="95" name="P95"> <MaterialType id="77" name="Plastic" /> <Buy id="21" name="Micer" /> <MaterialStatus id="10" name="Launched" /> <StateGroup id="32" name="EMS" /> <Response id="110" name="No-Way" /> <TotalAmount>0.0000</TotalAmount> <Cost>0.0000</Cost> <Decision id="81" name="NP" /> <MaterialClass id="41" name="New" /> <LaunchDate>2019-02-20T00:00:00</LaunchDate> <FileDate>2012-01-24T00:00:00</FileDate> <Rationale id="10" name="Map 5" /> <CostPerUnit>0.0000</CostPerUnit> <MCost>0.0000</MCost> <NumOfUnits>0.0000</NumOfUnits> <NumOfProducts>0</NumOfProducts> <MDCost>0.0000</MDCost> <OverHeadCost>0.0000</OverHeadCost> <RegulatoryCost>0.0000</RegulatoryCost> <OtherCost>0.0000</OtherCost> <CostComplete>0</CostComplete> <Category>4 – Maya Mfg.</Category> <Brand>Nike (USA), Reebok (UK), Puma (AUS)</Brand> <Attachment>0</Attachment> </item> </Materials> I tried your method, but in some columns I am not getting data at all(Just blank). I think it is due to two attributes in some elements. Is there any way solve this? Please let me know. Warnings I got: [rsMissingFieldInDataSet] The dataset ‘XMLFields’ contains a definition for the Field ‘MaterialStatus’. This field is missing from the returned result set from the data source. [rsErrorReadingDataSetField] The dataset ‘XMLFields’ contains a definition for the Field ‘MaterialStatus’. The data extension returned an error during reading the field. There is no data for the field at position 8. To avoid above warning I used following function: Function NullAsZero(ByRef F As Field) As Double If F.IsMissing Then Return 0 Else If F.Value Is Nothing Then Return 0 Else Return F.Value End If End If End Function Then I got bellow warning: [rsRuntimeErrorInExpression] The Value expression for the textrun ‘MaterialStatus.Paragraphs[0].TextRuns[0]’ contains an error: Unable to cast object of type 'System.String' to type 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Field'.
Free Windows Admin Tool Kit Click here and download it now
September 6th, 2011 11:52pm

Hi Neo_Deep, I too had a similar situation wherein i tried to use element path to solve this. You might not get data into columns which have id and name because we have element nodes "item, Materialtype and Material status" all of which are having the same attributes id and name. So try to keep an element path like below to get data from all attributes: <ElementPath IgnoreNamespaces="true"> Materials{}/item{@id,@name,MaterialStatus{@id, @name}, Materialtype{@id, @name},StateGroup{@id,@name},TotalAmount,MaterialCost,Buy{@id,@name},LaunchDate,CostPerUnit,MCost,NumOfUnits,NumOfProducts,MDCost,OverHeadCost,RegulatoryCost,OtherCost,CostComplete,Brand,Attachement,Decision{@id,@name},MaterialClass{@id,@name},FileDate,Rationale,Category}</ElementPath> Please refer to this link if you need help regarding elemental path:- http://msdn.microsoft.com/en-us/library/ms365158.aspx had similar question at :http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/7be34661-e90f-4f8c-b914-e21647d0af6d Thanks Raj Lanka
September 12th, 2011 1:01pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics