Matrix report by weekly - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Matrix report by weekly

Author

Topic

lily_wibisana
Starting Member

29 Posts

Posted - 2008-04-15 : 10:53:41

I need to display matrix report by weekly for half year (Jan – Jun)
Customer Name.
Prd wk1 wk2 wk3 wk4 wk5 wk6 wk7 wk8
A $2 $2 $2
B $3 $3 $3
C $4 $4 $4
.

How do I Display the whole week even though there are no data for certain week.
The report is by Customer per page.
My problem is, when there is no data for certain customer in certain week, the week doesn’t appear in the report.
Ex:
Customer Name.
Prd wk2 wk3 wk4 …..wk26
A $2 $2
B $3 $3
C $4
I need every single week is displayed in the report even there is no data for those week.
Greatly appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 13:16:54

Thats how the matrix works. It renders column dynamically based on data avialble. What you could do is to include a dummy row with 0 as value for each week so that it wont affect your figures and you will always have all weeks appearing in the report.

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-04-15 : 13:46:44

try this out...it works...may be you can create a stored proc based on the similiar logic below and then call that from your reporting service report...

USE Northwind
GO

CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
INSERT INTO Pivot VALUES (1992, 1, 3.1)
INSERT INTO Pivot VALUES (1992, 2, 3.2)

GO

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-04-15 : 13:48:46

this is the result of the above query.... as you can see q3 and q4 for 1992 have zeros but are still showing values...
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4
1992 3.1 3.2 .0 .0

lily_wibisana
Starting Member

29 Posts

Posted - 2008-04-16 : 08:41:50

But why when I created my SUM(Case()) query, it created double record.

CustName Prod wk1 wk2 wk3 wk4 wk5 wk26
AAA A 1
AAA A 1
AAA A 1
AAA B 1
AAA B 1
AAA B 1

Acturally the data is like this.
CustName Prod wk1 wk2 wk3 wk4 wk5 wk26
AAA A 1 1 1
AAA B 1 1 1
What did I do wrong? I followed your query statement.

lily_wibisana
Starting Member

29 Posts

Posted - 2008-04-16 : 08:44:39

Hi Visakh,
Can please give an example how to make dummy row? Do you mean use union?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 08:51:39

Yup use UNION ALL to add the dummy row query to your query.If you want more help please post your current query giving weekly values 7 your table DDL.

lily_wibisana
Starting Member

29 Posts

Posted - 2008-04-16 : 14:39:33

Yes, I used dummy union all. but the things is when I run my matrix report group by customer name, the customer who doesn't have the transaction for certain week, the week doesn't appear in the report.
since my report in reporting services, is list, and matrix.
in the list dataregion, I group by customer name and in that list, I have matrix.
If I just have matrix report, the report is fine. all weeks come up.
But I want the first option. since matrix report is not flexibel. when I group the fields, it take 1 coloumn already. and I have 2 group actually and another prod description and the price. go wide to the right side with 26 weeks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources