adding various fonts/weights to values in a string

HI
I am creating a form that needs to have strings of text in the text box.
The strings have numbers included and the numbers need to be a different
font. The form is built in a table, so splitting the row will not work.
Example:
Textbox 10 has the following value:
8. Fax Number
The number 8 needs to be arial narrow bold 6.96 and the fax number needs to
be arrial narrow 6.96
Any help would be appreciated.
Thank youHi Susan,
One of the ways to do what you need is to place a rectangle object right in
the textbox of the table. Then place two (or more) individual textboxed
inside of the rectangle that is inside of the table cell. You can set the
font weights on the individual textboxes at that point so:
"8" will be in its own textbox and the Fax Number data field in the other,
but they can be together within a single cell. This might add a little more
work, but it should solve the problem.
Rodney Landrum
"Susan R" <SusanR@.discussions.microsoft.com> wrote in message
news:B55B1631-A886-45A0-AFA5-5533CB87C0BE@.microsoft.com...
> HI
> I am creating a form that needs to have strings of text in the text box.
> The strings have numbers included and the numbers need to be a different
> font. The form is built in a table, so splitting the row will not work.
> Example:
> Textbox 10 has the following value:
> 8. Fax Number
> The number 8 needs to be arial narrow bold 6.96 and the fax number needs
> to
> be arrial narrow 6.96
> Any help would be appreciated.
> Thank you
>sql

Adding row numbers to results

If you have something like
select au_fname from authors order by au_fname
You get:
au_fname
--
Abraham
Akiko
Albert
Ann
Anne
Burt
Charlene
Cheryl
Dean
Dirk
Heather
Innes
Johnson
Livia
Marjorie
Meander
Michael
Michel
Morningstar
Reginald
Sheryl
Stearns
Sylvia
(23 row(s) affected)
is there a way to make the result put row numbers in front of the results?
Such as:
au_fname
--
1. Abraham
2. Akiko
3. Albert
4. Ann
...
Thanks,
TomYou can create a temorpary table variable
Declare @.Out Table
(RowID Integer Primary Key Identity Not Null,
fName VarChar(50))
Insert the data from your query into this table variable...
Insert @.Out(fName)
Select au_fname
from authors
order by au_fname
And then output the data from the table variable...
Select * From @.Out
Altogether Now...
Declare @.Out Table
(RowID Integer Primary Key
Identity Not Null,
fName VarChar(50))
-- --
Insert @.Out(fName)
Select au_fname
from authors
order by au_fname
-- --
Select * From @.Out
"tshad" wrote:

> If you have something like
> select au_fname from authors order by au_fname
> You get:
> au_fname
> --
> Abraham
> Akiko
> Albert
> Ann
> Anne
> Burt
> Charlene
> Cheryl
> Dean
> Dirk
> Heather
> Innes
> Johnson
> Livia
> Marjorie
> Meander
> Michael
> Michel
> Morningstar
> Reginald
> Sheryl
> Stearns
> Sylvia
> (23 row(s) affected)
> is there a way to make the result put row numbers in front of the results?
> Such as:
> au_fname
> --
> 1. Abraham
> 2. Akiko
> 3. Albert
> 4. Ann
> ...
> Thanks,
> Tom
>
>|||http://www.aspfaq.com/2427
http://www.aspfaq.com/
(Reverse address to reply.)
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uaE6X#cJFHA.4056@.TK2MSFTNGP14.phx.gbl...
> If you have something like
> select au_fname from authors order by au_fname
> You get:
> au_fname
> --
> Abraham
> Akiko
> Albert
> Ann
> Anne
> Burt
> Charlene
> Cheryl
> Dean
> Dirk
> Heather
> Innes
> Johnson
> Livia
> Marjorie
> Meander
> Michael
> Michel
> Morningstar
> Reginald
> Sheryl
> Stearns
> Sylvia
> (23 row(s) affected)
> is there a way to make the result put row numbers in front of the results?
> Such as:
> au_fname
> --
> 1. Abraham
> 2. Akiko
> 3. Albert
> 4. Ann
> ...
> Thanks,
> Tom
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:enIr27dJFHA.3340@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/2427
I actually did do the asp.net route, which was fairly easy, but it is good
to know the other ways as I am sure there are cases where it would be better
to get it from the database.
Thanks,
Tom
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:uaE6X#cJFHA.4056@.TK2MSFTNGP14.phx.gbl...
>|||> I actually did do the asp.net route, which was fairly easy, but it is good
> to know the other ways as I am sure there are cases where it would be
better
> to get it from the database.
Like what? This number is just for presentation.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ehncValJFHA.656@.TK2MSFTNGP14.phx.gbl...
> better
> Like what? This number is just for presentation.
If I were filling a Grid and not writing code when my page loads to go
through the grid, it might be easier to just let Sql give it to me, for
example.
Tom

