May 25, 2011 at 5:48 am
Hi,
I was wondering if someone would be able to help me with a problem I am having when creating a report using a matrix. This is the first time I have tried to use a matrix so forgive me if it is something very simple I have missed. I am using VS 2003 as I am reporting on a 2000 database.
I need to show a list of all members and their Total Customers each Month, The Total Customers will not always be populated as members will start at different times of the year etc
The problem I have is with the following Query:
SELECT a.memNo, B.Formonth, B.totalCustomers
FROM tableA A
LEFT JOIN TableB B ON B.MemNo = A.MemNo AND b.formonth >= '01 jan 2011'
When view the query in Management Studio it appears like
MemNOForMonthTotalCustomers
5555501 jan 20116
5555501 feb 2011 10
5555501 mar 2011 20
66666
However when this is then viewed in my Matrix I get an additional blank column at the beginning.
||01 Jan 2011| 01 Feb 2011| 01 Mar 2011
||TotalCustomers| TotalCustomers | TotalCustomers
55555|| 6| 10 | 20
66666||
I know the problem lies with the Left join but I am not able to work out a way to get rid of the column, If I change the join to a INNER then the column is removed but I am then losing MemNo's.
My question is is there a way to exclude this column from the matrix?
May 25, 2011 at 5:53 am
Do you have nulls in the date or month somewhere in the data?
May 25, 2011 at 7:03 am
Hi Yes, sorry, the MemNo 66666 would have all nulls as they do not have any entries in TableB. this is where I am having the problem.
May 25, 2011 at 7:07 am
You need to do a cross apply for the 12 months of the period to make sure you don't have nulls in that column.
You can also consider having 1 client with all 12 months and then filtering the null data all together.
If you need to show all clients then add 1 blank value for 1 valid month and you'll be set.
I don't do all months for all clients because that adds a lo of work for SSRS to display the data.
May 25, 2011 at 7:25 am
Thank you very much,
since the formonth will be a parameter I wrapped the b.ForMonth in an isnull to be able to get a valid date. This has gotten rid of the blank column.
SELECT a.memNo, isnull(B.Formonth,@formonth), B.totalCustomers
FROM tableA A
LEFT JOIN TableB B ON B.MemNo = A.MemNo AND b.formonth >= @formonth
Thank you very much for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply