How to trim XML's attribute values?

I have a program which generate a xml file from dataset using
DataSet.WriteXML function. The result is as follow:
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
12:00:00 AM" Name=" "
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
" ADD1=" " TRSS="3" TYPEDESC="
" >
<DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
" />
<DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
" />
<DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
" />
<DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
" />
</TD>
</TB>
</DataSet>
Is there any setting to set it to generate xml file with trim space values
and result should output as :
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
TYPEDESC="" >
<DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
<DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
<DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
<DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
</TD>
</TB>
</DataSet>
In your SQL SELECT or stored procedure just use LTRIM(column_name) or
RTRIM(column_name) to remove unwanted spaces then the resulting dataset will
not have them for the WriteXML format.
So to correct your data below it would be something like:
SELECT
RTRIM(TB_NO),
RTRIM(DESCRIPTION)
...
"ABC" <abc@.abc.com> wrote in message
news:elMHZEiOHHA.4484@.TK2MSFTNGP02.phx.gbl...
>I have a program which generate a xml file from dataset using
> DataSet.WriteXML function. The result is as follow:
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
> 12:00:00 AM" Name=" "
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
> " ADD1=" " TRSS="3" TYPEDESC="
> " >
> <DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
> " />
> <DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
> " />
> <DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
> " />
> <DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
> " />
> </TD>
> </TB>
> </DataSet>
>
> Is there any setting to set it to generate xml file with trim space values
> and result should output as :
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
> TYPEDESC="" >
> <DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
> <DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
> <DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
> <DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
> </TD>
> </TB>
> </DataSet>
>
>
sql

How to trim XML's attribute values?

I have a program which generate a xml file from dataset using
DataSet.WriteXML function. The result is as follow:
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
12:00:00 AM" Name=" "
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
" ADD1=" " TRSS="3" TYPEDESC="
" >
<DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
" />
<DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
" />
<DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
" />
<DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
" />
</TD>
</TB>
</DataSet>
Is there any setting to set it to generate xml file with trim space values
and result should output as :
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
TYPEDESC="" >
<DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
<DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
<DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
<DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
</TD>
</TB>
</DataSet>In your SQL SELECT or stored procedure just use LTRIM(column_name) or
RTRIM(column_name) to remove unwanted spaces then the resulting dataset will
not have them for the WriteXML format.
So to correct your data below it would be something like:
SELECT
RTRIM(TB_NO),
RTRIM(DESCRIPTION)
...
"ABC" <abc@.abc.com> wrote in message
news:elMHZEiOHHA.4484@.TK2MSFTNGP02.phx.gbl...
>I have a program which generate a xml file from dataset using
> DataSet.WriteXML function. The result is as follow:
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
> 12:00:00 AM" Name=" "
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
> " ADD1=" " TRSS="3" TYPEDESC="
> " >
> <DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
> " />
> <DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
> " />
> <DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
> " />
> <DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
> " />
> </TD>
> </TB>
> </DataSet>
>
> Is there any setting to set it to generate xml file with trim space values
> and result should output as :
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
> TYPEDESC="" >
> <DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
> <DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
> <DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
> <DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
> </TD>
> </TB>
> </DataSet>
>
>

how to transpose col values into row values?

Hello SQL people,

I need a way to transpose the values of a column into values in a row in a SqlServer7 table. Here is the problem:

I have a table employeeattendance.
Here is the script -

