How to Add new column - Mure

[nmmure]

Hi
Earlier Reports was developed based on SQL Command. Using Inner & outer joins. Each report buildup with 7 to 8 tables. Recently in the database Table they added a new column and set as primary key, previous primary key column as set to Non key column. How to change the column in my existing reports.
i was changed SQL Query Instead of old primary key column to new primary key column and deleted old command & Created new command with the modified SQL Query. But in the Report all the running total ( Group Total) formula's are showing me an error. How to change the old primary key to new primary key without any effects on reports. My Reports are very very Complex Reports. Please Excuse me for BAD GRAMMER.
Iam using SQL Server2000 & Crystal Reports XI.

Please help me any one

Thanks in advance.
Mure

 

[synapsevampire]

I think you should have just edited the existing Command object.

Saying that a key changed is helpful, but you should post WHAT changed...

The name? Different data type? Or is it a different column entirely, or?

-k

 

[nmmure]

They Added entire new Column (Ex: Assignment_Seq_ID) and the old column (Ex: Assignment_ID Primary key in the table previously ) is also in the table. Now they changed the primary key to Assignment_Seq_ID and they removed primary key on Assignment_ID.

In the reports how can i change the column Assignment_ID to Assignment_Seq_ID . i tried to edit the Command but the report is showing me the errors in group Totals.

Pls help me

Thanks in advance.

Mure

 

[Turkbear]

Hi,
What kind of errors?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Why would you not post the error message?

Using a Command Object is generally my second choice, if you create Views as the source for your reports, and something changes in the underlyign structure, you can often just tweak the View and all reports will function again.

-k

 

[nmmure]

Errors:

1) Failed to retrieve the data from database
2)Database Connector Error:'45s22:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'assignment_seq_id'.[Database Vendor Code;207]'

Thanks in advance

Mure

 

[nmmure]

ya,Iam agree with you.
Using Views or Stored Procedures is the best for reports either report is complex or easier. But these reports are developed some other developers. recently i joined and looking for these things. Please help me.

Thanks in Advance.

Mure

 

[IbeKaba]

This might be an oversimplification...but did you try going into each running and/or group total and change the field referenced to? That's what I would do
ibe

 

[Turkbear]

Hi,
That error means, for some reason. the command cannot find a column named assignment_seq_id

Can you access that database and table directly and check the column name?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Yep, it's saying that column doesn't exist, this has nothing to do with a Running total, it can't return the data.

-k

 

[nmmure]

