Formatting of Reports

I'm new to reporting services, My company is trying to evaluate the
service to see if we can use it to replace some legacy reports. I'm
able to get most of it done but there is one problem I haven't been
able to solve in the last several days.
Select Table1.columnA, Table1.columnB, Table1.columnC, Table2.columnA,
Table2.columnB
>From Table1 inner join Table2 on Table1.id = Table2.Table1_id
That's giving me the result set I need
AAA AAA AAA BBB BBB
AAA AAA AAA CCC CCC
AAA AAA AAA DDD DDD
The requirement for the report is that data on the report not be
duplicated, so the output should be formatted like
AAA AAA AAA BBB BBB
CCC CCC
DDD DDD
I have tried groups, that seems to do what I want but if I don't have
a header of for the group I'm unable to change it again later, any
advice or direction is appreciated.Hey Lance,
Each cell in your table has a property "Hide Duplicates", so if the
previous field is the same as the current field it doesn't show. You should
simply need to access the property page and then set this field to "True"
Michael C
"lance.sanchez@.gmail.com" wrote:
> I'm new to reporting services, My company is trying to evaluate the
> service to see if we can use it to replace some legacy reports. I'm
> able to get most of it done but there is one problem I haven't been
> able to solve in the last several days.
> Select Table1.columnA, Table1.columnB, Table1.columnC, Table2.columnA,
> Table2.columnB
> >From Table1 inner join Table2 on Table1.id = Table2.Table1_id
> That's giving me the result set I need
> AAA AAA AAA BBB BBB
> AAA AAA AAA CCC CCC
> AAA AAA AAA DDD DDD
> The requirement for the report is that data on the report not be
> duplicated, so the output should be formatted like
> AAA AAA AAA BBB BBB
> CCC CCC
> DDD DDD
> I have tried groups, that seems to do what I want but if I don't have
> a header of for the group I'm unable to change it again later, any
> advice or direction is appreciated.
>|||OK, i can get it to do what I want in the report builder from the
webpage but i cant seem to get the formating to work correctly in SQL
Server Business Intelligence Development Studio. any sugestions?
On Aug 20, 10:45 am, "lance.sanc...@.gmail.com"
<lance.sanc...@.gmail.com> wrote:
> I'm new to reporting services, My company is trying to evaluate the
> service to see if we can use it to replace some legacy reports. I'm
> able to get most of it done but there is one problem I haven't been
> able to solve in the last several days.
> Select Table1.columnA, Table1.columnB, Table1.columnC, Table2.columnA,
> Table2.columnB
> >From Table1 inner join Table2 on Table1.id = Table2.Table1_id
> That's giving me the result set I need
> AAA AAA AAA BBB BBB
> AAA AAA AAA CCC CCC
> AAA AAA AAA DDD DDD
> The requirement for the report is that data on the report not be
> duplicated, so the output should be formatted like
> AAA AAA AAA BBB BBB
> CCC CCC
> DDD DDD
> I have tried groups, that seems to do what I want but if I don't have
> a header of for the group I'm unable to change it again later, any
> advice or direction is appreciated.|||Are you saying when you deploy it to Report Manager it works, but when your
using preview in BID it doesn't? Thats strange...have you tried closing out
the solution, and restarting ti?
"lance.sanchez@.gmail.com" wrote:
> OK, i can get it to do what I want in the report builder from the
> webpage but i cant seem to get the formating to work correctly in SQL
> Server Business Intelligence Development Studio. any sugestions?
>
> On Aug 20, 10:45 am, "lance.sanc...@.gmail.com"
> <lance.sanc...@.gmail.com> wrote:
> > I'm new to reporting services, My company is trying to evaluate the
> > service to see if we can use it to replace some legacy reports. I'm
> > able to get most of it done but there is one problem I haven't been
> > able to solve in the last several days.
> >
> > Select Table1.columnA, Table1.columnB, Table1.columnC, Table2.columnA,
> > Table2.columnB
> >
> > >From Table1 inner join Table2 on Table1.id = Table2.Table1_id
> >
> > That's giving me the result set I need
> >
> > AAA AAA AAA BBB BBB
> > AAA AAA AAA CCC CCC
> > AAA AAA AAA DDD DDD
> >
> > The requirement for the report is that data on the report not be
> > duplicated, so the output should be formatted like
> >
> > AAA AAA AAA BBB BBB
> > CCC CCC
> > DDD DDD
> >
> > I have tried groups, that seems to do what I want but if I don't have
> > a header of for the group I'm unable to change it again later, any
> > advice or direction is appreciated.
>
>

Formatting drill down report with varying depths of hierarchy

