Distributed Partitions (Across multiple servers)

I have an existing table that I want to partition across multiple servers. I am having a hard time finding an article that explains how to do it.

I am in the process of designing applying a scale out architecture to our database...but have hit this brick wall.

Any help would be great.

Thanks!

Eric Elliston

SWFLParent.com

http://www.swflparent.com

Hi Eric,

I think you're thinking of distributed partitioned views. Have a look at http://msdn2.microsoft.com/en-us/library/aa479364.aspx which actually presents a few options available to you.

Cheers,

Rob

Distribute backup on multiple disks

I know ew can distribute backup files on multiple disks.
is there a way to specify the size also ? to illustrate :
my backup size is 105 GB. I want to specify to save xGB on
Disk1 and rest on disk2 . Is it poosible ? Any hellp will
be highly appreciated>--Original Message--
>I know ew can distribute backup files on multiple disks.
>is there a way to specify the size also ? to illustrate :
>my backup size is 105 GB. I want to specify to save xGB
on
>Disk1 and rest on disk2 . Is it poosible ? Any hellp will
>be highly appreciated
>.
>
Reply:
Opps on that last reply, you said backup files, and I was
thinking database files. Sorry about that.|||When you strip your backup to multiple files, these files seem to be of
similar size. As the result, you can take advantage of this observation. For
instance, if you want about 34GB on disk1 and the other on disk2, you can
issue the following:
BACKUP DATABASE <yourDB> to disk='DiskDrive1\yourDB.bak',
disk='DiskDrive2\yourDB1.bak',
disk='DiskDrive2\yourDB2.bak'
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Debasish" <debasish.paul@.karma-group.com> wrote in message
news:09aa01c38216$e87a5ad0$a101280a@.phx.gbl...
> I know ew can distribute backup files on multiple disks.
> is there a way to specify the size also ? to illustrate :
> my backup size is 105 GB. I want to specify to save xGB on
> Disk1 and rest on disk2 . Is it poosible ? Any hellp will
> be highly appreciatedsql

distrib.exe Many instances running

How many instances of distrib.exe should be running?

Yesterday I created multiple publications in SQL 2005. without counting, it looks like each publication has its own distrib.exe.... it this the way it is supposed to work?

Each instance it holding 8k of ram (not a problem right now) but I have many more publications to create...

Please let me know if this is the way SQL server works now?

Hi William,

I am guessing you were used to different behavior on SQL2000 where only one instance of distrib.exe is used to service multiple publications\subscriptions to the same subscriber database. The new behavior in SQL2005 is actually the result of a change in the default value of the 'independent_agent' publication property from 0 (false) to 1 (true). So, you can get the same behavior as SQL2000 by changing the 'independent_agent' property from 1 to 0 via sp_changepublication. I am also a bit curious to know why you are creating many publications. If your goal is to have finer granularity for subscription re-initialization, you can actually perform article level reinitialization using sp_reinitsubscription.

-Raymond

|||

Thanks for the explanation. I just wanted to make sure it was normal...

The reason for the multiple publications is not for the purpose of the subscriptions but rather the publications them selves. We have about 200 table that are changing several times a year based (data lenghts or additional columns). The table are bundles together based on when they are anticipated to change.

This is the explanation I was given when I was hired. I think it is just to make it easier to see what is what without opening the publications.

DISTINCT First Name and Last Name for Mail Merge

I'm trying to create a mail merge on my MS SQL database. Many of my owners are entered more than once as they might own multiple properties. I'm trying to write a query where each is only pulled one time and I'm having some trouble. I think I need to use SELECT DISTINCT, but I need to make sure I don't filter out people with the same first name and different last names and vice versa. Thanks!One of my old bosses insisted this could be done using soundex

don't know, but maybe that is an area to start?

http://www.4guysfromrolla.com/webtech/tips/t103101-1.shtml

good luck|||It doesn't need to be that deep.

I thought of another idea. How do I use SELECT DISTINCT to return all rows in my table and only unique address fields?|||I think that a group statement might accomplish this??

as in

select f_name from table group by f_name

I re-read your post...

address validation is a trick...

the address field can be set up many ways, and the street
type designation can also be setup in a whole slew
of different ways...

what does your address field look like (sample data)

thanks
tony|||Soundex is a practically useless algorithm.

You can select distinct First/Last names in a subquery, but then which address would you use if the two records do not agree?

