Multiple Y-Axis values in report

I will try to explain this best I can. I have a report where it shows total number of pieces over time. This range is from 0 to like 100 pieces. I have done a UNION query to add average accumulation (in inches).

If adding average accumulation to the "Data fields" section of the report in report designer, It shows the average accumulation on the bottom in releation to the number of pieces. (which is what I want.. But.. ) The numbers are not proportional obviously there wont be 60 inches of snow.. more like 1 or two inches.. So the line is very close to the bottom almost invisible..

Now that ive dont a muck up job of explaining.. This is what im looking for.. Some type of chart that will overlay the average accumulation but somehow be smart enough to show the different scale of precipitation vs pieces of equipment.

VS 2003 SQL Server 2000

Multiple y-axis are currently not supported through the built-in charts in Reporting Services. However, with RS 2005 there is a new feature called CustomReportItem which third party ISVs (Dundas, ChartFX, etc.) take advantage of to integrate their own charting solutions directly into RS 2005. These charts do support multiple y-axis within RS 2005.

-- Robert

|||It close to 1 year since the previous reply. Is multiple y-axis currently supported in reporting service now?

Multiple Y-Axis values in report

I will try to explain this best I can. I have a report where it shows total number of pieces over time. This range is from 0 to like 100 pieces. I have done a UNION query to add average accumulation (in inches).

If adding average accumulation to the "Data fields" section of the report in report designer, It shows the average accumulation on the bottom in releation to the number of pieces. (which is what I want.. But.. ) The numbers are not proportional obviously there wont be 60 inches of snow.. more like 1 or two inches.. So the line is very close to the bottom almost invisible..

Now that ive dont a muck up job of explaining.. This is what im looking for.. Some type of chart that will overlay the average accumulation but somehow be smart enough to show the different scale of precipitation vs pieces of equipment.

VS 2003 SQL Server 2000

Multiple y-axis are currently not supported through the built-in charts in Reporting Services. However, with RS 2005 there is a new feature called CustomReportItem which third party ISVs (Dundas, ChartFX, etc.) take advantage of to integrate their own charting solutions directly into RS 2005. These charts do support multiple y-axis within RS 2005.

-- Robert

|||It close to 1 year since the previous reply. Is multiple y-axis currently supported in reporting service now?

Multiple select and sum expressions on the same table SQL…?

Little complex but I'll try to explain the best I can
I have the following table…

UserID___Year___Month_____A1____A2____B1____B2

__1_______4______5______45_____5____43____0

__1_______4______6______10_____12___1_____3

__1_______5______5______12_____22___8_____7

__1_______5______6______11_____10___5_____0

__2_______4______5______9______65___33____66

__2_______4______6______3______3____10____11

__2_______5______5______44_____2___77_____1

I want to pull out the data to show the sum of the totals for each year the user is in. The idea is to then be able to see a comparison for year on year on a month by month basis

Eg.

UserID____TotalAYear4____TotalBYear4___TotalAYear5____TotalBYear5___Month

__1___________50_____________43___________34____________15__________5

__1___________22_____________4____________21____________5___________6

__2___________74_____________99___________21____________5___________5

There would be no data returned for UserID2 for month 6 as there is no data for them for year 5 month 6 therefore no comparison can be made.

I've having real difficulty getting the correct SQL syntax to do this. Any ideas or pointers would be great thanks

Thebest, most scalable way to do this would be to aggregate by month and year, and let the client-side code calculate the comparisons. So a simple:
SELECT UserID, Year, Month, SUM(A1 + A2) AS TotalA, SUM(B1 + B2) AS TotalB
FROM Table
GROUP BY UserID, Month, Year
Youcould pivot the data out, but you would need to go in and change the code with each new year that you add, and it would quickly become cumbersome...