Adding Row Numbers to Flat File Source

Hi,

I was wondering if it was possible to add an identity column to a flat file data source as it is being processed in a data flow. I need to know the record number of each row in the file. Can this be done with the derived column task or is it possible to return the value of row count on each row of the data?

Any help on this is greatly recieved.

Cheers,

Grant

Generating Surrogate Keys
(http://www.sqlis.com/default.aspx?37)

-Jamie

|||

You may have more than one option here; I would try with a script task in control flow that uses a variable to generate and adds it as a column to the pipeline.

Create a SSIS variable Int32. eg MyIdentityColSeed that is set to zero.

Then, in your data flow, after the flat file source create a script task with 1 output column (named for example MyIdentityCol) and use and script like this:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Row.MyIdentityCol = Variables.MyIdentityColSeed + Counter

Counter += 1

End Sub

Even when the code is very simple; I did not tested it, so take the time to debug it.

|||Rafael, Jamie,

Thanks for both your posts on this matter. I'm looking into Rafael's suggestion at present and see that the only way to access variables in the data flow section is within the PostExecute phase. That problem with this is that it doesn't seem to update the variable as i move through the rows in the dataflow. Is this because of how SSIS has been developped. Can you not set a variable anywhere else within the script. I'll look at Jamie's suggestion once this one has been exhausted.

Thanks,

Grant|||

The Pre and Post execute limitation is just that. It also makes more sense as you only need to read and write the variables pre/post, which is faster than accessing them for every row. Use local variables in the actual main execution code.

You may want to take a quick look at the Row Number Transformation, it may do exactly what you want with seed and increment settings already- http://www.sqlis.com/default.aspx?93

|||Got Rafaels suggestion to work. Didn't need to use a variable in the end just a counter that was initialized on the preexecute event which was incremented for each row. Many thanks for everyone's help.

Yet more new things learned today :) .

Thank you all.

Grant|||

Grant Swan wrote:

Rafael, Jamie,

Thanks for both your posts on this matter. I'm looking into Rafael's suggestion at present and see that the only way to access variables in the data flow section is within the PostExecute phase. That problem with this is that it doesn't seem to update the variable as i move through the rows in the dataflow. Is this because of how SSIS has been developped. Can you not set a variable anywhere else within the script. I'll look at Jamie's suggestion once this one has been exhausted.

Thanks,

Grant

Errr...both Rafael's and my suggestions were exactly the same

Glad you got it working anyhow!

-Jamie

|||

Grant,

I think Jamie is right; the approaches are identical. I do not keep records of web resources I used; so I was unable, like Jamie, to post a link to the original sorce. I guess a owe some credits to author of the script

Rafael Salas

|||Jamie,

Sorry, I'd had a previous link open from the same website and was looking at this which had some resemblence to what i wanted to do:

http://www.sqlis.com/default.aspx?93

As apposed to the link i'd clicked from your post. Apologies and i'll mark your post as an answer also.

Its been a bad day and i've got so many SSIS things in my head :)

Cheers,

Grant|||

Grant Swan wrote:

Jamie,

Sorry, I'd had a previous link open from the same website and was looking at this which had some resemblence to what i wanted to do:

http://www.sqlis.com/default.aspx?93

As apposed to the link i'd clicked from your post. Apologies and i'll mark your post as an answer also.

Its been a bad day and i've got so many SSIS things in my head :)

Cheers,

Grant

Ha ha. Cool. I'm not bothered about getting my "answer count" up, as long as the thread has at least one reply marked as the answer. thanks anyway tho!

-Jamie

Adding page numbers by using the template "Report Model Project"

Hi,
I'm trying - without results - to introduce pagenumbers in my reports
which are made by using the template "Report Model Project" of Visual
Studio 2005. I'm using SQL Server 2005 Reporting Services. Does anybody
know if this is possible en if so how to do it?
Ren=E9René,
You'll need to add a text box to the page footer then set the Expression of
the textbox to
=Globals!PageNumber
or this type of thing is more friendly:
="Page " + cstr(Globals!PageNumber) + " of " + cstr(Globals!TotalPages)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Thanks for your solution, but I can not use it. Your solution is OK
when using the template "Report Server Project" and not for the
template "Report Model Project" of Visual Studio 2005 as I'm using. It
looks that for the "Report Model Project" it is impossible to add a
textbox, a header and/or a footer. I think it is impossible to add a
page number. I hope I'm wrong about this.
Ren=E9|||Hi René,
I must be missing something here. The report model projects I've created are
essentially data universes, and contain no reports. However, reports can
subsequently be created which relate to these reports using the Report
Builder - presumably you are referring to these reports? If this is the
case, you can edit the rdl for these reports. It's not nice, but you could
add a pageFooter element at the bottom (before the </Report> element). For a
simple page number it would look something like that below. I'll find out
for you if there is a way this can be done using the BIDS GUI.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
<PageFooter>
<ReportItems>
<Textbox Name="textbox6">
<Left>2.5cm</Left>
<Top>0.25cm</Top>
<rd:DefaultName>textbox6</rd:DefaultName>
<Width>2.53968cm</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Globals!PageNumber</Value>
</Textbox>
</ReportItems>
<Height>0.88492cm</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageFooter>|||Hello Paul,
It works! Thanks.
After using Report Builder I've opened a XML editor and I've added and
adjusted your code.
Ren=E9|||Good news. Keep an eye on this thread though because I'll update it when I
find out definitively if there is an easier way to achieve this through the
BIDS GUI.
Regards,
Paul Ibison

