Please Help me ->Bruce Loehle-Conger

Hello Bruce, my name's Carlos and i have a big problem, i have two reports
the firtone is the "master report", and this jump to a second report, but
when the second report show, the parameter bar is hidde, and when i back to
the master report, the parameter bar is also hidden.
Could yuo help me please.
Thanks.I had the same problem. I found a work around, but probably not the best way.
Right now I just used the "jump to url" under navigation and put: ="?" &
Globals!ReportFolder & "/report_name"
"Carlos López." wrote:
> Hello Bruce, my name's Carlos and i have a big problem, i have two reports
> the firtone is the "master report", and this jump to a second report, but
> when the second report show, the parameter bar is hidde, and when i back to
> the master report, the parameter bar is also hidden.
> Could yuo help me please.
> Thanks.|||Thanks scraejtp, i will try this and i will tell you the result.
I found a blog where someone suggest to use the parameter rc:parameter=true
in the query string, but don't work for me,
"scraejtp" wrote:
> I had the same problem. I found a work around, but probably not the best way.
> Right now I just used the "jump to url" under navigation and put: ="?" &
> Globals!ReportFolder & "/report_name"
>
> "Carlos López." wrote:
> > Hello Bruce, my name's Carlos and i have a big problem, i have two reports
> > the firtone is the "master report", and this jump to a second report, but
> > when the second report show, the parameter bar is hidde, and when i back to
> > the master report, the parameter bar is also hidden.
> >
> > Could yuo help me please.
> >
> > Thanks.

please help access/sql server conversion

I am new to the db world. I am trying to convert an Access 2000 database to SQL Server 2000. f/e has forms, reports, macros, modules, b/e has tables which the f/e file has linked to via link manager. The b/e is on a shared network drive. A dozen users, about 150 tables. The b/e file is appoaching 1Gb. The plan is to keep the Access f/e.

I have read the microsoft Q;s on Upsizing, DTS, Access Project vs Access database, Access sv SQL, etc. I am having diffculty understanding the best approach for my situation.

Looking for some expert guidance before I jump in.

Thanx

CFWhy do you need to migrate your data hauling operation from pickup trucks to 18 wheelers in the first place? Load? Speed? Safety?

How about - just for starters - a simple/non-radical/initial approach:
Keep everything the way it is except for actual data in Access tables that could go into SQL tables instead with Access tables linked to it. This will for instance overcome one of the biggest Access limits: 2 GB per "mdb".
(As you probably know, once over 2 GB - in 2000 & 2002/XP - Access could crash at any moment with data from it never to be seen again. )|||Thanks for your quick reply.

Speed is the main thing, and there have been multiple user contention problems leading to lockup. The site is upgrading everything for speed.

When you say "move the actual data only" What's the proper method - simplest, to move the data only, i.e., how do i create the SQL tables automatically/safely (150 tables), and then weeks/days later move the Access data to the new tables?

Thanks,

CF|||The real Client-Server solution would be "by the book" conversion of Access "mdb" into Access Project (mdp) as a front end and storing data with data processing in SQL S.
This of course is easier said than done, especially on live and often used db.

What is really easy (although not a real solution) is to first setup tables in SQL S., work out minor differences of data types between Access an SQL S., run SQL S. import wizard to populate the tables and then link to them from Access. For one thing it is a quick workaround for JET limit of 2 GB per mdb since linked tables do not take lot of room, for another is getting data into SQL S. to find out stress-free any conversion issues, like for instance often with date/time fields.

I had Access run like hell this primitive way on well indexed datasets of over 50 GB.sql

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

PL/SQL effeciency question

Right now I have a few PL/SQL procedures that calculate count(*) and sum(*) values for different reports. So far it's a daily operation, with around 80000 records per day (seems to be increasing by 10000 records every week though)

it's not a complex procedure, just a lot of number crunching for each columns. Right now the basic logic is to use a separate SQL statement for each column(with different conditions, of course), and repeat it until all the reports are filled in.

So far it's taking about 20 secs for going thru every 10000 records. Add the time it takes to load the records in (about 25-30 secs per 80000 records), and it becomes about 3 minutes of operation each day.

I have been thinking about how I can improve the performance of the procedure. So far I am thinking about whether the performance would improve if I can change the logic by declaring a cursor for the records, and just go thru it one time, then put the value into different variables.

Any suggestion is appreciated

MarkOriginally posted by mchih
Right now I have a few PL/SQL procedures that calculate count(*) and sum(*) values for different reports. So far it's a daily operation, with around 80000 records per day (seems to be increasing by 10000 records every week though)

