Add Matrix Columns - SSRS

in the datasource that you are assigning, is it possible to club these two columns ?

assuming that you are using column group i think that can be done...

what do you mean.

in the matrix column i put dbo.Bookht_Portal_State.ENG_NAME
in the matrix row i use grouping like this dbo.Bookht_Terminal_Ports_Snapshot.date  - then dbo.City.City - then dbo.bookht_TelCenter.Name
and in the matrix data i put the dbo.Bookht_Terminal_Ports_Snapshot.cnt

my query is like this.

SELECT     dbo.Bookht_Terminal_Ports_Snapshot.cnt, dbo.Bookht_Portal_State.ENG_NAME, dbo.City.City, dbo.bookht_TelCenter.Name,
                      dbo.Bookht_Terminal_Ports_Snapshot.date
FROM         dbo.Bookht_Terminal_Ports_Snapshot INNER JOIN
                      dbo.Bookht_Portal_State ON dbo.Bookht_Terminal_Ports_Snapshot.STATE_ID = dbo.Bookht_Portal_State.STATE_ID INNER JOIN
                      dbo.bookht_TelCenter ON dbo.Bookht_Terminal_Ports_Snapshot.TelCenterID = dbo.bookht_TelCenter.pkid INNER JOIN
                      dbo.City ON dbo.Bookht_Terminal_Ports_Snapshot.CityID = dbo.City.pkid
WHERE     (dbo.Bookht_Terminal_Ports_Snapshot.date BETWEEN @startdate AND @enddate)

change you query like
(assuming that ENG_NAME is having the DESCRTION "Problem" Outofservice etc)

if it is someother field let me know which field holds that value

SELECT     dbo.Bookht_Terminal_Ports_Snapshot.cnt, case when dbo.Bookht_Portal_State.ENG_NAME = 'Out Of Service' then 'Problem' else dbo.Bookht_Portal_State.ENG_NAME end as ENG_NAME, dbo.City.City, dbo.bookht_TelCenter.Name,
                      dbo.Bookht_Terminal_Ports_Snapshot.date
FROM         dbo.Bookht_Terminal_Ports_Snapshot INNER JOIN
                      dbo.Bookht_Portal_State ON dbo.Bookht_Terminal_Ports_Snapshot.STATE_ID = dbo.Bookht_Portal_State.STATE_ID INNER JOIN
                      dbo.bookht_TelCenter ON dbo.Bookht_Terminal_Ports_Snapshot.TelCenterID = dbo.bookht_TelCenter.pkid INNER JOIN
                      dbo.City ON dbo.Bookht_Terminal_Ports_Snapshot.CityID = dbo.City.pkid
WHERE     (dbo.Bookht_Terminal_Ports_Snapshot.date BETWEEN @startdate AND @enddate)

thanks mate that worked by manipulating the sql server.can i do this directly in the Visual studio.

Also I want to know how can i show the column header when i scroll down the page.this feature we have in table.but i dont see this in matrix.how can i do this.please help.

thanks once again mate.

right click on the group --> Edit group -> there is an option(checkbox) which will say display column header while scrolling or something like that...check that out

thanks mate.your help was very helpful.I appreciate your help and time.

cheers
zolf

glad that iam helpful...:-))cheers

jaiganeshsrinivasan

i have modifed your query to combine more columns.is it possible to change their headings


SELECT     dbo.Bookht_Terminal_Ports_Snapshot.cnt, CASE WHEN dbo.Bookht_Portal_State.ENG_NAME = 'Out Of Service' THEN 'Problem'
WHEN dbo.Bookht_Portal_State.ENG_NAME = 'Under Construction' THEN 'Active User'
WHEN dbo.Bookht_Portal_State.ENG_NAME = 'Bucht Released' THEN 'Ready To Use'
ELSE dbo.Bookht_Portal_State.ENG_NAME end as ENG_NAME, dbo.City.City, dbo.bookht_TelCenter.Name,
                      dbo.Bookht_Terminal_Ports_Snapshot.date
FROM         dbo.Bookht_Terminal_Ports_Snapshot INNER JOIN
                      dbo.Bookht_Portal_State ON dbo.Bookht_Terminal_Ports_Snapshot.STATE_ID = dbo.Bookht_Portal_State.STATE_ID INNER JOIN
                      dbo.bookht_TelCenter ON dbo.Bookht_Terminal_Ports_Snapshot.TelCenterID = dbo.bookht_TelCenter.pkid INNER JOIN
                      dbo.City ON dbo.Bookht_Terminal_Ports_Snapshot.CityID = dbo.City.pkid
WHERE     (dbo.Bookht_Terminal_Ports_Snapshot.date BETWEEN @startdate AND @enddate)

in the query ? or in your report ? if in your report what do you want to change to ?

in my report i want to see User instead of active user in the Column header

use this.....

SELECT     dbo.Bookht_Terminal_Ports_Snapshot.cnt,
CASE WHEN dbo.Bookht_Portal_State.ENG_NAME = 'Out Of Service' THEN 'Problem'
WHEN dbo.Bookht_Portal_State.ENG_NAME = 'Under Construction' THEN 'User'
WHEN dbo.Bookht_Portal_State.ENG_NAME = 'Bucht Released' THEN 'Ready To Use',
WHEN dbo.Bookht_Portal_State.ENG_NAME = 'Active User'  THEN 'User'
ELSE dbo.Bookht_Portal_State.ENG_NAME end as ENG_NAME, dbo.City.City, dbo.bookht_TelCenter.Name,
                      dbo.Bookht_Terminal_Ports_Snapshot.date
FROM         dbo.Bookht_Terminal_Ports_Snapshot INNER JOIN
                      dbo.Bookht_Portal_State ON dbo.Bookht_Terminal_Ports_Snapshot.STATE_ID = dbo.Bookht_Portal_State.STATE_ID INNER JOIN
                      dbo.bookht_TelCenter ON dbo.Bookht_Terminal_Ports_Snapshot.TelCenterID = dbo.bookht_TelCenter.pkid INNER JOIN
                      dbo.City ON dbo.Bookht_Terminal_Ports_Snapshot.CityID = dbo.City.pkid
WHERE     (dbo.Bookht_Terminal_Ports_Snapshot.date BETWEEN @startdate AND @enddate)

thanks mate.

cheers...