if cross tab report goes beyond one page?

Hello friends,

I designed a report that shows sales of products datewise -

product 1/12 2/12 3/12 4/12 ...
------------------
abc 100 93 94 109 ...
xyz 7 100 56 98 ...
...
...

if i select 2 or 3 months then it shows 90 columns (per day one column) and i can view the report on screen in correct format. But when i send print, it prints product names and some 18 columns only on first page and on each consecutive pages it shows remaining date columns only (with data) and that is also in cut format (half column on previous page and half column on next page)

In a nut shell, is there any setting so that cross tab will work like EXCEL sheet of fixed row / fixed column facility so that on each new page it should print the fixed column.

Since title row is printing on each new page.

Pls advice.

Thanks in adv.
KSee if you find solution here
http://support.businessobjects.com/|||Hi kirankul,

If u found the solution please update this...

Thanks
Sanju|||Hi All,

I encounter the same problem and found the solution, I am not sure whetehr this solution is really suitable for your case, but I think it will.

You go to Cross-Tab expert by right click the cross tab that you have created then go to Customised style tab --> go to the grid option --> check the keep columns together and check the repeat row labels option

By choosing these options, the cross tab that you printed will have the row header name and column header names at every pages.

Cheers
CS

Identity Ranges

I'm using Merge replication on a database that was designed using integer identity columns for primary keys. When I create a publisher it's great because Sql Server will create rowguid columns for me on most of the tables; actually all but one table.

The problem comes when I try and use identity ranges for the subscribers. As a test I set up a range on one table, it only allowed for 10 in the range with an 80% threshold. I wanted to see what would happen when say my publisher db inserts 11 new rows. Well, I found out that after the 8th new row it wouldn't insert anymore as the range was exceeded and it gave me an error message saying
The identity range managed by replication is full and must be updated by a replication agent.

The question is; which agent needs to run in order for the new range to be assigned to the publisher? I have seen some people talk about an sp_ that can be run, but in a production environment I wont this to be automatic.

My altenative to ranged identies is using guid uniqueidentifiers. See my other post on this!!

regards

GrahamI assume this is SQL Server 2000.

To answer your questions:
Maybe the table on which merge is not adding the rowguid col is because the table already has a rowguid column?

And since you have 80% threashold, it is failing after the 8th row and I assume you have pub_idrange and range values each set to 10.
You can get by this situation by increasing the numbers, so that the probablity of them running out of numbers is less. Say like 10,000 or 100,000.

And the agent to be run when the id range is full is the merge agent. Running the merge agent will refresh the ranges on publisher and subscriber.
On the publisher, you can also run the sp_adjustpublisheridentityrange to refresh the range and that way you dont need to run the merge agent.

But typically in a production scenario, it is recommended that:
1. You have a decent sized value for the publisher and subscriber id range values
2. Merge agent to run frequently. That way the ranges will be refreshed (if needed) when merge agent runs.

IDENTITY Column produces gaps when insert fails

Hi

When an insert statement for a table having an identity column fails, there is a gap in the identity chain. Is this working 'as designed', or is there anything wrong with my configuration? I'm Using SQL 2005 Standard SP1.

Here's the script:

USE tempdb;
GO

-- Create Test Table
CREATE TABLE id_gaps
( id INT IDENTITY(1001,1)
, txt VARCHAR(10) NOT NULL UNIQUE
);
GO

-- Insert some useful data
INSERT INTO id_gaps (txt) VALUES ('Test 01');
INSERT INTO id_gaps (txt) VALUES ('Test 02');
INSERT INTO id_gaps (txt) VALUES ('Test 03');
INSERT INTO id_gaps (txt) VALUES ('Test 04');
GO

-- Insert a duplicate value unsing an explicit Transaction
-- It doesn't work using an implicit Transaction too.
BEGIN TRAN tr1
BEGIN TRY
INSERT INTO id_gaps (txt) VALUES ('Test 04');
COMMIT TRAN tr1;
END TRY
BEGIN CATCH
ROLLBACK TRAN tr1;
END CATCH
GO

-- Insert an additional row
INSERT INTO id_gaps (txt) VALUES ('Test 05');
GO

-- See the content of the table. There is a gap caused of the refused insert statement.
SELECT * FROM id_gaps;

-- Clean up
DROP TABLE id_gaps;

Thank you for any hints.

The following url may help you..

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

But it is not advisable.... Here i pasted the updated query (not recommanded)

USE tempdb;
GO

-- Create Test Table
CREATE TABLE id_gaps
( id INT IDENTITY(1001,1)
, txt VARCHAR(10) NOT NULL UNIQUE
);
GO

-- Insert some useful data
INSERT INTO id_gaps (txt) VALUES ('Test 01');
INSERT INTO id_gaps (txt) VALUES ('Test 02');
INSERT INTO id_gaps (txt) VALUES ('Test 03');
INSERT INTO id_gaps (txt) VALUES ('Test 04');
GO

-- Insert a duplicate value unsing an explicit Transaction
-- It doesn't work using an implicit Transaction too.
BEGIN TRAN tr1
BEGIN TRY
INSERT INTO id_gaps (txt) VALUES ('Test 04');
COMMIT TRAN tr1;
END TRY
BEGIN CATCH
ROLLBACK TRAN tr1;
END CATCH
GO

IF IDENT_CURRENT('id_gaps') = (Select max(id) from id_gaps)
Begin
SET IDENTITY_INSERT mytable OFF;
Insert INto id_gaps values('Test 05');
End
else
Begin
SET IDENTITY_INSERT id_gaps ON;
Insert INto id_gaps(id,txt) select max(id) + IDENT_INCR('id_gaps'),'Test 05' From id_gaps;
SET IDENTITY_INSERT id_gaps OFF;
End

-- Insert an additional row
INSERT INTO id_gaps (txt) VALUES ('Test 06');
GO

-- See the content of the table. There is a gap caused of the refused insert statement.
SELECT * FROM id_gaps;

-- Clean up
DROP TABLE id_gaps;

|||

It is not possible to maintain complete sequence control when using IDENTITY values. As you have discovered, there are many reasons an INSERT may fail.

If you must absolutely control a numerical sequence, then you should have a 'numbers' table, lock it, get the next number, increment, and then unlock -but only after you are certain that your insert will succeed.

|||

Thank you for these tips

It's not really necessary to have a continuous nubering, i'm just wondering if there's a mistake in my configuration. But i see it's the normal behavior the identity column works.