locked
SSRS 2005 - Removing blank spaces from dataset and parameter RRS feed

  • Question

  • Hi all

    I have a dataset, which shows items with the item telephone numbers. The telephone numbers are UK format. The telephone numbers can be written in a multitude of ways, with blank spaces in various places in the number, ie Phone Number for Item 1 may be '012 05632 256', Phone Number for Item 2 may be '01256 365253', Phone Number for Item 3 may be '01256 325 362', etc etc.

    What my report is trying to do is ask the user for a telephone number, which is called parameter @Phone. The report then goes away and returns the Item which has the matching telephone number that the user has entered in @Phone. What I dont know how to do is to make SSRS remove all blank spaces from @Phone (just in case the user enters the phone number with blank spaces into @Phone), then try to match this with all telephone numbers (Mxmservsiteequipment.installcomment) present in the table by removing all blank spaces from all Item telephone numbers (Mxmservsiteequipment.installcomment), then return the correct Item.

    Currently I have:

    SELECT   MXMSERVSITEEQUIPMENT.DATAAREAID, MXMSERVSITEEQUIPMENT.SITEID, MXMSERVSITE.NAME, MXMSERVSITEEQUIPMENT.SERIAL, 
               MXMSERVSITEEQUIPMENT.VENDORWARRANTYREF, INVENTTABLE.ITEMNAME, MXMSERVSITEEQUIPMENT.INSTALLCOMMENT AS Telephone
    FROM     MXMSERVSITEEQUIPMENT INNER JOIN
               MXMSERVSITE ON MXMSERVSITEEQUIPMENT.SITEID = MXMSERVSITE.SITEID AND 
               MXMSERVSITEEQUIPMENT.DATAAREAID = MXMSERVSITE.DATAAREAID INNER JOIN
               INVENTTABLE ON MXMSERVSITEEQUIPMENT.ITEMID = INVENTTABLE.ITEMID AND MXMSERVSITEEQUIPMENT.DATAAREAID = INVENTTABLE.DATAAREAID
    WHERE   (MXMSERVSITEEQUIPMENT.DATAAREAID IN (@CompanyID)) AND (MXMSERVSITEEQUIPMENT.INSTALLCOMMENT IN (@Phone))
    


    Can anyone suggest how I go about removing these blank spaces from both the @Phone parameter, and also from all entries within Mxmservsiteequipment.installcomment?

    Wednesday, August 3, 2011 8:47 AM

Answers

  • Hi,

     

       Use the following expressionin your parameter

     

         =replace(Fields!Paramater.Value," ","")

     

    Regards,

    Bharath R S

    • Marked as answer by Naz at ANSA Wednesday, August 3, 2011 9:55 AM
    Wednesday, August 3, 2011 9:16 AM
  • Figured it out, with the parameter @Phone1 and the wildcard, I had to format it as this:

    LIKE '%' + @Phone1 + '%'
    

     

    Cheers

     

    • Marked as answer by Naz at ANSA Wednesday, August 3, 2011 9:55 AM
    Wednesday, August 3, 2011 9:55 AM

