find order by date range or order id

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL

SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @.Date_ordered OR @.Date_ordered IS NULL) AND ([Date_ordered] <= @.Date_ordered2 OR @.Date_ordered2 IS NULL OR (Order_ID=ISNULL(@.OrderID_ID,Order_ID) OR @.Order_ID IS NULL))">

but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!

Thanks

Jez

If you use SqlDataSource, you can add this to the SqlDataSource:CancelSelectOnNullParameter="false"

And change your SelectCommand to:

SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order] WHERE [Date_ordered] >= ISNULL(@.Date_ordered, Date_ordered) AND ([Date_ordered] <= ISNULL(@.Date_ordered2, Date_ordered) AND Order_ID=ISNULL(@.OrderID_ID,Order_ID)">

|||

hi thanks for the code, right near the end you put OrderID_ID instead of Order_ID, and missed one ) at the end, but thank you very much for your help!

Jez

|||

Thank you for catching the typos. Glad that you got the idea.

|||

hey i just wondered if i could pick your brains again quickly, i wanted to have the same statement, but also see if it would also take input from a querystring, so could i just do the same thing, put all the WHERE clause in brackets, put a "OR" then just put where order_ID = ? and in the select perameters just have where ? = querystringorderID ?

Jez

Financial Accounting Cube

Hi,

I'm designing a cube for analyzing financial data from an accounting application.

Basically, the facts table looks like:

(idAccount, idTime, nValue)

that shows every financial operation for any account, and it's very easy to build a cube for analysing the accounting movements.

But there is another important measure, that's the balance of every account in any time.

A tipical report would be, for a whole year, the sum of movements of any account and the balance at the end of the period.

I think adding a new column to the facts table like:

(idAccount, idTime, nValue, nBalance)

wouldn't work, because any account has a balance in any time, even thought there isn't movements in that time period.

What do you think would be a good design approach?

Why not break it into two (Seems like they are unrelated facts that happen to have common dimensions):

idAccount, idTime, nValue

idAccount, idTime, nBalance

|||

Could work, but with that approach I would have a row in the fact table for every day and for every account, and that doesn't look very efficient.

Imagine a "sales" account, that has a balance value in day 10 of 10,000$, and there are more sales after that day:

(idAccount="sales", idTime=10, nBalance=10000)

(idAccount="sales", idTime=15,nValue=500)

so, (idAccount="sales", idTime=15, nBalance=10500)

That means, the balance value changes after any accounting operation.

And now, what happens if the user makes a report that has a row for every account with two more columns: nBalance and nValue, and chooses only the day 20?

For the "sales" account nValue = 0, that's clear, but, what happens with nBalance?

It should be 10500, becuase the balance didn't change after the day 15.

The problem is that I don't know how to create the nBalance measure in order to get that behaviour.

The only way I can figure out is filling the (idAccount, idTIme, nBalance) table with the balance of every account for every day, and that seems to be a waste of resources, because there're many accounts that have very few changes in their balance over the year.

Any suggestion?

P.S. Another problem is the calculation of aggregations for nBalance. Aggregations for nBalance in the account dimension must be a sum of nBalance for any account. But for time dimension, nBalance is the value of nBalance in the last time of the time dimension.

I can figure out many "real wolrd" problems similar to this. For example, imagine that you have a solution for identifying the stock in a warehouse. There are, at least, two main measures: the ins and outs of the warehouse and the actual stock in a certain value of time.