Here is one of the simplest methods:select YourTable.*
from YourTable
inner join --DistinctNames
(select Max(PrimaryKey) as PrimaryKey
from YourTable
group by FirstName,
LastName) DistinctNames
on YourTable.PrimaryKey = DistinctNames.PrimaryKey|||You da' Blindman! Thanks, that's exactly what I needed.|||I hate to post an extension to the question in the same place, but here goes. My owners are listed more than once if they own more than once parcel of land. What I need to do next is continue the merge to each only once, but add up their land acreage amounts and total for each person. Thoughts anyone? Thanks in advance.sql

distinct count with multiple partitions?

We analyze much of our data using distinct measures. Some of the underlying sources - i.e. daily page views are heavy and have millions of rows a day. Because of this I've created daily partitions to only process the incremental data that's arrived. However, I'm curious - how does distinct count perform when it needs to rollup over multiple partitions?

IE Say it's Dec 25th, and I have 25 unique partitions for each day of December thus far. Internally, how do the distinct measures correctly accumulate the unique instances of my measure? Are there any significant performance concerns to be aware of?Distinct Count measure will work fine with multiple partitions. AS keeps the distinct count values inside the partitions, and therefore it can correctly aggregate across them. Partition per day should be OK too.|||Arjun: Make sure you are on at least SQL 2005 SP1 QFE rollup, preferably SP2 CTP2. There were a couple of bugs fixed for distinct count measures over multiple partitions.|||Good to know, thanks! Jeff, do you have any details on the nature of the issues with distinct counts over multiple partitions?

Displaying multiple records from one record

Hi there,

I've a complex stored procedure, which I'm trying to get a list of one or more users based the list of records that are being display. If I mentioned the overall store procedure here it'll turn you off, so I'll put the questions in bitesize chunks.

Example would be if Bob and Luke work for Acrm Corp and John for Maxwellarms Ltd, and there was an intimediate table which linked both. With my stored procedure I want to display both names in the same row as Acrm Corp. Is there a way to do this?

User table
Userid Username

1 Bob
2 Luke
3 John

Store procedure result
Companyname Username

Acrm Corp Null
Maxwellarm Ltd Null

Regards,

Since my last post, I found that the CMS is storing individual ids.

Still it would be nice to know how others are doing this.

An addition to my post above, I believe if I wanted more than one firstname and surname to appear in the columns, that I would have to create a different stored procedure retrieving the unique id from the first stored procedure.

Displaying multiple datasets in one report

I have a store procs that return more that one result set i.e. multiple
selects in the store proc. The report always picks out the first result set
it finds. How can I get a report to show the other result sets that the store
proc generates? Rewriting the store procs is not an option due to time
constraints.Bad news. Currently RS only works with the first recordset. If you can't
rewrite the sp's, you might be able to create separate wrapper sp's that
return the individual recordsets.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Mike S" <MikeS@.discussions.microsoft.com> wrote in message
news:B3FCBC28-EE83-4FA6-AF23-6838DA338954@.microsoft.com...
>I have a store procs that return more that one result set i.e. multiple
> selects in the store proc. The report always picks out the first result
> set
> it finds. How can I get a report to show the other result sets that the
> store
> proc generates? Rewriting the store procs is not an option due to time
> constraints.

displaying multiple charts using a single dataset

Hello,

I have the following tables :

Area
areano
areaname

Locality

locno
areano

RequestType
reqid
reqdtls

WebSummit

SummitId

RequestorName

DateOfRequest

reqid

Areano

I want to display the total no. of Addition, Removals and Relocations during the past 3 months in a paticular Area.

I have written the query for this.

select q.[areaname] as Area,

classCount as TotalCount, ReqType

from(select a.areano,

a.classCount, ReqType

from(select areano,

count(*)as classCount, reqdtls as ReqType

from WebSummit

join RequestType

on WebSummit.reqid = RequestType.reqid

where date_created >=dateadd(mm,-3,getdate())

groupby areano, reqdtls

) a

join WebSummit b

on a.areano = b.areano

groupby a.areano,

a.classCount, ReqType

) p

join Area q

on p.areano = q.areano

The query works fine and has been tested. Now I want to display this in chart format.

The display should be in a chart format, one for each area showing a bar for each RequestType.

I have placed "TotalCount" in the Data Fields section and ReqType in Category fields. How do I use the Filter to set an expression so that it displays the data in a seperate chart for each Area.

