How do you select just one data item from a field?

[Billwork]

Below is a sample table (ProjectionRuns) I have in Access. I want to display some summary information from this table in Crystal reports (but only one element at a time).

ProjectionName Type Date ID

Name1 Projection 31/03/2004 1
Name2 Budget 09/04/2004 2
Name3 Projection 14/04/2004 3
Name4 Budget 10/03/2003 4
Name5 Projection 09/04/2002 5

Three data sets (and therefore three IDs) are used at any one time. I want to be able to state which three IDs I am refering to in the footer of the report.

Ideally, it would like something like:

Cash flow 1: Name1, Projection, ID: 1
Cash flow 2: Name2, Budget, ID: 2
Cash flow 3: Name5, Projection, ID: 5

However, as all of the ProjectionRuns information is stored as a ProjectionRuns.ProjectionName, or ProjectionRuns.Type etc. I will not be able to achieve it by using the following method:

Cash flow 1: <ProjectionRuns.ProjectionName> ...
Cash flow 2: <ProjectionRuns.ProjectionName> ...
Cash flow 3: <ProjectionRuns.ProjectionName> ...

As obviously the same information will be displayed each time. Can anyone help?

I basically want to know how to display specific data points from a table, where a common field name is used.

Thanks!

 

[synapsevampire]

How are you "referring" to those 3 IDs, using a parameter, or?

The sample data shows 5 IDs, the output shows 3.

Note that if people haven't responded to your post quickly, it's likely because it wasn't clear. Please post technical information, and state the requirements using technical terms whenever possible.

Crystal version
Database/connectivity used
Example data
Parameters required
Expected output

If you want to specify 3 IDs to use, and then display those rows, try:

Insert->Field Object->Right click Parameters and select New-> Name it->Check Allow Multiple Values->and select the same data type as the ID field (perhaps a number).

Select Report->Edit Selection Formula->Record and place something like:

{table.field} = {?MyParameter}

Now the report will prompt for the IDs

Since the details are now limited to those 3 rows, you don't need to place them in the report footer, but to demonstrate one means for concatenating data:

Insert->Text Object

Type in the beginning text, and drop fields in wherever you''d like as you go along. You can mix manually entered text and database fields in a text object.

Or you can use a formula, which may require the use of totext() for non string type data.

-k

 

[Billwork]

I thought it might have been unclear, it's not the easiest problem to describe! I'm using CR 10. The data is coming from Microsoft Access.

I am using user inputed parameters to pull in data from a database (These are called ActualID, ProjectionID and BudgetID). The parameters inputed refer to the IDs stored in a field called ProjectionRuns.ProjectionID, shown above.

I want to let the reader of the report know which IDs (and therefore which data) I am looking at. Now, if only one set of data was being reported on (hence one ID) I could easily display this information by dropping the <ProjectionRuns.ProjectionID> field into the report.

However, I want to specify each of the IDs and their associated names <ProjectionRuns.ProjectionName> to the reader.

So, if I selected IDs 1,2 and 5 using the ActualID, ProjectionID and BudgetID parameters, I would want to display somewhere in the report the following text:

Name: Name1 of Type Projection (ID: 1)
Name: Name2 of Type Budget (ID: 2)
Name: Name5 of Type Projection (ID: 5)

Now, the names, types and IDs are stored in the Access table as <ProjectionRuns.ProjectionName>, <ProjectionRuns.Type> and <ProjectionRuns.ProjectionID>. The only way I know of achieving the text output above is by dropping the fields in a text object to give the following

Name: {@ProjectionRuns.ProjectionName} of Type {@ProjectionRuns.Type} (ID: {?ActualID})
Name: {@ProjectionRuns.ProjectionName} of Type {@ProjectionRuns.Type} (ID: {?ProjectionID})
Name: (@ProjectionRuns.ProjectionName) of Type {@ProjectionRuns.Type} (ID: {?BudgetID})

This displays the inputed IDs great, but all of the other fields (.Name and .Type) display the same information, not the information corresponding to the relevant ID. I am really looking for a way of isolating the .type field for a particular ID on one line, then again on another line, but for a different ID.

I hope this is more clear. I'm kinda new to all this.

 

[synapsevampire]

OK, I think what you're trying to say is that you want to display the values of the parameters entered along with the their corresponding values from the database.

This is precisely what would be in the details anyway I think, with the exception of the parameter, but here's how to do it after it is already displayed in the details:

