Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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? 🙂
Solved! Go to 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.
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.
User | Count |
---|---|
121 | |
69 | |
66 | |
56 | |
52 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |