Creating a custom report with date ranges

[Former Member]

I am currently working on a report that by default has supplied us with the current on hand quantity for an item.  What I would like to be able to do is create a separate column in the report that supplies us with the on hand quantity of the same item in between a given date range.  I am running in to a couple of issues with this and was hoping someone would be able to assist me.

Thank you,

[abhilash_kumar · Accepted solution]

Hi Corey,

1) You would create a Date Range prompt

2) You would create a formula that looks something like this:

If {Date_field} IN {?Date_Range} then

     {On_Hand_Qty}

3) You would then insert a 'Sum' on this formula field and place it on the Group Header/footer/report footer.

-Abhilash

[Former Member · Accepted solution]

Could you please elaborate on what you mean {Date_field}

I was trying this piece of code but since the data types were not the same I kept getting error messages.

{SP_M4_OH_Items.QTY} IN {?Start} TO {?End}

   ^ is type numeric^                 ^  are dates ^

[Former Member · Accepted solution]

I found the date field that I will need and that solved the issue with respect to data types.

Now it seems that going off of the formula you suggested I arrive with this:

IF {SP_M4_OH_Items.LST_OH_QTY_DATE}IN {?LY_START}TO {?LY_END} THEN {SP_M4_OH_Items.QTY}

This returns and error message saying: "The result of the selection formula must be a boolean."

[Former Member]

Do you know of any articles or tutorial videos that could walk me through using the database's query tool with respect to the description commands and tables?

[abhilash_kumar]

It would vary depending on the database you're using.

You could find that info in the database specific forums/guides.

-Abhilash

[Former Member]

Thank you.

[Former Member]

I figured out how to do that but now I am getting all 0s as the quantity.  I think the issue now is the date i am using "SP_M4_OH_Items.LST_OH_QTY_DATE" is not the right date.  This is the only date time data type that shows up in my field explorer that is one of my Database Fields.  I am wondering if there is a way to query my Database for all of the date time data type fields to see if there are any more of them.  If there is a way to see all of the fields in the database that would be very useful as well.

[abhilash_kumar]

I don't have any clue about the tables/structure of your database so I can't comment on what the right field to use would be.

You could use the database's query tool and run a describe command against the table to find out field types.

-Abhilash

[Former Member]

Could this query tool be used to show me all of the tables in the database as well?

[abhilash_kumar]

Yes.

You can even create a blank report and connect to the table in question and use the 'Field Explorer' to check the datatypes.

-Abhilash

[Former Member]

I found the date field that I will need and that solved the issue with respect to data types.

Now it seems that going off of the formula you suggested I arrive with this:

IF {SP_M4_OH_Items.LST_OH_QTY_DATE}IN {?LY_START}TO {?LY_END} THEN {SP_M4_OH_Items.QTY}

This returns and error message saying: "The result of the selection formula must be a boolean."

[abhilash_kumar]

Don't add this as a Selection Formula.

Please create a new formula via the Field explorer and drop it on the report.

-Abhilash

[Former Member]

How would I go about doing that