Good Morning, all,
I have been tasked to design reports for our budget data.
The client wants to be able to drill down through our company
hierarchy.
Some departments are five levels deep in the hierarchy, but others are
only three levels deep.
Right now a very simplified version of my dataset looks like
LEVEL0 LEVEL1 LEVEL2 LEVEL4 BUDGET
900
500000
900
600 200000
900
400 100000
900 400
250 50000
900 400
350 50000
900 400 350
612 40000
900 400 350
611 10000
So, what i want is if i drill down to 600, I shouldn't be able to
drill down any further, because there is no lower level. But if I
drill down to 400, I should be able drill down further. Right now, I
can keep drilling down two more levels below 600 until I get to the
detail level.
Can I control this with layout properties?
any ideas, suggestions, resources, war stories, or good clean jokes
would be appreciated.
KathrynFor archival purposes, I solved this by using Analysis Services to
create a cube out of my relational data. The recursive relationship
above became a dimension named Par. I created a report in Reporting
Services using the cube as a datasource. When defining the Group for
Par, you have to use two properties, UniqueName and ParentUniqueName.
These properties are created automatically when you create your MDX
query using Query Builder.. In my case, when I edited the Par Group,
I set the Group On expression to Fields!Par.UniqueName and the Parent
group to Fields!Par.ParentUniqueName. Set Visibility to Hidden and
have the Toggle Item be the textbox itself, in my case Par (which
seems recursive, but that's what you want.)
Good luck,
Kathrynsql

Formatting database content

Hi
I have set up a SQL database to contain alist FAQ's for our company and
then plan to pull this info off using a web page.

So far I have entered the data but I am unable to control how it is
displayed inside SQL ie I cannot enter new blank lines I have tried
using lots of spaces but this does not work when I use the website to
display the info.

Is there a way of formatting and editing the data in the sql database
as I am unable to do this, if I try to edit the data in the database I
have to copy it to notepad delete what is in the database, edit the
text in notepad then paste it back in.

There must be a better way.

Please help

alamb200Sounds as if you need to convert your carraige return / line feed into the
HTML <BR> tag which will then give you the correct formatting on the screen.

If you want to do it in the database then use REPLACE( <col>, char(13) +
char(10), '<BR>' ) - check, it might be 10, 13 :)

Tony

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

<alamb200@.hotmail.com> wrote in message
news:1147771818.200249.269650@.j55g2000cwa.googlegr oups.com...
> Hi
> I have set up a SQL database to contain alist FAQ's for our company and
> then plan to pull this info off using a web page.
> So far I have entered the data but I am unable to control how it is
> displayed inside SQL ie I cannot enter new blank lines I have tried
> using lots of spaces but this does not work when I use the website to
> display the info.
> Is there a way of formatting and editing the data in the sql database
> as I am unable to do this, if I try to edit the data in the database I
> have to copy it to notepad delete what is in the database, edit the
> text in notepad then paste it back in.
> There must be a better way.
> Please help
> alamb200|||I have just had a look at the parameter tag but as I am new to SQL I am
not sure how I would put this place.

For example if my prefix charecter was / and my suffix charecter was \
for the sake of argument nad i wanted a new line after the full stop
in the following text how would I enter the information.

If your printer is no longer printing your logo's or overlays it may
have lost the images that have been "Flashed" on to it, to reflash your
printer follow the following instructions. Log on to SB Client and go
into the Company Files section. Next goto Runtime then Maintenance and
System Manager Menu.

Also I seem to be unable to edit text already in the database and have
to delete it and reenter it every time, is there an easier way of doing
that.

Thanks

Anthony

Tony Rogerson wrote:
> Sounds as if you need to convert your carraige return / line feed into the
> HTML <BR> tag which will then give you the correct formatting on the screen.
> If you want to do it in the database then use REPLACE( <col>, char(13) +
> char(10), '<BR>' ) - check, it might be 10, 13 :)
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> <alamb200@.hotmail.com> wrote in message
> news:1147771818.200249.269650@.j55g2000cwa.googlegr oups.com...
> > Hi
> > I have set up a SQL database to contain alist FAQ's for our company and
> > then plan to pull this info off using a web page.
> > So far I have entered the data but I am unable to control how it is
> > displayed inside SQL ie I cannot enter new blank lines I have tried
> > using lots of spaces but this does not work when I use the website to
> > display the info.
> > Is there a way of formatting and editing the data in the sql database
> > as I am unable to do this, if I try to edit the data in the database I
> > have to copy it to notepad delete what is in the database, edit the
> > text in notepad then paste it back in.
> > There must be a better way.
> > Please help
> > alamb200|||On 16 May 2006 04:13:58 -0700, alamb200@.hotmail.com wrote:

>I have just had a look at the parameter tag but as I am new to SQL I am
>not sure how I would put this place.
>For example if my prefix charecter was / and my suffix charecter was \
>for the sake of argument nad i wanted a new line after the full stop
>in the following text how would I enter the information.

