0

I am attempting to create an SSRS report from a table that collects daily database stats (FileSize, Free Space). I am creating a graph within SSRS, which allows for multiple selections of databases across multiple server. I would like to format the chart label. The formula currently looks like this:= IIF (MAX(Fields!ID.Value) > 1024, Format((MAX(Fields!ID.Value)/1024), "#,0.0 GB"), Format(Fields!ID.Value,"#,0.0 GB"))

However, this gives me this following when I run it for a single database that has a size of 116973 MB.

Basically I cna run this for multiple databases, one which may have a size in MB, other which may have a size in GB. I would like the labels to account for that. What am I missing or doing wrong.

Here is the query:

SELECT 
DISTINCT 
left(ServerName, len(ServerName)-1) as servername,
DatabaseName,
SUM(FileSizeMB),
PollDate
 from DBInfo
 Where DatabaseName IN (@Database) AND left(ServerName, len(ServerName)-1)  IN (@Servers)
 AND PollDate BETWEEN @StartDate AND @EndDate
 GROUP BY ServerName, DatabaseName, PollDate
 Order BY PollDate

Original size is calculated in GB.

Pedram
  • 6,256
  • 10
  • 65
  • 87
devista
  • 55
  • 3
  • 10
  • Your expression is referencing the `ID` field, but I don't see that column in your query. You should alias the `FileSizeMB` aggregate in the query so that the name comes through on your report dataset. Is the `ID` field what you renamed the aggregate to in the report? – StevenWhite Jun 06 '16 at 16:56
  • By default since i am using the SUM(FileSizeMB) and have not created any alias for it, it has referenced it as ID field. – devista Jun 06 '16 at 20:39

0 Answers0