Populate XML Using Table Data

Declare @XML XML

set @XML = ( Select * from pubs..authors Authors FOR XML AUTO, ELEMENTS )

Select @XML

Populate Table Variable Using XML

DECLARE  @tempTable TABLE ( userId INT, userName NVARCHAR(50), password NVARCHAR(50) )

DECLARE  @xml XML

SET @xml=' <row userId="67" userName="Kenny1" password="1234" /> <row userId="80" userName="Kenny2" password="5678" />'

INSERT INTO @tempTable

SELECT Tbl.Col.value('@userId', 'INT'),

Tbl.Col.value('@userName', 'NVARCHAR(50)'),

Tbl.Col.value('@password', 'NVARCHAR(50)')

FROM @xml.nodes('//row') Tbl(Col)



SELECT * FROM @tempTable

Insufficient result space to convert uniqueidentifier value to char

Got the above obscure error message whilst dynamically generating and then executing a case statement where I was casting the fields to varchar using:

'when ''' + c.[name] + ''' then cast(a1.' + c.[name] + ' as varchar)',

The above code was working fine until the column that had been added was a GUID.

Solution:
Simple, specify the length of the varchar!  Now works fine..
'when ''' + c.[name] + ''' then cast(a1.' + c.[name] + ' as varchar(40))',

Creating Dynamic Column List

Scenario:

Wish to query a table to see which rows have changed (basically an audit on the table) so the query involves a self join. The where clause needs to be created based on the columns available in the table - we can do this dynamically...

This solution was also modified to generate a Case statement using the columns in a table dynamically (solution 2)

Solution 1:

Nice piece of code... creates the WHERE clause of a dynamic query using the columns on the table

DECLARE @match_str nvarchar(max)

SET @match_str = '(' + CHAR(10) + '0=1' + CHAR(10)

-- selects our comparison column namesSELECT @match_str = @match_str + 'OR '
+ 'dd.[' + c.[name] + '] != ii.[' + c.[name] + '] OR ' + '(dd.[' + c.[name] + '] IS NULL AND ii.[' + c.[name] + '] IS NOT NULL) OR ' + '(dd.[' + c.[name] + '] IS NOT NULL AND ii.[' + c.[name] + '] IS NULL)' + CHAR(10)
FROM  sys.columns c
LEFT JOIN [develop].[internal_fields] fi ON fi.[field_name] = c.[name]
LEFT JOIN [meta].[audit_internal_udfs] iu ON iu.[column_name] = c.[name]
WHERE c.[object_id] = OBJECT_ID( 'dbo.TradeEntry') AND c.[name] != 'TradeEntryId' AND c.[name] NOT IN ( 'CreateDate', 'ChangeDate', 'RowVersion') AND c.system_type_id != 241 AND fi.[_identity] IS NULL AND
iu.[_identity] IS NULL

SET @match_str = @match_str + ')'

select '@match_str: provides column joins for comparisons to find changed fields later on'

select @match_str

 

Solution 2:

SELECT c.[name],column_id, 'when ''' + c.[name] + ''' then cast(a1.' + c.[name] + ' as varchar)', 'when ''' + c.[name] + ''' then cast(a2.' + c.[name] + ' as varchar)'
FROM  sys.columns c
LEFT JOIN [develop].[internal_fields] fi ON fi.[field_name] = c.[name]
LEFT JOIN [meta].[audit_internal_udfs] iu ON iu.[column_name] = c.[name]
WHERE c.[object_id] = OBJECT_ID( 'dbo.TradeEntry') AND
c.[name] != 'TradeEntryId' AND
c.[name] NOT IN ( 'CreateDate', 'ChangeDate', 'RowVersion', 'ProcessingDateTime') AND
c.system_type_id != 241 AND
fi.[_identity] IS NULL AND iu.[_identity] IS NULL

declare @caseStatement1 varchar(max)
select @caseStatement1 = coalesce(@caseStatement1 + ' ' + caseStatement1, 'case (colName) ' + caseStatement1)
from @cols

Retrieve a SQL Column based on its Ordinal Value

Scenario:

We have a columns ordinal position and want to retrieve the data based on this value. Not something we really want to do - but out of interest, how can it be done?

Solution:

/* find a column based on its ordinal value) -- handy keep me*/declare @col1 as varchar(128)
select @col1 = column_name from information_schema.columns where table_name = 'Trade'and ordinal_position = 2 --@position
select @col1

Note, to retrieve the column ordinal in the first place select column_id (ie the ordinal value) from sys.columns.. like this:
SELECT c.[name] , column_id
FROM sys.columns c
LEFT JOIN [develop].[internal_fields] fi ON fi.[field_name] = c.[name]
LEFT JOIN [meta].[audit_internal_udfs] iu ON iu.[column_name] = c.[name]
WHERE c.[object_id] = OBJECT_ID( 'dbo.Trade') AND c.[name] != 'TradeId' AND c.[name] NOT IN ( 'CreateDate', 'ChangeDate', 'RowVersion', 'ProcessingDateTime') AND c.system_type_id != 241 AND fi.[_identity] IS NULL AND iu.[_identity] IS NULL

Alternative to SQL INSERT Statements

Scenario: Typical insert statement such as the following example,,,

USE TestDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Second',2);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Third',3);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fourth',4);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fifth',5);
GO

The clause INSERT INTO is repeated multiple times. Many times DBAs copy and paste it to save time. There is another alternative to this, use UNION ALL and INSERT INTO … SELECT… clauses. Regarding performance there is not much difference.

Solution:

USE TestDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

The effective result is same.

Limitations of Calculated Columns

Some limitations of calculated columns:

- You cannot reference a value in a row other than the current row (as opposed to Excel for example).
- You cannot reference a value in another list or library.

- You cannot reference lookup fields in a formula (see earlier blog)

- You cannot reference the ID of a row for a newly inserted row. The ID does not yet exist when the calculation is performed.

The Keyword field vs. the Enterprise Keywords field

Scenario:
Its confusing - the fact that SharePoint 2010 has a Keywords site column and an Enterprise Keywords site column. The Keywords site column is stored in the “Core Document Columns” category and is of the “Multiple Lines of Text” type.

The Enterprise Keywords column is stored in the “Enterprise Keywords Group” category and is of the “Managed Metadata” type.

The last column is what you would expect in SharePoint 2010, it’s the site column that stores it’s contents in the Keywords term set in the Managed Metadata Service Application.

So what does the other Keywords column do?

Adding the column to the list shows you can manually enter values in the column, but that it won’t store the values in the Keywords term set in the term store. However adding both the Keywords and the Enterprise Keywords columns to a list has some unexpected behavior. Keywords that are added to the Enterprise Keywords column are also copied to the Keywords column and visa versa. It turns out that this is actually expected behavior to support an easy upgrade from the old 2007 Keywords field (of the Multiple Lines of Text type) to the new Enterprise Keywords field (of the Managed Metadata Type). The synchronization behavior is automatically enabled as soon as the Enterprise Keywords field is added to a list or library. It’s even described on the “Enterprise Metadata and Keywords Settings” page in the Library settings.

SSRS - Custom Code

When SSRS doesnt want to do what you want it to do - thankfully theres a coding option via the reports 'Report Properties'>'Code'.

Alas the code needs to be in VB.NET, cant have everything!

Heres some example VB.NET code. The following comes from an OLAP report


________________________________________________________________

Public SumOfNAV As Double
Public strTest As String

Public Function SumUp(ByVal Value As Double)
SumOfNAV = SumOfNav + Value
End Function

' Keep a total of the GrossExposure
Private GrossExpList As New System.Collections.Hashtable()

'Used only as a key to determine when to sum
Private FundNavDailyList As New System.Collections.Hashtable()
'Used to contain the current sum of Total Fund Nav
Private FundNavSumList As New System.Collections.Hashtable()

'Gross Exposure Summary Calcs
Public Function SumUpGrossValue(ByVal Key As String, ByVal Value as Double)
If GrossExpList.ContainsKey(Key) Then
GrossExpList.Item(Key) = GrossExpList .Item(Key) + Value
Else
GrossExpList.Add(Key, Value)
End If
End Function

Public Function TotalGrossValue (ByVal Key As String) As Double
If GrossExpList.ContainsKey(Key) Then
Return GrossExpList.Item(Key)
Else
Return 0
End If
End Function

' Fund Nav Summary Total Calcs
Public Function SumUpFundNavValue(ByVal DailyKey As String, ByVal SumKey As String, ByVal Value as Double)
If FundNavDailyList.ContainsKey(DailyKey) Then
If FundNavSumList.ContainsKey(SumKey) Then 'already exists do nothing
strTest = "abc"
Else 'add the new value to the list
FundNavSumList.Item(SumKey) = FundNavSumList.Item(SumKey)
End If
Else 'this is a new days nav figure so we should add it to the daily list and check to see if it needs adding to the summary list too
FundNavDailyList.Add(DailyKey, Value)

If FundNavSumList.ContainsKey(SumKey) Then 'already exists so add to running total
FundNavSumList.Item(SumKey) = FundNavSumList.Item(SumKey) + Value
Else 'add the new value to the list
FundNavSumList.Add(SumKey, Value)
End If
End If
End Function

Public Function TotalFundNavValue (ByVal Key As String) As Double
If FundNavSumList.ContainsKey(Key) Then
Return FundNavSumList.Item(Key)
Else
Return 0
End If
End Function

________________________________________________________________

SSRS - SUM problem in SSRS 2005 Report

In my SSRS 2005 report, I have one group in my table. In that group, I have a field that for each company record in my dataset, this field value is repeated for each record. This is a transactions table so you'd expect several records in the dataset for each company..that's not the problem.

example data from dataset:

TransID          CompanyID        Amount

1                                1000                       100

2                                1000                       100

3                                1000                       100

4                                2000                       400

5                                2000                       400

My SSRS 2005 Report has:

Group1 fields:

CompanyID                                            Amount

=Fields!CustomerNumber.Value              =Fields!CustAmount.Value

The output when previewed looks like this:

Company                Amount

1000                         100

2000                         400

Footer 209409730.83 (totals up all amounts! not just unique instances!)

Should be 500!!!

 I have a footer, and this is where the problem comes in. I am not able to sum 100 + 400 because if I do a SUM(=Fields!CustAmount.Value) It doesn't just sume up 100 + 400 but rather (100 + 100 + 100) + (400 + 400)

 SOLUTION!

Use list instead of table, then create a function in custom code to take in the reportItems!CustAmount.value (assumed textbox name is CustAmount) and do a manual sum up to a locally declared variable. Then in the show sum place just call the variable that stored the sum up value.
Below is the code to put in the Custom Code part in the SSRS report:

Public sum_of_cust As Integer
Public Function SumUp(ByVal Value As Integer)
sum_of_cust = sum_of_cust + Value
End Function

then add a textbox next to your CustAmount textbox that will have the following code:
=Code.SumUp(ReportItems!CustAmount.Value)

then to output the sum, use the following code:
=Code.sum_of_cust

SSRS - Using a report Text Field in a Chart's Calculation (ReportItems)

Access the textbox value via ReportItems collection by:

ReportItems!textbox.Value


Quick example, this was taken from the MarketCapAnalysis.rdl file in the standard reports:

=Format(cdbl(Fields!Field_Value.Value)/cdbl(ReportItems!textbox3.Value), "#,##0.00%")


Note: remember to convert before using the val in a calculation!​

SSRS - Chart Change Background Color

I can never remember where this is! So hence the blog entry...

SSRS - PDF Merging

Quite often we need to generate the individual report pdfs, then merge them all into a single document. To do this go to the website below - merge the docs (max of 10 at a time) and then remerge as needed (eg 30 docs.. merge 10 at a time, end up with 3 pdfs.. so now merge the newly created 3 pdfs together to create a single pdf output file) - alas 10 pdfs is the max to merge, a limitation of the website.

http://www.mergepdf.net/​

SSRS - XML Configuration

There are 5 major parts to the config to setup.

1. Set the TemplateId (this is obtained from the AM Grids properties).
2. Set default params. Note there are no double quotes ("") used here. Semicolon (;) seperated.
3. Set the trimDataSet to false - this then returns the lowest level of detail.
4. The name of the report must be specified (as per AP demo). Any spaces must be replaced with the underline character ( _ ). Nb sometimes there is a space at the end of the report name in AM (easiest way to test is to run the report from the localhost webpage - this is described within this page)
5. Specify the column names to be returned. Again, any spaces should be replaced with underlines ( _ ). There are other oddities regarding column naming in XML, these are described below (such as % is written as _Percent_)

SSRS - XML Params

Basically, for the AM Templates/reports params to be used in the SSRS reports, you need to have set up the params in the SSRS report.

In the XML [Data] tab (see yellow section in image above, press the [...] to access params), then select the [Parameters] tab. Copy and paste the following:

 

          Name:         Value:

parameters    =Replace(Parameters!queryParam.Value,"@snapshot_type=null","@snapshot_type=2")

When testing the report copy and paste the default values into the dialog box.

SSRS - Charts

Odd beasts! Out of the box they're quite straight forward. However, here are a few notes on their usage.

1. Custom Palette for a Pie-Chart

If, for example you have two pie charts on a single report, you may need for them to be coloured differently (ie use two different colour palettes).

Bizarrely, the only way to do this is to use some custom code to build a custom palette!

1.1 Define the chart series groups and category groups. (ie create the pie chart)

1.2 Define the custom color palette and add custom code.

The colorPalette variable stores the definition of our custom color palette, which has 15 distinct colors. The count variable keeps track of the total count of distinct grouping values in order to wrap around once we exceed the number of distinct colors in the custom color palette. The mapping hash table keeps track of the mapping between grouping values and colors. This ensures that all data points within the same data series have the same color. Later it is used to synchronize the custom legend colors with the chart colors. The following code goes into the custom code window of the report.

 

Private colorPalette As String() = {"Silver", "IndianRed", "RosyBrown", "Peru", "Goldenrod", "DarkKhaki", "Yellow", "YellowGreen", "PaleTurquoise", "Teal", "SkyBlue", "MediumPurple", "Plum", "HotPink"}
Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String
If mapping.ContainsKey(groupingValue) Then
Return mapping(groupingValue)
End If
Dim c As String = colorPalette(count Mod colorPalette.Length)
count = count + 1
mapping.Add(groupingValue, c)
Return c
End Function

 Cut and paste the above into the REPORT>PROPERTIES>CODE [Tab]

1.2 Call the GetColor() function to assign colors to data points.

=Code.GetColor(Fields!Security_Country_Name.Value)

The GetColor function is called from the fill color style properties. Edit the data value to open the Edit Chart Value dialog box and click the Appearance tab (Figure 15). Click the Series Style button and click the Fill tab. The current series group value is passed as an argument to the GetColor function, which is needed to map the internal group instance value to the color value.

As an alternative to the above, you may wish to designate a colour for each specific country (instead of using a palette to 'reandomly' assign the colout to each country on each page of the report). To do this, instead of the Report>Properties>Code as added above use the following function utilising a switch statement to determine a specific colour that will always be applied for each country:

function GetCountryColour(Security_Country_Name)
   dim CountryColour as string

  select case Security_Country_Name
    case "Albania"
       CountryColour = "LightGreen"
    case "Algeria"
       CountryColour = "yellow"
    case "Angola"
       CountryColour = "OrangeRed"
    case "Argentina"
       CountryColour = "CornflowerBlue"
    case "Australia"
       CountryColour = "Silver"
    case "Austria"
       CountryColour = "Red"
    case "Azerbajan"
       CountryColour = "Blue"
    case "Bahamas"
       CountryColour = "Green"
    case "EMU"
       CountryColour = "Purple"
    case "United Kingdom"
       CountryColour = "Orange"
    case else
       CountryColour = ""
    end select
       GetCountryColour= CountryColour
end function

Call the function in the same way as the palette code above using the syntax:

=Code.GetCountryColour(Fields!Security_Country_Name.Value)

SSRS - Reviewing The XML Name For A Column

If you dont see any data being populated in your SSRS column, chances are that the column name is appearing in the XML slightly differently to how its been named in the SSRS report.

Eg. AM Column named 'Nav %' would need to be named in the SSRS report as 'Nav__Percent_'

How to view the XML names:

1. Jump onto the Dev box (eg. projmansrv06.dev)
2. Open the web browser:
http://localhost/ReportingWebService/Service.asmx
3. Select GenerateDataSetFull
4. sqlViewObjectId = 101 (template/report no.)
5. parameters: (empty)
6. trimDataSet: false
7. Then press [Invoke]

SSRS - #ERROR

This is bloomin annoying!

Have some code in a textbox and all should work fine.. The data that is incoming may contain either vb.net 'nothing' (null) or a numeric. So the following should work just fine:

=iif(Fields!Day_Before_Yesterday_Price.Value is nothing, "", Format(CDbl(Fields!Day_Before_Yesterday_Price.Value), "#,##0.0000"))

However, all that is shown in the SSRS report is either a numeric or #Error (when there is no data). This is nuts! SSRS is playing up! So to get around this error we have to add some code, see the screen shot and follow steps 1-4:

And hey presto no more #Errors!

SSRS - Display Body Data In The Report Header

Need to put data into a textbox in the header, eg System Currency so that, [Market Value (USD)]. The solution was to place a textbox on the body of the report and make it hidden. In the textbox add in the data needed (i.e. =Fields or =Countrows). Then set that textbox to RepeatWith the table in the body.

In the header place a textbox and have it set to =ReportItems!hidden_textbox.Value.

Just doing that would give me the data in the header but only on the last page of the report (note if your report is only one page you might not experience this frustration). To get the data to show in the header for each page, need to move the textbox so it was positioned on top of the table. The easiest way to do this was to change the Left and Top Location properties of the textbox. It makes it a little hard to see parts of the table since the textbox covers up parts of it but it gets the data in the header.

Eg = "Market Value (" + ReportItems!textboxHiddenSystemCcy.Value + ")"

Displays as "Market Value (USD)"

SSRS - Checkbox

Oddly, SSRS does not have a checkbox control?!!! Yes its true! So, the workaround is to do the following:

1. create textbox (that will become your check box)
2. change font to Arial Unicode MS
3. in the expression window use:
   ChrW(&H2611) for a filled-in checkbox
   ChrW(&H2610) for an empty checkbox

Eg in a table's cell (text box), in the [Expression] type:

IIF(cbool(Fields!Canceled.Value), ChrW(&H2611), ChrW(&H2610))