SSRS

March 27, 2011

I’ve been creating SQL Server Reporting Services (2005) reports for an independent client of mine for several years, after spending six months writing them for a Microsoft Certified Partner back in 2006.

If you ever need to work with multi-valued parameters in SSRS 2005, you need to know about the following, life-saving page:

Passing multi-value parameter in stored procedure (SSRS report).

In case of sql query as well as stored procedure, all selected values of the multi-value parameter will be passed as ‘,’ (comma) separated. So we should use IN clause in WHERE to impose the desired condition.

Problem: If we use stored procedure passing an input parameter which is multi-value parameter, SSRS will take all those selected multiple values as different-2 parameters (as they all are ‘,’ separated). And thus if we do multiple selection (or SelectAll) error would be there, though it will work fine if we select only one value.

error(s): Must Declare Scalar variable @parameter OR number of parameters being passed are more than expected….

Now if preview the report it will work fine for single value of color, but if we select multiple colors from colors parameters, it will give error. To pass the complete list of selected values of @color parameter as a single value/string we should use a function JOIN().

In data set properties of AdventureWorks dataset, click on parameters tab and use JOIN() funtion as:

=JOIN(Parameters!Color.Value, “,”)

This function will join all the color values and will a give a comma separated values thus a single value as a whole will be passed to the SP….

To use this list of comma separated values in the stored procedure we need to split them in a proper way as they are as : ‘value1, value2, value3′, but we need them as ‘value1′, ‘value2′, ‘value3′ to use them in IN clause. So we need to have a UDF function which will take this string of values and will return all them splitted. And then use them in SP as:WHERE @Color IS NULL OR Color IN (SELECT * FROM SplitList(@Color, ‘,’))

where SplitList is a UDF which takes the list and the de-limiter as input parameters and splits all the values.

The function I always use for splitting is one I found in a book years ago:

CREATE FUNCTION [dbo].[fn_MVParam](@RepParam nvarchar(4000),
   @Delim char(1)=',')
RETURNS @Values TABLE (Param nvarchar(4000)) AS
   BEGIN
   DECLARE @chrind INT
   DECLARE @Piece nvarchar(4000)
   SELECT @chrind = 1
   WHILE @chrind > 0
      BEGIN
         SELECT @chrind = CHARINDEX(@Delim, @RepParam)
         IF @chrind > 0
            SELECT @Piece = LEFT(@RepParam, @chrind - 1)
         ELSE
            SELECT @Piece = @RepParam
         INSERT @Values(Param) VALUES (@Piece)
         SELECT @RepParam = RIGHT(@RepParam, LEN(@RepParam) - @chrind)
         IF LEN(@RepParam) = 0 BREAK
      END
   RETURN
END

SLX Web FAQ

March 15, 2011

I’ve been working on my first SLX Web project, going through the extreme learning curve for that over the past two weeks—adapting an existing LAN Tickets customization for the Web client.

Today, I stumbled on the following valuable document: Sage_SalesLogix_72_Web_Client_Programming_FAQ.


Checkbox Columns

December 1, 2010

If you need to add a checkbox column to an SLX LAN datagrid, here are the steps (after you’ve already set up the grid’s SQL/Layout properties):

  1. On the grid’s Properties page, clear the ReadOnly and RowSelect properties
  2. Go into the Columns property of the grid (i.e., click on the ellipses button for the property)
  3. Right-click on the column you want to set to a checkbox, and select Change Column Type…
  4. From the combo box, select Check, and OK
  5. Right-click on the column again, and select Properties…
  6. Make sure the Read-Only box is cleared
  7. For each of the other grid columns in turn, set the Read-Only property box to be checked (assuming you want the checkbox column to be the only editable column on the grid)

This is documented somewhere, right?

Yeah: Right here. 🙂


Migrating Users

October 12, 2010

The time may come when you need to transfer users from one SLX database to another.

In WG Admin, you can export some of the user fields into a tab-delimited file (i.e., go to the Users view, right-click, and select Export…).

You can also import users … from a .csv file with an entirely different column-format than the export. So you can’t simply export from one (source) database, and use the same file to import into the target.

That import file must have not only the correct format, but a specific name (which you cannot change, though you can move/copy it to a different location). You’ll find the blank version of it in C:\Documents and Settings\All Users\Application Data\SalesLogix\SaleslogixUserImport.CSV, and the column list here.

To get the complete set of importable user data out of the source database, for users on a particular Team, run the following query against the database in SSMS:

SELECT ISNULL(usr.UserName, ''), -- USERNAME
USERTYPE = CASE usy.Type
     WHEN 'A' THEN 'Add On'
     WHEN 'C' THEN 'Concurrent'
     WHEN 'M' THEN 'Remote'
     WHEN 'N' THEN 'Network'
     WHEN 'R' THEN 'Retired'
     WHEN 'T' THEN 'Web Only'
     WHEN 'V' THEN 'WebViewer'
     ELSE 'Not Recognized' END, -- USERTYPE
