Consolidate multiple data into one row Crystal 11

[GayleC]

I’m working in Crystal 11. Below is a sample of my data that is grouped by VisitID

I need the multiple RCodes to be reported in total in the RCodeSumm column. I used the following formula:

stringvar ReasonCode;
if RecordNumber=1 then
ReasonCode:= {TransactionDistributions.AdjustmentReasonCode1}
else
(if {PatientVisit.PatientVisitId}=previous ({PatientVisit.PatientVisitId}) then
ReasonCode := ReasonCode+","+{TransactionDistributions.AdjustmentReasonCode1}
else
ReasonCode:={TransactionDistributions.AdjustmentReasonCode1});
ReasonCode

and achieved the following results:

I need the last line of RCodeSum to populate every row in column as shown below:

I've played with this most of the day and can't figure it out. Any help would be appreciated

 

[kray4660]

So you want Crystal Reports to read ahead in the data and concatenate the data. The only way I know to do this, is with a sub-report. I hate put sub-reports at the detail level (performance killer). But sometimes it has to be done. Basically the sub-report would find all the RCodes for a visitID (that would probably your link to the sub-report). It would query the data and return the value. A slightly better way would to put the sub-report in a group header (VisitID?), then use a shared variable to return the value.

 

[pmax9999]

As kray4660 suggests, a sub-report is the only way you will achieve this in Crystal Reports.

The sub-report should be put in the Group Header with a Shared Variable used to pass the value back to the main report. Don't be tempted to put it in the Details section unless the number of records returned will always be relatively few. By placing it in the Details section, the sub-report will be doing the same query and return the same results multiple times. in your sample data above, VisitID 498451 would require the sub-report to be executed 3 times (3 hits of the database, the results produced 3 times, and the same concatenation performed 3 times). If placed in the Group Header, it would be executed only once, with the results repeated 3 times.

Hope this helps.

Cheers
Pete

 

[GayleC]

Thank you. I haven't worked with Subreports before so can I get a little clarification on the following statement by Pete? "The sub-report should be put in the Group Header with a Shared Variable used to pass the value back to the main report." Are you saying that once I have my subreport written, I need to place the results in the Group Header of my main report or in the group header of my subreport. The reason I ask is two-fold. First, to get my results in one row to pass over to the main report, I have to place the formula in the Group Footer of the subreport otherwise my results are only the first line. Second, in the main report I have a series of formulas, that only if certain conditions exist, the data from the subreport needs to be test. The formula that is driven off the results of the data in the subreport:

ie if {@RCode} in ["P9", "P7", "P3", "P20", "P2", "P18", "P17", "P16", "P14", "B9", "B8", "B7", "B23", "B22", "B20", "B16", "B15", "B14", "B13", "B12", "B11", "B10", "B1", "A8", "a6", "a5", "a1", "278", "276", "274", "273", "272", "270", "269", "268", "261", "258", "256", "254", "252", "251", "250", "249", "243", "242", "240", "239", "238", "236", "234", "233", "232", "231", "229", "228", "227", "226", "225", "224", "223", "222", "219", "215", "213", "212", "211", "210", "209", "208", "207", "206", "205", "204", "203", "202", "200", "199", "198", "197", "194", "189", "188", "185", "184", "182", "181", "180", "179", "178", "177", "176", "173", "175", "174", "171", "170", "169", "168", "167", "166", "165", "164", "163", "160", "159", "158", "157", "155", "154", "153", "151", "150", "149", "148", "147", "146", "142", "140", "139", "135", "133", "132", "130", "129", "128", "119", "117", "116", "115", "114", "111", "110", "109", "107", "106", "101", "97", "96", "95", "91", "90", "78", "76", "75", "74", "69", "66", "61", "60", "58", "56", "55", "54", "53", "51", "50", "49", "44", "40", "39", "35", "34", "33", "32", "31", "29", "27", "26", "24", "22", "21", "20", "19", "18", "16", "15", "14", "13", "12", "11", "10", "9", "8", "7", "6", "5", "4"] then
"Dirty" else
"Clean"
It is the results of this series of formuals in the Main Report that I actually need. So if the events occurs that triggers the need for the data in the subreport is need, I need the result to equal either “Clean” or “Dirty”
I can have Crystal run this test within the subreport, but I only need the result to be returned into the main report, if all the other conditions in the main report equal False. It doesn't seem that when I link the subreport to the main report that the data actually comes over, just a "picture" of the data is coming over.

 

