Please help - Is this even possible?

I am executing multiple stored procedures in a stored procedure, pulling back a table of results, which is activated by a crystal report. One of the stored procedures activates a DTS Package. My problem is that the activation of the dts package returns output results, which in turn throws off my crystal report. Is there a way to still run this stored procedure as is but without returning the DTS output results??

I am using Sql 2000 and Crystal 8.5.

I would really appreciate any suggestions.

ThanksYou can create the temp table and insert the execution of the dts package into the temp table. This will "capture" the results and effectively mask them from being seen as output. You will also probably want to use SET NOCOUNT ON in your procedure.

Please help - Is this even possible?

I am executing multiple stored procedures in a stored procedure, pulling back a table of results, which is activated by a crystal report. One of the stored procedures activates a DTS Package. My problem is that the activation of the dts package returns output results, which in turn throws off my crystal report. Is there a way to still run this stored procedure as is but without returning the DTS output results??

I am using Sql 2000 and Crystal 8.5.

I would really appreciate any suggestions.

ThanksYou'd be more likely to get an answer to this question in the MS-SQL forum (http://www.dbforums.com/f7).

-PatPsql

Placing multiple records on a single line (variables)

Hi, I am new to Crystal Reports, but I know Basic and other programming. I have Crystal Reports XI and am pulling data from our ERP/MRP system, Epicor Vista (Progress DB).

I've been asked to figure out a Crystal Reports for our company (I get thrown into these projects). I know what the report should look like and I know how I would go about some VB code in a macro in Excel if all the data was in worksheets(i.e. like tables).

Below is the data. Any help would be SO appreciated. So far I'm loving Crystal Reports and I can't wait to get some reports our company can start using but I'm stuck on understanding the timing and connection of formulas with the records.

Table1 "JobMtl"
Field "JobComplete":String
Field "JobNum":String
Table2 "JobOper"
Field "OpComplete":Boolean
Field "OprSeq":Number

{JobMtl.JobComplete}
False
True
{JobMtl.JobNum}
2010
2011
{JobOper.Complete}
False
True
{JobOper.OprSeq}
10
20
30
40

Let's say I dragged all 4 fields into a report. It would look like this.

JobNum JobComplete OprSeq OpComplete
2010 False 10 True
2010 False 20 True
2010 False 30 False
2010 False 40 False
2011 False 10 True
2011 False 20 False
2011 False 30 False

I would it to read like this

JobNum JobComplete PrevOp CurrOp NextOp
2010 False 20 30 40
2011 False 10 20 30

**Note: {JobMtl.JobComplete} will be used so I am only reporting jobs that are "not complete". I guess it means nothing to you guys, but I put it here because I was not sure if this will be involved in a formula.

Thanks,
Anthony

My email is ls1z282002_at_yahoo.com (replace "_at_" => "@.") if you would a *.RPT with the data I've shown.Check your e-mail.|||Come on
We all want to see the solution|||Here's what I got from her.

There is a second report that somone from another forum helped me on. I actually need to combine both of these into one because I like the report from SvB_NY because she mad the operations a single String. So I need to do some combining of the two.

I have another question that I'm posting below this.

Anthony|||After showing this to everyone at work, they of course asked for more detail in the report :)

What we have is for each operation {JobOper.OprSeq} it may be an Outsourced (There is a boolean field {JobOper.OprSeq}) that states whether that Operation is outsourced. If it is outsourced they want me to list the PONum & POLn. I know this is simple and I thought so too! I even got it too work! So here's the snag...if the Job does not have any outsourced operations it gets skipped in my report! My reasoning is our ERP software doesn't actually make a index for PO's if that job does not have any PO's against it. Makes sense to me. So how do I handle this?

In the report I attached there should be 3 jobs
2010
2011 => Not shown because no outsourced operations
2012

Am I going to have to create 2 seperate reports, save all the information in arrays. Then match up the arrays with some code and print out a report?

Thanks,
Anthony|||SvB_NY, Thanks for all the help!

I got my report to work and I published it into our ERP software. I appreciate all the help and attached is the final product, if anyone cares to look.

I did never get the PO's to work right because I found out we have multiple PO's to a given operation and that created multiple records for the operation, so my logic for getting the "Prev,Curr,Next" operation didn't work. This is OK though because I was running out of room for the data and I had to have a big comment field. If it was dire for us I'm sure I could get it to work, or I actually I would have posted the question here :)

