Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Add Matrix Columns - SSRS

Posted on 2007-10-17
14
Medium Priority
?
1,899 Views
Last Modified: 2010-08-05
hello there,

please have a look at the snapshot.i want to be able to add some of the columns together.i mean combine them into one.for example column Problem and Out of Service column i want to add their values together and have them under one column.please help i need this badly.

http://good-times.webshots.com/album/560907322SMsknK

cheers
zolf
0
Comment
Question by:zolf
  • 8
  • 6
14 Comments
 
LVL 14

Expert Comment

by:Jai S
ID: 20098750
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...
0
 

Author Comment

by:zolf
ID: 20098789

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)
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20098822
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)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:zolf
ID: 20098887

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.
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20098894
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
0
 
LVL 14

Accepted Solution

by:
Jai S earned 2000 total points
ID: 20098909
the option is
"group heading should remain visible while scrolling"
when you right click on a group you will get edit group and you will see this option
0
 

Author Comment

by:zolf
ID: 20098939


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

cheers
zolf
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20098973
glad that iam helpful...:-))cheers
0
 

Author Comment

by:zolf
ID: 20099231

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)
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20099239
in the query ? or in your report ? if in your report what do you want to change to ?
0
 

Author Comment

by:zolf
ID: 20100095

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

Expert Comment

by:Jai S
ID: 20100126
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)
0
 

Author Comment

by:zolf
ID: 20113642

thanks mate.
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20114006
cheers...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
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. …

572 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