I am trying to change the row and column heading to make it more meaningful in MDX.
For example instead of showing the_actual_cost, i want to show cost. This is done easily in SQL like
SELECT the_actual_cost AS cost......
Any help will be greatly appreciated.
Thanks
Mahesh
I am using Column Groups:
And I am getting:
Note how Today is being repeated three times. What I want is Today appearing once and centered (merged cell). Is this possible?
Hi there,
I am a new member of this site and I am not very much aware of T-sql's working.
My question is what if I need to get one column's data to be the heading of another column.
To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this
StuID - - - SubID - - - -Tot - - -Obt
1 - - - - - - -1 - - - - - - -50 - - - 38
1 - - - - - - -2 - - - - - - -50 - - - 41
1 - - - - - - -3 - - - - - - -50 - - - 42
1 - - - - - - -4 - - - - - - -50 - - - 40
2 - - - - - - -1 - - - - - - -50 - - - 35
2 - - - - - - -2 - - - - - - -50 - - - 40
2 - - - - - - -3 - - - - - - -50 - - - 42
2 - - - - - - -4 - - - - - - -50 - - - 41
StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of
StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4
1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40
2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41
The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted.
Thanks.
we have a table called evaluation_questions, the table has following fields
queId,Question, level, parentId
the queId is primary key(auto number),where as the field "question" will have question, heading or the subheading,
the level describes the hierarchy of the field "Question", 0 means its a heading, 1 means a subheading and 2 means a question.
where as the parentId means describes the immediate parentId, like if it is 1, then the parent will be English heading....
queIdQuestionlevelparentId
1English00
2Reading11
3Writing11
4recognizing words12
5Fluency22
so English is a heading as level is 0 and has no parent as parentId is also 0.
Reading is a subheading as level is 1 and has a prent English as its parentId=1 which is the queId oof English
same is the case with writing
where as recognizing words and fluency both are questions as the level is 2 and their parentId is 2 which means they come under reading.
Output:
Now What i want is to retrieve the all the questions and headings under a specified heading.like if i pass parentID as a parameter to stored
procedure i should get all the headings and questions under a specified parentID.i need to fill the dataset with it.
I have a table like this
EMPnoSERISSTATUS jOB POINTS
1AAPOC12
1BAQCS5
2CPREP4
2AASOS1
5AATDS4
1APVSS320
and i need a query or Stored prodedure to get data in the below format
EMPNOABC
133250
2104
5400
Note: A, B,C ... are field name in table (SERIS) and i would like that data as the column heading.
I dont want to use below query , as we dont know the value is dyanamic
SELECT empno,
SUM(CASE seris WHEN <B>'A'</B> THEN POINTS END ) AS <B> 'A' </B>
SUM(CASE seris WHEN <B> 'B' </B> THEN POINTS END ) AS <B>'B'</B>
FROM TABLE1
GROUP BY EMPNO
Hello,
I have a simple question, can someone provide syntax to wrap a column heading?
Example: Select sum(netprice) as 'Current YTD Invoice Amount'
I would like to see the output's column heading as:
Current YTD
Invoice Amount
----------------------
####.##
Thank you in advance,
Maria
I would like to make the column heading to be the current year for the Sales I'm adding below.
SELECT dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no,dbo.arslmfil_SQL.slspsn_name,
SUM(CASE WHEN year(getdate()) = qivsalesmtdytdcustsalesperson.year THEN Sales END) AS convert(varchar(4),year(getdate()))
FROM dbo.QIVSalesMTDYTDCustSalesPerson INNER JOIN
dbo.arslmfil_SQL ON dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no = dbo.arslmfil_SQL.humres_id
GROUP BY dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no, dbo.arslmfil_SQL.slspsn_name
What I have now gives me incorrect syntax near keyword convert.
Hi All,
I have never used PIVOT before but looks exactly what I want for this scenario:
I have rows of dates associated with ID of Hotels and Room avalability for each Hotel/Date..... I want to show the sum of the rooms per date as columns I am using something like this:SELECT dbHotelID ,[09/20/2007]as [Today],[09/21/2007]as [Today+1],[09/22/2007]as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbRoomNumber) FOR AvailableDate IN ([09/20/2007], [09/21/2007], [09/22/2007])) AS pAs you can see I know how may days I want in advance so know how many columsn so its not dynamic.. I just dont know what the dates are:I would like to do something like:
DECLARE @todayDate varchar(255),
DECLARE @todayPlusOne varchar(255),
DECLARE @todayPlusTwo varchar(255) SET @todayDate = CONVERT(CHAR, GETDATE(),101)SET @todayPlusOne = CONVERT(CHAR, DATEADD(d, 1, GETDATE(),101)SET @todayPlusTwo = CONVERT(CHAR, DATEADD(d, 2, GETDATE(),101) SELECT dbHotelID,@todayDate as Today,@todayPlusOne as [Today+1],@todayPlusTwo as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbHotelRoomAvailabilityNumber) FOR AvailableDate IN ([@todayDate], [@todayPlusOne], [@todayPlusTwo])) AS pBut I can’t seem to put the variable in the PIVOT value list or GETDATE() Anyone got any ideas or do I just try and do this another way and forgot PIVOT. I am using sql server 2005 express.
Thanks in advance.
Lee
In SSRS/RDL, How do you format a column heading to use carriage returns?
I have a couple of instance where I have a column heading that I want spread over 3 lines. For example, the column heading "= Actual Amount" I would want centered and displayed on 3 lines, as follows:
=
Actual
Amount
We run 2014 enterprise. I tried this with both table and matrix controls to no avail.
In the table scenario, I drag the table control over, instruct ssrs that a group name will go into column 1 and a sales figure in column2. Then I highlight the sales figure cell, add a column group on month number and generate my report off some june and july data.
ssrs understands that the months now expand horizontally but the rows alternate one with june filled in (blanks in july) and the next with july filled in for the same group name. I believe I got all my sort by conditions set but am not sure.
I tried all sorts of combos in the tablix and group properties before giving up.
i just clicked on Advanced mode in Column Group, and then in Row Group Side i set Fixed Data=true for first top static. I'm using local report not server report and i'm displaying that local report in Reportviewer. Now also its not working....
View 6 Replies View RelatedHello:
I have a situation where our users will key into a parameter field the name for the heading. I callled the parameter heading VarHdg1. How do I go about doing this? I tried printing @VarHdg1 but nothing came out. Please advise.
Thanks
Hi all,
I have a table that has integrity constraints in place and it is populated with the data. I need to rename one of the columns in the table. I am hesitant to use sp_rename procedure because when I used it before I get a warning message that says "changing any part of an object name could break scriptd and stored procedure." how big of a problem is it? Is there any other way to do it without hearting anything? Thanks.
How can you change a Column name for MS SQL 2000 ?
something like :
ALTER TABLE [dbo].[MyTable]
OldName RENAME NewName
GO
thank you
ie if i havex 1 2 3 4 5------------1| a b c d e2| f g h i j3| k l m n o4| p q r s t5| u v w x yand i want to change it tox 1 2 3 4 5------------1| a b c d e2| a b c d e3| a b c d e4| a b c d e5| a b c d eany ideas on how to do that?
View 2 Replies View RelatedHi
I have a query which produces effectively a pivottable. Is there any way I can dynamically assign the column headings ie the code on each line after AS rather than hard coded as I have currently
Extract of Current SP
CREATE PROC dbo.FairValeSummaryPivot
@BatchRunID INT
AS
SET NOCOUNT ON
SELECT
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn1 - Tn0' ,
MIN(CASE WHEN Tn = '0' THEN PVBalance END) AS 'Tn0 - Tn-1' ,
MIN(CASE WHEN Tn = '-1' THEN PVBalance END) AS 'Tn-1 - Tn-2',
MIN(CASE WHEN Tn = '-2' THEN PVBalance END) AS 'Tn-2 - Tn-3',
MIN(CASE WHEN Tn = '-3' THEN PVBalance END) AS 'Tn-3 - Tn-4',
MIN(CASE WHEN Tn = '-4' THEN PVBalance END) AS 'Tn-4 - Tn-5',
-- and so on
FROM FVSummary
WHERE BatchRunID = @BatchRunID
GO
what I would like would be along the lines of
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn' + Tn + ' - Tn' + Tn-1, ,
Hope this is clear
Cheers
I'm trying to create a heading for my report.
I want
sales figures between 09/11/2006 to 09/04/2007.
The date comes from parameters
What I have done is created 4 text boxes
textbox 1 = sales figures between
textbox 2 = =datepart("D", Parameters!param_datef.Value) & "/" & datepart("M", Parameters!param_datef.Value) & "/" & datepart("YYYY", Parameters!param_datef.Value)
textbox 3 = To
textbox 4 = =datepart("D", Parameters!param_datet.Value) & "/" & datepart("M", Parameters!param_datet.Value) & "/" & datepart("YYYY", Parameters!param_datet.Value)
Ok the problem is the text boxes when previewed are all over the place.
In crystal reports you can add a formula into a text box after text
Is this sort of thing possible in Reporting services
Thanks for all your help
Hello :
In the heading of the report, I want to put one text area, or i put a datum of the base.
He says to me :
Fields cannot be used in headings o feet of page.
Have you ideas??
Thank you very much
Hello I am having a table
table1
col1 (bit)
and i want to changethe col1 type for smallint
col1 (smallint)
true will be = 1
and false = 0
how can i do it ??
thank you
Hi all,
I have a problem writing a query to change row to column with some conditions and computations. It's appreciated if you guys can give some ideas.
Below is the sample data:
CREATE TABLE Item (Line int, Code nvarchar(8),Price decimal, Check nvarchar(1))
INSERT INTO Item VALUES (1,'001',200 ,'Y')
INSERT INTO Item VALUES (2,'002',300 ,'Y')
INSERT INTO Item VALUES (3,'003',500 ,'Y')
INSERT INTO Item VALUES (4,'004',1000,'N')
INSERT INTO Item VALUES (5,'005',2000,'N')
The expected result :
Line with check = 'N' must be converted to column(s).
Line Code Price Item004 Item005
1 001 200 1000 * (200/200+300+500) 2000 * (200/200+300+500)
2 002 300 1000 * (300/200+300+500) 2000 * (300/200+300+500)
3 003 500 1000 * (500/200+300+500) 2000 * (500/200+300+500)
Note : I would like to avoid using any cursors if possible.
Thanks in advance.
cheers,
erwine
... sql is fun...
Does anybody know how I can change the default value for a column?I was trying to remove the default value in order to add the new oneafterwards. This is what I tried:alter table /table-name/ drop default for /column-name/alter table /table-name/ alter column /column-name/(/new-decl-without-default/)It did not work. I cannot find a solution in the documentation. Maybeyou can help me out?Thank you,Johan
View 4 Replies View RelatedHi All,I am trying to change column name on an existing table. I am using SQLServer 7.As the table is quite big, it is taking quite long time to do it.By the way I could change the column name only through the EnterprizeManager.Is it possible to change the column name using SQL script?Why the change of column name will depend on the size of the table?Thanks for your answer.-Mokles
View 4 Replies View RelatedHi. Is there a way to remove a gap between identity column values? Some records were deleted and now we have identity column values that jump all over the place.
View 4 Replies View RelatedI want to change a column's data type from bit to int. There are data in the table already. I'm wondering if it is save/correct way to issue the following command to change the data type for that column.ALTER TABLE database_tableALTER COLUMN my_bit_column INT; Thanks.
View 1 Replies View RelatedHi guys,
Is there any way or method to CHANGE the DATATYPE of a column in a published table being used for transactional replication (MSSQL 2000), WITHOUT DROPPING THE SUBSCRIPTION ????
Im stuck in this mess and do have the option to drop the subscription, alter the table, create the subscription and rerun the snapshot or to recreate it by Manual Synchronisation either.
Can anyone help? Has anyone been across this dilemma before and have troubleshooted the problem? If yes, help is much appreciated.
MY PROBLEM:
~~~~~~~~~~~~~
'MyTable' is currently being published and has subscriptions to it. The PRIMARY KEY column 'id' has an Identity property as well. 'id' is of datatype smallint, however because of bad planning, i now need to change that datatype to an integer to support a larger range WITHOUT DROPPING SUBSCRIPTIONS.
I CANT DROP THE COLUMN EITHER AS IT IS BEING THE PRIMARY KEY COLUMN.
IS THERE ANY OTHER WAY I CAN DO TO ARCHIEVE MY GOAL? THANKYOU.
I have a problem with a union all that seems to be changing the results of a column based on using the union, when I run the querys separatly I get the expected results in the field!
Basically 3 queries pulled together for reporting purposes.
Orders (which can include a gift so the gifts are separated in the second query).
OrderGifts (pulled out of the orders).
Gifts (straight gifts with no associated orders).
The problem is showing up in the first ORDERS query. I can pull a specific order and the calculation for the AMOUNT(eg:$15.20) is correct, but when I combine the tables I get a different AMOUNT(eg:$15.199999999999999).
Here are the values for the fields in the amount calc:
totitems=12.9500
totcredits=.0000
discount=.0000
tax=.0000
refund=.0000
convrate=1.0
postage=2.2500
giftamt=15.0000
I tried converting each of the fields to an decimal befre the calc and same results
I tried ROUND and same thing!
I have been trying to chase this down all day, cant figure out what the problem is or how to get around it...whats going on here I am missing?
Here is the query, if you need any other info just let me know, I would GREATLY appreciate ANY direction of figuring this out!
select * from
(
--orders
select
acctnbr as PartnerId,
'O' as TrnType,
ordnbr as TrnId,
0 as FundId,
odate as WorkDate,
adate as PostDate,
sdate as ShipDate,
cast(
(
IsNull(totitems, 0) -
IsNull(totcredits, 0) -
IsNull(discount, 0) -
IsNull(tax, 0) -
IsNull(refund, 0) +
IsNull(postage, 0)
) * IsNull(convrate, 1)
as Decimal(10,2))
as Amount,
batchnbr as BatchId,
motvcode as MotivationCode,
currcode as CurrencyType,
Status,
paycode as PaymentType,
paytype as CardType,
paynbr as CardNbr,
expire as CardExpire,
ChargeAuth,
ConvRate,
MediaCode,
hcf.dbo.KmaiProjMotvTest(0,motvcode) as ProjMotvName
from kmai.dbo.o01omst
UNION all
--orders gifts
select
acctnbr as PartnerId,
'G' as TrnType,
giftref as TrnId,
giftfundid as FundId,
odate as WorkDate,
null as PostDate,
null as ShipDate,
ROUND((giftamt * IsNull(convrate, 1)),2) as Amount,
batchnbr as BatchId,
giftmotvcode as MotivationCode,
currcode as CurrencyType,
Status,
paycode as PaymentType,
paytype as CardType,
paynbr as CardNbr,
expire as CardExpire,
ChargeAuth,
ConvRate,
MediaCode,
hcf.dbo.KmaiProjMotvTest(giftfundid,giftmotvcode) as ProjMotvName
from kmai.dbo.o01omst
where adate is null
and giftfundid <> ''
UNION ALL
--gifts
select
acctnbr as PartnerId,
'G' as TrnType,
gh.giftref as TrnId,
convert(int, fundid) as FundId,
gdate as WorkDate,
applydate as PostDate,
null as ShipDate,
ROUND((IsNull(gd.amt, 0) * IsNull(convrate, 1)),2) AS Amount,
batchnbr as BatchId,
motvcode as MotivationCode,
currcode as CurrencyType,
Status,
paycode as PaymentType,
paytype as CardType,
paynbr as CardNbr,
expire as CardExpire,
ChargeAuth,
ConvRate,
MediaCode,
hcf.dbo.KmaiProjMotvTest(fundid,motvcode) as ProjMotvName
from kmai.dbo.g03ghdr gh
inner join kmai..g04gdtl gd
on gh.giftref = gd.giftref
)
UnionOfGiftsAndOrders
Dear All,I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.trying to use:exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'I get:Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.There is no mistype, the table name and column name are correct.Can temp table's column names not be altered?If yes, how?Thanks in advance!
View 7 Replies View RelatedI'm new to mssql.
I need to change the size of a column of a table from char(255) to char(500). I used the line:
alter table table_name alter column column_name char(500)
When I run that command, I get a message that it was sucessful. However, when I try to enter data into the changed column, the number of characters I can enter is still 255. I check the information schema for the column and the 'character_maximum_length' field is 500.
What is the problem here? Is the maximum allowable length for char 255? How can I get a column to have 500 characters?
I am trying to make the following update: All the columns are fine except for the 'name' column. datatype for 'name' column in the target table is varchar(30) and the datatype for 'name' in the sourcetable is varchar(40), I cannot change the datatype of the column 'name' to varchar(40) because I am told it may affect performance. what I want to do is just update the first 'name' column of the target table by the first 30 characters of the source table column 'name'
I am using the following query, is it possible to do it or are there any other ways I can update the column without changing the datatype?
MERGE INTO [S].[dbo].[AF_Copy] AS TargetTable
USING (SELECT source_code, name, addr1, city, zip FROM
[D].[D_TEST].[dbo].[SO_Copy]) AS SourceTable
ON ([TargetTable].[Code] = [SourceTable].[source_code])
[code]...
Hi,
I was wondering how to modify a column's properties, like its datatype or length.
I know I'm supposed to do something like this:
ALTER TABLE MyTable ALTER COLUMN MyColumn int(20)
My main concern is, what will the database do to all the data in the column, if the column used to be, say nvarchar(50)? Will I lose the data in the column if I change the datatype? And what if I had data in this column that was longer than 20 characters? Will the data now be truncated? What can I do to make sure that nothing happens to the data once I change the datatype?
Any help would be greatly appreciated! Thanks! =)
Hi,
Is it possible to change the format of the identity column? i.e. I have and integer value that will be the ID for the records to follow in the database. Instead of the values being 1,2,3,4,5,6,7,8,9 etc I want them still to be in sequence but I want the format to be 001,002,003,004,005,006,007,008,009 etc.
Is it possible to do that in SQL when you're creating the table?
This subject has been posted several times, but I haven't seen a goodanswer.Problem:I want to change the order of the columns in a table using T-SQL only.Explanation:After running your code, I want to see the following table...CREATE TABLE [dbo].[TableName] ([First_Column] [int] NULL ,[Second_Column] [varchar] (20) NULL) ON [PRIMARY]look like this...CREATE TABLE [dbo].[TableName] ([Second_Column] [varchar] (20) NULL ,[First_Column] [int] NULL) ON [PRIMARY]Limitations:Don't post if your post would fall in the following categories:1. If you don't think it can be done2. If you think Enterprise Manager is the only way to do this3. If you think I should just change the order of my Selectstatements4. If you want to state that order column doesn't matter in arelational database5. If you want to ask me why I want to do thisWish:Hopefully the answer WON'T involve creating a brand new table, movingthe data from old to new, dropping the old table, then renaming thenew table to the old name. Yes, I can do that. The table I'm workingwith is extremely huge -- I don't want to do the data juggling.Thanks in advance!
View 2 Replies View RelatedWe have a query in which we have data in fields called TS1Min, TS1Max, TS1Avg, TS2Min, TS2Max, TS2Avg etc.
We have a different table in which we define that vale of TS1, TS2, as an example TS1 might equal RED, TS2 might equal BLUE.
We have written a query that puts TS1Min, TS1Max, TS1Avg, TS2Min, TS2Max, TS2Avg in a temp table #TEMPA
and we also put the values of RED and BLUE in another temp table #TEMPB
now we want to select * from #TEMPA but rename the headings TS1Min to display RED-TS1Min, TS1Max as RED-TS1Max, TS1Avg as RED-TS1Avg etc...
any ideas on how to do this