[GayleC]

I figured out my issue above, I had not set up a shared variable. But now I'm having issues with the formula I mentioned above:

ie if {@RCode} in ["P9", "P7", "P3", "P20", "P2", "P18", "P17", "P16", "P14", "B9", "B8", "B7", "B23", "B22", "B20", "B16", "B15", "B14", "B13", "B12", "B11", "B10", "B1", "A8", "a6", "a5", "a1", "278", "276", "274", "273", "272", "270", "269", "268", "261", "258", "256", "254", "252", "251", "250", "249", "243", "242", "240", "239", "238", "236", "234", "233", "232", "231", "229", "228", "227", "226", "225", "224", "223", "222", "219", "215", "213", "212", "211", "210", "209", "208", "207", "206", "205", "204", "203", "202", "200", "199", "198", "197", "194", "189", "188", "185", "184", "182", "181", "180", "179", "178", "177", "176", "173", "175", "174", "171", "170", "169", "168", "167", "166", "165", "164", "163", "160", "159", "158", "157", "155", "154", "153", "151", "150", "149", "148", "147", "146", "142", "140", "139", "135", "133", "132", "130", "129", "128", "119", "117", "116", "115", "114", "111", "110", "109", "107", "106", "101", "97", "96", "95", "91", "90", "78", "76", "75", "74", "69", "66", "61", "60", "58", "56", "55", "54", "53", "51", "50", "49", "44", "40", "39", "35", "34", "33", "32", "31", "29", "27", "26", "24", "22", "21", "20", "19", "18", "16", "15", "14", "13", "12", "11", "10", "9", "8", "7", "6", "5", "4"] then
"Dirty" else
"Clean"

I have several examples where instead of returning "Dirty" the formula is returning "Clean:

PatientVisitId: 497923 B20,45 Result should be Dirty, but it is returning Clean
PatientVisitID: 498451 236, B15, 6 Result should be Dirty but it is returning Clean

In the first example 45 is not in my list for "Dirty" but I used "in" to indicate if one of the items is in the Dirty list it should return "Dirty", in the 2nd example, all of the codes are in the "Dirty" list.

 

[pmax9999]

The sub-report would need to be placed in the GH of the main report, as the results are only available in the subsequent sections.

Essentially the sub-report would be a replica of the main report, but limited to data for a single VisitID.

The starting point is therefor to start with a copy of the main report, add a VisitID parameter, and a line in the record selection formula along the lines of:

and {Table.VisitID} = {?Parameter}

In broad terms you should be aiming for that report to return a single string representing the RCodeSum result, assigned to a Shared StringVar. Once that report is returning the correct result, add it as a sub-report in the GH of the original report, and create a formula that returns the value of the Shared StringVar.

Regards
Pete

 

[GayleC]

Thanks Pete, my subreport is a copy of my main report, I set up the VisitID parameter and added it to the record selection formula to pull include the parameter. Placed the shared string variable Group Footer of my subreport and suppressed all other sections of the report. When I added it to my main report it was forcing me to give it a VisitID to fulfill the needs of the subreport instead of taking that information from the parameters of the main report. The main report pulls all visitid's within a specified date range. I don't believe I need to add the VisitID parameter in the subreport since the two reports are linked already based on the VisitID. It seems to be working fine without that parameter in the subreport.

Also, the data in the shared stringvariable report is needed for a formula and not for actual display on the report, so I think I'm better off running the formula on the subreport and pulling over the result vs the actual data. However I'm having issues with the formula. I posted it in the previous thread but I'll repost it again.