Anthony

Placing a table on multiple files

I have very two big tables and I want to put them on
multiple files within a file group. I know that a
filegroup can have multiple files,but I am not sure how
exactly the table spans across the files.
Is there any way that I can put the table on multiple
files?
Please provide me some examples.
Thanks for help.
RajIf you have multiple files in a filegroup, and create the table on that =filegroup then the table will automatically be allocated space across =the two files. This space will be taken as the table grows, space is =taken in the same ratio as the currently available free space within the =file.
What would be interesting is:
What do you mean by big?
What are you looking to achieve?
Are the two(or more) files all physically on different drives or merely =separate files on the same array.
The benefits you may see (or not see) are all affected by the above.
Mike John
"Raj" <vmresumes@.yahoo.com> wrote in message =news:909a01c345b3$0b4f6140$a401280a@.phx.gbl...
> I have very two big tables and I want to put them on > multiple files within a file group. I know that a > filegroup can have multiple files,but I am not sure how > exactly the table spans across the files.
> > Is there any way that I can put the table on multiple > files?
> > Please provide me some examples.
> > Thanks for help.
> > Raj|||Thanks for the response.
We have 2 fact tables each 10 GB. To gain the performance and aslo easy
management of files, I wanted to put on different physical disks.
Raj.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:OuDOvSdRDHA.2144@.TK2MSFTNGP11.phx.gbl...
If you have multiple files in a filegroup, and create the table on that
filegroup then the table will automatically be allocated space across the
two files. This space will be taken as the table grows, space is taken in
the same ratio as the currently available free space within the file.
What would be interesting is:
What do you mean by big?
What are you looking to achieve?
Are the two(or more) files all physically on different drives or merely
separate files on the same array.
The benefits you may see (or not see) are all affected by the above.
Mike John
"Raj" <vmresumes@.yahoo.com> wrote in message
news:909a01c345b3$0b4f6140$a401280a@.phx.gbl...
> I have very two big tables and I want to put them on
> multiple files within a file group. I know that a
> filegroup can have multiple files,but I am not sure how
> exactly the table spans across the files.
> Is there any way that I can put the table on multiple
> files?
> Please provide me some examples.
> Thanks for help.
> Raj|||Sounds very sensible then - sorry for the questions but you often get =people worrying about a huge (2Gb) table and then creating multiple =filegroups on the same raid 5 array, which all gets a bit pointless!
Mike
"Vish" <mocherla_v@.hotmail.com> wrote in message =news:eHjqdPeRDHA.2480@.tk2msftngp13.phx.gbl...
> Thanks for the response.
> > We have 2 fact tables each 10 GB. To gain the performance and aslo =easy
> management of files, I wanted to put on different physical disks.
> > Raj.
> > "Mike John" <Mike.John@.knowledgepool.com> wrote in message
> news:OuDOvSdRDHA.2144@.TK2MSFTNGP11.phx.gbl...
> If you have multiple files in a filegroup, and create the table on =that
> filegroup then the table will automatically be allocated space across =the
> two files. This space will be taken as the table grows, space is taken =in
> the same ratio as the currently available free space within the file.
> > What would be interesting is:
> > What do you mean by big?
> What are you looking to achieve?
> Are the two(or more) files all physically on different drives or =merely
> separate files on the same array.
> > The benefits you may see (or not see) are all affected by the above.
> > Mike John
> > "Raj" <vmresumes@.yahoo.com> wrote in message
> news:909a01c345b3$0b4f6140$a401280a@.phx.gbl...
> > I have very two big tables and I want to put them on
> > multiple files within a file group. I know that a
> > filegroup can have multiple files,but I am not sure how
> > exactly the table spans across the files.
> >
> > Is there any way that I can put the table on multiple
> > files?
> >
> > Please provide me some examples.
> >
> > Thanks for help.
> >
> > Raj
> > >=20

placing a new line character

hi all

i have a filed in my database with name address which contains addresses.

i want to split address in multiple line .

LIke

addressline1

addressline2

addressline3

i.e

Robart Peter

4th Banglow road

MC city

can any one help me.

vbNewLine or vbcrlf functions can be used to wrap the content to next line.|||thanks

placing a new line character

hi all

i have a filed in my database with name address which contains addresses.

i want to split address in multiple line .

LIke

addressline1

addressline2

addressline3

i.e

Robart Peter

4th Banglow road

MC city

