Retrieving user specific data through SQL in Crystal Reports?

[RazorT]

I'm upgrading a system from Access to SQL.

In Access I link the server mdb to a local mdb where a table holds the users parameters. Then when I print a report it filters no problem, even if I run the report manually (though, it is usually displayed through the system of course).

What's the best way to do this in SQL Server?

e.g. InvoiceReport will use qryInvoice and I need it to pick up specific data based on that users choices, e.g. ClientReference

many thanks in advance for your help
cheers
Raz

[VBFnewcomer]

I'm on here because I'm learning vb.net

Then I believe you should be posting in .Net Forum unless ofcourse you are trying to achieve the result using classic VB

[RazorT]

Sorry, I should've said..... Yes this is a 'classic VB' question.

(I was making a lot of .net progress, but it will be a long time until I stop using VB)

[RazorT]

I have been advised to write the report data to a table but I dont like that solution; seems too messy.

I guess I need something like this

{PrintInvoice.priUserID} = {@UserID}

But I'm not familiar with setting parameter values in SQL Server

[zeezee]

cant you pass the useid as a parameter to the report?
What CR version are you using?
I think the ideal place for this thread is the Reports section

[RazorT]

Version 6 of Crystal

I didnt post this in the Reports section because my question concerns connecting to the reports rather than any specific report problem.

My system has 20 or so users who all need to see specific client/customer/person data displayed on a report.

It was easy in Access and I was hoping it would be easy through SQL Server ?!?

[zeezee]

Ok what user are you talking here?
DB user or your system user or windows user?
Cant you pass the user id by a parameter from vb to report?

[RazorT]

System Users. I guess I could pass paremeters in (if I knew how) ??

[zeezee]

create a parameter field in the report. I hope its there in CR 6.
And I guess the you are using the Crystal Report viewer control , not Crystal Report Control.
to pass the value you can use like this

Dim myreport As CRAXDRT.Report
    myreport.ParameterFields(1).AddCurrentValue "test"

remeber the index for the parameterFields collection should match the order of parameters in the report.
This I tried in CR 8, I hope its same for CR6

[RazorT]

NOW THAT is encouraging. It means a bit of work as its completely different from how I'm working at present, but it does look straightforward.

Excellent. Thanks for your help.

I'll check back later and let you know how I get on.