1. Home
  2. Point-Of-Sale Software
  3. Difficulties with SelCriteria

Difficulties with SelCriteria

I am running into the old problem of needing a report that shows the sales from last week in specific departments and categories across multiple stores in HQ. I know that I can make a report using syntax like:

Department=X AND DateSold=Last Week OR Department=Y AND DateSold=Last Week OR Department=Z AND DateSold=Last Week OR

But that's a lot of DateSolds to have to change each time they run the report. I'd like to be able to put the date part of the query in the SelCriteria, but when I use this:

SelCriteria = "([Transaction].Time > )" GroupBy = "" SortOrder = ""

and then choose multiple Departments, it returns ALL the sales in the departments because of the "OR" selection between each of them. I thought that surrounding the SelCriteria with parentheses would fix the problem, but it has not. Any help would be appreciated.

We've not yet updated to 2.0, if that matters. A project for the Fall...

Thanks,

-CP

----------------------------- Chris Powell General Manager / CRO Lone Star Comics & mycomicshop.com

Reply to
CP
Loading thread data ...

use the report definition SelCriteria to specify the department like

SelCriteria = "Department.Name in ('X','Y','Z')"

and then use the runtime filter to specify the DateSold.

Reply to
Matt Hurst

That is more unusual, to have the date stay the same, but can be done as well. Specify like this:

SelCriteria = " [Transaction].Time>'1/1/2010' and [Transaction].Time < '2/1/2010' "

the choose departments at runtime.

I need the report to be more flexible than that, unfortunately, as the dates never change and the depts / categories being queried change all the time. It may be impossible, I was just hoping I'd made a simple mistake.

Thanks for taking a look!

-CP

---------------------------- Chris Powell General Manager / CRO Lone Star Comics & mycomicshop.com

Reply to
Matt Hurst

Sorry, misunderstood, you want the report to always show the previous week.

You could use the variables and in your date filter, but that wouldn't be exactly the same as specifying Last Week at runtime, because this would give you Sun-Sun instead of Sun-Sat.

Begin Filter FieldName = "[Transaction].Time" FilterOp = reportfilteropBetween FilterLoLim FilterHilim FilterNegated = False FilterConnector = reportfilterbooleanconAND End Filter

Reply to
Matt Hurst

Here is a solution that will work - specify the date in the report SelCriteria using the variables, like:

SelCriteria = "[Transaction].Time> and [Transaction].Time< "

which will evaluate last week correctly, because the WeekStart will use midnight on that date and will result with everything prior to that time. Then add just the departments in the runtime filters.

Reply to
Matt Hurst

OK, scratch that, back to your original problem of having the filter OR override your selection criteria. For that you would have to run the report one department at a time, or use the variables in the date filters and just live with it including the following Sunday.

Reply to
Matt Hurst

Try this. Have not tried it now but I think it should work...

SelCriteria = "[Transaction].Time > (DATEADD(wk, DATEDIFF(wk, 0, getdate())

- 1, -1)) and [Transaction].Time < (DATEADD(wk, DATEDIFF(wk, 0, getdate()),

-1))"

Tim

"CP" wrote:

Reply to
TimB

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.