I tried using =Fields!Area.Value and set it to Area1 to display only Area1 values, however the preview shows nothing.

Plz help me out.

Here's some sample data

Area

AreaNo AreaName

1 Area1

2 Area2

3 Area 3

4 Area4

5 Area5

6 Area6

7 Area7

Locality

LocNo AreaNo

1 1

2 1

3 1

1 2

2 2

1 3

2 3

3 3

4 3

1 4

2 4

1 5

2 5

3 5

RequestType

reqid reqdtls

1 Addition

2 Removal

3 Relocate

WebSummit

SummitId RequestorName DateOfRequest reqid AreaNo

1 John 12/6/2007 1 1

2 Jack 13/6/2007 1 1

3 Bill 12/6/2007 2 1

4 Ben 12/6/2007 3 1

5 Dale 14/6/2007 1 2

6 Evjen 15/6/2007 1 2

7 Fuller 16/6/2007 2 2

8 Jimmy 16/6/2007 3 2

9 Kart 16/6/2007 3 2

10 Fuller 16/6/2007 3 2

Regards,

Vidya

Any one has a clue?

Displaying Multiple Categories (from seperate tables) to be viewed on one page

Hello.

I just created separate tables for each of my categories and wanted to know how to return them all to be viewed on one page using the SQL Datasource (or whatever) This is for user accounts. I just need to know that part.

Sincerely,

Computergirl

use UNION ALL

select * from table1

union all

select * from table2

displaying more then one record from the same table in the same field

I was wondering if there was any way to make crystal reports grab multiple records from the same table and displahy them in one database field. Basicly I have a table that users would type info into each line of text that they type would equal one record on the table. When I run the report I need to display all the lines of text that they typed in. If I just refrence the table in the database field in crystal it will only pull the first record from the table and thats it.

Here is an example of the table
00000101 0001 This is a tes of the emergency broadcast system, if there was an actual emergency you would hear
00000101 0002 about it after the tones.This is a tes of the emergency broadcast system, if there was an actual
00000101 0003 emergency you would here about it after the tones.This is a tes of the emergency broadcast system

Now if I run the report the only thing it will dislay is the first line (record number "0001")

Thanks for any helpAdd a Textbox to your report. Then drag the first DB Field into the textbox, then drag the second DB Field into the same textbox.

I attached a picture of an Address field from one of my reports. It's a textbox and I dropped the 5 DB fields onto it (Name, Address, City, State, Zip)

Displaying Fields from Multiple Datasets (RS2000)

Hi guys, I have this problem of displaying fields from multiple datasets.

When I drag a field from the first dataset into a table, it works and displayed correctly. However when I dragged a field from a second dataset, it will be shown as

=First(Fields!FirstName.Value, "DataSet2") for Strings and

=Sum(Fields!StatusFlag.Value, "DataSet2") for Integers

I just want the integer from that single row but it sums up all the rows. When I removed the SUM keyword, the IDE will show build errors when I try to build the report. Is there any way to get around this? thanks!

Use the First keyword for integers as well.|||According to the data types, the IDE will always choose SUM as the aggregate for numeric values and FIRST as the aggregate for non-Integers. As Brad mentioned you can changed that, this is just a suggestion of the Designer.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Displaying Dynamically created columns

Hello,
I have a stored procedure that accepts input parameters and depending
on the input, returns multiple columns. For example depending on the month
range passed in, the stored procedure creates and populates temporary table
with columns for each month and returns the values in the temporary table.
How do I display these dynamically created columns using Reporting Services
(the number of coulmns returned by the stored proc varies between different
runs). Any help is appreciated.Sunil,
You would need to use the matrix control and then edit the
matrix_columngroup to use the data returned from your stored proc.
Hope this helps.
Bill Youngman
Anexinet, Inc.
"Sunil Oliver" <SunilOliver@.discussions.microsoft.com> wrote in message
news:D11DBA4E-C99F-4E37-8C0D-7F9D76192001@.microsoft.com...
> Hello,
> I have a stored procedure that accepts input parameters and
depending
> on the input, returns multiple columns. For example depending on the month
> range passed in, the stored procedure creates and populates temporary
table
> with columns for each month and returns the values in the temporary table.
> How do I display these dynamically created columns using Reporting
Services
> (the number of coulmns returned by the stored proc varies between
different
> runs). Any help is appreciated.

