Prev: conditional formatting of row
Next: VS2010
From: kodaksmile on 14 May 2010 17:41
I am trying to create a space usage report and the challenge I have is the
date is stored as a varchar in the column in the table. I can convert this
easily in sql (so that when you group and order the data it does so correctly
when there is data from the prior year) however I cannot get the data to
group in the report properly. Here is the query:

select db, sum(tot) as TotalAvailSpace, sum(used) as TotalUsedSpace, sum(tot)-
sum(used) as TotalFreeSpace,
cast(entrydt as datetime) from dbaspace
where entrydt>=(getdate()-361)
group by cast(entrydt as datetime), db
order by cast(entrydt as datetime)

which when run in sql, returns the data in the correct order. I cannot seem
to group by the cast of entrydt in the report though.. only on entrydt which
when I do that, it puts each entry under the database name instead of the
other way around.

From: kodaksmile on 14 May 2010 19:00
Figured it out... I was going about it the wrong way. I tweaked the query to
order by using the cast above and then added a field to my dataset
(expression based) that does the conversion and modified report groupings etc
to use the new field.

kodaksmile wrote:
>I am trying to create a space usage report and the challenge I have is the
>date is stored as a varchar in the column in the table. I can convert this
>easily in sql (so that when you group and order the data it does so correctly
>when there is data from the prior year) however I cannot get the data to
>group in the report properly. Here is the query:
>
>select db, sum(tot) as TotalAvailSpace, sum(used) as TotalUsedSpace, sum(tot)-
>sum(used) as TotalFreeSpace,
>cast(entrydt as datetime) from dbaspace
>where entrydt>=(getdate()-361)
>group by cast(entrydt as datetime), db
>order by cast(entrydt as datetime)
>
>which when run in sql, returns the data in the correct order. I cannot seem
>to group by the cast of entrydt in the report though.. only on entrydt which
>when I do that, it puts each entry under the database name instead of the
>other way around.

 | 
Pages: 1
Prev: conditional formatting of row
Next: VS2010