Report By Year

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

I have a SQL view that shows data by Category and Year - so the data may
look something like this.
Category - Year - Amount
Fruit - 2006 - $12,000
Fruit - 2007 - $16,000
Vegetables - 2006 - $15,000
Vegatables - 2007 - $25,000

Say I want to show that data in Tabular view with Category being the Y-Axis
and Year being the X-Axis, how would I go about doing this? I'm having a
hard time trying to figure out where to start. An Access-style report
doesn't seem to work because I have no way (that I know of) to have a
dynamic X-axis (years may vary based on what report the user requests). Is
the SQL query good enough or do I need to do something with the query in
order to get the years into separate columns (instead of a Year column as
above I would have a 2006 and a 2007 column containing the amounts for those
years)? A full solution may not be necessary if anyone could just point me
in the right direction to start.

Thanks for any assistance!
Ryan
 
Ryan said:
I have a SQL view that shows data by Category and Year - so the data may
look something like this.
Category - Year - Amount
Fruit - 2006 - $12,000
Fruit - 2007 - $16,000
Vegetables - 2006 - $15,000
Vegatables - 2007 - $25,000

Say I want to show that data in Tabular view with Category being the Y-Axis
and Year being the X-Axis, how would I go about doing this?
Click to expand...
<snip>

Yeah, this is the lacking feature in SQL Server that I miss the most
from my Access days (I'm assuming you're using SQL Server).

I don't think SQL Server will create dynamic columns for you as the
Access cross-tab did. Nevertheless, if you know the years in advance,
maybe you can use something like this (adapted straight from the
docs):

Select Category,
[2004] = Sum(Case [year] when 2004 then amount else 0 end),
[2005] = Sum(Case [year] when 2005 then amount else 0 end),
[2006] = Sum(Case [year] when 2006 then amount else 0 end),
[2007] = Sum(Case [year] when 2007 then amount else 0 end)
From YourView
Group by Category

And if you got this far, having a Totals columns is just a query away:

Select A.*, Total = (A.[2004] + A.[2005] + A.[2006] + A.[2007])
From (
Select Category,
[2004] = Sum(Case [year] when 2004 then amount else 0 end),
[2005] = Sum(Case [year] when 2005 then amount else 0 end),
[2006] = Sum(Case [year] when 2006 then amount else 0 end),
[2007] = Sum(Case [year] when 2007 then amount else 0 end)
From YourView
Group by Category
) A

HTH.

Regards,

Branco.
 
Thanks for the advice. Knowing the years in advance could be an issue,
because I want to allow the user to select the years they want to view (a
set of years in sequential order, ex. 2006, 2007, 2008). Though I suppose
this could still be done in SQL using a Stored Procedure with a query
similar to what you have here. I'm using a reporting tool (Component One
Reports) which is very similar to Access so I'll keep poking around on that
to see if theres an easier solution on the front-end (report) side.

Ryan

Branco Medeiros said:
Ryan said:
I have a SQL view that shows data by Category and Year - so the data may
look something like this.
Category - Year - Amount
Fruit - 2006 - $12,000
Fruit - 2007 - $16,000
Vegetables - 2006 - $15,000
Vegatables - 2007 - $25,000

Say I want to show that data in Tabular view with Category being the
Y-Axis
and Year being the X-Axis, how would I go about doing this?
Click to expand...
<snip>

Yeah, this is the lacking feature in SQL Server that I miss the most
from my Access days (I'm assuming you're using SQL Server).

I don't think SQL Server will create dynamic columns for you as the
Access cross-tab did. Nevertheless, if you know the years in advance,
maybe you can use something like this (adapted straight from the
docs):

Select Category,
[2004] = Sum(Case [year] when 2004 then amount else 0 end),
[2005] = Sum(Case [year] when 2005 then amount else 0 end),
[2006] = Sum(Case [year] when 2006 then amount else 0 end),
[2007] = Sum(Case [year] when 2007 then amount else 0 end)
From YourView
Group by Category

And if you got this far, having a Totals columns is just a query away:

Select A.*, Total = (A.[2004] + A.[2005] + A.[2006] + A.[2007])
From (
Select Category,
[2004] = Sum(Case [year] when 2004 then amount else 0 end),
[2005] = Sum(Case [year] when 2005 then amount else 0 end),
[2006] = Sum(Case [year] when 2006 then amount else 0 end),
[2007] = Sum(Case [year] when 2007 then amount else 0 end)
From YourView
Group by Category
) A

HTH.

Regards,

Branco.
Click to expand...
 
Post your question to microosft.public.sqlserver.programming. I believe you
*can* do this with a SQLServer query, and they would be the ones to tell
you how.

Then come back and post the answer here so we know, too. ;-)