Displaying data from multiple datasets in the same table

I have two datasets, the first one attached to a table, and I would like
to display a field from the second dataset based on a criteria. Think as an
analogy to joins, but for various reasons I cannot merge the queries to get
a single dataset (data is coming from different databases). So, I have a
common key and I want to lookup into the second dataset? How can I achieve
that?
Practical example: first dataset retrieves a list of people, with their
address and a column the contains the country code. In a different DB I have
a table that has associations for a country code a country name. How can I
display a list of people and the country name? No join at query level is
acceptable.
Regards,
MariusA data region can only be bound to one dataset. Why is a join in a query
unacceptable? Can you create a view in your database with a join in it and
create a dataset off of that view? Would that be an acceptable alternative?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marius Cristian CONSTANTIN" <mconstantin.nos.pam@.bigfoot.com> wrote in
message news:ewqG%23aGrEHA.348@.TK2MSFTNGP15.phx.gbl...
> I have two datasets, the first one attached to a table, and I would
like
> to display a field from the second dataset based on a criteria. Think as
an
> analogy to joins, but for various reasons I cannot merge the queries to
get
> a single dataset (data is coming from different databases). So, I have a
> common key and I want to lookup into the second dataset? How can I achieve
> that?
> Practical example: first dataset retrieves a list of people, with
their
> address and a column the contains the country code. In a different DB I
have
> a table that has associations for a country code a country name. How can I
> display a list of people and the country name? No join at query level is
> acceptable.
> Regards,
> Marius
>|||It's not acceptable because we are not using a database, but instead a
custom data extensions that gets data from our business layer objects. In
business layer we do complex calculations, so writing the reporting as pure
SQL queries wouldn't be feasible. So we have business layer object that
retrieves a list of persons, and a business layer that retrieves a table
with mapping from country codes to names. We would prefer not to implement
joins in our custom data extensions if it would be possible. Also, I guess
that subreports would do it, but is seems to be that it would be an
overkill, and would raise problems when going to numeric (orders with items,
and items with prices in a different dataset), because for example we could
have two different tables, and show the difference between two prices.
Regards,
Marius Cristian CONSTANTIN
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:Oc03VBHrEHA.1964@.TK2MSFTNGP12.phx.gbl...
>A data region can only be bound to one dataset. Why is a join in a query
> unacceptable? Can you create a view in your database with a join in it and
> create a dataset off of that view? Would that be an acceptable
> alternative?
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Marius Cristian CONSTANTIN" <mconstantin.nos.pam@.bigfoot.com> wrote in
> message news:ewqG%23aGrEHA.348@.TK2MSFTNGP15.phx.gbl...
>> I have two datasets, the first one attached to a table, and I would
> like
>> to display a field from the second dataset based on a criteria. Think as
> an
>> analogy to joins, but for various reasons I cannot merge the queries to
> get
>> a single dataset (data is coming from different databases). So, I have a
>> common key and I want to lookup into the second dataset? How can I
>> achieve
>> that?
>> Practical example: first dataset retrieves a list of people, with
> their
>> address and a column the contains the country code. In a different DB I
> have
>> a table that has associations for a country code a country name. How can
>> I
>> display a list of people and the country name? No join at query level is
>> acceptable.
>> Regards,
>> Marius
>>
>

Displaying data from multiple databases