All replies

  • Hi,

     

       Use the following expressionin your parameter

     

         =replace(Fields!Paramater.Value," ","")

     

    Regards,

    Bharath R S

    • Marked as answer by Naz at ANSA Wednesday, August 3, 2011 9:55 AM
    Wednesday, August 3, 2011 9:16 AM
  • Hi Bharath, thanks for that. I have created a second parameter, @Phone1, which is hidden and whose value is:

    =replace(Parameters!Phone.Value," ","")
    
    

     

    Following on from that, I changed the report to the following:

    SELECT   MXMSERVSITEEQUIPMENT.DATAAREAID, MXMSERVSITEEQUIPMENT.SITEID, MXMSERVSITE.NAME, MXMSERVSITEEQUIPMENT.SERIAL, 
               MXMSERVSITEEQUIPMENT.VENDORWARRANTYREF, INVENTTABLE.ITEMNAME, REPLACE(MXMSERVSITEEQUIPMENT.INSTALLCOMMENT, ' ', '') AS Telephone
    FROM     MXMSERVSITEEQUIPMENT INNER JOIN
               MXMSERVSITE ON MXMSERVSITEEQUIPMENT.SITEID = MXMSERVSITE.SITEID AND 
               MXMSERVSITEEQUIPMENT.DATAAREAID = MXMSERVSITE.DATAAREAID INNER JOIN
               INVENTTABLE ON MXMSERVSITEEQUIPMENT.ITEMID = INVENTTABLE.ITEMID AND MXMSERVSITEEQUIPMENT.DATAAREAID = INVENTTABLE.DATAAREAID
    WHERE   (MXMSERVSITEEQUIPMENT.DATAAREAID IN (@CompanyID)) AND (REPLACE(MXMSERVSITEEQUIPMENT.INSTALLCOMMENT, ' ', '') LIKE '%@Phone1%')
    ORDER BY MXMSERVSITEEQUIPMENT.SERIAL


    However, when I Preview the report and enter '0161' for @Phone, and then run the report, I get no recorsd returned. This is even though most of the items contain the numbers '0161'. Any clues as to what I may be doing wrong?

    Cheers
    Naz


    Wednesday, August 3, 2011 9:47 AM
  • Figured it out, with the parameter @Phone1 and the wildcard, I had to format it as this:

    LIKE '%' + @Phone1 + '%'
    

     

    Cheers

     

    • Marked as answer by Naz at ANSA Wednesday, August 3, 2011 9:55 AM
    Wednesday, August 3, 2011 9:55 AM
  • Hi Naz,

     

         Can you place only the phone filter and try out the query?

     

    Regards,

    Bharath

    Wednesday, August 3, 2011 9:56 AM

  • The UK uses a range of number lengths and formats. Are you sure you have the full picture as to what they all are?

    NSN is National Significant Number. This is all of the digits after the 0 trunk code or +44 country code.

    This list covers all of the options for the UK (listed in national format):


    7 digit NSNs

    •     0800 1111
    •     0845 46 47

    9 digit NSNs

    •     (016977) 2xxx
    •     (016977) 3xxx
    •     (01xxx) xxxxx
    •     0500 xxxxxx
    •     0800 xxxxxx

    10 digit NSNs

    •     (013873) xxxxx
    •     (015242) xxxxx
    •     (015394) xxxxx
    •     (015395) xxxxx
    •     (015396) xxxxx
    •     (016973) xxxxx
    •     (016974) xxxxx
    •     (016977) xxxxx
    •     (017683) xxxxx
    •     (017684) xxxxx
    •     (017687) xxxxx
    •     (019467) xxxxx
    •     (011x) xxx xxxx
    •     (01x1) xxx xxxx
    •     (01xxx) xxxxxx
    •     (02x) xxxx xxxx
    •     03xx xxx xxxx
    •     055 xxxx xxxx
    •     056 xxxx xxxx
    •     070 xxxx xxxx
    •     07624 xxxxxx
    •     076 xxxx xxxx
    •     07xxx xxxxxx
    •     08xx xxx xxxx
    •     09xx xxx xxxx

    Valid formats for geographic numbers include 2+8, 3+7, 4+6, 4+5, 5+5 and 5+4 (and 0+10 for NDO numbers).

    Non-geographic numbers mostly use 0+10 format, but some 0800 numbers and all 0500 numbers use 0+9 format.

    Most code found on the web caters for only a few of these, not the full set.

    The international format adds +44 and a space before the NSN digits.

    The national format adds the 0 trunk code before the NSN digits. For 01 and 02 numbers the area code should be in parentheses, except for NDO numbers (NDO numbers are those where the subscriber number begins 0 or 1).

    NDO numbers are National Dialling Only. These have been around for several decades and need the area code to be dialled even when called locally from within the same area. These numbers are used for alarm systems, computer communication systems and other lines that are not dialled for voice calls. They are also used as the termination point for non-geographic numbers. NDO numbers are not supposed to be advertised nor directly called by subscribers. NDO numbers are always 0+10 format.

    There are proposals to open up the NDO ranges in some areas for use by normal subscribers starting some time in 2012.
    • Edited by lesset Monday, October 17, 2011 7:38 AM
    Sunday, October 16, 2011 5:33 PM