ISNULL(usy.UserTemplate, ''), -- USERTEMPLATE
ISNULL(usr.Prefix, ''), -- PREFIX
ISNULL(usr.FirstName, ''), -- FIRSTNAME
ISNULL(usr.MiddleName, ''), -- MIDDLENAME
ISNULL(usr.LastName, ''), -- LASTNAME
ISNULL(usr.Suffix, ''), -- SUFFIX
ISNULL(usr.Email, ''), -- EMAIL
ISNULL(usr.Title, ''), -- TITLE
ISNULL(usr.Region, ''), -- REGION
ISNULL(usr.Division, ''), -- DIVISION
ISNULL(usr.Department, ''), -- DEPARTMENT
ISNULL('ADMIN', ''), -- MANAGER
     -- ISNULL(usy.ManagerID, ''), -- MANAGER
ISNULL(usy.IsManager, ''), -- ISMANAGER
ISNULL(wrk.Address1, ''), -- WORKADDRESS1
ISNULL(wrk.Address2, ''), -- WORKADDRESS2
ISNULL(wrk.City, ''), -- WORKCITY
ISNULL(wrk.State, ''), -- WORKSTATE
ISNULL(wrk.PostalCode, ''), -- WORKPOSTALCODE
ISNULL(wrk.County, ''), -- WORKCOUNTY
ISNULL(wrk.Country, ''), -- WORKCOUNTRY
ISNULL(usr.Phone, ''), -- WORKPHONE
ISNULL(usr.Direct, ''), -- DIRECTPHONE
ISNULL(usr.Fax, ''), -- FAX
ISNULL(usr.Mobile, ''), -- MOBILEPHONE
ISNULL(usr.PagerTerm, ''), -- PAGERNUMBER
ISNULL(usr.PINNumber, ''), -- PAGERPIN
ISNULL(adr.Address1, ''), -- HOMEADDRESS1
ISNULL(adr.Address2, ''), -- HOMEADDRESS2
ISNULL(adr.City, ''), -- HOMECITY
ISNULL(adr.State, ''), -- HOMESTATE
ISNULL(adr.PostalCode, ''), -- HOMEPOSTALCODE
ISNULL(adr.County, ''), -- HOMECOUNTY
ISNULL(adr.Country, ''), -- HOMECOUNTRY
ISNULL(usr.Home, ''), -- HOMEPHONE
ISNULL(usr.AccountingUserID, ''), -- ACCOUNTINGID
ISNULL(pro.AssetID, ''), -- ASSETID
ISNULL(pro.AssetDesc, ''), -- ASSETDESC
HireDate = CASE WHEN pro.HireDate IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.HireDate)
END,
BirthDate = CASE WHEN pro.BirthDate IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.BirthDate)
END,
ISNULL(pro.Spouse, ''), -- SPOUSE
ISNULL(pro.SSN, ''), -- SSN
ISNULL(pro.EmployeeNumber, ''), -- EMPLOYEENUMBER
USERDATE1 = CASE WHEN pro.USERDATE1 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE1)
END,
USERDATE2 = CASE WHEN pro.USERDATE2 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE2)
END,
USERDATE3 = CASE WHEN pro.USERDATE3 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE3)
END,
USERDATE4 = CASE WHEN pro.USERDATE4 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE4)
END,
USERDATE5 = CASE WHEN pro.USERDATE5 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE5)
END,
ISNULL(pro.USERTEXT1, ''), -- USERTEXT1
ISNULL(pro.USERTEXT2, ''), -- USERTEXT2
ISNULL(pro.USERTEXT3, ''), -- USERTEXT3
ISNULL(pro.USERTEXT4, ''), -- USERTEXT4
ISNULL(pro.USERTEXT5, ''), -- USERTEXT5
USERNUM1 = CASE WHEN pro.USERNUM1 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM1)
END,
USERNUM2 = CASE WHEN pro.USERNUM2 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM2)
END,
USERNUM3 = CASE WHEN pro.USERNUM3 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM3)
END,
USERNUM4 = CASE WHEN pro.USERNUM4 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM4)
END,
USERNUM5 = CASE WHEN pro.USERNUM5 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM5)
END,
CONVERT(varchar(max), ISNULL(pro.NOTES, '')), -- NOTES
ISNULL(usr.WindowsSID, ''), -- WINDOWSSID (Don't import if the target
    -- database is on a different network than the source database)
