Hello,

I have a dataset that needs to be inserted into pivotchart.

when i "select all columns for pivotchart --> insert --> pivotchart --> existing table(same tab)"and put the values in the VALUES box server name into AXIS

the value data changes equal to each other therefore rendering it useless.

i have tried changing the values field settings 

Home , settings , advanced , use system operators to " comma's " (since the values row has dots) also didn't work

removing the letters from the values row also didn't work.

also changing chart type did not work

"i would love to upload a picture to clearify the situation but technet wont allow me "

this is a first time since it happened, it worked fine before , but since i changed my Query its not displaying the chart right anymore.

how can i create a pivotchart with the values from dataset that will be displayed correctly?

thank you in advance,

Enma

• Edited by Enma_Ai2012 Sunday, June 07, 2015 11:51 AM

hello,

thank you for the reply i have posted a request to verify account so i can upload a picture which will help clearify my issue.

i have a query for diskspace monitoring for several customers

DECLARE @SQL VARCHAR(8000), @sname VARCHAR(3)
SELECT @sname=CONVERT(VARCHAR(MAX),SERVERPROPERTY('PRODUCTVERSION'))  
SELECT @sname=CONVERT(TINYINT,SUBSTRING(@sname,1,CHARINDEX('.',@sname)-1))
IF @sname=8  
BEGIN  
SET @SQL='USE [?]  
SELECT ''[?]'' [Dbname]  
,[name] [Filename]  
,CASE STATUS & 0x40 WHEN 0x40 THEN ''LOG'' ELSE ''ROWS'' END [Type]  
,filename [FilePath]  
,size/128.0 AS [TotalSize_MB]  
,CONVERT(INT,FILEPROPERTY(name, ''SpaceUsed''))/128.0 [Space_Used_MB]  
,CASE STATUS & 0x100000 WHEN 0x100000 THEN convert(NVARCHAR(3), growth) + ''%''  
ELSE CONVERT(NVARCHAR(15), (growth * 8)/1024) + '' MB'' END [Autogrow_Value]  
,CASE maxsize WHEN -1 THEN CASE growth WHEN 0 THEN ''Restricted'' ELSE N''Unlimited'' END  
ELSE CONVERT(NVARCHAR(15), (maxsize * 8)/1024) + '' MB'' END [Max_Size]  
FROM [?].dbo.sysfiles'  
END  
ELSE  
BEGIN  
SET @SQL=' USE [?]  
SELECT ''[?]'' [Dbname]  
,[name] [Filename]  
,type_desc [Type]  
,physical_name [FilePath]  
,CONVERT(INT,[size]/128.0) [TotalSize_MB]  
,CONVERT(INT,FILEPROPERTY(name, ''SpaceUsed''))/128.0 AS [Space_Used_MB]  
,CASE is_percent_growth  
WHEN 1 THEN CONVERT(VARCHAR(5),growth)+''%''  
ELSE CONVERT(VARCHAR(20),(growth/128))+'' MB''  
END [Autogrow_Value]  
,CASE max_size  
WHEN -1 THEN CASE growth  
WHEN 0 THEN CONVERT(VARCHAR(30),''Restricted'')  
ELSE CONVERT(VARCHAR(30),''Unlimited'') END  
ELSE CONVERT(VARCHAR(25),max_size/128)  
END [Max_Size]  
FROM [?].sys.database_files'  
END  


IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE name='##Fdetails')  
DROP TABLE ##Fdetails  

CREATE TABLE  ##Fdetails (Dbname VARCHAR(MAX),Filename VARCHAR(MAX),Type VARCHAR(10),Filepath 

VARCHAR(MAX)  
,TotalSizeDBF_MB INT,UsedSpaceDBF_MB INT,Autogrow_Value VARCHAR(15),Max_Size VARCHAR(30))  

INSERT INTO ##Fdetails  
EXEC sp_MSforeachdb @SQL   exec sp_executesql @stmt=N'
                begin try
                declare @filestats_temp_table table(
                file_id int
                ,       file_group_id int
                ,       total_extents int
                ,       used_extents int
                ,       logical_file_name nvarchar(500) collate database_default
                ,       physical_file_name nvarchar(500) collate database_default
                );

                insert into @filestats_temp_table
                exec sp_msforeachdb ''USE [?] exec (''''DBCC SHOWFILESTATS'''')''

SELECT 
(row_number() over (order by t2.name))%2 as l1
                , t2.name as [file_group_name]
                ,       t1.logical_file_name
                ,       t1.physical_file_name
-- nieuw
, t3.Type
, t3.Dbname
, t3.Autogrow_Value
, t3.Max_Size
-- einde nieuw
                ,       CAST( cast(case when (total_extents * 64) < 1024 then (total_extents * 

64)
                when (total_extents * 64 / 1024.0) < 1024 then  (total_extents * 64 / 1024.0)
                else (total_extents * 64 / 1048576.0)
                end as decimal(10,2)) as varchar(20)) + case when (total_extents * 64) < 1024 

then '' KB''
                when (total_extents * 64 / 1024.0) < 1024 then  '' MB''
                else '' GB''
                end as space_reserved
                , CAST( cast(case when (used_extents * 64) < 1024 then 

(used_extents * 64)
                when (used_extents * 64 / 1024.0) < 1024 then  (used_extents * 64 / 1024.0)
                else (used_extents * 64 / 1048576.0)
                end as decimal(10,2)) as varchar(20)) + case when (used_extents * 64) < 1024 then 

'' KB''
                when (used_extents * 64 / 1024.0) < 1024 then  '' MB''
                else '' GB''
                end as space_used
                from    @filestats_temp_table t1 
                inner join sys.data_spaces t2 ON t1.file_Group_id =t2.data_space_id
inner join ##Fdetails t3 on t3.Filepath = t1.physical_file_name
end try
                begin catch
                select -100 as l1
                ,       ERROR_NUMBER() as file_group_name
                ,       ERROR_SEVERITY() as logical_file_name
                ,       ERROR_STATE() as physical_file_name
                ,       ERROR_MESSAGE() as space_reserved
                ,       1 as space_reserved_unit, 1 as space_used, 1 as space_used_unit
                end catch ;
              ',@params=N''

--these are the results from the query

PRIMARY	tempdev	J:\tempdb.mdf	ROWS	[tempdb]	10%	Unlimited	5.73 GB	5.19 MB
PRIMARY	modeldev	C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\model.mdf	ROWS	[model]	100 MB	Unlimited	2.25 MB	1.31 MB
PRIMARY	MSDBData	C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf	ROWS	[msdb]	10%	Unlimited	364.19 MB	317.00 MB

when i put the columns into their respective "server name AXIS,dbname AXIS, space_used/space_free into VALUES"

the space_used and space_free value change from 5.19MB to something entirely else, same for all my values.

i use Excel 2013 and SQL Server management 2008 2012 and 2014 

as soon as my account is verified i upload a screenshot to help me  clearify it. 

please wait

Regards,

Enma

• Edited by Enma_Ai2012 Monday, June 08, 2015 7:23 AM

Hi Enma,

Based on your image, I find you count the COUNT values of space_used and space_reserved. I suggest you change the VALUES area, try to SUM the values of space_used and space_reserved (As shown in the following figure).

Please click Count of space_used- select Value Field Settings-in Summarize value field by TAB, please select Sum-click OK

The same approach also applies to change Count of space_reserved.

Try this method and check if it works for your issue.

Hope its helpful.

Regards,

George Zhao
TechNet Community Support