my data base table is as follow

Hi, my data base table is as follows

PersonID City Sex 1 New York M 2 Boston M 3 Seatle F

and i need to make a report as shown below:

Male

Female Total % Male City



New York 5 8 13 38% Boston 20 35 55 36% Seatle 10 6 16 63%

I'm assuming i need to use a matrix, but how can i get a total and a % column

You can use a standard table for this. To get the total just use an expression to add male and female together. To get the percentage, once again use an expression to evaluate (male/total)*100.

|||Hi sluggy

I am new to reporting services and about to start creating some (matrix) reports. I am interested in your comment that the example above can be created using a tabular report.

I wonder if you could help me out on this scenario:
Say I have a db table with sales for a particular year. (i.e 2003 and 2004). Sol the database table is: field1 = Sales, field2 = Date ( assume the years are static )
Using a tabular report is it possible to calculate the sums for each...? This effectivly what a matrix report would do very well as it allows grouping by columns.

Any response would be greatly appreciated.

Regards,
Neil
|||You could still use a tabular report for eachyear. Matrix reports are when you have information for rows, columns, details (and page header which is optional). In your case, you just have Rows (which will be Date) and Sales (which is column name) and SUM(Sales) group by year (which will be your table details)|||

NeilSt wrote:

Using a tabular report is it possible to calculate the sums for each...?

It sure is - there are many posts on this forum explaining how to do a total row on a table. Totalling is done at the group level, so you can have subtotals within grand totals.

NeilSt wrote:

This effectivly what a matrix report would do very well as it allows grouping by columns.

A matrix is more for when you have a dynamic number of columns. In your prior example, you could use a matrix if Male, Female, Male percentage and Total were on the rows and you had a column grouping on state. But it is just as easy to do it as a table, with Male/Female/etc being the columns and the states being the rows.

|||Hi

The answer to my post seems to assume that the Years field would be a row. I am saying... Row = sales and column = 2003 and 2004. Its more a column group that is required no ? if so how are they done in tabular reports ?

Maybe im missing something...

thanks for the help again.

Neil

Example:
2003 | 2004 | Total

Sales 100 | 50 | 150
now to get the total is fine i guess.. but sales is made up of a number of sales within the year. So its effectivly a sum of all sales in 2003... thats what im trying to work out how to do.

|||

Neil,

You can sum up your sales for all available years in the database in the SQL query itself like this:

(if you have stored the year in a separate column in the table)

SELECT SUM(Sales) AS Sales, Year

FROM SalesTable

GROUP BY Year

(if you have not stored the year in a separate column in the table but in a datetime column, us this)

SELECT SUM(Sales) AS Sales, YEAR(SalesDate) AS Year

FROM SalesTable

GROUP BY YEAR(SalesDate)

and in your matrix, add a row group on Sales field and column group on Year field.

Shyam

|||Shyam,

Yes that is possible, I think when I first responded to this thread, I was mor einterested to know if its possible to do column grouping in tabular reports...? It seems that is what all this comes down to..

Thanks for your help.

Neil
|||Maybe someone can help me decide which style of report makes sense for this mockup of a report:
Columns: Jan - Dec
Rows: City

So lets say we can to find the number of sales for a city form Jan to Dec...
So looking something like,

Jan | Feb | March | April | May | June | Mid Totals | July |August etc... | Totals for full year

nyc: 1 2 3 3 3 3 15 3 4 x
nj: 1 1 1 1 1 1 6 1 1 x
la: 2 2 2 2 2 2 12 2 5 x

So which is the best report type to use.. ? Matrix supports column grouping ... tabular only supports row grouping from what I can tell...

each record in the db contains a month , sale value and city...

I leave it up to you all to let me know which one is best suited (tabular/matrix)

Neil
|||

Of course Matrix is suitable. But your report layout woulc make more sense if you could have an additional ROW grouping on Year besides City .

Shyam

|||Hey man.

Thanks again, maybe you can explain to me how one can work out the Total for years column...
which calculates the sum of each row.. This is a limitation of the tabular report (no column grouping) ?

Maybe you could point me in the right direction for this situation: Say i have under each row group, subtotals. But for every detail textbox I check the month of that record and only display it in the appropriate column (jan or feb etc..) how can I get the row group sub total to produce a result for each month.. and not a subtotal of the row group (city), I guess this can be done using a SQL query but im wondering if RS can provide some support for it...

I require this table to have static columns and therefore using a matrix seems too limited.. And there are these small issues stopping me from knowing if its possible to implement using a tabular report...

Neil
|||

- Removed -

|||WOAH!

that is one heck of a thread jack. so anyone have an answer for my original question?
|||

I'm sorry, I didn't intend to thread-jack... I thought we were talking about similar things.

My apologies.

|||sorry