ISNULL(usr.UserCompany, '') -- USERCOMPANY
FROM sysdba.UserInfo usr
LEFT OUTER JOIN sysdba.UserProfile pro
ON usr.UserID = pro.UserID
LEFT OUTER JOIN sysdba.UserSecurity usy
ON usr.UserID = usy.UserID
LEFT OUTER JOIN sysdba.SecRights rig -- for Team export
ON usr.UserID = rig.AccessID -- for Team export
LEFT OUTER JOIN sysdba.SecCode sec -- for Team export
ON rig.SecCodeID = sec.SecCodeID -- for Team export
LEFT OUTER JOIN sysdba.Address adr
ON usr.HomeAddressID = adr.AddressID
LEFT OUTER JOIN sysdba.Address wrk
ON usr.AddressID = wrk.AddressID
WHERE sec.SecCodeDesc = 'MyTeam' -- Team name,
     -- for Team export
AND usy.Type <> 'W' -- ADMIN user
AND usy.Type <> 'P' -- OTB Template (All Access, etc.) users

Right-click over the result set in SSMS, and Select All; then right-click again, and Copy.

Open the SaleslogixUserImport.CSV file, and Paste the result set into it, below the header row. Then Save the workbook in its original name and format.

Then, in WG Admin, go to Insert -> Import Users -> From CSV File…, select the SaleslogixUserImport.CSV, and import it.

You can’t import Retired users directly, so if you need to import those (for data integrity) the easiest way is to first import all of the non-retired users (in a single .CSV file). If you then create a second .CSV file with just the Retired users, but change all of their USERTYPE column values to “Network” (say), then any of those users who don’t have licenses available will automatically get converted into Retired ones by the import.

The above won’t bring the UserSecurity.ManagerID across (since that won’t be the same UserID in the target database as it was in the source). So unless you want to get a lot fancier (i.e., building a UserID lookup table, to transform those values), you’ll need to update that field manually in the target database.

If you want to get all of the users (not just those on a particular Team) out via the above SQL, just delete the SecRights and SecCodeID table joins, and the “MyTeam” WHERE-clause.

Also, if the target database is on a different network than the source, don’t import the WindowsSID field. That field gets populated when you click “Use Windows Authentication” for the User in WG Admin.


CustomSettings Table

October 9, 2010

If you need to check whether a given User is a member of a particular Team, Ryan Farley has provided a very useful IsMember function:

' sGroupType = "T" for team, and "D" for department
Function IsMember(ByVal sUser, ByVal sGroup, _
    ByVal sGroupType)

    Dim objRS

    If sGroupType = "T" Then sGroupType = "G"
    Set objRS = CreateObject("ADODB.Recordset")
    With objRS
         Set .ActiveConnection = _
             Application.GetNewConnection
         .Open "select accessid from secrights where " _
             & "seccodeid = " _
                   & "(select seccodeid from seccode where " _
                   & "(seccodedesc = '" & sGroup & "') " _
                   & "and (seccodetype = '" & sGroupType _
                   & "')) and (accessid = '" & sUser & "')"

         If Not (.EOF Or .BOF) Then
            IsMember = (.Fields(0).Value & "" = sUser)
         Else
             IsMember = False
         End If
         .Close
    End With

    Set objRS = Nothing

End Function

When using that function, you’ll typically hard-code the Team name in your scripts. But suppose that your client’s SLX administrator then changes that name—that would break your code.

You can eliminate that possibility by storing the Team name in the CustomSettings table, which is then visible in WG Admin, on the Tools > Options > Custom tab.

On that tab, click Add, and then enter the Category (“Teams”), Description (“My Sales Team”), Version (“1”), and Type (“Data”).

The Validation field on that tab takes a TABLE.FIELD string, which will cause the unique values for that field to be loaded into the Value dropdown. So if you want to validate against the existing Teams (and Users) in the database, enter SECCODE.SECCODEDESC for the Validation string, and then select the Team (e.g., “My Sales”) you want to validate against in your code, from the Value dropdown.

You’ll also need to build a simple function to retrieve that value from the CustomSettings table, based on its Category and Description values, to return the DataValue field value. That is, a wrapper around this:

strValue = GetField("DataValue", 
"CustomSettings", "Category = '" & strCategory _
& "' AND Description = '" & strDescription & "'")

With that implemented, if the client’s SLX administrator ever changes the team names (for whatever reason), you can just change the Value for this entry on the Custom tab, and your code will continue to work as it should.


Program XML/HTTP with VBScript

October 5, 2010

Program XML/HTTP with VBScript:

' amazon_price.vbs
' This VBScript prompts for an ASIN, and returns the price.
' Usage: double-click amazon_price.vbs, enter ASIN.

