[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Adding Static Row and Column Names to Matrix Report - SSRS

Posted on 2011-04-19
6
Medium Priority
?
1,349 Views
Last Modified: 2012-05-11
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  
0
Comment
Question by:jtrapat1
  • 4
  • 2
6 Comments
 
LVL 9

Accepted Solution

by:
sureshbabukrish earned 2000 total points
ID: 35432995
add a query using UNION to above query

the new query should pull with rowvalue as value( which you have mentioned as static) and columnheader as what you want , if u feel that you should get 10 static cols either with data or with 0
then add 10 queries with UNION
select each rowvalue, column header, 0
like this 10 rows
0
 

Author Comment

by:jtrapat1
ID: 35433176
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

0
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 35433299
what column is used for row grouping and what is used for column grouping? and what is columns used in the data region
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:jtrapat1
ID: 35433461
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
0
 

Author Comment

by:jtrapat1
ID: 35433788
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  
0
 

Author Comment

by:jtrapat1
ID: 35451011
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question