it's not a complex procedure, just a lot of number crunching for each columns. Right now the basic logic is to use a separate SQL statement for each column(with different conditions, of course), and repeat it until all the reports are filled in.

So far it's taking about 20 secs for going thru every 10000 records. Add the time it takes to load the records in (about 25-30 secs per 80000 records), and it becomes about 3 minutes of operation each day.

I have been thinking about how I can improve the performance of the procedure. So far I am thinking about whether the performance would improve if I can change the logic by declaring a cursor for the records, and just go thru it one time, then put the value into different variables.

Any suggestion is appreciated

Mark
Generally it is preferable to avoid procedural logic if you want best performance. Maybe you could combine all (or many of) your counts and sums into a single query using DECODE (or CASE) to filter the records:

SELECT SUM( DECODE( col1, 'x', 1, 0 )) as COUNT_WHERE_COL1_IS X
, COUNT(*) TOTAL_COUNT,
, SUM( DECODE( col3, 123, col4, 0 )) as SUM_COL4_WHERE_COL3_IS_123
...
FROM ...|||it might be difficult to put many of the query together, since each of them has a different where clause.

eg:

table temp(
card_type
card_amount
message_type
message_response
.
.
.
)

a sample query would be
select count(*), sum(card_amount) from temp
where card_type = x
and message_type = y
and message_response = z;

now that i think about it, it might be possible to use a GROUP BY to get all the values with similar WHERE clause, but I don't know how to store the values individually so i can access them later (eg. put it into different table)

TIA

Mark

Pivot Tables

Greetings, can anyone tell me how to create a pivot table view? (If at all) I want to manipluate data to a view to use in Crystal reports. Current table has producta "A", "B" & "C" with three options "i", "ii" & "iii" each with amounts per product & options:

A i 10
A ii 20
A iii 30
B i 5
B ii 7
B iii 9
C i 2
C ii 4
C iii 6

I want to create a view that lists the product "A" and Options "i" in the rows, with Colums "i", "ii" and "iii" and their corresponding amounts listed in the relevant colums. Thanx!there's probably a better solution out there, but this will get you started:

declare @.tbl table (
product char(1) not null, options char(3) not null, price money not null)
insert @.tbl values ('A', 'i', 10)
insert @.tbl values ('A', 'ii', 20)
insert @.tbl values ('A', 'iii', 30)
insert @.tbl values ('B', 'i', 5)
insert @.tbl values ('B', 'ii', 7)
insert @.tbl values ('B', 'iii', 9)
insert @.tbl values ('C', 'i', 2)
insert @.tbl values ('C', 'ii', 4)
insert @.tbl values ('C', 'iii', 6)

select p.product, [i]=t1.price, [ii]=t2.price, [iii]=t3.price
from (select distinct product from @.tbl) p
inner join @.tbl t1
on p.product = t1.product and t1.options = 'i'
inner join @.tbl t2
on p.product = t2.product and t2.options = 'ii'
inner join @.tbl t3
on p.product = t3.product and t3.options = 'iii'|||It sounds like you are trying to create a CROSSTAB query, and you can look up how to do it using CASE statements by searching Books Online for keyword "crosstab".

However, this will NOT give you a try pivot table report, which is dynamically configurable by the user. I'm not sure that Crystal can even do this. As a matter of fact, for a pivot table you don't want oto have your data in crosstab format. If you truly want pivot functionality and the ability to slice, dice, and summarize your data dynamically, create a pivot table in Excel or in an ASP page that links to the data in your table, (through a view, preferably), and leave your data in its current columnar format.

blindman|||actually crystal was able to do it even when it was part of vb4.0

but i still think that having this static approach is better than allowing users to build pivots dynamically, because if they transpose columns and rows (intentionally or not) the whole thing will croke (or may croke).|||Would this not be equivilent to a cube? It seems that A, B, and C would be one dimension, and i, ii, and iii would be a second dimension, and the values would be the intersection points of the the two dimensions...

| A | B | C
------
i | 10 | 5 | 2
------
ii | 20 | 7 | 4
------
iii | 30 | 9 | 6
------|||this is exactly what i was talking about in the previous post!|||Originally posted by ms_sql_dba
this is exactly what i was talking about in the previous post!

Bah.. sorry.. I started typing, and got side tracked for a little while and didn't get to finish my post until there had been 3 other replies!

SQL supports the creation of cubes if you didn't want to do a bunch of joins.. that might get costly if you had lots of dimensions...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agcubesintro_80qb.asp|||Thanx for the advice! Appreciate your time!