asked on

Adding Static Row and Column Names to Matrix Report - SSRS

I have a matrix report in SSRS and because of the table structure I can't get all of the row names and column headers returned by a sql query.

Rows: My rows will be the same for each grouping on the report so they need to be static-

Columns: My column headers are dynamic and are being returned by the query in the cross join
but I am not getting all of the column names. I would like to see these even if there is no match so that I can display a 0 in the grid.

Is there any way that I can add the row names statically and also use the grouping functionality of the matrix to bring back the correct query results?

Thanks
John

ASKER CERTIFIED SOLUTION

sureshbabukrish

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jtrapat1

ASKER

hi-
thanks for the response-
can you explain that answer a little more?
attached is my current query -
i already use a UNION to get a previous year's data;
are you saying I need to add 10 more unions to this query-
one for each row name that i need?.

thanks in advance.
john

Select	Class	= A.OGS_DOB_CLASS_FW
	, FuelType	= C.D750_Fuel_Type
	, PeriodType	= 1
	, Amount	= Count(*)
	, ChangeAmount	= Count(*) * -1
	, ClassSize	= Count(*)
	, ChangeGroup	= 1
	, MilesAmt = SUM(A.MILEAGE_END_REPORT_PERIOD_FW) 
	,A.[OGS_DOB_CLASS_FW],
C.[D750_FUEL_TYPE], 
B.[D750_VEHICLE_TYPE],
A.[MONTH_FW],
A.[YEAR_FW],
A.RECORD_NUMBER_FW,
A.COMPANY_CODE_FW,
A.VEHICLE_ID_FW ,
A.CHASSIS_NUMBER_FW,
A.MILEAGE_END_REPORT_PERIOD_FW,
A.ODOMETER_DATE_FW,
A.DATE_ADDED_TO_INVENTORY_FW,
A.VEHICLE_TYPE_FW 

--From ....
FROM  dbo.OGS_D750_DETAIL_FW A 
INNER JOIN 
[OGS_D750_VEHICLE_TYPE_FW] B 
ON  B.[DOB_CLASS_FW]=A.[OGS_DOB_CLASS_FW] 
AND B.[VEHICLE_TYPE_FW]=A.[VEHICLE_TYPE_FW]
AND B.[NO_OF_SEATS_FW]=A.[NO_OF_SEATS_FW]
INNER JOIN 
OGS_D750_FUEL_TYPE_FW C
ON C.[FUEL_TYPE_FW]=A.[FUEL_TYPE_FW]
AND C.[FUEL_DESIGNATION_FW] = A.[FUEL_DESIGNATION_FW] 

Where A.MONTH_FW = @EndMonth AND A.YEAR_FW = @EndYear 
GROUP BY 	A.[OGS_DOB_CLASS_FW],
		C.[D750_FUEL_TYPE],
C.[D750_FUEL_TYPE], 
B.[D750_VEHICLE_TYPE],
A.[MONTH_FW],
A.[YEAR_FW],
A.RECORD_NUMBER_FW,
A.COMPANY_CODE_FW,
A.VEHICLE_ID_FW ,
A.CHASSIS_NUMBER_FW,
A.MILEAGE_END_REPORT_PERIOD_FW,
A.ODOMETER_DATE_FW,
A.DATE_ADDED_TO_INVENTORY_FW,
A.VEHICLE_TYPE_FW 
--
Union All
Select	Class		= A.OGS_DOB_CLASS_FW
	, FuelType	= C.D750_Fuel_Type
	, PeriodType	= 2
	, Amount	= Count(*)
	, ChangeAmount	= Count(*)
	, ClassSize	= 0
	, ChangeGroup	= 1
		, MilesAmt = SUM(A.MILEAGE_END_REPORT_PERIOD_FW) 
,	A.[OGS_DOB_CLASS_FW],
C.[D750_FUEL_TYPE], 
B.[D750_VEHICLE_TYPE],
A.[MONTH_FW],
A.[YEAR_FW],
A.RECORD_NUMBER_FW,
A.COMPANY_CODE_FW,
A.VEHICLE_ID_FW ,
A.CHASSIS_NUMBER_FW,
A.MILEAGE_END_REPORT_PERIOD_FW,
A.ODOMETER_DATE_FW,
A.DATE_ADDED_TO_INVENTORY_FW,
A.VEHICLE_TYPE_FW 

--From ....
FROM  dbo.OGS_D750_DETAIL_FW A 
INNER JOIN 
[OGS_D750_VEHICLE_TYPE_FW] B 
ON  B.[DOB_CLASS_FW]=A.[OGS_DOB_CLASS_FW] 
AND B.[VEHICLE_TYPE_FW]=A.[VEHICLE_TYPE_FW]
AND B.[NO_OF_SEATS_FW]=A.[NO_OF_SEATS_FW]
INNER JOIN 
OGS_D750_FUEL_TYPE_FW C
ON C.[FUEL_TYPE_FW]=A.[FUEL_TYPE_FW]
AND C.[FUEL_DESIGNATION_FW] = A.[FUEL_DESIGNATION_FW] 
Where A.MONTH_FW IN (@StartMonth,@EndMonth  ) AND A.YEAR_FW IN (@StartYear ,@EndYear ) 
GROUP BY 	A.[OGS_DOB_CLASS_FW],
		C.[D750_FUEL_TYPE],
C.[D750_FUEL_TYPE], 
B.[D750_VEHICLE_TYPE],
A.[MONTH_FW],
A.[YEAR_FW],
A.RECORD_NUMBER_FW,
A.COMPANY_CODE_FW,
A.VEHICLE_ID_FW ,
A.CHASSIS_NUMBER_FW,
A.MILEAGE_END_REPORT_PERIOD_FW,
A.ODOMETER_DATE_FW,
A.DATE_ADDED_TO_INVENTORY_FW, 
A.VEHICLE_TYPE_FW

Open in new window

sureshbabukrish

what column is used for row grouping and what is used for column grouping? and what is columns used in the data region

jtrapat1

ASKER

hi- my field for row grouping is -
[D750_FUEL_TYPE
and my field for column grouping is-
D750_VEHICLE_TYPE

and the data region is-a function right now-
CountRows()

thanks
john

jtrapat1

ASKER

i've created another table with all of the values i need for my row names-
Is there some way that I can create a second dataset-
do a left join and bring in the values i need to show and get the correct counts for my grid?

could i use a cte ?
since this will always be the same 7 or eight rows.

John

jtrapat1

ASKER

hi again.
ive been working on this non-stop since we first spoke and ive come to the conclusion that my database structure is terrible; let me know your thoughts.
this shouldnt be this difficult.
Basically, I have this lookup table that we are calling a crosswalk, and i need to pull in the value where the two fields meet so that i can display this on my report.
well, the lookup table has duplicates and doesnt seem right to me-
attached are the aforementioned fuel-types and vehicle type tables that are THE joining fields that I need for the matrix pivot.
also are the valid values.
is there any way I can re-design these tables?
there are only about 8 fuel types and 22 different vehicle types -
but they are my intersection fields.
I even tried to do a case statement inside my giant union query where I could test 2 values-like this
CASE
WHEN (C.FUEL_TYPE_FW LIKE '%CNG3600%' AND C.FUEL_DESIGNATION_FW = 'DEDICATED') THEN 'NATURAL GAS'
ELSE 'UNKNOWN'
END AS [D750FUELTYPE],
but that didnt work;
also i tried to union these two tables with the available values as lookups but that didnt work either.

Let me know what you think.
Thanks
John
fuel.docx
vehicles.docx