Calculate the values from a underlying table

I am building a customerlist within the customer sales of a period. I have a dataset with two tables: "customer" and "customer_ledger_entry".

In the report I will present the customer number, the customer name and the sales. The problem is the sales value is not available as a field, but I have to calculate this value from the "customer_ledger_entry" table. In this table are several entries (invoices, credit notes, etc.)

How to calculate the values from a underlying table?

Here is a post that may work. You would populate the sales value into a dictionary object and then reference it based on the key you decide to use.

(from my blog at http://sqlrs.blogspot.com)

One common problem in reporting and BI solutions is how to incorporate data from both an OLAP cube and relational tables. The UDM in SQL 2005 attempts to solve this, however it really means you still need to build the information into your cubes and dimension attributes.

What if you don't want to or can't?

Reporting Services provides a Custom Code tab within the Report Properties page. You can access various VB.NET objects and system assemblies, and reference external assemblies. One of the internal assemblies is the Dictionary object.

Steps to lookup values from a reference table in SQL:

Drag a list onto the report.
Drag a textbox into the list, or a field from the relational dataset. Modify the textbox to contain =Code.setValue(Fields!KeyField.Value, Fields!ValueField.Value)

Create another list below. Drag another textbox into the list. Modify the textbox expression to hard-code the key for now. =Code.getValue("MyKey")

In the Code Properties window, try the following:

public dict as new System.Collections.Generics.Dictionary(Of System, System)

function setValue(value as object, value2 as object) as object
dict.Add(value,value2)
return value
end function

function getValue(value as object) as object
return dict(value)
end function

Afterwards, you can hide the list box (or table or whatever) that loads the variable with the setValue function. The dictionary still gets populated.

If you have properly bound a table to the first list control, you should be able to lookup results in the second table.

This can be applied in many scenarios, including adding relational reference data to MDX results, and creating a relationship between two separate datasets.

I'd be interested to know if anyone uses this. It seems to have many different applications. One could possibly involve showing two sets of information, for things like variances or budget vs. actual data. If a value doesn't exist in the dictionary, the original field could be returned. If it does exist, the adjustment could be returned.

Note that Generics is .NET 2.0 - for 2000 you may need to use a different syntax but the concept is the same. Basically you're using a dictionary object (could be a hash table or whatever) to store a value by a key. Then you're looking up that value in a table (or list or whatever) to do further calculations.

cheers,

Andrew

|||

Selectis,

I believe you may want to create 2 dataset for your report, the first dataset for the "customer" and the second dataset for "customer_ledger_entry". You can then calculate your values like SUM(Fields!Sales.value,"customer_ledger_entry", customer name like =(Fields!FirstName,"customer")

I hope this was what you were looking for.

Ham

|||

I've made a the two datasets like you said. My expression is:

=Sum(Fields!Sales__LCY_.Value, "customer_ledger_entry"), (Fields!Customer_No_.Value, "customer_ledger_entry") like = (Fields!No_.Value, "customer")

When I run the report I get the message "The Value expression for the textbox refers to a the field "Customer_No_". Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

|||

Selectis,

I must have misunderstood what you were trying to accomplish, I thought you when trying to display values from 2 different tables but I didn’t realize you want to reference the 2 datasets together.

If you can do the following:

You can nest data regions within other data regions. For example, if you want to create a sales record for each sales person in a database, you can create a list with text boxes and an image to display information about the employee, and then add table and chart data regions to show the employee's sales record.

I hope this helps

Ham

|||

I′ve found a way to calculate the value: I use one dataset with two tables: "customer" and "customer_ledger_entry".

The report contains a table and I've used the SUM function for the customer sales: =SUM(Fields!Sales.Value). Now my report calculates all the sales lines from the "customer_ledger_entry". That is not what I want. But the solution is to choose "Edit Group" for the detail line in your report. In the Group settings I added a sorting on the customer and I added a "Group on" expression. Now the SUM function only calculates the records for the customer listed on the line.