I am using a subreport to lookup up a name from a code value since the lookup
table is in a separate database from the main report. How do I limit the
white space on the name field? I have defined a single textbox in the
subreport, but when I preview the subreport it displays a complete line.
Similarly, when the main report previews the subreport displays the complete
line. I have tried placing the textbox in a rectangle, but it doesn't appear
to make a difference. Any suggestions?If you have a table for the data of the main report, add another column to
the table and put the subreport in that column. If it is a single value they
it will not go to another row.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:7C1E0D63-E86D-4A54-A7BE-7EBB2BD7F4F1@.microsoft.com...
>I am using a subreport to lookup up a name from a code value since the
>lookup
> table is in a separate database from the main report. How do I limit the
> white space on the name field? I have defined a single textbox in the
> subreport, but when I preview the subreport it displays a complete line.
> Similarly, when the main report previews the subreport displays the
> complete
> line. I have tried placing the textbox in a rectangle, but it doesn't
> appear
> to make a difference. Any suggestions?|||You could also try putting the subreport in a rectangle. (Can you guess,
I'm stuck on rectangles for solving white space problems?)
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OZux6OM3EHA.3596@.TK2MSFTNGP12.phx.gbl...
> If you have a table for the data of the main report, add another column to
> the table and put the subreport in that column. If it is a single value
> they it will not go to another row.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> news:7C1E0D63-E86D-4A54-A7BE-7EBB2BD7F4F1@.microsoft.com...
>>I am using a subreport to lookup up a name from a code value since the
>>lookup
>> table is in a separate database from the main report. How do I limit the
>> white space on the name field? I have defined a single textbox in the
>> subreport, but when I preview the subreport it displays a complete line.
>> Similarly, when the main report previews the subreport displays the
>> complete
>> line. I have tried placing the textbox in a rectangle, but it doesn't
>> appear
>> to make a difference. Any suggestions?
>

display parameter value on Report Builder generated report

hi all,
is it possible to display the parameter value on the report for
parameters which contain multiple selection ?
i can display the parameter value if it has only 1 value. how can i
display the values if there are more than 1 ?
Thanks!Yes can be done. use
e.g in your text box
=Join(Parameters!empno.Value, ",")
Amarnath
"minority" wrote:
> hi all,
> is it possible to display the parameter value on the report for
> parameters which contain multiple selection ?
> i can display the parameter value if it has only 1 value. how can i
> display the values if there are more than 1 ?
> Thanks!
>|||thanks Amarnath.
previously, i tried Parameters!empno.value because i only filter by 1
value but will have error. so i didn't think that the Join function
will work.
thanks.

Display over 256 characters for VARCHAR(3500) field

Hopefully, someone can help me.
I am working with a database that contains multiple fields within the tables that are being used for Clinical notes. The fields are defined as VARCHAR(3500). But when I try to extract data (either through Query Analyzer or Crystal Reports), only the first 256 characters are displayed. I ran a query to give me the length of the maximum entry size which returned 2722 characters, yet only 256 are displayed.

How do I go about extracting ALL of the data from this field? Any help is much appreciated.

Thanks in advance.In Query Analyzer, select Options from the menu and bump up the maximum output characters, which defaults to 256.

Display multiple rows as one record

Hello,
I have sales history stored in a table in the following format:
Customer Year Sales
1 2006 5000
1 2005 8000
1 2004 2000
2 2006 6000
2 2005 10000
2 2004 20000
Can someone give me an example of a query that would output the data like
this:
Customer 2006Sales 2005Sales 2004Sales
1 5000 8000 2000
2 6000 10000 20000
Thanks in advance.
MikeThe word you're looking for here is PIVOT.
http://msdn2.microsoft.com/en-us/library/ms177410.aspx has all the
information you can throw a stick at.
Rob
"Mike" wrote:

> Hello,
> I have sales history stored in a table in the following format:
> Customer Year Sales
> 1 2006 5000
> 1 2005 8000
> 1 2004 2000
> 2 2006 6000
> 2 2005 10000
> 2 2004 20000
> Can someone give me an example of a query that would output the data like
> this:
> Customer 2006Sales 2005Sales 2004Sales
> 1 5000 8000 2000
> 2 6000 10000 20000
> Thanks in advance.
> Mike
>
>|||Isn't Pivot new in 2005? Any ideas how to do this in SQL 2000?
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:3D08D090-BA8D-4A8D-95A5-40E39F27C253@.microsoft.com...
> The word you're looking for here is PIVOT.
> http://msdn2.microsoft.com/en-us/library/ms177410.aspx has all the
> information you can throw a stick at.
> Rob
> "Mike" wrote:
>
like|||You can use case when statement in SQL Server 2000.
For example,
SELECT CustomerID,CASE Year WHEN 2006 THEN Sum(Sales) END '2006Sales'
...,
"Mike"?? ??? ??:

> Hello,
> I have sales history stored in a table in the following format:
> Customer Year Sales
> 1 2006 5000
> 1 2005 8000
> 1 2004 2000
> 2 2006 6000
> 2 2005 10000
> 2 2004 20000
> Can someone give me an example of a query that would output the data like
> this:
> Customer 2006Sales 2005Sales 2004Sales
> 1 5000 8000 2000
> 2 6000 10000 20000
> Thanks in advance.
> Mike
>
>|||Ah, that's more complicated.
One method is to do it on the client side... ;)
Another would be to write a nasty query like:
select s.customer,
'sales2006' = sum(s.sales2006),
'sales2005' = sum(s.sales2005),
'sales2004' = sum(s.sales2004)
from
(
select customer
, 'sales2006' = case when year = 2006 then sales else 0 end
, 'sales2005' = case when year = 2005 then sales else 0 end
, 'sales2004' = case when year = 2004 then sales else 0 end
from sales
) s
group by s.customer
See how this works for you...
Rob
"Mike" wrote:

