show all values?

  • Thread starter Thread starter Bryan Haas
  • Start date Start date
B

Bryan Haas

Hi -

I have a report that groups by merchant and then by
store. If one of the stores has no data, it drops off
the report. I'd prefer to have the report show all
values even if they are zero. If the whole report had no
data, I could use the "on no data" option but in this
case I am not sure what to do when I have partial data.
This requirement is similar to wanting to select "show
all values" in an Excel pivot table report. Suggestions?
 
Bryan said:
I have a report that groups by merchant and then by
store. If one of the stores has no data, it drops off
the report. I'd prefer to have the report show all
values even if they are zero. If the whole report had no
data, I could use the "on no data" option but in this
case I am not sure what to do when I have partial data.
This requirement is similar to wanting to select "show
all values" in an Excel pivot table report. Suggestions?
Click to expand...


It's the issue of a report not being able to display
nonexistent data.

Take a look at the report's record source query to see if
there's anything for the report to work with, if not, then
change the query so that there's at least one record for
every merchant. This can be done by adding the merchant
table to the query any selecting the join type that shows
all entries from merchants and any matching entries from the
stroes table (Left Join).
 
Thank you for your quick response. This data is in a
single table with a merchant, store and user ID column.
I am working off of an ODBC link so can't seed it with
data and just want to have a set placeholder for every
store so that people don't wonder if there was data...but
instead see there in fact was none. Any ideas?
-Bryan
 
Bryan said:
Thank you for your quick response. This data is in a
single table with a merchant, store and user ID column.
I am working off of an ODBC link so can't seed it with
data and just want to have a set placeholder for every
store so that people don't wonder if there was data...but
instead see there in fact was none.
Click to expand...


How do you expect the report to know which stores didn't
have anything to report if you don't have a table of all
stores?

If you can get away with the stores that have ever had
something to report, then create a query that selects all
the stores:

SELECT DISTINCT Store
FROM thetable

And then join that query to your existing table using an
outer join so that all known stores are represented:

SELECT qryStores.Store, thetable.Merchant, thetable.whatever
FROM qryStores LEFT JOIN thetable
ON qryStores.Store = thetable.Store
WHERE thetable.saledate > somedate
OR thetable.saledate Is Null

and base the report on this query.
 
Makes sense. I'll give it a try.

thank you!
-----Original Message-----



How do you expect the report to know which stores didn't
have anything to report if you don't have a table of all
stores?

If you can get away with the stores that have ever had
something to report, then create a query that selects all
the stores:

SELECT DISTINCT Store
FROM thetable

And then join that query to your existing table using an
outer join so that all known stores are represented:

SELECT qryStores.Store, thetable.Merchant, thetable.whatever
FROM qryStores LEFT JOIN thetable
ON qryStores.Store = thetable.Store
WHERE thetable.saledate > somedate
OR thetable.saledate Is Null

and base the report on this query.
Click to expand...
 
Back
Top