I am trying to pass a shared number

[DianaStewart]

I am trying to pass a shared number variable from my Subreport to my main report. In my subreport I have three groups. Group1 is the Table1.Group, the second group is Table1.EmployeeName, and the third group is Table1.Date. The Table1.Date is grouped by Month. This is where I have my formula with the shared variable:

whileprintingrecords;
if month({table1.date}) = 1 then
Shared NumberVar Abc := Sum(Table1.Hours, Table1.Date, "monthly&quot

Basically each name should have a value for each month.

The value of the last name grouped is being passed into the Main report rather than the value for each employee.

What am I doing wrong or how can I resolve this problem?

Diana

 

[Naith]

Hi Diana,

I noticed that you've started another thread based pretty much on your previous thread. When you do that it's quite difficult for people trying to solve your problem to keep track of all the information collected by the guys here which make solving your problem easier.

If you can, try to keep it all in one thread so guys like me can have an easy go of it.

Where in the main report is your subreport placed?

In order to return for every guy in Group2, it should be placed in Group3. Also, from the last thread you started, it sounds like you haven't linked your subreport to your main report. You don't always have to link subreport to main, but in most instances, you do. This is one of them. Link on all your group fields, then explain what's happening.

Naith

 

[DianaStewart]

My subreport is placed in my Group Header1 (Table1.Group)

My subreport and main report are not linked. Should I link the subreport and the main report?

In my subreport I have the report grouped by Table1.Group
This is all regions in the Midwest area: Utah, Mississippi

Then I have Group2 grouped as Last name of Employee: Smith, Brown

Then I have Group3 as Table1.Date, this is grouped by month


The formula is placed in Group 3:

whileprintingrecords;
if month({table1.date}) = 1 then
Shared NumberVar Abc := Sum(Table1.Hours, Table1.Date, "monthly&quot


In the main report I have the report (Group1) by Table1.Group (this is the Midwest region as well).

Group2 is the Last Name of employee grouped..

The subreport is placed in Group Header1 (midwest region)
and the forumla to display the variable is placed in Group Footer2:

Whileprintingrecords;
shared NumberVar ABC;
ABC

This how the subreport looks like:


UTAH
Brown
January 12

Smith 18


The main report looks like this:
UTAH
Brown
January 18

Smith 18


**As you can see the value 18 which is the value of Smith in the subreport is being placed under all the names..


Help Help Help! hahaha

 

[Naith]

Yeah, you need to link 'em. Use the fields I mentioned before you link on. And link on any parameters you have too.

Here's the deal with subreports; for each time you want the subreport to return a value - the subreport needs to have run at least once.

By placing the subreport in Group1 of the main report - there's no way you're going to get it to return a new value every time you get a new Group2. This is because the subreport is only running for each new region, not for each new employee.

Is the stuff in the subreport visible to the user when he's looking at the main report? Or is the purpose of the subreport simply to populate the ABC variable? If it's the latter, you should endeavour to have the formula run for ABC only once for each return to the main report, because with every subsequent run, the previous ABC value is going to get lost in the subreport - and so only the most recent ABC value in the subreport will be returned to the main report.

Try putting the subreport in the GroupHeader2 and the formula which calls the ABC variable in GroupFooter2 of the main report.

Now what do you got?

Naith

 

[DianaStewart]

I placed the subreport into Group Header2 as you suggested and the formula to retrieve is placed in the Group Footer2.

I have three parameter fields in the main and subreport: Date1, Date2 and Group. I linked the three parameter fields.

I run the report and I get the same result(s). Would you like to see the report with "Save Data with Report"?

This is becoming frustrating... I thought this would be easier!

 

[DianaStewart]

Maybe I am approaching this all wrong. How can I then pass the values for each month for this person from the shared variable in the subreport to the main report?

 

[Naith]

What are you expecting the subreport to do that the main report can't do? Why is it there? Give us an example of how you expect your main report to look.

 

[DianaStewart]

Naith,

All I want to do is pass the shared variable for each name for each month onto the main report. How can I do this? Tell me step by Step and I will try it out..

Diana

 

[Naith]

It helps to understand your reasoning for doing certain things a certain way. Otherwise I'm only giving you advice based on a half-assed understanding of the problem.

Based on what I see so far, this is what I think:

Okay, let's backtrack a little so I can be clear that I understand what it is you're trying to achieve.

You want a main report grouped by Region, and Employee. You have a subreport in the Employee header, which is grouped by Region, Employee, and Date. Then you're trying to pass back monthly values for each month to the main report.

Firstly, you need to have a monthly date group in the main report. Your main report should now be grouped by Region, and Employee, and then Date(Monthly). Your subreport should be in the Date(Monthly) header. The subreport should be linked on the three groups.

The formula you use to call the Shared Variable value should be placed in the Date(Monthly) footer of the subreport. If you don't want to show this footer in the main report, and you only want the information to be displayed at the employee level, then you'll need to pass a a value into twelve variables - one for each month. Return each of these variables into the Employee group footer.

Frankly, though - based on what you've said so far, you don't even need a subreport for what you're trying to do.

Naith

 

[DianaStewart]

Naith,

I appreciate your help dear! The reason I am using a subreport is because I am trying to link three databases, one is an ODBC and the other two are seperate databases. When I try to link one of the databases I receive the error File Link Warning. The specified fields may not link successfully due to different type. When I click OK I get the error ODBC error.. So to avoid getting this error, I use a subreport, the subreport access another data source, and whats good about the subreport is the datasources do not connect rather the fields instead...

I will try what you suggested in your reply.. thanks dear!

 

[DianaStewart]

Naith,

I can I use the previous function as well. If the Table1.Name is not the previous Name then store in Var1 else
store in Var2 else store in Var3...

What do you suggest?

 

[Naith]

If you mean, can you logically use next and previous; the answer is most definitely yes. If you mean should you, then I would be inclined to say no.

Next and previous isn't very reliable as far as values are concerned, because there's always a possibility that you could legitimately get two concurrent equal values which are two valid separate database values as opposed to a variable which hasn't reset yet.

If you're going to use the 12 variables, I was thinking you'd use something along the lines of If Month({Date}) = 1 then JanuaryVariable := JanuaryVariable + Whatever...

Naith

 

[DianaStewart]

Lets start from the beginning Naith. On my subreport I have the report grouped by GroupHeader1 = Region, GroupHeader2 Last Name and GroupHeader3 Date (by Month)... On this report in GroupHeader3 I have 12 different formulas for each month. If month(Table1.date) = 1 then VariableJan = Sum(table1.Time, table1.date,"monthly&quot

When I preview this report the values are correct.

How can I pass each of these Variables for each name to the main report where the values will show up under the name on the main report?

Why is this becoming so difficult?

Diana

 

[Naith]

The formula you posted should be:

If month(Table1.date) = 1 then Shared NumberVar VariableJan := Sum(table1.Time, table1.date,"monthly&quot

The formula used to call this in the main report should be:

WhilePrintingRecords;
Shared NumberVar VariableJan;

This needs to be placed in the Date group footer in your main report. If you place it in your Employee footer, you will only get the last values from the subreport - although this will be fine if the subreport runs for every month once for each employee.

Naith

 

[DianaStewart]

Naith,

Have you ever come across the error: ODBC error: Invalid parameter number??

 

[Naith]

One of your formulas may be invalidating the WHERE clause of the SQL generated by the report.

Go to Database, Show SQL, and show me the SQL is being generated. Also, confirm, if you can, that you're connecting to a SQL Server database.

Naith

 

[DianaStewart]

I am not connecting to SQL. When I click on Database menu the Show SQL is greyed out... The only other query I have is when you click on the Report menu and select Edit Selection Forumla.