ie if {@RCode} in ["P9", "P7", "P3", "P20", "P2", "P18", "P17", "P16", "P14", "B9", "B8", "B7", "B23", "B22", "B20", "B16", "B15", "B14", "B13", "B12", "B11", "B10", "B1", "A8", "a6", "a5", "a1", "278", "276", "274", "273", "272", "270", "269", "268", "261", "258", "256", "254", "252", "251", "250", "249", "243", "242", "240", "239", "238", "236", "234", "233", "232", "231", "229", "228", "227", "226", "225", "224", "223", "222", "219", "215", "213", "212", "211", "210", "209", "208", "207", "206", "205", "204", "203", "202", "200", "199", "198", "197", "194", "189", "188", "185", "184", "182", "181", "180", "179", "178", "177", "176", "173", "175", "174", "171", "170", "169", "168", "167", "166", "165", "164", "163", "160", "159", "158", "157", "155", "154", "153", "151", "150", "149", "148", "147", "146", "142", "140", "139", "135", "133", "132", "130", "129", "128", "119", "117", "116", "115", "114", "111", "110", "109", "107", "106", "101", "97", "96", "95", "91", "90", "78", "76", "75", "74", "69", "66", "61", "60", "58", "56", "55", "54", "53", "51", "50", "49", "44", "40", "39", "35", "34", "33", "32", "31", "29", "27", "26", "24", "22", "21", "20", "19", "18", "16", "15", "14", "13", "12", "11", "10", "9", "8", "7", "6", "5", "4"] then
"Dirty" else
"Clean"

I have several examples where instead of returning "Dirty" the formula is returning "Clean:

VisitId: 497923 RCode: B20,45 Result should be Dirty, but it is returning Clean
VisitID: 498451 RCode: 236, B15, 6 Result should be Dirty but it is returning Clean

In the first example 45 is not in my list for "Dirty" but I used "in" to indicate if one of the items in RCode is in the list it should return "Dirty", in the 2nd example, all of the codes are in the "Dirty" list.

Again your feedback has been extremely helpful.

 

[pmax9999]

I hadn't really given any though to the Dirty/Clean formula. At this stage all I was trying to address was the issue of the sub-report.

To link the sub-report to the main report so that the VisitID gets passed through automatically, do the following:
[ul]
[li]From the Menu Bar, select Edit >> Subreport Links;[/li]
[li]From the left panel, select the VisitID field and click on the ">" arrow to move it to the right panel;[/li]
[li]From the drop-down list at the bottom left, select the parameter you added earlier to the sub-report[/li]
[li]Click OK.[/li]
[/ul]

I will take a look at the other issue when I get a chance. If I cant see the problem, would you be prepared to make the report available (with saved data) for me to have a look at. It is just that it would probably be easier and save a bit of back and forth while we work though the issues.

Regards
Pete

 

[pmax9999]

Please also post the code for {@RCode}, and the section it is in.

 

[pmax9999]

Based on my understanding of the report structure and your data (which is obviously limited), I think your approach to testing for Clean/Dirty codes is unnecessarily complex (and obviously flawed given the results).

I would create a simple formula as follows and place it in the details section (suppressed):
[Code {@CleanDirtyTEST}]
If {TransactionDistributions.AdjustmentReasonCode1} in [ the full list of your "dirty" codes ]
Then 0
Else 1
[/Code]

In the Group Header/Footer, place the following formula:

If	Minimum({@CleanDirtyTEST}, [Table.VisitID]) = 0
Then	'Dirty'
Else 	'Clean'

This test could be done successfully in the main report.

Hopefully this will get you close to the results you are looking for. If not, I think I would need to see the actual report to assist further.

Cheers
Pete.

 

[GayleC]

Thanks Pete!

This thing woke me up at 4 am and I came up with a similar solution to my Clean/Dirty issue, however I had not thought of using Maximum in the formula which works better than what I was planning on using. It's working now. Thank you so much!