Adding page numbers by using the template "Report Model Project"

Hi,
I'm trying - without results - to introduce pagenumbers in my reports
which are made by using the template "Report Model Project" of Visual
Studio 2005. I'm using SQL Server 2005 Reporting Services. Does anybody
know if this is possible en if so how to do it?=20
Ren=E9Ren,
You'll need to add a text box to the page footer then set the Expression of
the textbox to
=Globals!PageNumber
or this type of thing is more friendly:
="Page " + cstr(Globals!PageNumber) + " of " + cstr(Globals!TotalPages)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Thanks for your solution, but I can not use it. Your solution is OK
when using the template "Report Server Project" and not for the
template "Report Model Project" of Visual Studio 2005 as I'm using. It
looks that for the "Report Model Project" it is impossible to add a
textbox, a header and/or a footer. I think it is impossible to add a
page number. I hope I'm wrong about this. =20
Ren=E9|||Hi Ren,
I must be missing something here. The report model projects I've created are
essentially data universes, and contain no reports. However, reports can
subsequently be created which relate to these reports using the Report
Builder - presumably you are referring to these reports? If this is the
case, you can edit the rdl for these reports. It's not nice, but you could
add a pageFooter element at the bottom (before the </Report> element). For a
simple page number it would look something like that below. I'll find out
for you if there is a way this can be done using the BIDS GUI.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
<PageFooter>
<ReportItems>
<Textbox Name="textbox6">
<Left>2.5cm</Left>
<Top>0.25cm</Top>
<rd:DefaultName>textbox6</rd:DefaultName>
<Width>2.53968cm</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Globals!PageNumber</Value>
</Textbox>
</ReportItems>
<Height>0.88492cm</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageFooter>|||Hello Paul,
It works! Thanks.
After using Report Builder I've opened a XML editor and I've added and
adjusted your code. =20
Ren=E9|||Good news. Keep an eye on this thread though because I'll update it when I
find out definitively if there is an easier way to achieve this through the
BIDS GUI.
Regards,
Paul Ibison

Adding line sequence numbers

hi all

I am stuck with something that seems easy but im obviously clueless as how to execute the idea.

I have a custom table that houses invoices on the details level. So for example i have:

InvcNo
00000001
00000001
00000001
00000002
00000002
00000003

and so forth

What I am wanting to do in another column is keep track of the sequence number for each distinct invoice like:

SeqNo
1
2
3
1
2
1

I am working in a stored proc and i cant get past adding the numbers up at each line as a whole and not reseting when the next invoice number is present. Any help would be so greatly appreciated.

ThanksWill this help?
http://www.sqlservercentral.com/columnists/mcoles/sequentialordering.asp|||Thanks...but I dont have 2005 (

Is it possible to pull the example that is given in 2005 section in a prior version of SQL?|||Can you uniquely identify each record for a specific InvcNo?|||Yes, I have an InvcKey column set up|||create table #t1 (InvcKey int, InvcNo int)
insert into #t1 select
1,1 union all select
2,1 union all select
3,1 union all select
4,2 union all select
5,2 union all select
6,3

select count(*) 'SeqNo', a.InvcKey, a.InvcNo from #t1 a, #t1 b
where a.InvcNo=b.InvcNo
and a.InvcKey>=b.InvcKey
group by a.InvcKey, a.InvcNo

drop table #t1|||Ok, Ive got it...I should have played around some before asking further

SELECT COUNT(*) AS [SeqNo], c.InvcNo, c.InvcKey
FROM table c, table d
WHERE d.InvcKey <= c.InvcKey AND d.InvcNo = cInvcNo
GROUP BY c.InvcNo, c.InvcKey
ORDER BY c.InvcKey

Thanks for the help!

tibor|||You should probably use a JOIN.
Using

FROM table1, table2, ... , tableN