can any one help me.

vbNewLine or vbcrlf functions can be used to wrap the content to next line.|||thanks

Place results in Colmn rather than rows

I have a few tables that i need to run a query on and instead of having them appear in multiple rows how do i return teh results in columns instead.

eg: System Name

1 Mr A

1 Mr B

2 Mr C

2 Mr D

INTO System Name1 Name2

1 Mr A Mr B

2 Mr C Mr D

SELECT CASE WHEN THEN ELSE END

Adamus

|||

SELECT CASE Name

WHEN System_ID = '1',

THEN

Name2

ELSE

Name3

END

Not sure i get you?

|||declare @.table table
(
[System] int,
[Name] varchar(5)
)

insert into @.table
select 1, 'Mr A' union all
select 1, 'Mr B' union all
select 2, 'Mr C' union all
select 2, 'Mr D'

select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])
from @.table a
group by a.[System]
|||

Thanks

The names Mr A, Mr B etc will be in the hundreds so don't really fancy typing them all out. There could be up to 4 or 5 different names per system.

i have tried to adapt to this but doesn;t work:-

declare @.table table

(

[System] int,

[Name] varchar(5)

)

insert into @.table

select System_ID, (firstname + ' ' + surname) as Name union all

select System_ID, (firstname + ' ' + surname) as [Name 1] union all

select System_ID, (firstname + ' ' + surname) as [Name 2]

where system = 1

From ((((dbo.System as S..........followed by my joins....

Select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])

from @.table a

group by a.[System]

How do i do this when i need to search for the criterea?

|||the table variable is for demonstrating the script.

use the query and change to your actual table name.

select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])
from @.table a
group by a.[System]|||

ok . got it working partially,

The Min and Max just returns 2 results? Some have 3 or 4 names?

|||do this in your front end application. It can be done in T-SQL but it will not be clean

PIVOT/CROSS TAB/Converting Rows to (multiple group) Columns

Hello All,

I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.

Consider that we have Product Category, Product and its monthly sales information retrieved as follows:

CategoryID ProductID ProductName Month UnitPrice QtySold SalesAmount 1 1 Panel Jan 5 10 50 1 1 Panel Feb 5 15 75 1 1 Panel Mar 5 20 100 1 2 Frame Jan 10 30 300 1 2 Frame Feb 10 25 250 1 2 Frame Mar 10 20 200 1 3 Glass Jan 20 10 200 1 3 Glass Feb 20 20 400 1 3 Glass Mar 20 30 600

I would like it to be converted into following result set:

CategoryID ProductID ProductName UnitPrice QtySold_Jan SalesAmt_Jan QtySold_Feb SalesAmt_Feb QtySold_Mar SalesAmt_Mar 1 1 Panel 5 10 50 15 75 20 100 1 2 Frame 10 30 300 25 250 20 200 1 3 Glass 20 10 200 20 400 30 600

I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.

Thanks.

What you are attempting to do is BEST done with the client application. SQL Server excels at storing and retreiving data. These kinds of 'transformations', while possible, are not the best use of a very expensive resource.

However, if you must, these articles demonstrate several variations of how to accomplish your goal -and they offer 'concrete' examples

Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

.

pivot tables & offline cube access

Is it possible to have multiple pivot tables in the same Excel workbook accessing the same offline cube file?

Currently, when the user is connected to the server it connects to a .asdatabase to populate many pivot tables. I need to allow the user to access a local cube file when they are not connected to the server.

I am using excel vba macros to connect the pivot caches to the online data source, which works perfectly. I would like to do the same to connect the pivot caches to the local cube file.

Is this possible? The local cube file is created off the server database using MDX (create global cube).

Thanks,
Lyn

If this is AS2005 - there is a bug in this area that is being fixed in the SP2 release. I think the fix would allow you to do what you've described.|||

Is this possible? The local cube file is created off the server database using MDX (create global cube).

yes possible , but there is a problem. u have to run this MDX statment manually , I tries to put it in a schedule in a jobs list , but it says the MDX syntax is worng, any help?

Karim

pivot tables & offline cube access

Is it possible to have multiple pivot tables in the same Excel workbook accessing the same offline cube file?

Currently, when the user is connected to the server it connects to a .asdatabase to populate many pivot tables. I need to allow the user to access a local cube file when they are not connected to the server.

I am using excel vba macros to connect the pivot caches to the online data source, which works perfectly. I would like to do the same to connect the pivot caches to the local cube file.

