Hello,
So I have a crosstab report and two of the records are displaying wrong values sometimes. Here is a simple representation of what I'm working with. This is not the actual data I'm using. I just created a mock-up to improve readability.
| Shirts | Pants | Coats |
----------------------------------------------------------------
| Sales | $15 | $25 | $70 |
Store 1 | Items Sold | 2 | 1 | 2 |
| Profit | $5 | $10 | $20 |
----------------------------------------------------------------
| Sales | $12 | $20 | $72 |
Store 2 | Items Sold | 3 | 1 | 6 |
| Profit | $2 | $15 | $10 |
----------------------------------------------------------------
| Sales | $20 | $13 | $40 |
Store 3 | Items Sold | 1 | 2 | 1 |
| Profit | $5 | $3 | $23 |
----------------------------------------------------------------
My issue is that (Store 1.Items Sold) is all correct, then (Store 2.Items Sold) is all incorrect, and then (Store 3.Items Sold) is only correct for Shirts.
So my question is... where do you guys think I should look into finding the error? We have written a query to test the report data and everything is correct except for that one record (Items Sold). It's correct for some stores, all wrong for some stores, and only partially correct for some stores.
Any help is appreciated! Thank you!
Because you do not describe the pattern of the incorrect values, it would be difficult to identify what is going on.
You will need to talk to your modelling counterpart and he might be able to help you.
Here are some things to investigate:
In the cases where the items sold values are wrong, are the other two measures right? Why do you suppose that is so?
When you look at the generated sql, what do you see which could be of interest?
If you remove stores from your crosstab what happens?
If you modify your report to have stores and your measures but not the clothes types (i.e. put the stores on the columns and leave the measures on the rows) what happens?
What does the aggregations of the report look like? Are they correct or incorrect?
If you use a list report rather than a crosstab what happens?
What are the fact grains for the two dimensions? Are you trying to project a query below the grain of one of the dimensions?
How are these facts and the dimensions modelled?
Is this report using SQL which you've written or is it from your model?