Inserting report details to a table
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20395
Printed Date: 03 May 2024 at 11:48pm
Topic: Inserting report details to a table
Posted By: Central
Subject: Inserting report details to a table
Date Posted: 27 Jan 2014 at 1:43am
|
I am looking to insert the report details to a table in SQL Server? Mainly want to capture what page the record is on the report plus a couple of other things. Can this be done and if so how?
|
Replies:
Posted By: lockwelle
Date Posted: 27 Jan 2014 at 4:47am
|
not really. CR by it's nature does not write to the database, it just reads. If you would like to write to the database, you could use a stored procedure, but as far as what page a user if viewing, I don't know of a way to track that, not in the database.
|
Posted By: Central
Date Posted: 27 Jan 2014 at 4:53am
|
Thanks for getting back. Is that a stored procedure within Crystal Reports and if so, how is this created or is it a view within the database. I am trying to get a record that has the page number on each detail within the report.
|
Posted By: lockwelle
Date Posted: 27 Jan 2014 at 10:45am
you could write a stored procedure and have Crystal use it to retrieve data from the database. While it is executing, it could update/insert into another table.
As for the page number, I'll be honest, I don't know about that, as depending on settings in report, CR will paginate in such a way that I am not sure that we can assign a value to what page a detail will be printed on.
As a caveat to the above, if more than 1 person is running the report at the same time, whose values are there could be dicey...it's all a matter of how you save the data and how you use it.
Again, knowing what page something is on, may be hit or
miss.
|
Posted By: kostya1122
Date Posted: 27 Jan 2014 at 12:08pm
for this to make sense you have to have specific order by
you could create a counter table in sql like
select primery_key_field,
ROW_NUMBER ( )
OVER ( order by [somefield] )/46 + 1 as page_no
into counter_table
from table
assuming you have 46 records per page
|
Posted By: Central
Date Posted: 28 Jan 2014 at 12:11am
|
Thanks for getting back. The reason I want the page number is so I can do a table of content on a different report. If I have the page number on what page the record is on then I can have a from and to page for a record. This I can do in the background. If I can set up a table and able to insert the page number as I run the report I can hopefully acheive this. I hope this makes sense as at the moment I think I am getting too confused and maybe there is a simply way of acheiving this.
|
Posted By: Central
Date Posted: 28 Jan 2014 at 12:12am
|
Thanks for getting back. Please see note above.
|
Posted By: Central
Date Posted: 28 Jan 2014 at 3:21am
|
I have read that some folk have created a temp table, inserts data into the temp table then selects the data from the temp table via Crystal reports. If this can be done then it could solve my problem. Next question....how is this done if it can be done?
|
Posted By: lockwelle
Date Posted: 28 Jan 2014 at 4:48am
|
subreport...it can have a different data source than the main report...though the temp table would be either a global temp table, or an actual table that is used just for this report.
|
Posted By: Central
Date Posted: 28 Jan 2014 at 4:57am
|
This may sound rather daft of me but can I create a temp table, input the details from the main report along with the page number. If so, how is this done. Sorry about this but I am a novice when it comes to Crystal Reports.
|
Posted By: kostya1122
Date Posted: 28 Jan 2014 at 6:48am
to create a table you need sequel server management studio
with appropriate rights.
you could also try to create a counter field in crystal
something like
numberVar ct;
Whileprintingrecords;
ct := ct + 1;
maybe use that in a formula
for example if you have constant number of records per page you could divide counter by that number and that should be your page number.
|
Posted By: lockwelle
Date Posted: 28 Jan 2014 at 7:03am
yes, kostya is correct in that...the caveat is that Crystal by itself will not write out to the database. It will only query the database once per report, which is why if you use a stored procedure you could write to the database.
Again, what Kostya is saying about a constant number of records per page will allow you to 'guess'/'predict' what the page number will be. If you have fields that 'Can Grow' the results can be unpredictable. CR is a pain that way.
Again, the hard part is guessing what page a record is on, and then writing that to a table, just because the actual result will basically be unknown until it's too late (ie after CR has formatted the report).
Hopefully, your report will be 'well behaved' and you will be able to find an algorithm that closely matches the actual page printing. All sorts of things can affect this, 'Keep Together', 'New page before', 'new page after' different fonts/bold/italic. These are just the items off the top of my head that can help/confuse the algorithm.
How could they help? Let's say that you have a new page before each group, and that the details always stay on the same page, then it is pretty easy to predict what page each group will be on.
On the other hand, you several nested groups with new page before and can grow fields and keep together and unknown number of details, it would be a very complex task to determine what page anything is on.
Not meaning to scare you, just that this could be a very difficult task.
|
Posted By: Central
Date Posted: 28 Jan 2014 at 10:13pm
|
Thanks for all your help. I will take everything on board and give it a go. Thanks again
|
|