Hey everybody, I'm really boggled on how to get my report to show properly. Here are the fields of my tables that are pertinent to the problem.
Table Equipment : This table contains information about computers that the company has purchased.
Fields
CRDLno - Primary key, Integer.
Price - price of equipment, money
Date_of_purchase - When the equipment was purchased, date
Table Add-ons: This table contains information about add-ons for the computers, such as monitors or printers.
Fields:
Item_number- Primary key, integer.
CRDLno - Describes what computer the add-on is attached to.
Price - price of the add-on
Date_of_purchase - When the equipment was purchased
What I need to do is to make a report that lists all of the computers that were purchased along with their add-ons which should be displayed below the computer. It should include all of the fields, and the equipment should be sorted by date. There should
be a price total for every Equipment entry and it's add ons, as well as a price total for every year. Here is a sample of what I would like the report to look like:
CRDLno Equipment Name Add-on Price Date of Purchase Total Price Total yearly cost
It doesn't have to look exactly like that, I just figured it would be helpful to illustrate the kind of end result that I am looking to achieve. I am using Visual Basic.NET and the standard ReportViewer control. Any help would be IMMENSELY appreciated, I've
flirted with making my own SQL queries to make it work but I just haven't been able to tackle the problem.
The first thing that you have to do is create a dataset that will support the grouping that you want to do later. Here is the SQL...
SELECT
e.CRDLno
AS EquipmentID
,e.Price
AS EquipmentPrice
,e.Date_of_Purchase
AS EquipmentPurchaseDate
,ao.Item_number
AS AddOnID
,ao.Price
AS AddOnPrice
,ao.Date_of_Purchase
AS AddOnDateOfPurchase
FROM
Equipment e
LEFT
OUTER JOIN [Add-ons] ao
ON e.CRDLno
= ao.CRDLno
Once you have a dataset configured make sure that you can execute it by clicking on the "!" icon in the toolbar. I HIGHLY recommend that you use the SQL Text Editor and the graphical query builder tool.
So now you can move to the Layout tab. Here you will need to use a "Table" control.
Fist start by dragging the table control onto the report surface. Then go to the datasets tab (not the one with the sql) you should see it by the properties.
Drag the Equipment columns one by one onto the middle row of the table. You will see the column names show up in the top row as you drag the data columns on the table.
Now you need to do the grouping. You can right click on the far left side of the middle row (just outside the table) and you will see "Add Group" in the context menu. Go ahead and click on "Add Group".
The expresion that you are grouping on is the EquipmentID column from the dataset. Us the expresion builder to help you with the value of the expression if you aren't familuar with the syntax.
Now on the new group column add the elements from the data set for the add-ons. Do this the same way that you added the equipment columns.
Run the report and tweek it to suite your particular look and feel requirements.
Wow! Great! Thanks so much for the help! I had no idea about the grouping function - that's what was truly holding me back to a large degree. Anyways, thank you so much! You were very helpful, and I really appreciate the in-depth response! Kudos!
It must be a little buggy, I marked it as an answer, then I came back and it was unmarked, so I re-marked it. I'll make sure you get the credit :) The one thing I am currrently hung up on is how to make there be totals for the year's purchases. Like totalling
the cost of all equipment and add-ons for 2004,2005,2006, individually by year. Any clue on how I might be able to do this? I may have to use a different dataset or something; I'm unsure. I'll mark your answer as the answer regardless, but if you have any
idea how I can make it through this problem, it would be thoroughly appreciated. Thanks again!
Also, I can't seem to get more than one add-on to appear for each CRDL. There are some 'equipment' pieces that have multiple add-ons. Is there any way to address this?
So lets talk about the totals. I asume that you have the grouping part done and so I will work from the idea that you have some groups or at least one group as described above.
The grouping above is on the specific hardware and then the addons to that hardware.
Each grouping has a footer and if you work inside that footer row you can add expressions to calculate stuff. For instance if you wanted to caluclate the sum total of all the items in a specific group you could add an expression to the column in the group
footer row like =SUM(datafield-reference-here). I highly recommend that you use the expression builder to do this because the syntax is a bugger. To get to the expression builder just right click on the column in the group footer row that you want to add to
and then look at the common functions in the expression builder. I think that you will find "SUM" under Agregate group in the expression builder.
A better example of SUM is
=Sum(Fields!Total.Value)
Total is the data field in the data set that is mapped to the table you have the grouping in.
No in your specific case above you aren't grouping anything by years so you will need to add that grouping first and then you can sum the columns the same way I described above.
Of couse you have to have the year column in your dataset before you can group on it.
Here is the sql you need to get the year for the purchase based on the sql above. Just add it to the bottom of the SELECT clause
,DATEADD(yyyy, DATEDIFF(yyyy, 0, e.Date_of_Purchase), 0) AS EquipmentPurchaseYear
Once the date is done you can right click on the first row of data, the far left handle outside of the table where you have the Equipment name but not any of the ad ins.
Then you will can select Edit Group and configure the group on the new year column for the quipment.
codycalebra
Member
6 Points
22 Posts
SQL/report confusion
Mar 31, 2007 06:27 PM|LINK
Hey everybody, I'm really boggled on how to get my report to show properly. Here are the fields of my tables that are pertinent to the problem.
Table Equipment : This table contains information about computers that the company has purchased.
Fields
CRDLno - Primary key, Integer.
Price - price of equipment, money
Date_of_purchase - When the equipment was purchased, date
Table Add-ons: This table contains information about add-ons for the computers, such as monitors or printers.
Fields:
Item_number- Primary key, integer.
CRDLno - Describes what computer the add-on is attached to.
Price - price of the add-on
Date_of_purchase - When the equipment was purchased
What I need to do is to make a report that lists all of the computers that were purchased along with their add-ons which should be displayed below the computer. It should include all of the fields, and the equipment should be sorted by date. There should be a price total for every Equipment entry and it's add ons, as well as a price total for every year. Here is a sample of what I would like the report to look like:
CRDLno Equipment Name Add-on Price Date of Purchase Total Price Total yearly cost
1 Gateway computer 1500.00 7/4/04
1 Monitor 200.00 7/4/04
1 Printer 100.00 7/4/04
------------------------------------------------------------------------------------------------------------------------------------------
1800.00
2 Dell computer 1400.00 12/4/04
2 Speakers 50.00 12/4/94
------------------------------------------------------------------------------------------------------------------------------------------
1450.00
-------------------------------------------------------------------------------------------------------------------------------------------
3250.00
It doesn't have to look exactly like that, I just figured it would be helpful to illustrate the kind of end result that I am looking to achieve. I am using Visual Basic.NET and the standard ReportViewer control. Any help would be IMMENSELY appreciated, I've flirted with making my own SQL queries to make it work but I just haven't been able to tackle the problem.
Thank you,
Cody
r_pedersen
Member
161 Points
61 Posts
Re: SQL/report confusion
Mar 31, 2007 07:51 PM|LINK
The first thing that you have to do is create a dataset that will support the grouping that you want to do later. Here is the SQL...
SELECT
e.CRDLno AS EquipmentID
,e.Price AS EquipmentPrice
,e.Date_of_Purchase AS EquipmentPurchaseDate
,ao.Item_number AS AddOnID
,ao.Price AS AddOnPrice
,ao.Date_of_Purchase AS AddOnDateOfPurchase
FROM
Equipment e
LEFT OUTER JOIN [Add-ons] ao ON e.CRDLno = ao.CRDLno
Once you have a dataset configured make sure that you can execute it by clicking on the "!" icon in the toolbar. I HIGHLY recommend that you use the SQL Text Editor and the graphical query builder tool.
So now you can move to the Layout tab. Here you will need to use a "Table" control.
Fist start by dragging the table control onto the report surface. Then go to the datasets tab (not the one with the sql) you should see it by the properties.
Drag the Equipment columns one by one onto the middle row of the table. You will see the column names show up in the top row as you drag the data columns on the table.
Now you need to do the grouping. You can right click on the far left side of the middle row (just outside the table) and you will see "Add Group" in the context menu. Go ahead and click on "Add Group".
The expresion that you are grouping on is the EquipmentID column from the dataset. Us the expresion builder to help you with the value of the expression if you aren't familuar with the syntax.
Now on the new group column add the elements from the data set for the add-ons. Do this the same way that you added the equipment columns.
Run the report and tweek it to suite your particular look and feel requirements.
codycalebra
Member
6 Points
22 Posts
Re: SQL/report confusion
Apr 03, 2007 04:46 PM|LINK
r_pedersen
Member
161 Points
61 Posts
Re: SQL/report confusion
Apr 04, 2007 07:06 PM|LINK
It apears that you marked my reply as the answer but then unmarked it as an answer. Is that right? Do you need more help with this issue?
If you need more help please post a reply to the tread and I will do what I can. If you have got it please give me credit for the answer.
Thanks
codycalebra
Member
6 Points
22 Posts
Re: SQL/report confusion
Apr 05, 2007 03:43 PM|LINK
It must be a little buggy, I marked it as an answer, then I came back and it was unmarked, so I re-marked it. I'll make sure you get the credit :) The one thing I am currrently hung up on is how to make there be totals for the year's purchases. Like totalling the cost of all equipment and add-ons for 2004,2005,2006, individually by year. Any clue on how I might be able to do this? I may have to use a different dataset or something; I'm unsure. I'll mark your answer as the answer regardless, but if you have any idea how I can make it through this problem, it would be thoroughly appreciated. Thanks again!
Cody
codycalebra
Member
6 Points
22 Posts
Re: SQL/report confusion
Apr 05, 2007 04:32 PM|LINK
r_pedersen
Member
161 Points
61 Posts
Re: SQL/report confusion
Apr 06, 2007 06:25 PM|LINK
Thanks for the extra effort on the credit!
So lets talk about the totals. I asume that you have the grouping part done and so I will work from the idea that you have some groups or at least one group as described above.
The grouping above is on the specific hardware and then the addons to that hardware.
Each grouping has a footer and if you work inside that footer row you can add expressions to calculate stuff. For instance if you wanted to caluclate the sum total of all the items in a specific group you could add an expression to the column in the group footer row like =SUM(datafield-reference-here). I highly recommend that you use the expression builder to do this because the syntax is a bugger. To get to the expression builder just right click on the column in the group footer row that you want to add to and then look at the common functions in the expression builder. I think that you will find "SUM" under Agregate group in the expression builder.
A better example of SUM is
=Sum(Fields!Total.Value)
Total is the data field in the data set that is mapped to the table you have the grouping in.
No in your specific case above you aren't grouping anything by years so you will need to add that grouping first and then you can sum the columns the same way I described above.
Of couse you have to have the year column in your dataset before you can group on it.
Here is the sql you need to get the year for the purchase based on the sql above. Just add it to the bottom of the SELECT clause
,DATEADD(yyyy, DATEDIFF(yyyy, 0, e.Date_of_Purchase), 0) AS EquipmentPurchaseYear
Once the date is done you can right click on the first row of data, the far left handle outside of the table where you have the Equipment name but not any of the ad ins.
Then you will can select Edit Group and configure the group on the new year column for the quipment.