building up a matrix 1

[mjd3000]

I have the following SQL that I want to display in a report with totals grouped by year, quarter, month and week.

select case statecode when '0' then 'Qualified' when '1' then 'Open' when '2' then 'Disqualified' end as 'Status',
datepart(year, createddate) as 'year', datepart(quarter, createddate) as 'quarter',
datename(month, createddate) as 'month',
DATEPART(week, createddate) - DATEPART(week, DATEADD(month, DATEDIFF(month, 0, createddate), 0)) +1 as 'week',
count(*) as 'enqtotal'
from bo_main
group by datepart(year, createddate), datepart(quarter, createddate), datename(month, createddate),
DATEPART(week, createddate) - DATEPART(week, DATEADD(month, DATEDIFF(month, 0, createddate), 0)) +1,
statecode
order by datepart(year, createddate), datepart(quarter, createddate), datename(month, createddate),
DATEPART(week, createddate) - DATEPART(week, DATEADD(month, DATEDIFF(month, 0, createddate), 0)) +1,
statecode

I have added a matrix to my report, and then added column groups for year (=Fields!year.Value), quarter (=Fields!quarter.Value), month (=Fields!month.Value) and week (=Fields!week.Value). Then I have a row under these columns showing =Fields!Status.Value to the left and =Fields!enqtotal.Value underneath the column groups.

I now need to add a row with the total of each enqtotal for each year/quarter/month/week combination. But upon right clicking my =Fields!enqtotal.Value cell, there is no option to add a subtotal.

This is my first report matrix, so could somebody tell me what I am doing wrong? If you need further explanation than please let me know.


Many thanks!

 

[jymm]

I have done reports where you get this out of a TABLE (not a martix)
Jan count
Feb count
Apr count
Q1 total
...
grand total

and also have done similar with a MATRIX where the number of columns to be 'analyzed' is variable - so this report was showing
Quarter Month Product Name
Pacakaging (Bags/Bulk/Truck...)
Q1 Jan count
...

so I would get a variable number of columns depending on the number of products or packaging options for that month & quarter.

which do you really want? a variable number of columns or a static number of columns?

btw - if you are looking for the matrix subtotals Right click on the group field in the matrix and 'Subtotal' is one of the options.

 

[mjd3000]

Hi,

What I am looking to produce is an output like this :

2009...............etc
Q1 Q2 Q3 Q4
Jan Feb Mar Apr May...etc
Wk1 Wk2 Wk3 Wk4 Wk 1 Wk 2...etc
Opt1 2 1 5
Opt2 4 0 4
Opt3 3 4 1
Total 9 5 10

Yet when I right click on the group field there is no Subtotal option listed. Am I going about this in totally the wrong way?

 

[jymm]

and if Opt1, opt2.. are a static number of things, a table might be easier than a matrix - my two cents

with that - may I suggest something

check out this guy's post on advanced matrix techniques... some of the terminology and examples are the best I found

http://www.simple-talk.com/sql/sql-server-2005/advanced-matrix-reporting-techniques/

also check out thes MS tutorials - a few of them made it SO simple when I started a few years back

http://msdn.microsoft.com/en-us/library/ms170246.aspx

with that said - I like to teach folks HOW to fish so please do not take this as a brush off... you just might be able to find the answer quicker than I can provide you help through Tek-Tips