Nested dataregions

Hi!

I'm having some problems with a basic thing I guess.

I have a table of visits as the whole Dataset. Let's call it group A.

I then group the visits per customer, let's call that group B.

After that i filter out some unwanted visits and call that group C.

The hiarchy then looks like this, A contains B that contains C.

The problem is that I want to know the number of rows in C above the actual table. Something like

CustomerName (John Doe) Number of visits (54 from group C but printed out while in group B)

Visit 1 blablabal
Visit 2 blablabla
... and so on ...

As far as I understand the aggregate functions, you can only use them on the current group or a group above, never below. I tried to make an invisible textbox below C and it works if there is just one customer. If there are more than one customer the sum flipped between all the customers. (A gets the sum of B, and so on).

Thanks
Johan

But you can have multiple C group sections (each time your grouping expression changes), correct? If an outer group can reference an inner group, there will be multiple totals that are only known at runtime.|||

In my case, I only have one C groping per B grouping. Its a table that filters out preknown types of visits. Its the number of rows in this table that I would like to print out above the table in question, inside grouping B. What I did was to create a textbox within the C grouping that I referenced from a B grouping. Works for one (1) customer, but if there are more customers the totals get calculated correctly, but displayed in the wrong order.

I realise that it might be inpossible to do this, but I would hope that I at least could "transfer" a number higher up if I know that it only would be one other C grouping.

What I would like to do is something like this:

(pseudocode)

=Count(Fields!VisitId.Value, "not VisitType=BadVisit")

Am I making sence at all? :)

|||Yes, you are but in general you can have more than one group sections and referencing an inner group is not allowed. Why don't you bring the C totals at the B level in your dataset? A simple scalar function (assuming SQL Server) will do the job.|||

The problem is that I don't deside the datasource at the moment and all I have is a view containing Visits of two types. The first type is carried out visits and the second one is missed visits.

They are not in any specific order and missed and carried out visits could be at random rows.

If I could change the dataset, are you suggesting that I put the total amount of visits with each row per customer and just read the first row by each customer grouping to get the value? Kind of like how they denormalized the name of the customer in every row?

Thanks for a quick answer by the way! :)

|||Correct. In this way, you don't have waste your time to find a hack. In addition, you'll never go wrong by pushing work to the database.

Nested case prediction query question

I have a question about what is possible with a prediction query
against a nested table. Say I have a basic customer-product case and nested table mining model like so:

Mining Model DT_CustProd
(
[Id] ,
[Gender] ,
[Age]
[Products] Predict
(
[ProductName] ,
[Quantity]
)
)
Using Microsoft_Decision_Trees

I can write a query to find the probability of product (and quantity) A like so:

SELECT (select * from Predict(Products,INCLUDE_STATISTICS)
where ProductName = 'A' )

FROM DT_CustProd

NATURAL PREDICTION JOIN

(SELECT 'M' AS [Gender],
27 AS [AGE] ) AS t

What if I know that the query customer (M,27) in question has purchased product B, how can I use that in the prediction join to predict product A? The fact that product B was purchased might influence the prediction, right?

Yes, the fact that B was purchased will likely influence the prediction and the model (by marking the table as Predict) actually uses existing products information in predicting new products. The changed query should look like below (a generalized example, for a customer that bought B and C):

SELECT (select * from Predict(Products,INCLUDE_STATISTICS)
where ProductName = 'A' )

FROM DT_CustProd

NATURAL PREDICTION JOIN

(SELECT 'M' AS [Gender],
27 AS [AGE],

(SELECT 'B' AS ProductName, 2 AS Quantity UNION

SELECT 'C' AS ProductName, 1 AS Quantity

) AS Products

) AS t

NEED VERY BASIC HELP

At my workplace our ERP software in on a VFP database. I have learned quite
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.
Please see my comments to your other posts.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:uSJS23GwFHA.2808@.TK2MSFTNGP10.phx.gbl...

NEED VERY BASIC HELP

At my workplace our ERP software in on a VFP database. I have learned quite
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.
Please see my comments to your other posts.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:uSJS23GwFHA.2808@.TK2MSFTNGP10.phx.gbl...

NEED VERY BASIC HELP

At my workplace our ERP software in on a VFP database. I have learned quite
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.Please see my comments to your other posts.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:uSJS23GwFHA.2808@.TK2MSFTNGP10.phx.gbl...