Hi Anthony,

wiith the right front-end, there's no need to use parameters. For
instance, copy and paste the following into Query Analyzer and execute;
you'll see that the output has exactly the lline breaks yoou asked for:

CREATE TABLE test (KeyColumn int NOT NULL PRIMARY KEY,
DataColumn varchar(1000) NOT NULL)
INSERT INTO test (KeyColumn, DataColumn)
VALUES (1, 'If your printer is no longer printing your logo''s or
overlays it may have lost the images that have been "Flashed" on to it,
to reflash your printer follow the following instructions.
Log on to SB Client and go into the Company Files section.
Next goto Runtime then Maintenance and System Manager Menu.')
go
SELECT * FROM test
go
DROP TABLE test
go

(Note: beware of extra line breaks inserted by Usenet software. In the
intended code, there are line breaks after the periods, but no other
line breaks in the VALUES clause.)

>Also I seem to be unable to edit text already in the database and have
>to delete it and reenter it every time, is there an easier way of doing
>that.

That, too, is a problem with your frontend.

May I ask you what tool you are using as your frontend?

--
Hugo Kornelis, SQL Server MVP|||Hi

I am using a web page as my front end using ASP to pull the info from
the SQL database so I am in the position of either having multiple
columns with small bits of info in my database so I can format them how
I want

The other option is to have some commands built into the text in the
database entry which will be read by the system and control the
formatting when it is read.

Is this possible?

When I am trying to edit the info in the database all I am doing is
right clicking on the table in Enterprise manager and display all
columns and working on it from there.

Is this the right way to do this?

Anthony

Hugo Kornelis wrote:
> On 16 May 2006 04:13:58 -0700, alamb200@.hotmail.com wrote:
> >I have just had a look at the parameter tag but as I am new to SQL I am
> >not sure how I would put this place.
> >For example if my prefix charecter was / and my suffix charecter was \
> >for the sake of argument nad i wanted a new line after the full stop
> >in the following text how would I enter the information.
> Hi Anthony,
> wiith the right front-end, there's no need to use parameters. For
> instance, copy and paste the following into Query Analyzer and execute;
> you'll see that the output has exactly the lline breaks yoou asked for:
> CREATE TABLE test (KeyColumn int NOT NULL PRIMARY KEY,
> DataColumn varchar(1000) NOT NULL)
> INSERT INTO test (KeyColumn, DataColumn)
> VALUES (1, 'If your printer is no longer printing your logo''s or
> overlays it may have lost the images that have been "Flashed" on to it,
> to reflash your printer follow the following instructions.
> Log on to SB Client and go into the Company Files section.
> Next goto Runtime then Maintenance and System Manager Menu.')
> go
> SELECT * FROM test
> go
> DROP TABLE test
> go
> (Note: beware of extra line breaks inserted by Usenet software. In the
> intended code, there are line breaks after the periods, but no other
> line breaks in the VALUES clause.)
> >Also I seem to be unable to edit text already in the database and have
> >to delete it and reenter it every time, is there an easier way of doing
> >that.
> That, too, is a problem with your frontend.
> May I ask you what tool you are using as your frontend?
> --
> Hugo Kornelis, SQL Server MVP|||On 17 May 2006 02:01:41 -0700, alamb200@.hotmail.com wrote:

>Hi
>I am using a web page as my front end using ASP to pull the info from
>the SQL database so I am in the position of either having multiple
>columns with small bits of info in my database so I can format them how
>I want
>The other option is to have some commands built into the text in the
>database entry which will be read by the system and control the
>formatting when it is read.
>Is this possible?

Hi Anthony,

I'm not an ASP guy, so I don't know. Are you saying that linebreaks in a
long character data column are not displayed properly by ASP? Have you
consiedered asking for alternatives in one of the ASP groups?

>When I am trying to edit the info in the database all I am doing is
>right clicking on the table in Enterprise manager and display all
>columns and working on it from there.
>Is this the right way to do this?

No, definitely not. Data entry through Enterprise Manager is broken in
far more ways than I care to remember. Some of these errors are listed
by Aaron Bertrand at http://www.aspfaq.com/show.asp?id=2455.

--
Hugo Kornelis, SQL Server MVP|||Hi

Go to http://212.50.191.220/techfaq and enter restart in the search box
in the bottom center this pops up a new page of info pulled from my SQL
database. As you can see it can be difficult to read so I would like to
add some line breaks and if possible some other formatting.

When I pull the info in it comes as one big lump of text so ASP cannot
format it what I beleive from one of my earlier replies is that I can
embed some commands into the text held in the entry to be pulled out of
the database that ASP can read and turn into html formatting when it is
displayed on the page.

Is this correct and if so how do I add them.

Anthony
Hugo Kornelis wrote:
> On 17 May 2006 02:01:41 -0700, alamb200@.hotmail.com wrote:
> >Hi
> >I am using a web page as my front end using ASP to pull the info from
> >the SQL database so I am in the position of either having multiple
> >columns with small bits of info in my database so I can format them how
> >I want
> >The other option is to have some commands built into the text in the
> >database entry which will be read by the system and control the
> >formatting when it is read.
> >Is this possible?
> Hi Anthony,
> I'm not an ASP guy, so I don't know. Are you saying that linebreaks in a
> long character data column are not displayed properly by ASP? Have you
> consiedered asking for alternatives in one of the ASP groups?
> >When I am trying to edit the info in the database all I am doing is
> >right clicking on the table in Enterprise manager and display all
> >columns and working on it from there.
> >Is this the right way to do this?
> No, definitely not. Data entry through Enterprise Manager is broken in
> far more ways than I care to remember. Some of these errors are listed
> by Aaron Bertrand at http://www.aspfaq.com/show.asp?id=2455.
> --
> Hugo Kornelis, SQL Server MVP|||On 18 May 2006 06:53:21 -0700, alamb200@.hotmail.com wrote:

>Hi
>Go to http://212.50.191.220/techfaq

"The page cannot be found"

>When I pull the info in it comes as one big lump of text so ASP cannot
>format it

I can only repeat my previous suggestion: if ASP has trouble retaining
embedded CR/LF combinations from a varchar column, then the best place
to seek help is an ASP group.

--
Hugo Kornelis, SQL Server MVP|||(alamb200@.hotmail.com) writes:
> When I pull the info in it comes as one big lump of text so ASP cannot
> format it what I beleive from one of my earlier replies is that I can
> embed some commands into the text held in the entry to be pulled out of
> the database that ASP can read and turn into html formatting when it is
> displayed on the page.

Neither do I know much ASP, but I would assume that if you get some
text from the database, and spit it out between regular <P> tags,
that any line breaks will be blissfully ignored, as that is how HTML
works.

You could use the <PRE> tag to maintain the line-breaks, but the text
would be presented in a monospaced font, so it may look a bit ugly.

You could run this:

SELECT replace(col, char(13) + char(10), <BR />)

to change line breaks to <BR /> tags. The <BR /> introduces a line break
in the text.

You could also store the text in HTML format.

But as Hugo says, asking in an ASP group is probably a good idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Format a string

Hi all,
My company haa a big detabase and i am working on a marketing and sale repor
t.
i had lot of junk data like:
D-CAB PZTN4-3553-01 TOYOTA,Plate Hook A,7.0mm,(SET)
TOYOTA LINER D-CAB (PZO53-0K003), Plate, Robot Hook A, 7.0 MM.
i want to take out only ( D-CAB) but i coudnt make it from the string.
Can u all help me.
thanx
waiting for solution
from
SufianIf you need the first word from every row, you can use something like
this:
SELECT ProductName, LEFT(ProductName,
ISNULL(NULLIF(CHARINDEX(' ',ProductName),0),
LEN(ProductName))) FROM Northwind..Products
Razvan|||Dear thax for ur suggestion/.
but please read the questation i had asked to write a query in which where
ever the D-CAB is there it return me that not only the first.
if can be done in many ways.
but i need what i asked so please if u know then pls.
thanx
--
waiting for solution
from
Sufian
"Razvan Socol" wrote:

> If you need the first word from every row, you can use something like
> this:
> SELECT ProductName, LEFT(ProductName,
> ISNULL(NULLIF(CHARINDEX(' ',ProductName),0),
> LEN(ProductName))) FROM Northwind..Products
> Razvan
>|||Is this what you need:
SELECT CASE
WHEN ProductName LIKE '%D-CAB%' THEN 'D-CAB'
WHEN ProductName LIKE '%Something else%' THEN 'Something else'
-- and so on...
END
FROM YourTable
If it's anything like that, I'd recommend that you create a table with
the keywords and use it in an UPDATE query to fill a new column in the
main table.
Razvan|||The following will update a field named CarName and remove D-CAB.
update
MyTable
set
CarName = replace(CarName,'D-CAB','')
"Mohd Sufian" <sufian@.aeroflex.co.th> wrote in message
news:609D681C-7EDE-4E87-80D5-204E437AB453@.microsoft.com...
> Hi all,
> My company haa a big detabase and i am working on a marketing and sale
report.
> i had lot of junk data like:
> D-CAB PZTN4-3553-01 TOYOTA,Plate Hook A,7.0mm,(SET)
> TOYOTA LINER D-CAB (PZO53-0K003), Plate, Robot Hook A, 7.0 MM.
> i want to take out only ( D-CAB) but i coudnt make it from the string.
> Can u all help me.
> thanx
>
>
> --
> waiting for solution
> from
> Sufian