Robin S.
------------------------------------------------------
Ryan said:
Thanks for the advice. Knowing the years in advance could be an issue,
because I want to allow the user to select the years they want to view (a
set of years in sequential order, ex. 2006, 2007, 2008). Though I
suppose this could still be done in SQL using a Stored Procedure with a
query similar to what you have here. I'm using a reporting tool
(Component One Reports) which is very similar to Access so I'll keep
poking around on that to see if theres an easier solution on the
front-end (report) side.

Ryan

Branco Medeiros said:
Ryan said:
I have a SQL view that shows data by Category and Year - so the data
may
look something like this.
Category - Year - Amount
Fruit - 2006 - $12,000
Fruit - 2007 - $16,000
Vegetables - 2006 - $15,000
Vegatables - 2007 - $25,000

Say I want to show that data in Tabular view with Category being the
Y-Axis
and Year being the X-Axis, how would I go about doing this?
Click to expand...
<snip>

Yeah, this is the lacking feature in SQL Server that I miss the most
from my Access days (I'm assuming you're using SQL Server).

I don't think SQL Server will create dynamic columns for you as the
Access cross-tab did. Nevertheless, if you know the years in advance,
maybe you can use something like this (adapted straight from the
docs):

Select Category,
[2004] = Sum(Case [year] when 2004 then amount else 0 end),
[2005] = Sum(Case [year] when 2005 then amount else 0 end),
[2006] = Sum(Case [year] when 2006 then amount else 0 end),
[2007] = Sum(Case [year] when 2007 then amount else 0 end)
From YourView
Group by Category

And if you got this far, having a Totals columns is just a query away:

Select A.*, Total = (A.[2004] + A.[2005] + A.[2006] + A.[2007])
From (
Select Category,
[2004] = Sum(Case [year] when 2004 then amount else 0 end),
[2005] = Sum(Case [year] when 2005 then amount else 0 end),
[2006] = Sum(Case [year] when 2006 then amount else 0 end),
[2007] = Sum(Case [year] when 2007 then amount else 0 end)
From YourView
Group by Category
) A

HTH.

Regards,

Branco.
Click to expand...
Click to expand...
 
Hi Ryan,

For the report you want to present, I think the SQL Server reporting
service is quite good for present such summary data. And the Matrix data
region in Reporting Service is the exact one that can present the data view
you want. You can have a look at the following reference:

#SQL Server 2005 Books Online Working with Matrix Data Regions
http://msdn2.microsoft.com/en-us/library/ms157334.aspx

#Reporting Services - Getting the Matrix to Display Two Subtotals for the
Same Group
http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMat
rixToDisplayTwoSubtotalsForTheSameGroup.aspx

Also, if you do not want to install reporting service, and only want to
present such a report in your client application, Visual Studio 2005 has
provided a reportviewer control(for both ASP.NET and winform application)
that can display client report(use the same template syntax as Sql server
reporting service report). Here are some useful articles discussing on
using ReportViewer control of VisualStudio 2005:

#Adding Data Regions to a ReportViewer Report
http://msdn2.microsoft.com/en-us/library/ms252086(VS.80).aspx

#Adding Matrix Data Regions
http://msdn2.microsoft.com/en-us/library/ms251709(VS.80).aspx

#Build Client-Side Reports Easily
http://www.ftponline.com/vsm/2005_11/magazine/features/rjennings/

#ReportViewer Control in Visual Studio 2005
http://gotreportviewer.com/

Hope this helps. If you have any more specific questions on this, please
feel free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.
 
Steven,

Thanks this is just what I needed. I'm able to create the matrix to get the
data I want. The downsides so far is that I am having trouble getting the
column headers for my static fields to show up, the matrix fields that have
no data need to show 0 but they're showing up as blanks, and I'm not real
fond of the report designer interface. But I guess those are all things
I'll get used to and/or find a fix for eventually.

Ryan
 
Thanks for your reply Ryan,

For column header of static columns, I've ever found some existing
discussion on this and static columns seems hasn't header text setting as
dynamic column. However, someone has been able to use the top/left cell to
add some header text for all those static columns.

In addition, here are some other useful resource on reportviewer based
client report designing:

#Walkthrough: Creating a ReportViewer Report
http://msdn2.microsoft.com/en-us/library/ms252073(VS.80).aspx

#Defining a Report Layout
http://msdn2.microsoft.com/en-us/library/ms251786(VS.80).aspx

#ReportViewer User Interface Reference
http://msdn2.microsoft.com/en-us/library/ms251680(VS.80).aspx

http://blogs.msdn.com/ChrisHays/

Hope this also helps.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi Steven,

Thats exactly the workaround I ended up using, I put a table in the upper
left cell of the matrix that had static header names. Thanks for the links!

Ryan
 
Back
Top