Got a problem modifying a report that currently works nicely to do something a bit different.
Currently the report (actually a subreport) links a class in Intersystems Cache (looks like a table to ODBC) to a SQL table. All works well and surprisingly not too slowly - three cheers for Crystal!
Now I need to modify the report. Based on a date parameter, the columns I want back from the SQL table change. "No problem" I thought - I can write a stored procedure to masquerade as the SQL table. The stored proc runs the appropriate SQL to get the columns I want, and I use aliases so the complexity is hidden from Crystal.
However, when I use the visual linking expert to link the class to the stored proc, Crystal tells me "Invalid file link. Not an indexed field." The fields at either end of the link are the same, but I guess Crystal can't see through the SP that the index exists.
I have tried switching off "Use indexes or server for speed" but that does not help. Does anyone have a solution?
I could discard the stored proc and link to the table again, bring back all the columns and use formulas to choose which field to show, but this would mean more data over the network, and several formulas to change in several sub reports in several main reports, increasing the work and complexity.
Hope this is something you gurus have seen and solved.
Thanks in advance,
Bill.
Currently the report (actually a subreport) links a class in Intersystems Cache (looks like a table to ODBC) to a SQL table. All works well and surprisingly not too slowly - three cheers for Crystal!
Now I need to modify the report. Based on a date parameter, the columns I want back from the SQL table change. "No problem" I thought - I can write a stored procedure to masquerade as the SQL table. The stored proc runs the appropriate SQL to get the columns I want, and I use aliases so the complexity is hidden from Crystal.
However, when I use the visual linking expert to link the class to the stored proc, Crystal tells me "Invalid file link. Not an indexed field." The fields at either end of the link are the same, but I guess Crystal can't see through the SP that the index exists.
I have tried switching off "Use indexes or server for speed" but that does not help. Does anyone have a solution?
I could discard the stored proc and link to the table again, bring back all the columns and use formulas to choose which field to show, but this would mean more data over the network, and several formulas to change in several sub reports in several main reports, increasing the work and complexity.
Hope this is something you gurus have seen and solved.
Thanks in advance,
Bill.