> Isn't Pivot new in 2005? Any ideas how to do this in SQL 2000?
> "Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
> news:3D08D090-BA8D-4A8D-95A5-40E39F27C253@.microsoft.com...
> like
>
>|||Hi Rob,
With all due respect, why would you reinforce the idea that MS
has treated this subject in depth. It is superficial at best. It doesn't
even come up to the level of the Access crosstab. MS needs its
martini shaken not stirred. You do want a better drink don't you?:)
www.rac4sql.net
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:3D08D090-BA8D-4A8D-95A5-40E39F27C253@.microsoft.com...
>.
> http://msdn2.microsoft.com/en-us/library/ms177410.aspx has all the
> information you can throw a stick at.
>.|||If you don't want to reinvent the wheel try Rac.
No maintenance,no code,more time to think (and play:)
www.rac4sql.net
"Mike" <mbaith@.yahoo.com> wrote in message
news:etaY4oYbGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Isn't Pivot new in 2005? Any ideas how to do this in SQL 2000?|||Alright, maybe not all the information you can throw a stick at, but
certainly enough to be able to do this type of query. And I agree that the
PIVOT functionality is still really limited because you have to provide the
values you want to pivot over. But it's much better for people to go to the
MSDN site and read through what's written there than to have me say "Here's
a
query...", don't you think?
"Steve Dassin" wrote:

> Hi Rob,
> With all due respect, why would you reinforce the idea that MS
> has treated this subject in depth. It is superficial at best. It doesn't
> even come up to the level of the Access crosstab. MS needs its
> martini shaken not stirred. You do want a better drink don't you?:)
> www.rac4sql.net
> "Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
> news:3D08D090-BA8D-4A8D-95A5-40E39F27C253@.microsoft.com...
>
>|||Hi,
I know your trying to be helpful and do respect that.My b_t_h is with MS.
I can't believe that they have the arrogance to threat a sophisticated
'enterprise'
to the shallowness of PIVOT.And it doesn't stop there.When a cheap
utility embarrasses the worlds largest software company something is wrong.
Their 2005 product should be a far superior offering.Perhaps humilation and
ridicule will pay dividends.
www.rac4sql.net
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:CE63524D-5A6A-4526-82A2-86C641498C91@.microsoft.com...
> Alright, maybe not all the information you can throw a stick at, but
> certainly enough to be able to do this type of query. And I agree that the
> PIVOT functionality is still really limited because you have to provide
the
> values you want to pivot over. But it's much better for people to go to
the
> MSDN site and read through what's written there than to have me say
"Here's a
> query...", don't you think?
> "Steve Dassin" wrote:
>|||You might want to check out my article on this subject here ::
http://vadivel.blogspot.com/2004/12...el.blogspot.com
"Mike" wrote:

> Hello,
> I have sales history stored in a table in the following format:
> Customer Year Sales
> 1 2006 5000
> 1 2005 8000
> 1 2004 2000
> 2 2006 6000
> 2 2005 10000
> 2 2004 20000
> Can someone give me an example of a query that would output the data like
> this:
> Customer 2006Sales 2005Sales 2004Sales
> 1 5000 8000 2000
> 2 6000 10000 20000
> Thanks in advance.
> Mike
>
>

Display multiple reports

Does anyone know if there is a way to display multiple reports in one Crystal Report Viewer?
I'm using the Crystal Reports that are bundled with Microsoft Visual Basic .NET (2003).
Thank you,
Sandiyou can do with the help of subreports
Open the report and add other report as Subreport

Display Multiple Records In One Grouping Line