Is not advised.|||Being a self join, would I really need JOIN?|||Being a self join, would I really need JOIN?It is semantics. The affect it the same but the style you used is considered old fashioned in many quarters. Whether or not it is a self join is irrelevent.

Rather excitingly - you will have created a Theta Join when you have finished :)|||Rather excitingly - you will have created a Theta Join when you have finished :)

neat! normally you'd have to pay $360k to get to that level (http://en.wikipedia.org/wiki/Operating_Thetan)!

Adding Leading Zeros

I need to make sure that numbers returned form a SELECT have at least one digit before the decimal point. Right now I have the following SQL statement in a DTS

SELECT PIN, ROUND (SUM(AREA/43560), 2) AS Expr1
FROM table GROUP BY PIN ORDER BY PIN

This produces lines like this ...

1-0005 -01-001,6250.410000
1-0008 -01-001,940.810000
1-0010 -01-001,9.230000
1-0010 -01-001A,.730000
1-0010 -01-002,73.520000
1-0010 -01-003,.680000

I need the output to look like this (check lines 4 and 6) ...

1-0005 -01-001,6250.410000
1-0008 -01-001,940.810000
1-0010 -01-001,9.230000
1-0010 -01-001A,0.730000
1-0010 -01-002,73.520000
1-0010 -01-003,0.680000

Any ideas?

DavidHello,

when you use Oracle you can convert the sum to a char with a special format mask

SELECT PIN, TO_CHAR(ROUND (SUM(AREA/43560), 2), '0.99' AS Expr1

Hope that helps ?

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Oops. Forgot to mention that I'm running SQLServer 7. Haven't found a comparable function yet.

David|||Hello again,

that is important :)

SELECT PIN, CAST(ROUND (SUM(AREA/43560), 2) AS MONEY) AS Expr1

I am using the enterprise manager and it look ok ...

Hope this help ?

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||When I tried

CAST(ROUND (SUM(AREA/43560), 2) AS MONEY)

I still got the following results

6250.4100
940.8100
9.2300
.7300
73.5200
.6800

:confused:

However, taking your lead, I tried

CAST(ROUND (SUM(AREA/43560), 2) AS CHAR)

and got

6250.410000
940.810000
9.230000
0.730000
73.520000
0.680000

:)

Works for me! Thanks!

David

adding dummy column in Sql Query

Hi for all

How to Add a Dummy column in my Sqlquery like Serial Numbers
for example my query is
Select OrderNo,OrderDate,OrderValue,DummyColumn as Slno from OrderTable Where OrderValue>4000

( DummyColumn this column was not in Table )

so i want the result like

OrderNo OrderDate OrderValue Slno
---------------
Rd001 10/25/2001 $5000 1
Rd105 12/15/2002 $5657 2
Rd441 10/15/2001 $8000 3
Rd543 05/22/2002 $9040 4
Rd333 09/05/2002 $5170 5
Rd662 11/25/2002 $9556 6

How to Get this result. Is it possible with out using StoredProcedures and Temp Tables.

Please Give some sugessions.hii
please try this code in the northwind database, to get an idea

declare @.cmp varchar(100)
declare @.dc int
set @.dc = 0
declare cur_sor cursor for select companyname from customers
set @.dc = @.dc + 1
open cur_sor
fetch next from cur_sor into @.cmp
while @.@.fetch_status = 0
begin
select @.dc as dummycolmn,@.cmp as company
fetch next from cur_sor into @.cmp
set @.dc = @.dc + 1
end

close cur_sor
deallocate cur_sor

regards
ramki

adding All option to a sorted list

Hi.
My DB holds a table with a varchar column containg numbers.
In RS I need to create a dataset that will sort these numbers and add 'All'
value to this dataset.
My problems is:
As the dataset holds sting values and it performs lexicographic sort, if I
perform cast to int on these numbers (to sort them correctly) I can't add the
'All' value.
Furthermore, Does anyone knows how to refer to a custom made dataset defined
in the RS?
Thanks,
GuyTry something like
select right('0000' + myvarcharfield,4) from table order by 1
union
select 'All'
for a set of numbers not longer than 4 characters... This will sort them
correctly I think without converting them to ints
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"GuyR" <GuyR@.discussions.microsoft.com> wrote in message
news:56FE3261-1DEF-48DD-B4B3-07F2F992AC4A@.microsoft.com...
> Hi.
> My DB holds a table with a varchar column containg numbers.
> In RS I need to create a dataset that will sort these numbers and add
> 'All'
> value to this dataset.
> My problems is:
> As the dataset holds sting values and it performs lexicographic sort, if I
> perform cast to int on these numbers (to sort them correctly) I can't add
> the
> 'All' value.
> Furthermore, Does anyone knows how to refer to a custom made dataset
> defined
> in the RS?
> Thanks,
> Guy
>