I have a query which is linked to a list.
I get line by line data a bit like this
Y or N Country Measure 1 Measure 2
y USA 1000 2000
y UK 500 200
n France 600 400
I then have a second table linked to the same query.
I want the following:
Y or N Measure 1 Measure 2
y 1500 2200
n 600 400
Which would seem easy enough, just pull in the required items. Unfortunately, it only shows n, with measure 1 and measure 2 having the total values. I have the line by line output in the same report so I know the y data is there.
I changed the rollup aggregate function to total in the Y or N column and it then brings back Y and N but the measures just show the total (same value for both cells), it doesn't split it out.
Any ideas why this would happend? I'm seriously baffled. The Y or N field is a data item with a case statement if that matters.
Thanks
Try t
It is aggregating the values. If you don't want that create a new query with same columns, then go to query explore and set
auto group and summarize property of query to No.
I tried that, it made the summary show everything line by line and it shows there is Y and N data.
Unfortunately I need the data summarised. I tried to keep the auto group and summarize property to no but then set the aggregation of the columns in the list to summarise.
This did not work, I keep getting individual rows.
Any ideas how I can get it to summarise properly?
Also, the query it's running off is a joined query, does that require it to have different settings?
Thanks
Have you tried to check the "Country" Data Item in the properties of the second list?
May the absense of the data item produces a different result for the if statement
I tried that too, didn't work. It gets y and n to show, but just has the same value (Grand total value) for both.
I tried to tick the measures too, didn't fix it.