Tabular rpt detail column expression err

Question

Tabular rpt detail column expression err

MalaSuerte

Old Hand

Points: 319
More actions
September 28, 2006 at 5:27 pm

#169838

I am fairly new to RS and tabular reports. Really?
I have a tabular report with header, detail and footer. In the detail I have a column (allocated_fte) that I need to ‘calculate’ (not one from dataset). The calculation looks like this.
allocated_fte.value = (allocated_fte.value + ((percent*pcn_fte)/100))
When I enter this I get an error message something like: “cyclical reference to 'allocated_fte' not allowed”.
How can I accumulate/calculate a column in a tabular report detail and then sum it in the group footer without being outside of scopes or having a cyclical reference?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 1

If I'm reading your question right, this is a classic "percentage of total in the detail band" problem. The classic answer is that you need to preprocess your data to make the appropriate information to the detail lines as they are evaluated.

By "classic" I mean that it doesn't matter what reporting engine you use, as far as I am aware. Even if a multi-pass engine (such as Crystal Reports) hides this fact from you, the activity is still occurring somehow. I am not an RS expert, and I don't know if RS has a better answer than what I'm about to tell you (IOW, a way of hiding this activity). But the following simple example should show you what I mean.

In this example, we'll show salespeople's quantity sold by customer, and we also want to show what percent of total that customer's quantity represents for each salesperson. So we can do this to make the appropriate data available in a single query, for the purposes of RS use, I'll call out the "preprocessing" item in green:

SELECT SalesPerson, 
       SUM(Order_Quantity) AS Customer_Quantity, 
       Customer, 
       (SELECT SUM(Order_Quantity) FROM OrderHeader S2 
       WHERE S2.SalesPerson = S.SalesPerson) AS Total_Quantity 
       FROM OrderHeader S 
GROUP BY SalesPerson, Customer

... Now your SalesPerson footer can have the Total_Quantity value quite easily, of course, and the detail band can do something like this. This expression is a little overcomplicated by prettification, but I find it helpful to underscore the "percent-of-total"-ness of the expression as I try to write it for you -- as I said, I'm not an RS expert <g>:

=CStr((ROUND(Fields!Customer_Quantity.Value /

Fields!Total_Quantity.Value,4)) * 100) &

"%"

HTH,

>L<

MalaSuerte Old Hand Points: 319 More actions · Answer 2

Thx LSN. That helps to some degree. Unfortunately I can’t write a correlated sub-query since my data source is an old Informix database that doesn’t support that functionality. It looks like I’m doomed to aggregate hell.

Can I write this whole mess in VB in the ‘Code’ area of RS ?

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 3

Bleh. I have been thinking about this for a couple of hours now. And I probably shouldn't have done <g>. (I keep waiting for somebody whose field this is to step in and give you/us the right answer...)

Here's what I think:

1) Yes, you probably could handle it with code that you called on a group level, which changed the value of a GroupTotal variable. Basically it would work like this:

* -- You'd have a function that would take a param of the (in my example) salesperson's name or key, and store the result. Your embedded code would look like this:

Public GroupTotal as Integer

Function SetGroupTotal(SalesPerson As String)
   ' run a query here to get your answer.
   ' store it to the var:
   GroupTotal = <the answer>
   Return ""
End Function

* -- That Return "" statement looks suspicious, doesn't it <g>? Well, you're going to *call* this function as if you are going to be returning a value to be placed in an expression somewhere in the header of the (in my example) salesperson group. Like this:

=Code.SetGroupTotal(Fields!SalesPerson.Value)

... I just put the call you see above in an empty column header. It returns an empty string because I don't need anything to show up, I just need to run the code.

Of course, you could return the salesperson's name and put this call in an "important" header cell instead, no sense wasting it.

* -- So now your detail level can use the Code.GroupTotal value in calculations. It should be correct for the appropriate duration of the engine's work to derive the values for those lines. This would actually work (at least in my tests it seems to).

2) *Should* you do it that way?

Well... y ou could probably smarten it up some by grabbing the whole summary set as a recordset, still with a public reference, at the beginning of the report run, rather than making separate calls.

IOW SELECT SUM(Order_Quantity) AS Salesperson_Total, SalesPerson FROM OrderHeader GROUP BY SalesPerson, and hold on to it for the duration of the run.

Now your function just needs to fetch the right value from that recordset rather than doing multiple queries.

Being an XML expert, btw, not a RS expert, I'd probably grab the whole thing as an XML document and SelectSingleNode rather than playing with the recordset in the function -- but that's neither here nor there.

The real question is: shouldn't we be doing this by using multiple datasets (which is supported by the reporting model) and data regions rather than grubbing around with this manually?

I would have thought so, but I was unable (because of complete inexperience, prolly) to figure out exactly how, and to my surprise I find that there is not really a good way to get a reference to additional datasets you may have attached to a report definition within the VB embedded code.

But this leads to my third suggestion:

3) OK, so you're using Informix. Still, you clearly *do* have a MS SQL Server instance sitting around somewhere. Is there any way you can set up Informix as a linked server and maybe construct the query as a stored proc on the MS SQL Server end? That would make it nice and tidy.

Sigh. Sorry I can't think of anything better. Again, I'm waiting for somebody to tell us how it is supposed to work...

>L<

MalaSuerte Old Hand Points: 319 More actions · Answer 4

LSN: I think the solution you provided in #1 will work. I too am waiting for the RS gurus to weigh in on this issue but maybe it's beneath them? Frankly, I don't know, but would appreciate any suggestions even if were to point out a "Blinding flash of the Obvious" - or something that I should have picked up by RTFM.

Thanks also for the tip on setting up a Linked server to our legacy Informix DB. 99.99% of the databases in our Enterprise are SQL Server 2005 or 2000 so we have SQL Server instances everywhere - which is nice. We're exploring the linked server solution this morning for other projects and not just this RS report.

Thx again. I'll post the results of the SQL and the Link server issue.

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 5

Yeah, I know the suggestion in #1 will work, and #2 is basically an optimized version of #1, so it's a little more work than #1 but I would still give it a try. The linked server solution (#3) will still probably be better perf.

I will be interested in what you find out in that regard, if you won't mind posting. I can help if you run into trouble doing the actual linking, in fact. But I am not at all sure about relative perf and will appreciate any information you can share.

Meanwhile, I agree, this should be blindingly obvious. As I said it is a classic reporting question. Hope somebody else speaks up.

>L<