Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
addyb77
Helper I
Helper I

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

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? 🙂

1 ACCEPTED SOLUTION
addyb77
Helper I
Helper I

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.

View solution in original post

1 REPLY 1
addyb77
Helper I
Helper I

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.