[princejlynn]

[quote]

This is the assignment:

• Create SQL database Chart of Accounts report. The report will display all fields with a break based on the first two digits of the account number and subtotal of the balance field at each break. A grand total of the balance field will be provided at the end of the report. Submit query, screen shots of output(MS Word) and Chart of Accounts report for grading.


Below is the query statement I have been trying to use, but I can't seem to figure out the syntax to get it to group the account numbers. The report is giving me the balance of each account number instead of the group of account numbers. For example, I have several account numbers beginning with 12 and several with 13, 14, etc. How do I group all of the 12's and get their balance, and the 13's, etc. Any assistance would be much appreciated. Thanks.
[quote]

SELECT Accounts.AccountID, SUM(Inventory.QuantityAvailable * Items.ItemPrice) AS 'Balance'
FROM Store_Locations, Inventory, Items, Accounts, Departments
WHERE Store_Locations.LocationID=Accounts.Location2ID
	AND Inventory.ItemName2 = Items.ItemName
	AND Accounts.Location2ID = Store_Locations.LocationID
	AND Accounts.Department2ID = Departments.DepartmentID
	AND Accounts.Inventory2ID = Inventory.InventoryID
GROUP BY AccountID
WITH CUBE;

[baavgai]

princejlynn, on 8 Jun, 2008 - 10:09 AM, said:

Create SQL database Chart of Accounts report. The report will display all fields with a break

Let's take if from the top. You're suposed to create a report. Further, you're told to have breaks and sub totals and all kinds of junk that SQL doesn't do! Your query has issues because it only shows two fields. I'm not sure how cube is used here; don't worry, you don't need it.

First, you need to determine all the fields you need to show in your report and wrote a basic SQL statement that returns all of them. You then need to decide what kind of reporting tool you're going to use.

If this is Oracle, there is some rudimentary stuff in SQLPlus. If it's SQL Server 2005 you may have access to reporting services. There's crystal reports or MS Access. The list of possible reporting tools is long, which one are use using?

[princejlynn]

Quote

Thanks for the response. I am aware of all of the other fields, but at the moment I was just trying to get functionality. I will add the rest in at the end, unless that effects my query. The cube was experimental, and you are right, did nothing for me. I am using SQL Server 2005 With SQL Server Business Intelligence Reporting Tool. So, you are saying, that it is not really the query that is the problem? It is the reporting tool? Thanks.

[princejlynn]

Ok.....I have tried this:

SELECT left(Accounts.AccountID,2), SUM(Inventory.QuantityAvailable * Items.ItemPrice) AS 'Balance'
FROM Store_Locations, Inventory, Items, Accounts, Departments
WHERE Store_Locations.LocationID=Accounts.Location2ID
	AND Inventory.ItemName2 = Items.ItemName
	AND Accounts.Location2ID = Store_Locations.LocationID
	AND Accounts.Department2ID = Departments.DepartmentID
	AND Accounts.Inventory2ID = Inventory.InventoryID
GROUP BY left (AccountID,2);

This actually works when I execute it, but when I try to preview the report, I get the following error:

[rsFieldReference] The Group expression for the table ‘table1’ refers to the field ‘AccountID’.  Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘AccountID’ refers to the field ‘AccountID’.  Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Build complete -- 2 errors, 0 warnings

Any suggestions? This is due tomorrow 6/9. Thanks.

[baavgai]

It you want to use it in a report, all fields need a name the first one doesn't have it.

e.g.

SELECT left(Accounts.AccountID,2) as Acct, SUM(Inventory.QuantityAvailable * Items.ItemPrice) AS Balance
...

[princejlynn]

baavgai, on 9 Jun, 2008 - 04:33 AM, said:

It you want to use it in a report, all fields need a name the first one doesn't have it.

e.g.

SELECT left(Accounts.AccountID,2) as Acct, SUM(Inventory.QuantityAvailable * Items.ItemPrice) AS Balance
...

ok, this is working, but now I need to get the grand total of all of the groups. I am getting the balance of each account group. How do I add all of it up to get that grand total? It does not recognize balance in this query as it hasn't been created at this point.