Drill down or add another column (Need to seperate multiples of the same query)

[addyb77]

Hi all

I'm currently working on a project dashboard that pulls various bits of data from our sql servers and im stuck on a particular module.

Basically i have 36 servers and at the moment i have added a query for my first server which retrieves database info:

DECLARE
    @SqlStatement nvarchar(MAX)
    ,@DatabaseName sysname;

IF OBJECT_ID(N'tempdb..#DatabaseSpace') IS NOT NULL
    DROP TABLE #DatabaseSpace;

CREATE TABLE #DatabaseSpace(
    DATABASE_NAME   sysname
    ,LOGICAL_NAME   sysname
    ,FILE_SIZE_MB   decimal(12, 2)
    ,SPACE_USED_MB  decimal(12, 2)
    ,FREE_SPACE_MB  decimal(12, 2)
    ,FILE_NAME      sysname
    );

DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases;

OPEN DatabaseList;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM DatabaseList INTO @DatabaseName;
    IF @@FETCH_STATUS = -1 BREAK;
    SET @SqlStatement = N'USE '
        + QUOTENAME(@DatabaseName)
        + CHAR(13)+ CHAR(10)
        + N'INSERT INTO #DatabaseSpace
    SELECT
        [DATABASE_NAME] = DB_NAME()
        ,[LOGICAL_NAME] = f.name
        ,[FILE_SIZE_MB] = CONVERT(decimal(12,2),round(f.size/128.000,2))
        ,[SPACE_USED_MB] = CONVERT(decimal(12,2),round(fileproperty(f.name,''SpaceUsed'')/128.000,2))
        ,[FREE_SPACE_MB] = CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,''SpaceUsed''))/128.000,2))
        ,[FILENAME] = f.name
    FROM sys.database_files f;';

    EXECUTE(@SqlStatement);

END
CLOSE DatabaseList;
DEALLOCATE DatabaseList;

SELECT * FROM #DatabaseSpace;

DROP TABLE #DatabaseSpace;;

Now this returns the info i need fine but as this will be the same query for multiple servers i have no way of knowing which server will be which when i add the data (in a tablix) to the dashboard. My first thought was to add a custom column to the table and manually add the respective server names the queries relate to but it seems this is not possible.

My second thought was to create a text box with the server name displayed and then drill down through this to the query but again there doesnt seem to a away to do this. My only option at the moment is to display the details in a tablix and add a title with the server name, shrink the visual so it just shows the servername and then click the focus mode button which will display all the data. I then rinse and repeat for each server which is not really ideal.

Any ideas of a way round this guys and gals? 🙂

[addyb77 · Accepted solution]

Apologies all i stupidly missed something. You can manually add a custom column with text, i'd simply just not got the syntax right doh!!

Add Custom Column > Add column name

Custom column formula = "Server name" 

So now i import the query, add a custom column with the server name in, repeat for all other queries and then append queries. I can then have them all in one big tablix and use a slicer or chiclet slicer to filter as required. Just tested on 2 appended queries for 2 servers and it appears to work.