Passing parameters from VB.Net to Crystal to a stored Procedure

[Ragnarox]

Hello all,

I have created a report in CR9 that is an invoice for our customres. This invoice is not a static item, it can have anywhere from 2 to 6 different columns in the middle of the report. Basically the report is set up like this

Col 1 to Col 3 - set standard for each report

Col 4 to Col 12 - generated at run time by the stored procedure with values being passed back into the report

Col 13 - standard total for each report

So for each group, depending on what they will be billed for, the stored procedure creates the tables, but it needs a parameter in order to get the correct columns.

So that parameter needs to be passed in from VB because it is the primary key for the billing table. Is this possible to do? I have looked through my books and on their site but have not found anything in regards to this.



Any help, as always, is greatly appreciated.

Brian

 

[MisterMo]

In order for Crystal to build the structure of the report it must have the full list of fields (columns) before hand and written in stones, sort of speak.

I am pretty sure you cannot have the same SP returning 6 fields or 9 fields depending on input.

What you can do is to have a report for every possible output using the same SP

then insert them as subreports in a report and depending on input you will run only one subreport and suppress the others







Mo

 

[Ragnarox]

Mo,

That is not something that is possible to do. We insure over 500 groups and i do not want to have to create a report for each one. My thinking was that it should be possible to create the master report with all possible fields, and then with each change of the parameter have it only fill up those that it needs.

Is there another program out there that will work in this way so that we do not have to make a new report for each school, and for each time the school increases or decreases the number of plans that they have through us?

Any help, as always, is greatly appreciated.

Brian

 

[MisterMo]

I was thinking more in line of 5 different reports

starting from 2 extra columns to 6 extra columns + static columns

All you need is a smart way to know which subreport should be used, you could do a pre-check to see how many columns are returned from VB and then run the report using the desired subreport





Mo

 

[Ragnarox]

Mo,

Here is the problem with that. We sell Long Term and Short Term Disability, Voluntary and Basic Life, Voluntary and Basic AD&D insurance. Not every school will have the same combination of products. There is also a possibility that we will begin selling even more types of insurance in the near future.

The invoice I am trying to create will look at the billing master table and see what types of products are going into this bill through a stored procedure and create the colum headers. Then I have another stored procedure that will get each individuals premium amount for the coverages that they have.

So one group could have Long Term Disability and Voluntary Life, another have Long Term Disability and Short Term Disability, etc. Management wants just one invoice that will be handle any combination of insurance products a group has. My stored procedures are able to do this and so is the report, after I enter in the new parameters for the stored procedures.

What I am wanting to figure out is if it is possible to send those values over programmatically because the stored procedures are based on primary keys and the users will not know what those are.

I have already tried the following:

Dim document as New ReportDocument
Dim paramValu as New CrystalDecisions.Shared.ParameterValues()
Dim paraDiscValue as New CrystalDecisions.Shared.ParameterDiscreteValue()

paraDiscValue = billingpk
paramValu.Add(paraDiscValue)

document.DataDefinition.ParameterFields("@BillPK").ApplyCurrentValues(paramValu)

This did not work, cause it still asked for the parameter values when it tried to display the invoice. If there is another way, or another place I can look, please let me know.

Any help, as always, is greatly appreciated.

Brian

 

[MisterMo]

is there a way for you to know what the column might be by reading the row results?

eg the policy could have a code that associte it to Long Term or Short Term Disability

could you give some details of the data returned

Mo

 

[Ragnarox]

Mo,

Not really, because the header returns as 1 long row and the detail is all premiums.

The header subreport goes and gets what products are tied to the specified invoice and places those in the page header area.

Then the detail section goes to another stored procedure and grabs the individual premiums for each product that the person has.

The only way that I know that the correct premium is in the correct column is that both stored procedures get the products in the exact same order. So now the only thing I need is the ability to send over the parameter for the stored procedures to use, because the user will not know the primary key of the invoice being generated.



Any help, as always, is greatly appreciated.

Brian