Skip to Content
0
Former Member
Jun 30, 2009 at 06:30 PM

Issue with arranging data in a table

24 Views

Hi All,

Need some suggestions as to how i can go about doing this.

Issue: I am unable to represent all the Measures in a table for different products.

In my report i am bringing in Budget and Actuals in different scenarios.

Scenarios:

1. Net Sales.

2. Cost of Sales.

3. Total Expenses.

We have an object that categorizes this and hence using multiple data provider to do this.

Note: We have in issue with our data and hence Net Sales and Cost of Sales are available by Product and Total Expenses by Department Number. And added to that not all products have an associated Dept #.

In one of the tabs of the report i need to arrange Net Sales, Cost of sales and Total Expenses by product. Any suggestions on how to go about doing this.

I tried creating a variable that would say (If <Dept #> = "XYZ" Or <Product Name> = "abc" then "abc") and so on for all the product list. Arranged the corresponding Measures. After doing this the table shows values for only those Products which have any value for Total Expenses i.e i see values only for those rows which have a Dept # associated for each product (as the join is on product for all the 3 data providers.). Rest of the products dont have any values in them.

Once i "Unlink" the Product Name link in Total Expenses data provider i can seee the right values for Net Sales and Cost of Sales, but the Total Expenses values are getting messed up.

When i see these values in independet tables i.e one table having Net Sales and Cost of Sales and a second table having Total expenses the values match exactly.

I can understand that its showing me the values at the SQL level. How can i mix these 2 scenario's to get the required results that business is looking for.

Do let me know in case any furthur clarification is required.

Thanks,

Adnan.