Discovery and Inventory Group

 View Only

Error - Report Export to Spreadsheet

  • 1.  Error - Report Export to Spreadsheet

    Posted Nov 19, 2012 12:57 PM

    Hi All,

    I've been working with this problem for a while now and not got round to working out what it was until recently.

    I've found that reports i've been making myself with just Raw SQL wouldn't export to Spreadsheet. No reason or error just when you select export and select the options and press ok it just goes back to the page and nothing exports.  Exporting to XML and HTML work fine but not spreadsheet.

    I was using a report I created recently and clicked export to spreadsheet and it exported. Instantly I started comparing it to other reports. Most of them were pretty much the same.

    A good example of this was a simple Add and Remove Programs i was mocking up for someone:

    SELECT [i].[Name],
    [i].[User],
    [ARProgram].[DisplayName],
    [ARProgram].[DisplayVersion],
    [ARProgram].[InstallDate],
    [ARProgram].[EstimatedSize],
    [ARProgram].[ParentDisplayName],
    [ARProgram].[UninstallPath]
    FROM dbo.vComputer i       
    LEFT OUTER JOIN [Inv_AddRemoveProgram] AS [ARProgram] ON ([i].[Guid] = [ARProgram].[_ResourceGuid])
    WHERE
    [i].[Name] like lower('%ARPCOMPUTERNAME%')
    AND
    [ARProgram].[InstallFlag] = 1
    


    Compared to the working script it was missing the TrusteeScope, i added that and it didnt work, I removed the [ and ] from around the table names and that also had no effect. I changed the case of the SELECT, FROM, WHERE and JOINS, again nothing.

    Then it struck me... Column Header titles!
    by adding just one column header title it works absolutely fine.....


    E.g.

    SELECT [i].[Name],
    [i].[User],
    [ARProgram].[DisplayName] [Display Name],
    [ARProgram].[DisplayVersion],
    [ARProgram].[InstallDate],
    [ARProgram].[EstimatedSize],
    [ARProgram].[ParentDisplayName],
    [ARProgram].[UninstallPath]
    FROM dbo.vComputer i       
    LEFT OUTER JOIN [Inv_AddRemoveProgram] AS [ARProgram] ON ([i].[Guid] = [ARProgram].[_ResourceGuid])
    WHERE [i].[Name] like lower('%ARPCOMPUTERNAME%')
    AND [ARProgram].[InstallFlag] = 1

     

    Anyone got any clues why this would be the case. Now i know its not a problem but just a bloody odd one!

     

    - EDIT -

    It appears to do with some of the column headers in the selection, some of these column headers get their titles changed to shortended versions e.g. DisplayName is shown as Name and not DisplayName. Forcing the custom title resolves this and forces it to work correct.