Sub AmazonQuery(ASIN_In)
  Dim SelectedText
  Dim MSXML
  Dim XMLURL
  Dim Loaded

  ' Make sure that some text is selected
  If ((Len(ASIN_In) = 0) Or (ASIN_In < " ")) Then
     WScript.Echo "Please enter an ASIN."
     Exit Sub
  End If

  ' Set Associate ID and Developer Token
  AffiliateTag = "insert associate tag"
  DeveloperToken = "insert developer token"

  ' Create an instance of the MSXML Parser
  Set MSXML = CreateObject("MSXML.DOMDocument")

  ' Set MSXML Options
  MSXML.Async = False
  MSXML.preserveWhiteSpace = False
  MSXML.validateOnParse = True
  MSXML.resolveExternals = False

  ' Form the request URL
  XMLURL = "http://xml.amazon.com/onca/xml3" + _
     "?t=" + AffiliateTag + _
     "&dev-t=" + DeveloperToken + _
     "&page=1" + _
     "&f=xml" + _
     "&mode=books" + _
     "&type=lite" + _
     "&AsinSearch=" + ASIN_In

  ' Issue the request and wait for the response
  Loaded = MSXML.Load(XMLURL)

  ' If the request is loaded successfully, continue
  If (Loaded) Then
     ' Look for the ErrorMsg tag
     Set XMLError = MSXML.SelectNodes("//ErrorMsg")

     ' If it exists, display the message and exit
     If XMLError.length > 0 Then
        WScript.Echo MSXML.SelectSingleNode("//ErrorMsg").text
        Exit Sub
     End If

     ' If there's no error, use XPath to get the product name
     ' and the price
     WScript.Echo "The price of " + _
       MSXML.SelectSingleNode("ProductInfo/Details/ProductName").text + _
          " is " + _
       MSXML.SelectSingleNode("ProductInfo/Details/OurPrice").text
  Else
     WScript.Echo "The service is not available."
  End If

End Sub

strASIN = InputBox("Please enter a product ASIN.")

AmazonQuery(strASIN)

Customized Plugins Query

August 15, 2010

 If you need to get a list of customized plugins out of an SLX database—for doing upgrades, etc.—the following query will be useful:

SELECT Family AS Family, [Name], ModifyDate, Company,
    CompanyVersion, Author, Family AS Family2,
    [Type] = CASE [Type]
	WHEN 0 THEN 'Process'
	WHEN 1 THEN 'Sales Process'
	WHEN 2 THEN 'Scripts, Basic (Legacy)'
	WHEN 3 THEN 'Views, Sales (Legacy)'
	WHEN 4 THEN 'Profiles'
	WHEN 5 THEN 'Scripts, SQL'
	WHEN 6 THEN 'System Reports'
	WHEN 7 THEN 'Templates, SalesLogix'
	WHEN 8 THEN 'Groups, Sales'
	WHEN 9 THEN 'Report Profiles'
	WHEN 10 THEN 'Reports, SalesLogix'
	WHEN 11 THEN 'SalesLogix WP templates'
	WHEN 12 THEN 'Templates, Word (2.x, 3.x, 4.x)'
	WHEN 13 THEN 'Macros'
	WHEN 14 THEN 'Strips, Menu'
	WHEN 15 THEN 'Strips, Toolbar'
	WHEN 16 THEN 'Nav Bars'
	WHEN 17 THEN 'Reports, IQ'
	WHEN 18 THEN 'Bitmaps'
	WHEN 19 THEN 'Reports, Crystal'
	WHEN 20 THEN 'Context Menus'
	WHEN 21 THEN 'SupportLogix Reports'
	WHEN 22 THEN 'Listviews, Support (Legacy)'
	WHEN 23 THEN 'Groups, Support'
	WHEN 24 THEN 'Detail Views, Support'
	WHEN 25 THEN 'Templates, Word (5.x)'
	WHEN 26 THEN 'XML Database Schema'
	WHEN 27 THEN 'VBScript'
	WHEN 28 THEN 'Active Forms'
	WHEN 29 THEN 'ActiveX Controls'
	WHEN 30 THEN 'MainViews'
	WHEN 31 THEN 'Global Scripts'
	WHEN 32 THEN 'Ticket'
	WHEN 33 THEN 'Contract'
	WHEN 34 THEN 'XML File'
	WHEN 37 THEN 'FileAttach'
	WHEN 38 THEN 'Text'
	END, Description
FROM sysdba.Plugin
WHERE Released = 'T'
AND Company <> 'SalesLogix'
AND Company <> 'Best Software'
AND Company <> 'Sage Software'
AND Company <> 'Sage'
AND Company <> 'Legacy'
-- and Type IN ('14', '15', '16', '27', '28', '30',
-- '31', '2', '3', '8', '23')
-- and Name NOT LIKE 'C[_]%'
-- underscore character is a wildcard in SQL
AND [Name]+Family IN (SELECT [Name]+Family 
     FROM sysdba.Plugin
     WHERE IsNull(Released, 'F') = 'F' 
     AND Company = 'SalesLogix')
     -- to return only plugins which are customized versions
     -- of OTB ones
ORDER BY [Type], Family, [Name]