I have a query that returns a field called Type. Possible values for Type are A, B, C, or Unknown. I want to create a table that will show the number of rows of each Type but not the not the Unknown type. Also I like the table to show the percentage of
known types versus total types. So the table show look like this:
Type Count
A number of rows with A Type
B number of rows with B Type
C number of rows with C Type
Total: (number of rows with A, B, or C Types / number of all rows with A, B, C, or Unknown Types)
Please someone tell me how to do this in Visual Studio 2005 reports. Thank you.
I have modified the my previous posted query. Use the following query to fetch and display the data on the report. This will give you:
type name (Excluding UNKNOWN)
number of rows for that type (Excluding UNKNOWN)
percentage of each known type
select types.[type],count(*) as total,
convert(decimal,count(*))/convert(decimal,t.tall)*100 as percentage from
types,(select count(*) as tall from types) as t where type!='UNKNOWN'
group by type,t.tall
sql server
Please Mark As Answer if it helped.
MCPD ASP.NET 4.0 and 3.5, MCTS WSS, MOSS, SharePoint 2010, MCT
Microsoft Community Contributor Award 2011
kinglionhear...
Member
5 Points
25 Posts
Not showing all the values of a particular field
Mar 05, 2010 05:06 PM|LINK
I have a query that returns a field called Type. Possible values for Type are A, B, C, or Unknown. I want to create a table that will show the number of rows of each Type but not the not the Unknown type. Also I like the table to show the percentage of known types versus total types. So the table show look like this:
Type Count
A number of rows with A Type
B number of rows with B Type
C number of rows with C Type
Total: (number of rows with A, B, or C Types / number of all rows with A, B, C, or Unknown Types)
Please someone tell me how to do this in Visual Studio 2005 reports. Thank you.
adeelehsan
All-Star
18255 Points
2731 Posts
Re: Not showing all the values of a particular field
Mar 06, 2010 10:47 AM|LINK
Hi
Use the following query which will give you 2 columns data:
I assumed that your table name is types and there is a field with the name type.
sql server
MCPD ASP.NET 4.0 and 3.5, MCTS WSS, MOSS, SharePoint 2010, MCT
Microsoft Community Contributor Award 2011
adeelehsan
All-Star
18255 Points
2731 Posts
Re: Not showing all the values of a particular field
Mar 06, 2010 11:04 AM|LINK
Hi
I have modified the my previous posted query. Use the following query to fetch and display the data on the report. This will give you:
sql server
MCPD ASP.NET 4.0 and 3.5, MCTS WSS, MOSS, SharePoint 2010, MCT
Microsoft Community Contributor Award 2011