How to share parameter value between main and subreport?

Hi All,

I am really lousy when it comes to creating reports. So I am seeking your expert advise/experience.

I have a report that uses a stored-procedure as its data source. This procedure has a parameter. Within this report, I created a subreport which also sources to yet another stored-procedure that also has a parameter which expects the same value as the param of the main report.

When I run the report, say in preview mode, I do not want to be prompted with the parameter twice (i.e. once for the main report, and another for the subreport). How can I edit/modify subreport so that in its select or formula it will say something like:

{?@.ParamOfSubReport} = {?@.ParamOfMainReport}

Many thanks in advance.

_msd_You need to use shared variable and you can use it anywhere in the report|||Right click on your sub-report and choose "Change sub-report links".

Example:

"?parameter1" in the main report links to "?Pm-?parameter1" in the sub-report, then choose the field in the datasource that the parameter is tested against.

When you enter the parameter in your main report it will be passed to the sub-report preview also.|||How do I pass a value returned from the stored proc in the main report to the sub report. I'm getting an error "cannot link from stored procedure" when I pass the value from main report to subreport using subreport links.

Immediate response would be appreciated. Thanks.|||Assign value to shared variable and use that in Sub report|||Hi, I saw this old thread and I am working on the same issue. I'm working on a simple dumb shell of a report in Crystal 8.5 as a test for how to pass parameters into a sql server 2000 stored procedure via a subreport. I'd like to deliver this as straight Crystal without using vb or other shells to call the report from.

Eventually I need to modify a copy of a main report with data and about 8 subreports, all pulling from stored procedures using one parameter called ID. I am getting many requests for this ID parameter, for each subreport, even though I am linking the parameters via subreport links.

The subreport wants to link into the stored procedure using the parameter name from the stored procedure, which is @.ID. The main report only wants to give the subreport a subordinate link such as ?Pm-@.ID. I cannot name the stored procedure parameter with a name like ?Pm-@.ID because it violates the rules of transact-sql. It sees the hyphen as a subtraction. I can see the logic of using the same variable names, but how do I get rid of that bothersome hyphen?

I have also tried using shared variables and they connect to the subreport ok, but I'm still not closing the loop by linking into the stored procedure based on the shared variable.

I am trying to use the same information in multiple places without making the user enter the same information over and over again.

Has anyone had this problem before?sql

How to set user who cant modify any StoredProc and data in Tables but can read data in Tab

Please give me advise ????

Go into Management Studio, connect to your database and go to Security-->Users from the treeview. Right click and select New User. Create the use based on the relevant login and then go to the Securables option where you can add tables and stored procedures and select which rights the user will have.

How to set user who cant modify any StoredProc and data in Tables but can read data in Tab

Please give me advise ????

Go into Management Studio, connect to your database and go to Security-->Users from the treeview. Right click and select New User. Create the use based on the relevant login and then go to the Securables option where you can add tables and stored procedures and select which rights the user will have.

How to set up a stable backup plan?

Our database is more than 20,000,000 bytes and increase largely everyday

who can give me some advise about stable backup?

My database is sqlserver2005 and now i use files to backup including full backup and diff backup,whether is better to use device?

any ideas? thanks in advance

Your backup plan will depend on your recovery point objective (RPO) and recovery time objective (RTO). How long should it take for you to restore your databases should a disaster strikes? How critical is your data and from when do you need to have your data restore? You should also look at your database recovery model as this will affect your RPO and RTO.|||

thanks in advance,however RPO and RTO seem a little different for me,can u give me some details about that? and any ideas about what i did now

1.I first set up maintenace plan ,backup full and diff database to files and specify schedule

2.set up a job,type is ssis and package is from "maintencace plan/***" and specify schedule same with backup plan

two issues:

1.whether i need to backup transaction log in maintenace plan?

2.what's the difference backup to files and backup devices? which is better?

I think if i write T-SQL script in job and backup database to device, looks simple,is it right?

any ideas about thisT-SQL script?

much appreciate

|||

Hi Andrew,

RTO is the maximum amount of time that the service can be unavailable whereas RPO is the maximum amound of data that can be lost following a disaster. So, your requirements may be to have the service back up and serving clients within 2 hours following a hardware disaster (RTO), but you could afford to lose 1 full day's worth of data because, for instance, it's easily reproduced (RPO).

Define your RPO and RTO requirements first, then a stable, suitable backup plan can be created to meet the RPO requirement. Although your chosen backup strategy will contribute to meeting your RTO objective, RTO is usually fulfilled via high-availability options such as clustering, mirroring, offsite dr etc. (assuming, of course, that your RTO dictates a high-availability solution!)

For an example, you could define a daily full backup job at 00:00, followed by a tran log backup job every 15 minutes. This would give you an RPO of 15 minutes.

Cheers,

Rob

|||

much appreciate for all