Nicole McDaniels
asked on
Possibly a grouping issue?
I have a report that contains a subreport with summaries for number of records per month (Date Opened). RIght now there are only 9 records in the DB for 2 different months so, I didn't define any links to the subreport and I get the correct numbers. However, when I add the link to Record ID, it's as if only 1 record ID is being sent as opposed to the 9. Even if I try to link on Date Opened, I still only get 1 records being sent. I have tried all different types of grouping but no luck. Any ideas?
ASKER
The main report can't send multiple Record ID's to the subreport so that the subreport can perform summaries? I swear I have done that in the past.
I currently am grouping in my main report by DATE OPENED. I have my subreport in the Group Footer. Then, in the subreport, I am also grouping by DATE OPENED so that it can summarize the number of records within each month. Ideally, I want all Record ID's to be sent to the subreport so that the subreport can summarize the number of records per month. I would do it in the main report but there are other summaries that I need to include. They want it in a table format
The parameter that I need to eventually create will be several PR (Record) ID's
Month # of Investigations #of Total Samples % calculation
January 5 376 5
February 36 768 46
I currently am grouping in my main report by DATE OPENED. I have my subreport in the Group Footer. Then, in the subreport, I am also grouping by DATE OPENED so that it can summarize the number of records within each month. Ideally, I want all Record ID's to be sent to the subreport so that the subreport can summarize the number of records per month. I would do it in the main report but there are other summaries that I need to include. They want it in a table format
The parameter that I need to eventually create will be several PR (Record) ID's
Month # of Investigations #of Total Samples % calculation
January 5 376 5
February 36 768 46
You can send multiple values but you have to collect them in an array and then send the array to the subreport in a shared variable
mlmcc
mlmcc
ASKER
I tried to find that in another section but most were for string variables and this is a number variable. I saw you posted this on one but the person needed to link a numeric and string. I only need it to be numeric so how would I change this to pass multiple numbers? I wasn't sure what the part on "strides" was for? And are all of these formulas in the main report?
In the report header add a formula
Name - DeclareVariables
WhilePrintingRecords;
Global StringVar strIDs;
''
Select all
Open in new window
I get an error for this section saying starting with "strides" the remaining text isn't part of the formula
In the ID group header add a formula
Name - BuildList
WhilePrintingRecords;
Global StringVar strIDs;
strides := strides & ", " & {IDField};
''
Select all
Open in new window
Add a formula to use as the link
Name - DisplayIDs
1:WhilePrintingRecords;
2:Global StringVar strIDs;
3:strIDs
Select all
Open in new window
After adding the link, edit the subreport selection formula
{IDField} IN Join({?pm_DisplayIDs},',')
In the report header add a formula
Name - DeclareVariables
WhilePrintingRecords;
Global StringVar strIDs;
''
Select all
Open in new window
I get an error for this section saying starting with "strides" the remaining text isn't part of the formula
In the ID group header add a formula
Name - BuildList
WhilePrintingRecords;
Global StringVar strIDs;
strides := strides & ", " & {IDField};
''
Select all
Open in new window
Add a formula to use as the link
Name - DisplayIDs
1:WhilePrintingRecords;
2:Global StringVar strIDs;
3:strIDs
Select all
Open in new window
After adding the link, edit the subreport selection formula
{IDField} IN Join({?pm_DisplayIDs},',')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
strides is presumably a typo (Maybe auto-correct?). That should be strIDs (the variable name), so the line should be:
strIDs := strIDs & "," & {IDField};
Note that I removed the space that was after the ",". If you're doing a string comparison (as mentioned later), that space could keep the record selection from finding a match (unless you added a space in front of the field value in the subreport). For example, "123.00" <> " 123.00" (with a leading space).
Also, that record selection formula won't work, for a couple of reasons. First, it uses Join, which is used to combine the elements of a string array into a single string, but strIDs is just a string, not an array. You'll get an error if you try to use Join on a string. Instead, one option is to use Split to create an array from the values in the string.
And your field is a number, so you can't just compare that field to the values in the strIDs string.
You could try something like:
CStr ({IDField}) IN Split ({?pm_DisplayIDs}, ',')
However, there could be a performance problem with that approach. If CR doesn't know how to tell the db to convert your field to a string, it will have to get the db to send the records for every ID, and then the report will filter out the ones that aren't in that list. If that's a problem, you could try converting the string values in the list back to numbers and then comparing those values. That's a bit more complicated, so I'm not going to try to get into it at this point (since it may not be necessary).
James
strIDs := strIDs & "," & {IDField};
Note that I removed the space that was after the ",". If you're doing a string comparison (as mentioned later), that space could keep the record selection from finding a match (unless you added a space in front of the field value in the subreport). For example, "123.00" <> " 123.00" (with a leading space).
Also, that record selection formula won't work, for a couple of reasons. First, it uses Join, which is used to combine the elements of a string array into a single string, but strIDs is just a string, not an array. You'll get an error if you try to use Join on a string. Instead, one option is to use Split to create an array from the values in the string.
And your field is a number, so you can't just compare that field to the values in the strIDs string.
You could try something like:
CStr ({IDField}) IN Split ({?pm_DisplayIDs}, ',')
However, there could be a performance problem with that approach. If CR doesn't know how to tell the db to convert your field to a string, it will have to get the db to send the records for every ID, and then the report will filter out the ones that aren't in that list. If that's a problem, you could try converting the string values in the list back to numbers and then comparing those values. That's a bit more complicated, so I'm not going to try to get into it at this point (since it may not be necessary).
James
ASKER
The solution is listed in the other message
When you link a subreport the field is added to the report filter and the value is passed as a parameter.
Where is the subreport?
At any time the report only has 1 record open in the database so linking will generally only pass 1 value.
mlmcc