Importing large amounts for selection
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21900
Printed Date: 19 May 2024 at 11:16am
Topic: Importing large amounts for selection
Posted By: TorLang
Subject: Importing large amounts for selection
Date Posted: 04 Mar 2016 at 2:28am
Quick question for you experts.
I need to use a large amount of account numbers as part of the selection for a data pull.
I realize that I can use a parameter, then import the accounts numbers as a txt file. However, I have about 30000 of them. Is there any other way to do this, besides moving over pages after pages of accounts in the multi selection parameter?
|
Replies:
Posted By: DBlank
Date Posted: 04 Mar 2016 at 2:39am
I am unclear what you are asking.
Are you asking how to create the possible values that populate a run-time crystal parameter? Or are you asking how to filter the content of a report using this 'list'?
|
Posted By: TorLang
Date Posted: 04 Mar 2016 at 3:02am
|
What I am asking is there another way getting thousands of account number into the report selection other than having to import a txt file into a parameter, then when running it, having to move pages upon pages of account numbers over to the right side in the paramete, due to the limitation of "multiple value" parameters where Crystal breaks up the values into pages.
|
Posted By: DBlank
Date Posted: 04 Mar 2016 at 3:06am
if you have the them in a table you can just use it as an inner join.
If you know the conditoins that got you the 'list' to begin with you can use those conditions on the where clause.
If you have the ablity to create a stored proc or a view you can import them into that.
You can use the formula editor in the select and just paste them all.
|
Posted By: TorLang
Date Posted: 04 Mar 2016 at 3:12am
Dan,
When you say past the 50000 accounts into the select statement, I dont follow how that can be done easily.
This is my current select:
{TPB200_BILLING_INVOICE.ivo_ext_id} = {?IVO}
IVO being the account # field parameter. How would I formulate this if I just paste the accounts into this selection?
By the way, they are string fields in the database table.
3000011160-0001
3000108940-0001
3000010904-0001
3000011241-0001
3000011257-0001
4000064516-0001
4000110787-0001
4000119659-0001
4000123798-0001
4000127062-0001
4000146968-0001
4000153694-0001
4000154807-0001
4000157969-0001
4000163825-0001
4000172407-0001
4000186686-0001
4000187662-0001
4000187911-0001
4000190336-0001
|
Posted By: DBlank
Date Posted: 04 Mar 2016 at 3:16am
So that was my original question.
Are you trying to populate the ?IVO list so a user can pick from this list to run a few a time? or are you trying to use all records from an '?IVO list' to limit a larger table to these 30,000 records that are part of an ?IVO list.
|
Posted By: TorLang
Date Posted: 04 Mar 2016 at 3:20am
I am using all the records from the ?IVO list to limit a larger table. I am having this issue pretty frequently where accounting people are asking for huge data pulls on selected accounts, and I just haven't found an easier way of getting these high numbers of accounts into the selection w/o using a parameter :-)
|
Posted By: DBlank
Date Posted: 04 Mar 2016 at 3:21am
|
How are they delivering the list to you? Word? Excel?
|
Posted By: TorLang
Date Posted: 04 Mar 2016 at 3:24am
|
Excel, with just a single column populated. I then dump them into a txt so I can import (into parameter selection)
|
Posted By: DBlank
Date Posted: 04 Mar 2016 at 3:36am
You might be able to just use the excel file to include as another source and use it to join to {TPB200_BILLING_INVOICE.ivo_ext_id}.
When they give you a new file do a save as and replace the joined excel file with a copy of the new list.
I have had issues with some text files doing this so test it out. If you can get it working it is the simplest to maintain.
Or
export the data into word
do a find and replace
find: ^l
replace with: ","
this would replace all of the line feeds with a ","
turning
3000011160-0001
3000108940-0001
3000010904-0001
3000011241-0001
3000011257-0001
4000064516-0001
4000110787-0001
4000119659-0001
4000123798-0001
4000127062-0001
4000146968-0001
4000153694-0001
4000154807-0001
4000157969-0001
4000163825-0001
4000172407-0001
4000186686-0001
4000187662-0001
4000187911-0001
4000190336-0001
into
3000011160-0001”,”3000108940-0001”,”3000010904-0001”,”3000011241-0001”,”3000011257-0001”,”4000064516-0001”,”4000110787-0001”,”4000119659-0001”,”4000123798-0001”,”4000127062-0001”,”4000146968-0001”,”4000153694-0001”,”4000154807-0001”,”4000157969-0001”,”4000163825-0001”,”4000172407-0001”,”4000186686-0001”,”4000187662-0001”,”4000187911-0001”,”4000190336-0001”,”
fix the start and end to correct format
[“3000011160-0001”,”3000108940-0001”,”3000010904-0001”,”3000011241-0001”,”3000011257-0001”,”4000064516-0001”,”4000110787-0001”,”4000119659-0001”,”4000123798-0001”,”4000127062-0001”,”4000146968-0001”,”4000153694-0001”,”4000154807-0001”,”4000157969-0001”,”4000163825-0001”,”4000172407-0001”,”4000186686-0001”,”4000187662-0001”,”4000187911-0001”,”4000190336-0001”]
select all
copy
open your select expert
go into the formula
{TPB200_BILLING_INVOICE.ivo_ext_id} IN (paste values)
|
Posted By: DBlank
Date Posted: 04 Mar 2016 at 4:05am
|
if you try the join process make sure to enforce the join
|
Posted By: TorLang
Date Posted: 04 Mar 2016 at 4:17am
The last option is viable, except crystal does not accept the "italic" quotes
”3000108940-0001”, which is what I see when I paste them into the record select screen. Not sure why it does that......
|
Posted By: DBlank
Date Posted: 04 Mar 2016 at 4:24am
|
resolved or do you still need more options?
|
Posted By: TorLang
Date Posted: 04 Mar 2016 at 4:27am
I need to figure out why the quotes turn italic from word pasting into the crystal selection....
|
Posted By: TorLang
Date Posted: 04 Mar 2016 at 4:29am
|
and Crystal does not like italic quotes :-)
|
|