Please Help Me !

I am as Visual Basic 6.0/.Net Developer
Wath Is The Microsoft SQL Server Reporting Service ?
Wath Is The Microsoft SQL Server Analisie Service/Olap?
Wath Is The Microsoft Engleech Query ?
Thank You !These are features of Microsoft's SQL Server product. The latest
version is SQL Server 2005.
The documentation for SQL Server 2005 can be downloaded from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx.
If you have further new user questions you might be best to post them
to the microsoft.public.sqlserver.newusers newsgroup.
I hope that helps.
Andrew Watt [MVP]
On Thu, 20 Apr 2006 00:09:53 +0200, "Meftah Tayeb"
<SQLSRVX86@.hotmail.com> wrote:
>I am as Visual Basic 6.0/.Net Developer
>Wath Is The Microsoft SQL Server Reporting Service ?
>Wath Is The Microsoft SQL Server Analisie Service/Olap?
>Wath Is The Microsoft Engleech Query ?
>Thank You !

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

PIVOT question

I have a basic table consisting of several thousand records. Im trying to generate a pivot query for a report. the table consists of records, each of which has a recieved date ( small date time ) and a tranactioncount ( int ) . Im looking to generate a PIVOT to show the show the month and year counts for the files recieved

ie

YEAR | JAN FEB MAR APR etc

2004 ! 2 34 67 43

2005 | 12 2 3 1

can anybody explain in laymans terms how to do this

Thank you in advance

are you using SQL 2000 or 2005 ?

If 2005, use the pivot function|||i am using sql2005, ive looked up the documentation on the PIVOT function but still am having diffaculity in applying it|||

To do this in 2000, check out my reply of this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=249991&SiteID=1

However, the PIVOT method in 2005 is better. What errors are you getting?

|||

You can do something like below using PIVOT operator:

select p.yr as Year, p.[1] as [Jan], p.[2] as [Feb], ....

from (select year(receivedate) as yr, month(receivedate) as mn, trancnt from tbl) as t

pivot (sum(t.trancnt) for t.mn in ([1], [2], [3], ...)) as p

|||

I am curious to know what are the practical uses of the Pivot function assuming you do not grant end users acces to your database.

On my side, I use the pivot function to pack more data in Reporting Services while avoiding the Matrix reports shortcomings, however it is very tedious to build. If you want if maitenance free. You have to break the rule of "NO dynamic SQL ever".

Any thoughts?

Philippe

|||Yes, the use cases of PIVOT operator is very restrictive right now. If you want to pivot on say multiple measures or aggregates then you have to use the traditional SQL approach of CASE expressions and GROUP BY in the query. The dynamic list generation for PIVOT operator is also something we have heard frequently from customers. A future version of SQL Server might provide additional enhancements, optimizations for PIVOT which will improve the usage & performance of the query. So if you find instances where you can achieve the result using PIVOT operator then use it instead of the CASE / GROUP BY approach. Hope this clarifies.