To display parameter values for a multiple entry, you might use (I'll assume that they are numbers, the lack of technical information provided will likely result in more discovery):

Place this in the details to collect the data, and replace the text "Hello there" with what you want, and you can also concatenate in your fields:

whileprintingrecords;
Stringvar Array MyArray;
Redim Preserve MyArray[ubound({?Param})];
numbervar X;
For X := 1 to ubound({?Param}) do(
MyArray[X] := "Hello there "+ totext({?Param}[X],0,"")+{table.field}
);

To later display it, use:
whileprintingrecords;
Stringvar Array MyArray;
join(MyArray,chr(13))

Make sure that you turn on the Can Grow option when displaying it.

Again, showing example data and expected output always clarifies things.

-k

 

[Billwork]

Thanks for that. I understand what you have done, but the same problem still remains. After implementing your code (with some minor additions)

WhileReadingRecords;

numbervar Array UserInputs;
UserInputs := [{?ActualsID}, {?ProjectionID}, {?BudgetID}];

Stringvar Array MyArray;
Redim Preserve MyArray[ubound(UserInputs)];
numbervar X;
For X := 1 to ubound(UserInputs) do(
MyArray[X] := "Projection Name: "+ totext(UserInputs[X],0,"")+ " " + {ProjectionRuns.ProjectionName}
);

...(and inputing parameter values of 1 {?ActualsID}, 4 {?ProjectionID} and 6 {?BudgetID}) the display I get is:

Projection Name: 1 Current Business Plan
Projection Name: 4 Current Business Plan
Projection Name: 6 Current Business Plan

And this highlights my problem quite nicely. The entry "Current Business Plan" appears on each line. Ideally, I am looking for this display:

Projection Name: 1 Initial projection run
Projection Name: 4 Updated projections
Projection Name: 6 Current Business Plan

Presumably, the reason "Current Business Plan" appears on each line is because the last ProjectionID to be referenced when the data was grabbed was that entered for {?BudgetID}. I need to find some way of indexing {ProjectionRuns.ProjectionName} with the associated ProjectionID.

Below is a sample of the table (ProjectionRuns) in Microsoft Access. The field names appear in the top row.

ProjectionName Date Type ProjectionID

Initial projection run 31-Mar-04 Projection 1
Test budget 09-Apr-04 Budget 2
Test Actuals 09-Apr-04 Actuals 3
Updated projections 11-Apr-04 Projection 4
Projection 14-Apr-04 Projection 5
Current Business Plan 21-Oct-03 Budget 6

Thank you for your time so far!

 

[lbass]

I think you could hard code this into the formula if you have a limited set of projection names and if they always have the same ID number, as in:

whileprintingrecords;
stringvar parmdesc1 := "";
stringvar parmdesc2 := "";
stringvar parmdesc3 := "";
numbervar counter := 0;
numbervar i := ubound({?ActualsID});
numbervar j := ubound({?ProjectionID});
numbervar k := ubound({?BudgetID});

for counter := 1 to i do(
parmdesc1 := parmdesc1 + "Projection Name: " + {?ActualsID}[counter] + " "+(if {?ActualsID}[counter] = 3 then "Test Actuals" else "") + chr(13));
for counter := 1 to j do(
parmdesc2 := parmdesc2 + "Projection Name: " +{?ProjectionID}[counter] + " "+ (if {?ProjectionID}[counter] = 1 then "Initial Projection Run" else
if {?ProjectionID}[counter] = 4 then "Updated Projections" else
if {?ProjectionID}[counter] = 5 then "Projection" else "") + chr(13));
for counter := 1 to k do(
parmdesc3 := parmdesc3 + "Projection Name: "+ {?BudgetID}[counter]+ " " + (if {?BudgetID}[counter] = 2 then "Test Budget" else
if {?BudgetID}[counter] = 6 then "Current Business Plan" else "") + chr(13));
parmdesc1 + parmdesc2 + parmdesc3;

-LB

 

[Billwork]

Thanks lbass for your post, and apologies for my delayed response. I was able to implement your code without problems, but unfortunately we are constantly changing and updating the projection names and so a hard coded solution probably won't be suitable going forward.

I was able to reach a satisfactory solution (although not ideal) by inserting a subreport and linking the parameter values to this subreport. This allowed only the data we were interested in to be easily summarised.

A colleague of mine who is a more experienced database user offered this explanation of our problem. It is a bit late now, I appreciate, but it may shed some more light on the problem. Our main need is satisfied for now, but it would be useful to access specific pieces of data in future reports I am sure. This is the alternative explanation:

----

We seem to have a fundamental problem understanding how to get data from multiple tables from crystal reports if the tables are not linked. While I understand that the main body of the report comes from a single SQL SELECT statement that can have linked tables,

SELECT `ProjectionDetail`.`ProjectionID`, `ProjectionDetail`.`PDATE`, `ProjectionDetail`.`PAMOUNT`, `ProjectionRuns`.`ProjectionID`, `ProjectionRuns`.`ActualsDate`, `ProjectionDetail`.`CATEGORY1`, `Rates`.`GBP/USD`, `Rates`.`GBP/EUR`, `ProjectionDetail`.`PCURRENCY`, `Rates`.`RateRunID`, `ProjectionDetail`.`CATEGORY4`, `ProjectionDetail`.`CATEGORY3`, `ProjectionDetail`.`CATEGORY2`, `ProjectionDetail`.`COMPANY`, `ProjectionDetail`.`RevorExp`

 FROM   (`ProjectionDetail` `ProjectionDetail` INNER JOIN `ProjectionRuns` `ProjectionRuns` ON `ProjectionDetail`.`ProjectionID`=`ProjectionRuns`.`ProjectionID`) INNER JOIN `Rates` `Rates` ON `ProjectionDetail`.`PDATE`=`Rates`.`PDATE`

 WHERE  `Rates`.`RateRunID`=2 AND (`ProjectionDetail`.`ProjectionID`=32 OR `ProjectionDetail`.`ProjectionID`=32) AND (`ProjectionDetail`.`COMPANY`='Holdings' OR `ProjectionDetail`.`COMPANY`='Holdings' OR `ProjectionDetail`.`COMPANY`='Holdings')

I can't figure out how to access information from other tables that aren't used in this select statement. I would ideally like to be able to simply get a piece of data (and actually put it in a formula that I can use elsewhere) by using a separate SELECT statement with a WHERE clause (for instance, SELECT ProjectionRuns.Description WHERE ProjectionID = {?ActualID}) and putting the result into a formula. I recognize that a SELECT clause by its nature typically returns multiple values, so maybe this wouldn’t work anyway, but hopefully it’s clear what I’m trying to do here.

----

If this sheds any new light on the problem, it would be interesting to hear anyone's thoughts, but please don't worry if not! As I said, we have got a 'work around' solution which is fine for our requirements at the moment.

Thank you again for all your help!

Billwork