Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Importing large amounts for selection Post Reply Post New Topic
Page  of 2 Next >>
Author Message
TorLang
Groupie
Groupie


Joined: 13 Jun 2012
Location: United States
Online Status: Offline
Posts: 50
Quote TorLang Replybullet Topic: Importing large amounts for selection
    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?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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'?
IP IP Logged
TorLang
Groupie
Groupie


Joined: 13 Jun 2012
Location: United States
Online Status: Offline
Posts: 50
Quote TorLang Replybullet 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.

Edited by TorLang - 04 Mar 2016 at 3:03am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
TorLang
Groupie
Groupie


Joined: 13 Jun 2012
Location: United States
Online Status: Offline
Posts: 50
Quote TorLang Replybullet 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


Edited by TorLang - 04 Mar 2016 at 3:14am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
TorLang
Groupie
Groupie


Joined: 13 Jun 2012
Location: United States
Online Status: Offline
Posts: 50
Quote TorLang Replybullet 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 :-)


Edited by TorLang - 04 Mar 2016 at 3:20am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 04 Mar 2016 at 3:21am
How are they delivering the list to you? Word? Excel?
IP IP Logged
TorLang
Groupie
Groupie


Joined: 13 Jun 2012
Location: United States
Online Status: Offline
Posts: 50
Quote TorLang Replybullet 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)
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.