Is this possible? The local cube file is created off the server database using MDX (create global cube).

Thanks,
Lyn

If this is AS2005 - there is a bug in this area that is being fixed in the SP2 release. I think the fix would allow you to do what you've described.|||

Is this possible? The local cube file is created off the server database using MDX (create global cube).

yes possible , but there is a problem. u have to run this MDX statment manually , I tries to put it in a schedule in a jobs list , but it says the MDX syntax is worng, any help?

Karim

pivot tables & offline cube access

Is it possible to have multiple pivot tables in the same Excel workbook accessing the same offline cube file?

Currently, when the user is connected to the server it connects to a .asdatabase to populate many pivot tables. I need to allow the user to access a local cube file when they are not connected to the server.

I am using excel vba macros to connect the pivot caches to the online data source, which works perfectly. I would like to do the same to connect the pivot caches to the local cube file.

Is this possible? The local cube file is created off the server database using MDX (create global cube).

Thanks,
Lyn

If this is AS2005 - there is a bug in this area that is being fixed in the SP2 release. I think the fix would allow you to do what you've described.|||

Is this possible? The local cube file is created off the server database using MDX (create global cube).

yes possible , but there is a problem. u have to run this MDX statment manually , I tries to put it in a schedule in a jobs list , but it says the MDX syntax is worng, any help?

Karim

Pivot Multiple Values

Is there a way to pivot multiplie values in one 'run'.... In the order of ...

PIVOT ( SUM(DSH_TICKETS) FOR CPRF_NBR IN ([1], [2], [3], [4], [5])

SUM(HALL_CAPACITY) FOR CPRF_NBR IN ([1], [2], [3], [4], [5]) ) PVT

I know that there would be a problem with the headers, but that i could solve by using a second dummy for cprf_nbr and increase it with 10 (ex.)

Until knwo i did the jobg with a case statement, but it would be much nicer with a PIVOT.

No. This is not possible with PIVOT operator. You will have to use the old approach of using multiple aggregate functions with CASE expressions and GROUP BY which is what PIVOT does right now.|||

I am not 100% sure that this matches, but I wrote a blog about taking a set that looked like:

GroupBy PropertyName value1 value2
- -- --
First Property1 1.00 2.00
First Property2 2.00 4.00
Second Property1 4.00 8.00
Second Property2 8.00 16.00

And pivoted to look like:

groupBy Property1-value1 Property1-value2 Property2-value1 Property2-value2
- - -- - -
First 1.00 2.00 2.00 4.00
Second 4.00 8.00 8.00 16.00

The idea was to add another layer in there and Break down the set into two queries (in your case one for DSH_TICKETS, and another for HALL_CAPACITY) and then change the names of the columns to DSH_TICKETS-1, DSH_TICKETS-2, etc, then pivot on these names for the group.

Here it is: http://drsql.spaces.msn.com/blog/cns!80677FB08B3162E4!758.entry

There I used max, but sum should work (I think :)

Pivot multiple columns

I have a table the records the results of three different tests that are graded on a scale of 1-7.The table looks something like this.

PersonIdTestATestBTestC

1454

2624

3556

4151

I would like to have a SQL statement that would pivot all this data into something like this

Test1234567

A1001110

B0100300

C1002010

Where the value for each number is a count of the number of people with that result.

The best solution that I have been able to come up with is to pivot each test and UNION ALL the results together.Is there a way to do this in a single statement?

(If this has already been covered I apologize, but I could not find the solution.)

Try:

use tempdb

go

Code Snippet

create table dbo.t1 (

PersonId int not null unique,

TestA int,

TestB int,

TestC int

)

go

insert into dbo.t1 values(1, 4, 5, 4)

insert into dbo.t1 values(2, 6, 2, 4)

insert into dbo.t1 values(3, 5, 5, 6)

insert into dbo.t1 values(4, 1, 5, 1)

go

;with unpvt

as

(

select

stuff(Test, 1, 4, '') as Test,

[Value]

from

dbo.t1

unpivot

(

[Value]

for [Test] in ([TestA], [TestB], [TestC])

) as unpvt

)

select

Test,[1], [2], [3], [4], [5], [6], [7]

from

unpvt

pivot

(

count([Value])

for [Value] in ([1], [2], [3], [4], [5], [6], [7])

) as pvt

order by

Test

go

drop table dbo.t1

go

AMB