Query to help create RDL

Usually, I would use the SSRS wizard to quickly create my table with all my fields. In this case I have to add more fields onto an existing report that has my custom formatting of each column (eg. showing/hiding columns).

So for a quick solution, I developed this ad hoc query that I can then use to pull the fields I would need to add from a view into the report. This ends up being a lot of copy & paste actions into the xml (view code) of the rdl.

 

CREATE PROCEDURE [hrr].[usp_RDL_ColumnField_XML]

(

       @view varchar(50)

)

AS

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;

 

       --DECLARE @view varchar(50)

       --SET @view = 'V_EVALUATIONS'

      

--COPY & PASTE INTO DATASET TO ADD MORE FIELDS

SELECT

' + c.NAME  + '">

       ' + c.NAME  + '

       ' +

       CASE t.NAME

       WHEN 'varchar' THEN 'System.String'

       WHEN 'int' THEN 'System.Int32'

       ELSE 'true'

       END +'

' as 'COPY & PASTE INTO DATASET TO ADD MORE FIELDS'

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

 

---COPY & PASTE INTO COLUMN SECTION  --

SELECT

'

       1.5in

' as 'COPY & PASTE INTO COLUMN SECTION  -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

 

 

---COPY & PASTE INTO FIRST TABLIX CELLS HEADER --

SELECT

'

      

              + c.NAME + '">

                     true

                     true

                    

                          

                                 

                                        

                                                ' + REPLACE(c.NAME,'_',' ') + '

                                               

                                        

                                 

                                 

                          

                    

            Textbox' + c.NAME + '

           

                          

                           SteelBlue

                           2pt

                           2pt

                           2pt

                           2pt

                    

             

      

' as 'COPY & PASTE INTO FIRST TABLIX CELLS HEADER -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

 

---COPY & PASTE INTO FIRST TABLIX CELLS Rows --

SELECT

'

      

              +c.NAME+'">

                     true

                     true

                    

                          

                                 

                                        

                                                =Fields!'+ c.NAME + '.Value

                                               

                                        

                                 

                                 

                          

                           2pt

                           2pt

                           2pt

                           2pt

                    

             

      

' AS 'COPY & PASTE INTO FIRST TABLIX CELLS Rows -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

 

 

--- COPY & PASTE INTO Tablix Column Hierarchy section --

SELECT

'

      

              =IIF(INSTR(JOIN(Parameters!fields.Value,","),"' + b.NAME + '.' + c.NAME+'") > 0,FALSE,TRUE)

      

' AS 'COPY & PASTE INTO Tablix Column Hierarchy section -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

END

 

 

GO

 

Reminder: Bulk insert through a store procedure

Ok, my problem was I had a large collection of item; where each item is suppose to be inserted in a database and I didn't want to do a bulkcopy.

This is accomplished by using XML in the store procedure and sending it in through the ADO.Net

In the C# code:

//Make xml
using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["connectionString"]))
{
   SqlCommand cmd = new SqlCommand("insBulkFTSEDCFileDetail", conn);
   cmd.CommandType = CommandType.StoredProcedure;
   StringBuilder sb = new StringBuilder();
   sb.Append("\r\n");
   foreach (oEDCItem item in edc)
   {
      sb.AppendFormat("\r\n",
      item.ModuleName, item.PartNumber, item.SerialNumber, item.TestPosition, item.SupplierName, item.EDC, item.Opt);
}
   sb.Append("");

   cmd.Parameters.AddRange(new SqlParameter[] {
   new SqlParameter("@EDCFileID", EDCFileID),
   new SqlParameter("@LastUpdateUserID", UserID),
   new SqlParameter("@XMLDOC", sb.ToString())});

   conn.Open();
   cmd.ExecuteNonQuery();
   conn.Close();
}



The stored procedure:

Create Procedure insBulkFTSEDCFileDetail
{
   @EDCFileID int,
   @LastUpdateUserID int,
   @XMLDOC varchar(MAX)
}
AS

DECLARE @xml_handle int

EXEC sp_XML_preparedocument @xml_handle OUTPUT, @XMLDOC

INSERT INTO [TABLE]
(EDCFileID,
ItemType,
Model,
SerialNumber,
EDCPosition,
SupplierName,
EDC,
Options,
LastUpdateDate,
LastUpdateUserID)

SELECT @EDCFileID,
EDCXML.ItemType,
EDCXML.Model,
EDCXML.SerialNumber,
EDCXML.EDCPosition,
EDCXML.SupplierName,
EDCXML.EDC,
EDCXML.Options,
getUTCDate(),
@LastUpdateUserID
FROM OPENXML( @xml_handle, '/EDCItems/EDCItem')
WITH ( ItemType varchar(8),
Model varchar(50),
SerialNumber varchar(50),
EDCPosition varchar(50),
SupplierName varchar(50),
EDC varchar(50),
Options varchar(80)) AS EDCXML

EXEC sp_XML_removedocument @xml_handle

MS ASP.Net assessment Question: Verify XML from Schema

You load an XML document into an instance of the XmlDocument class. You add elements to it.

You need to validate the changes that you made to the XML document against a schema. You do not want to create a new instance of the Document Object Model (DOM).

What should you do?

A) Create an XmlNodeReader by using the XML document. Set the validation settings on the XmlReaderSettings object. Create a validating reader that wraps the XmlNodeReader object.
B) Save the contents of the XML document to the file system. Create a new instance of the XmlDocument class. Call the Load method, and pass in the file path of the recently modified XML document.
C) Create an XmlNodeReader by using the elements of the XML document that changed. Set the validation settings on the XmlReaderSettings object. Create a validating reader that wraps the XmlNodeReader object.
D) Save the contents of the XML document to a string. Create a new instance of the XmlDocument class. Call the LoadXml method, and pass in the string representation of the recently modified XML document.

A- seems to be correct, the XmlReaderSettings allows for setting the schema to be used and you can then just use a XmlReader that would use that. I am assuming that the XmlNodeReader could do the same.
B- would work, but seems excessive amount of time to accomplish.
C- Same as A, but only looks at the newly created nodes, this seems to be the quickest. The XmlNodeReader is suppose to keep track of where in the schema the current not would be referencing.
D- inefficient, holding xml in string is probably not the best choice.

A good way of doing this was found on MSDN: http://support.microsoft.com/kb/318504/