Hi
I am using SQL Server 2000 & Crystal reports Xi, Reports was developed using SQL Command. All are very complex reports & all are working fine.
Recently Changed Database, In that they modified one table (assignments), in that (assignments) Table they added one new column (Assignment_Seq_Id) set as primary key, they changed previous primary key column (Assignment_ID) to Non key column ( Removed primary key). Now both the 2 columns are in the table.
How to change this one in reports, I was tried to edit the SQL Command & tried to add to the Report. That is not accepting, they showing me the error like “in your database column (assignment_seq_id) is not existed”. Changed DSN & this time SQL Command is accepted. & showing error running total formula error. In my reports all are formula fields. I don’t know how to do this one. In my report detail section all most all are running total fields and very complex design one. Very difficult to redisn this again. How to ADD the column without any disturbing existing layout.
Pls help me.
Here iam pasting the Query which iam using,
SELECT
facility.facility_name,employee.last_name+', '+employee.first_name AS Employ,employee.wms_employee_id,activity.activity_name,sum(assignment_tasks.total_indirects/3600000.00)as total_indirect,sum(assignment_tasks.total_delays/3600000.00)as total_delay,
sum((assignment_tasks.total_paid_breaks+assignment_tasks.total_unpaid_breaks)/3600000.00)as total_break,sum(assignment_tasks.total_paid_breaks/3600000.00) as total_paid_breaks,sum(assignment_tasks.standard_time/3600000.00)as standard_time,sum(assignment_tasks.total_standard_actuals/3600000.00)as total_std_actual,sum(assignment_tasks.adjusted_standard_time/3600000.00)as adjusted_std_time,sum(assignment_tasks.no_of_units)as units,sum(assignment_tasks.no_of_cases)as cases,sum(assignment_tasks.no_of_pallets)as pallets,count(*)as lines
FROM
assignment_tasks INNER JOIN activity ON assignment_tasks.activity_id = activity.activity_id INNER JOIN assignments ON assignment_tasks.assignment_id = assignments.assignment_id INNER JOIN work_schedule ON assignments.work_schedule_id = work_schedule.work_schedule_id INNER JOIN schedule ON work_schedule.schedule_name = schedule.schedule_name INNER JOIN employee ON assignment_tasks.employee_seq_id = employee.employee_seq_id LEFT OUTER JOIN zone ON assignment_tasks.zone_id = zone.zone_id LEFT OUTER JOIN microzone ON ssignment_tasks.microzone_id = microzone.microzone_id INNER JOIN facility ON employee.facility_id = facility.facility_id LEFT OUTER JOIN activity_group ON activity.activity_group_name = activity_group.activity_group_name LEFT OUTER JOIN tasking_group ON activity_group.tasking_group_name = tasking_group.tasking_group_name
WHERE
work_schedule.schedule_date between {?fromdate} and {?todate} AND assignments.split_assignment_flag='N' AND assignments.master_assignment_id is null AND assignments.status in ( 'COM', 'CLS') AND assignment_tasks.wms_performed_ontimeclock is null {?where} GROUP BY facility.facility_name, employee.last_name,employee.first_name, employee.wms_employee_id, activity.activity_name
{FromDate, Todate & where are the parameter fields}
Thank you very much

 

[synapsevampire]

If you edited the SQL Command you wouldn't then Add it, editing is done in place to the existing Command, that's what editing means, it doesn't mean creating a new one and adding it.

Note that this SQL doesn't reference the Assignment_Seq_Id anywhere, so how could it possibly be adding in this column?

You need to check your SQL or post the SQL that you need to use, not the old one.

In general using the Add Command is a bad idea, and you've discovered why, if something chanhges in the database, you get to change EVERY report.

Consider using Views on the database, then you can modify the View and the report would be fine.

-k

 

[nmmure]

Hi, Iam agree with you, when ever iam going to develope reports either simple Or complex reports iam going to use Views are Stored procedure. i never used the SQL command.

These reports are developed some other person. In the database some tables ( 'Assignmets' & 'assignments_tasks' )they added anothere column assingnmet_seq_id. now i need to edit the existing query
instead of

INNER JOIN assignments ON assignment_tasks.assignment_id = assignments.assignment_id
to

INNER JOIN assignments ON assignment_tasks.assignment-seq_id = assignments.assignment_seq_id

that time it is accepted. How to do this one

Thank you very much.

 

[synapsevampire]

Open the database explorer and right click the command object and select edit and change the sql.

-k

 

[nmmure]

Ya, I did that one. that time in my reports runing total fields are showing me the error. i don't why i will get the those errors. the following formula they are used
if({#gt_stdactuals})<>0 then
({#gt_stdtime} / {#gt_stdactuals}) * 100
else 0

Thanks for you kind help, thankQ very much

Mure

 

[Turkbear]

Hi,
What error do the running totals produce?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CR85user]

quote: if({#gt_stdactuals})<>0 then
({#gt_stdtime} / {#gt_stdactuals}) * 100
else 0

As lbass stated earlier - a little digging is required.

If the example running total isn't working then backtrack it to it's component parts.

Show what {#gt_stdactuals} and {#gt_stdtime} are totalling, which field they are referencing - Are they referencing the original primary key or the new? etc etc...

You will find it if you look hard enough through the data.

Even seemingly complex reports are often quite simple when you get down to it.

'J

 

[nmmure]

Thank you very much. i found the solution. Now iam working on that.