if exists (select * from sysobjects where id = object_id(N'[dbo].[EmployeeAttendance]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EmployeeAttendance]
GO

CREATE TABLE [dbo].[EmployeeAttendance] (
[EmployeeID] [varchar] (20) NULL ,
[AttendanceDate] [varchar] (10) NULL ,
[Status] [char] (1) NULL
) ON [PRIMARY]
GO

insert into [EmployeeAttendance] Values ( '20001', '2003-03-28' , 'Y')
insert into [EmployeeAttendance] Values ( '20001', '2003-03-29' , 'N')
insert into [EmployeeAttendance] Values ( '20001', '2003-03-30' , 'N')
insert into [EmployeeAttendance] Values ( '20002', '2003-03-28' , 'Y')
insert into [EmployeeAttendance] Values ( '20002', '2003-03-29' , 'N')

Now if i say
select * from employeeattendance
Output is -
EmployeeID AttendanceDate Status
20001 2003-03-28 Y
20001 2003-03-29 N
20001 2003-03-30 N
20002 2003-03-28 Y
20002 2003-03-29 N

But i want the output some thing like this-

20001 2003-03-28 Y 2003-03-29 N 2003-03-30 N
20002 2003-03-28 Y 2003-03-29 N

Please, any help would be greatly appreciated. Thanks, GolaI had answer a similar question before. See my replay at view and link table thread on 14 March 2003

ionut|||Originally posted by ionut calin
I had answer a similar question before. See my replay at view and link table thread on 14 March 2003

ionut

Thanks ionut for reply, but i am using MS sql server 7.0 and i have only one table.

Thanks once again.

Gola Munjal|||you could do it in your application program, or else use transact-sql

see
http://sqlteam.com/item.asp?ItemID=11021
and
http://sqlteam.com/item.asp?ItemID=2368

rudy

how to total the values in a numeric column?

I'm building a report using the matrix control. I have financial data in
different line items (Salary, Rental Expense etc) that needs to be shown by
month (so the columns have months). I have it pretty much working but can't
figure out how to sum up the numbers in each column at the bottom so I can
have a total for every month. Could anyone help?
Thanks a lot.
BobRight-click on the row field that you wish to total, in your case it
would be the Salary field. Choose Sub-total from the menu.
To format the subtotals you need to right-click->properties on the tiny
green triangle that appears on the Total cell.|||ahhh. I was thinking this must be something really simple since it's such a
common function. You can't believe how much time I had spent trying to
figure this out. Thanks a lot.
Bob
"grahamiec" <grahamrichter@.gmail.com> wrote in message
news:1123769689.664448.10490@.g14g2000cwa.googlegroups.com...
> Right-click on the row field that you wish to total, in your case it
> would be the Salary field. Choose Sub-total from the menu.
> To format the subtotals you need to right-click->properties on the tiny
> green triangle that appears on the Total cell.
>

How to tell if a function is called within a trigger

How can I tell whether a function has been called, along with the values that were passed into it? My SQL is generated by Visual Basic, and on an insert, a trigger fires, which then *might* call the function depending on a condition that's difficult to monitor. Ideally, I'd be looking for a simple function that would write to a log, or print a message somewhere. "Print" does not work within functions. I'm running SQL Server 2000.You could create a stored procedure that writes to a log, and then call that stored procedure from the trigger. Kinda messy, but will work.|||

1. Profiler

2. Put code in the trigger that inserts relevant values (funtion input parameters, record IDs) into a table at the point the function would be called. Comment out or remove this code after testing. I routinely do this in Try Catch blocks (or 2000 error handling) of sprocs during dev.

|||You also cannot perform an INSERT to a permanent table in a function. If this is important you might consider upgrading to SQL Server 2005 and using a SET CONTEXT_INFO to save some information. I guess you could call a procedure from the trigger but I am really not sure how much this will buy you -- you are sort-of already in a psedudo procedure since you are in a trigger.

how to suppress zeroes after decimal point at the end in a value

hi,
i have a sql table field price and datatype is
decimal 13(20,6).
when i insert values to this field, values are being
inserted correctly. i.e. 13.45 inserted as 13.45 and
145.653 inserted as 145.653 only.
But while fetching only the values are coming as 13.450000,
145.653000, because the datatype is decimal 13(20,6) with
6 decimals. but i want 13.45, 145.653 as in the table.
How suppress the unwanted zeroes at the end of those
numbers.
any help.
thanks,
hari.
see following example:
drop table test
create table test(c1 decimal (15,5))
insert into test values (3.567000)
insert into test values (232233.567000)
insert into test values (3.567)
query:
select c1,reverse(substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) - 1)
)) 'no_zeros'
from test
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||hi thanks,
but it is not working for whole number like ex:11120
it is giving it as 11120. (with point at the end)
how to do that.
thanks,
hari.
>--Original Message--
>see following example:
>drop table test
>create table test(c1 decimal (15,5))
>insert into test values (3.567000)
>insert into test values (232233.567000)
>insert into test values (3.567)
>query:
>select c1,reverse(substring(reverse(cast(c1 as varchar
(25))) ,
>patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
>len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as
>varchar(25)))) - 1)
>)) 'no_zeros'
>from test
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||try:
select c1, reverse(
case when substring(substring(reverse(cast(c1 as varchar(25)))
,
patindex('%[^0]%', reverse(cast(c1 as varchar(25))))
,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 1)) ,1,1) = '.'
then
substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) + 1 ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 2))
else
substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 1))
end)
'no_zeros'
from test
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"hari" <anonymous@.discussions.microsoft.com> wrote in message
news:207601c4a145$b5262d90$a601280a@.phx.gbl...[vbcol=seagreen]
> hi thanks,
> but it is not working for whole number like ex:11120
> it is giving it as 11120. (with point at the end)
> how to do that.
> thanks,
> hari.
> (25))) ,
> reverse(cast(c1 as
|||See if this works:
select
replace(rtrim(replace(
replace(rtrim(replace(
c1,'0',' ')),' ','0')
,'.',' ')),' ','.')
from test
Steve Kass
Drew University
hari wrote:
[vbcol=seagreen]
>hi thanks,
> but it is not working for whole number like ex:11120
> it is giving it as 11120. (with point at the end)
> how to do that.
>thanks,
>hari.
>
>(25))) ,
>
>reverse(cast(c1 as
>
>

How to Sum non-duplicate values

Hi,
I have to create a report with a data as follows-
Voucher Branch Item ItemAmount
-- -- -- --
V1 Branch1 Item1 100
V2 Branch1 Item1 100
V3 Branch1 Item1 100
V4 Branch1 Item2 50
V5 Branch1 Item2 50
V6 Branch1 Item3 75
V7 Branch2 Item1 150
V8 Branch3 Item5 250
The table should have branch group and Sum(ItemAmount) per Branch.
I need to display ItemAmount totals per Branch but the totals should
consider only distinct ItemAmount per Item.
Total per Branch1 should be 100 + 50 + 75 = 225 When I say
Sum(ItemAmount, "Branch") in Branch group it's calculating 100 + 100 +
100 + 50 + 50 + 75 = 475.
Any suggestions would be appreciated.On Mar 16, 7:18 pm, "Chiru" <uchira...@.gmail.com> wrote:
> Hi,
> I have to create a report with a data as follows-
> Voucher Branch Item ItemAmount
> -- -- -- --
> V1 Branch1 Item1 100
> V2 Branch1 Item1 100
> V3 Branch1 Item1 100
> V4 Branch1 Item2 50
> V5 Branch1 Item2 50
> V6 Branch1 Item3 75
> V7 Branch2 Item1 150
> V8 Branch3 Item5 250
> The table should have branch group and Sum(ItemAmount) per Branch.
> I need to display ItemAmount totals per Branch but the totals should
> consider only distinct ItemAmount per Item.
> Total per Branch1 should be 100 + 50 + 75 = 225 When I say
> Sum(ItemAmount, "Branch") in Branch group it's calculating 100 + 100 +
> 100 + 50 + 50 + 75 = 475.
> Any suggestions would be appreciated.
I would suggest changing the report query (or stored procedure) to
something like this:
SELECT BRANCH, SUM(DISTINCT(ITEMAMOUNT))
FROM TABLE_X
GROUP BY BRANCH
Or, set up a separate dataset that has this query in it and use it for
the sums. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

How to sum a column in a SQL query

Can someone tell me how to sum values in a column from a resultant Query. For example, consider this query which in part
calls a view named "Rpt_OverallIndMarket"

select * from Rpt_OverallIndMarket order by MarketId,Year,Quarter

The resultant query from the previous query will contain a column named TotalSquareFeet. How do I sum all of the TotalSquareFeet values in
that column?

Do you just need the sum or you need the data + sum? If you need both perhaps your front end is best to calculate the sum. Most controls like datagrid etc have features to calculate a sum. If thats not possible you might need to use a GROUP BY in the query but that might affect the results.

|||

Dont know exactly if this is what you want but this is what I understood:

SELECT Sum(ALL(columnname)) FROM table

|||

select sum(TotalSquareFeet) from Rpt_OverallIndMarket returns 1 row with 1 column, the sum of total square feet
----------
select sum(TotalSquareFeet), MarketId, Year,Quarter from Rpt_OverallIndMarket group by MarketId,Year,Quarter
returns 1 row per MarketId, Year, Quarter with the sum across that MarketId, Year and Quarter (IOW, the subtotals).
----------
select 'Total ' as SubTotal, sum(TotalSquareFeet), max(MarketeId), max(Year) ,max(Quarter) from Rpt_OverallIndMarket
union all
select 'Detail', TotalSquareFeet, MarketId, Year,Quarter from Rpt_OverallIndMarket
order by 1, 2, 3,4

returns detail and total lines in 1 answer set, ordered by the first 4 columns. Those max() functions are just place holders since a union requires the same number and types of fields in all parts of the union

Detail 10 a 2000 3
Detail 15 a 2000 2
Detail 8 b 2001 1
Total 33 b 2001 3 <-- The 'b 2001 3' are just place holders, you can ignore them

My point in giving this last example is that you can use subtotaling and unions to do a lot of the subtotaling work for you -- all you need to do is include a literal column (eg, "Detail", "Total") telling your program what the row represents.

|||

Did you get the answer you wanted yet?

The two choices in the posts above are basically use theSUM function in SQL with a GROUP BY clause, or to do it yourself in code.

If you're not familiar with grouping, I'd recommend you read:http://www.singingeels.com/Articles/Understanding_SQL_Complex_Queries.aspx

how to subtract value at previous row from current row

Hi all,

I need help writing a query that will subtract the values of 2 rows from the same column to display in the result set. Some background information: a table has a sales column that keeps track of sales by the minute, but this is done in a cumulative manner, i.e, sales at row 3(minute 3) = sales recorded @. minute 2 plus sales @. minute 3. Therefor to get the actual sale at min 3, i would have subtract value at row 2 from row 3. make sense? it sounds very easy but I am having a hard time refering back to the previous row and am dealing with more than 1000 rows. i thought about doing a self join on the table but could not get it to do what i want.

would appreciate any help i can get. thanks

Well, here's one methodology - but it is a bit dangerous if you ever delete records from this table. You will need to add a row identifying column (an auto incrementing number) - in this SQL statement I'm presuming it has a increment of 1.

SELECT (TableA.RowNumValue - TableB.RowNumValue) AS Val

FROM

YourTable TableA

INNER JOIN

YourTable TableB

ON

TableB.ID = TableA.ID - 1

This joins the table on itself, matching the previous record based on the autonumber ID (simply called "ID" here).

Once again, if you ever delete records this methodology won't be accurate. Of course, since you are entering the data "cumulatively", deleting data would be a problem for you anyway.

As a side question: why is it necessary to save the data in a cumulative manner? A database is designed for storage and aggregation - you'll save yourself a lot of headache if you simply store the data point as is, and sum the records up in a sql query.

--
Tony Alicea
http://www.theabstractionpoint.com
clarity of mind and creativity in application software development...

|||

Hi Tony,

Thank you so much for your time and input. I will try the self join again based on the method you showed me. The fisrt time I did, I used another field based on time and tried to manipulate but it didn't work....but your method makes sense
And I agree with you, it really doesn't help at all that the data is stored in this manner. Unfortunately, I have limited say in the design of it and this will probably be the last time I use it. But you are absolutely right.

Hey, thanks again.|||

Ah, trying to query a data structure that you didn't design. I've been there. Well, hope it works. If it does don't forget to mark the thread as answered to close it out.

Let us know how it goes!

how to subtract value at previous row from current row

Hi all,

I need help writing a query that will subtract the values of 2 rows from the same column to display in the result set. Some background information: a table has a sales column that keeps track of sales by the minute, but this is done in a cumulative manner, i.e, sales at row 3(minute 3) = sales recorded @. minute 2 plus sales @. minute 3. Therefor to get the actual sale at min 3, i would have subtract value at row 2 from row 3. make sense? it sounds very easy but I am having a hard time refering back to the previous row and am dealing with more than 1000 rows. i thought about doing a self join on the table but could not get it to do what i want.

would appreciate any help i can get. thanks

Well, here's one methodology - but it is a bit dangerous if you ever delete records from this table. You will need to add a row identifying column (an auto incrementing number) - in this SQL statement I'm presuming it has a increment of 1.

SELECT (TableA.RowNumValue - TableB.RowNumValue) AS Val

FROM

YourTable TableA

INNER JOIN

YourTable TableB

ON

TableB.ID = TableA.ID - 1

This joins the table on itself, matching the previous record based on the autonumber ID (simply called "ID" here).

Once again, if you ever delete records this methodology won't be accurate. Of course, since you are entering the data "cumulatively", deleting data would be a problem for you anyway.

As a side question: why is it necessary to save the data in a cumulative manner? A database is designed for storage and aggregation - you'll save yourself a lot of headache if you simply store the data point as is, and sum the records up in a sql query.

--
Tony Alicea
http://www.theabstractionpoint.com
clarity of mind and creativity in application software development...

|||

Hi Tony,

Thank you so much for your time and input. I will try the self join again based on the method you showed me. The fisrt time I did, I used another field based on time and tried to manipulate but it didn't work....but your method makes sense
And I agree with you, it really doesn't help at all that the data is stored in this manner. Unfortunately, I have limited say in the design of it and this will probably be the last time I use it. But you are absolutely right.

Hey, thanks again.
|||

Ah, trying to query a data structure that you didn't design. I've been there. Well, hope it works. If it does don't forget to mark the thread as answered to close it out.

Let us know how it goes!

how to store values during dataflow

The point is, i want to calculate the max id of a table using Aggregate Transformation, then insert some rows with a OLEDB Command and finally , with another OLEDB Command select those rows with id >(max_id) calculated before.

How can i get a value that was calculated before? Can i store it in a variable?

Many Thanks!

On the Control Flow add a 'Execute SQL Task' which has something like SELECT MAX(ID) AS MAXID FROM YOURTABLE, store the result into a variable.

Do your inserts, then SELECT * FROM YOURTABLE WHERE ID > YOURVARIABLE

|||

Thanks Paul,

The only way to store this results on a variable is from an 'Execute SQL Task'? can i do the same from either a OLEDB Command or Agregate Transformation?

|||You can use a script component to store values in a variable inside a data flow. You have to write it in the PostExecute method, though, which runs after all the rows pass thorugh it. However, I don't know if that is the best approach, given the scenario you are describing. But, ultimately, it's your call.|||

Thank you very much jwelch, ill try it!

How to store OR get Time value in a column

How can i store the time value in a column. DateTime stores dd/mm/yyy hh:mm:ss , i want only hh:mm:ss , and i also may compare the the values. And also how can i get current time to a column value, something like getdate() ?

MasterG wrote:

How can i store the time value in a column. DateTime stores dd/mm/yyy hh:mm:ss , i want only hh:mm:ss , and i also may compare the the values. And also how can i get current time to a column value, something like getdate() ?

Hi MasterG,

You can use the following T-SQL to get hh:mm:ss from a datetime value;

CONVERT(CHAR(8), GETDATE(), 108)

To insert the time into a column, you could use;

INSERT
MyTable (TheTime)
VALUES
CONVERT(CHAR(8), GETDATE(), 108)

You may want to check out Books Online for further information, I also found this article which should be really interesting to you; http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk
|||

Thanks for great solution. This one is very helpfull

Happy Coding...

How to stop report parameter taking default value

I have a SSRS2000 report with three parameters:
1) Company - values from a query including NULL for ALL companies - no
default value
2) From date - has default value
3) To date - has default value
My problem is that the report runs automatically when launched. Even though
there is no default value specified for the Company parameter it takes the
NULL value.
Is there any way to change this to force the Company prompt to display a
<select a value> and allow selection of one of the options?
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, ScotlandHello magendo_man,
Instead of letting it take the value or NULL... change to: (CompanyID =@.Company OR @.CompanyID = "ALL"). Then in your query that supplies the
company names add a UNION SELECT "ALL". Don't specify a default for that
parameter.
Hope that helps!
Cheers,
Kathy
"magendo_man" wrote:
> I have a SSRS2000 report with three parameters:
> 1) Company - values from a query including NULL for ALL companies - no
> default value
> 2) From date - has default value
> 3) To date - has default value
> My problem is that the report runs automatically when launched. Even though
> there is no default value specified for the Company parameter it takes the
> NULL value.
> Is there any way to change this to force the Company prompt to display a
> <select a value> and allow selection of one of the options?
>
> --
> Magendo_man
> Freelance SQL Reporting Services developer
> Stirling, Scotland

