Passing formula field to subreport fails on Crystal XI/BOE XI

[tazbert]

Anyone passing formula fields to subreport to feed into a stored proc? I'm using Crystal XI to develop the report and I haven't consistently been able to get it to work.

The main report is essentially a shell to call the subreport. I have created a variable to store the currentceusername value so it can be passed to the subreport as a parameter. This doesn't work immediately since I get the error message "Database connector error '42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or Function 'usp_PG0123...' expects parameter '@username', which was not supplied. [Database vendor code: 201]

But I have set up that parameter in the Subreport Link. My workaround is to edit the subreport, refresh the report and enter the parameters to the subreport to retrieve data. Close the subreport and the main report will now function properly and pass the data to the subreport.

After it is working locally in my CR XI, I save the report to the BOE XI server. Running the report on the server I get "Error in file reportnamerpt: Database Connector Error". My local copy of CR XI is patched with the latest SP and hotfixes. Our BOE XI server is on SP2 (not SP3) I believe with some hot fixes.

I can't figure out what I'm not doing right... I have been constantly removing and re-creating the subreport and re-creating the sublinks.

Anyone have any suggestions?

Thx.
Patrick

 

[lbass]

I don't know much about stored procedures, but as a datasource, I believe they are processed before being accessed in the CR, so I don't think a subreport could feed a parameter value to the stored procedure--especially since subreports are processed in a later pass than the main report, too. However, I'm unclear from your description whether the stored procedure is the used in the main report or subreport.

Maybe someone else who is more familiar can add to or correct my thoughts on this.

-LB

 

[elsenorjose]

I use procs quite a bit and one thing that I've noticed is that you need to make sure the proc is in a section in the main report that's before any other data in the main. In other words, a report header or page header. I've also noticed occasionally that I need to 'pre-initialize' the proc by using it twice if I need the data from the proc in say a group header or footer in the main. For example, I have a report that has a proc as a data source for a subreport to display a concatenated field as a split field, i.e.; removing a '|' character between strings and displaying it in a carriage return list. If I just insert a sub to display the data, I sometimes have to refresh the report twice. But, if I add another subreport to the report header, suppress it but link it on the same parameter as the 'real' subreport, I never have this issue. I think because it initializes before any other section in the report, when it's time to actually display the data I need, the proc is ready. Wonky, and probably not very 'best practice' but it works.

Oh, and be sure your parameter in the main report links to the proc's @parameter in the sub, not the ?PM-parameter.

Hope this helps a little.

 

[tazbert]

Thx for the tips. I'll try putting the subreport in a different section than the main report detail. According to Business Objects it should work this way though:


1338878 - Passing CurrentCEUserName to stored procedure parameter in Crystal Reports

Symptom

How to pass the CurrentCEUserName to a Stored Procedure parameter without being prompted for a value for the user in Crystal Reports

Resolution

1. Create a blank Crystal report.
2. Create a formula and insert the special field "CurrentCEUserName"
3. Goto Insert menu select subreport.
Connect the sub report to the stored procedure.
4. In the main report right click on the sub report
5. Select Change Sub report link.
6. Select the formula from the main report and link it to the sub report stored procedure parameter.
7. Refresh the main report and you can observe the report is refreshed without prompting for the parameter.

 

[lbass]

In your first post, you mentioned that you set up a "variable". What is the formula you created? It should just be a formula containing the word:

currentceusername

Also, did you set up the sub to reimport upon opening? There is also a setting for verifying stored procedures upon opening. Not at all sure, but possibly one of these settings could make a difference.

-LB

 

[troop49]

I can't seem to pass the variable from subreport to another subreport???

In the group footer i have the subreport that does the calc that i need in another subreport with in the same group footer.

In the main report i created a formula call ShrAccumLv
like this: shared NumberVar Leave

Then in the subreport i created a formula called ShrAccumLv
like this: Shared NumberVar Leave := Sum ({ESYEMLVD.LEAVE_BAL}) * {ESYAPPTR.HOURLY_RATE}

HOW DO I PASS IT ON TO THE SECOND SUBREPORT i can't see it?

 

[lbass]

troop49,

You should start a new thread in forum149 or forum767.

-LB