Thread: Help with report please

Hello,

I am running version ML 8.00

I would like to run a report, and I guess the best place for me to do it is via the Orders -> Reports.

I would like to bring back the following fields:

Date of transaction, Order Number, Category and Amount. How can I do this? Considering one particular order number can include items from different categories.

Please help

You can follow this KB here - https://support.aspdotnetstorefront....barticleid=212

Thank you for this, but how do I know what the headings are called? The ones that i want to bring back, where can I view the available fields to ensure I spell them correctly as per what is in the database?

Thank you

You need to use SQL language in custom reports. If you don't know this you will need someone who does or a programmer to create an XML package.
I recommend a book by Ben Forta called "SQL Server and T-SQL". It's the second book by Forta on SQL. SAMS is the publisher.
SQL is not that hard.

Hello,

Thank you for getting back.

I think I might be OK with creating the SQL as I read a KB about it...

Using this as an example:

INSERT INTO CustomReport (Name, Description, SQLCommand)
VALUES ('Test Report', 'Test Description of our report', 'SELECT Email, BIllingZip FROM ORDERS')

I just dont know where to look to find out the names of the SELECT fields.

Basically I am happy with the default order report that you can run, but I would like to add the Category as an additional column. How do I do this please

there is no way to do this in general (e.g. a product could be mapped to 27 categories, departments, manufacturers, etc)...but if you know the structure of your product to entity mappings, you'd have to mod the report SQL to join in the category and then just add that to the output.

Hmmm...

Yes I found the Report by Category pretty much useless

Because I realised it brings back the product numerous times if its been mapped to various categories, but doesnt even tell you which products these are, so when you run it it seems as if your sales as much higher than they should be because products are appearing multiple times.

I have been using these reports for accounting purposes and now realised my figures have been too high

The report you are asking is do-able, but you'll have to do some more advanced SQL to generate it. More than likely you need to aggregate up the product-category data and possibly display only the top-most category per product in the report.

agreed, doable, but a bit custom. you really have to know the exact structure of your products (and mappings) if you are using them to pull over into accounting/etc...

Might make a nice little customization report project