How to stop automatic excution of a report?

Hi,

I have a report containing one multi-value parameter with default values given. When open I report in Report Manager, report executes automatically. I need to stop this automatic execution of report. I need to give user an option to check if default values are ok for him or not. Is there any way other than removing default value from parameter to do this?

Thanks in anticipation.

Saeed

Sorry, there is no way to achieve this behavior in Report Manager. You need to remove the default value, otherwise the report will render.|||

It is unfortunate that this was not considered as a requirement during development. However, you can work around this by adding an additional report parameter. Add a Start Report parameter as a string with 1 non-queried availabIe value like 'Yes'. The report will not render until the user selects the only available option for that parameter. Its not the best solution but is a viable workaround to the default rendering problem.

|||Hi
Rather than use default parameters in a report why dont you make the report show a list of valid values e.g. from a query and the user will sellect appropriate value.
e.g.

A report uses a paramater param1; create a dataset with a sql query that generates valid values and make your report parameter populate from this dataset. You can set the default value to null so that the report prompts for the parameters from the valid list.

How to stop automatic excution of a report?

Hi,

I have a report containing one multi-value parameter with default values given. When open I report in Report Manager, report executes automatically. I need to stop this automatic execution of report. I need to give user an option to check if default values are ok for him or not. Is there any way other than removing default value from parameter to do this?

Thanks in anticipation.

Saeed

Sorry, there is no way to achieve this behavior in Report Manager. You need to remove the default value, otherwise the report will render.|||

It is unfortunate that this was not considered as a requirement during development. However, you can work around this by adding an additional report parameter. Add a Start Report parameter as a string with 1 non-queried availabIe value like 'Yes'. The report will not render until the user selects the only available option for that parameter. Its not the best solution but is a viable workaround to the default rendering problem.

|||Hi
Rather than use default parameters in a report why dont you make the report show a list of valid values e.g. from a query and the user will sellect appropriate value.
e.g.

A report uses a paramater param1; create a dataset with a sql query that generates valid values and make your report parameter populate from this dataset. You can set the default value to null so that the report prompts for the parameters from the valid list.