Is it possible to display three different record values in one grouping
header? For instance, I have End of Year values for three different records
for a grouping called Burbank. I am grouping by Burbank, but I want to show
the Year End value for Burbank in 2005, 2006, and a 2007 projection, but they
are three separate records in my query. If statements will only grab the
first year and subreports slow down the report a ton just for one value.
Thanks,
BJYou could always use a case statemnt for each year in your select statement.
"bjkaledas" <bjkaledas@.discussions.microsoft.com> wrote in message
news:D0704272-4328-44F9-8902-55CE36C704A8@.microsoft.com...
> Is it possible to display three different record values in one grouping
> header? For instance, I have End of Year values for three different
> records
> for a grouping called Burbank. I am grouping by Burbank, but I want to
> show
> the Year End value for Burbank in 2005, 2006, and a 2007 projection, but
> they
> are three separate records in my query. If statements will only grab the
> first year and subreports slow down the report a ton just for one value.
> Thanks,
> BJ|||How would I interact with the select statement in my report though? I have
the say 3 separate records in my SELECT statement of my dataset, but in the
report I want to display them all in one row. It is almost like a Matrix,
but I don't want to grow across. I want to preacknowledge that I have a
value from 2005, 2006, and 2007 that I want to display from three separate
records, but on one line of the report.
"Ben Watts" wrote:
> You could always use a case statemnt for each year in your select statement.
> "bjkaledas" <bjkaledas@.discussions.microsoft.com> wrote in message
> news:D0704272-4328-44F9-8902-55CE36C704A8@.microsoft.com...
> > Is it possible to display three different record values in one grouping
> > header? For instance, I have End of Year values for three different
> > records
> > for a grouping called Burbank. I am grouping by Burbank, but I want to
> > show
> > the Year End value for Burbank in 2005, 2006, and a 2007 projection, but
> > they
> > are three separate records in my query. If statements will only grab the
> > first year and subreports slow down the report a ton just for one value.
> >
> > Thanks,
> >
> > BJ
>
>|||You can put a sub report in a cell on a line. I have done this on the main
detail line and I have done this in an additional detail line below. That
might work for your needs. Takes a little playing around with the subreport
to make it look correct when embedded in the main report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bjkaledas" <bjkaledas@.discussions.microsoft.com> wrote in message
news:AA264F47-3040-40C6-9C87-9805596376B0@.microsoft.com...
> How would I interact with the select statement in my report though? I
> have
> the say 3 separate records in my SELECT statement of my dataset, but in
> the
> report I want to display them all in one row. It is almost like a Matrix,
> but I don't want to grow across. I want to preacknowledge that I have a
> value from 2005, 2006, and 2007 that I want to display from three separate
> records, but on one line of the report.
> "Ben Watts" wrote:
>> You could always use a case statemnt for each year in your select
>> statement.
>> "bjkaledas" <bjkaledas@.discussions.microsoft.com> wrote in message
>> news:D0704272-4328-44F9-8902-55CE36C704A8@.microsoft.com...
>> > Is it possible to display three different record values in one grouping
>> > header? For instance, I have End of Year values for three different
>> > records
>> > for a grouping called Burbank. I am grouping by Burbank, but I want to
>> > show
>> > the Year End value for Burbank in 2005, 2006, and a 2007 projection,
>> > but
>> > they
>> > are three separate records in my query. If statements will only grab
>> > the
>> > first year and subreports slow down the report a ton just for one
>> > value.
>> >
>> > Thanks,
>> >
>> > BJ
>>

Display IP address from client in a report

I know that may seem like a weird request, but bear with me. I have multiple possible web sites that would request a report from a given Report Server, and I'd like the IP address of whatever machine requested the report to be displayed somewhere on said report. Is there a way to achieve this?

Thank you!

NOTE: To simplify things, I literally want to display whatever IP the reporting server sees. No need to think complicated about if its a server, a client, etc. Just whatever IP the report server is seeing.

You can use a parameter in query string that contain IP address of the server macchine that require the report.

|||

You can get a client's IP address by:

string GetIPAddress(){return HttpContext.Current.Request.UserHostAddress;}

You can put this code in a custom assembly. You can call this assembly in your report:http://geekswithblogs.net/davyknuysen/archive/2007/03/26/109901.aspx

|||

I thought about embedding code, but unfortunately, the Request object is not set in a report (had to import System.web in it first to test that).

And sending the IP as a parameter would be the easy way, with a catch: Servers tend to have a lot of IPs, even more so if you include the IPv6 ones. Its difficult to "guess" which one is exposed to the server. Can do some tricks using subnet masks and such, but the ideal would be for the server to find it, itself.

My plan B is to have a config in machine.config or something (multiple apps need it, not just web) with the IP as a key, and we'll have to make sure not to forget to change it, or something...