Grouping issue
-
I need some help with a report I'm working on...
I am creating a report where I'm listing products that falls into certain categories. I'm grouping the products by company who we sell the products to and thats working fine.
My problem is that I have to split the products into certain categories. The categories are not stored in the database, but if they matches some conditions they fall into that category. That would be easy except that some products will fall into multiple categories.
I need a report format like this:
CompanyA product1
CompanyA product2
CompanyA prodcut5
CompanyB product1
CompanyB product3
CompanyB product5
I created a formula that checks if a product falls into a category using a case statement, but if the product is accepted into the first category then it will never be checked against the other ones.
So, do anyone have a suggestion on how I can get multiple checks on a product and at the same time be able to group on that checks so a product will be displayed multiple times in the correct categories?
-
> The categories are not stored in the database
They should be. You cannot report something what does not exist.
> but if they matches some conditions they fall into that category
So, you have business rules hardcoded... Nice.
What if customer wants to change one or more conditions?
Do you call Microsoft every time you want to change your screen resolution?
Then why your customer must accept such approach?
> if the product is accepted into the first category
Can you please define "first category"?
Which one is 1st, which one is 2nd?
_____________
Code for TallyGenerator -
Thx for your reply
The categories is not stored in the database as I said and yes I have to hardcode the categorization of the products, but they will not change ever (I know its not the best solution but I have no say in this)
Sorry for being a bit short in my description, but I'll try to be more specific.
We got 3 categories and they are as below:
1. Only products that is made out of cotton or wool and was shipped to a company using our delivery service.
2. Only products that is made out of cotton and was shipped to a company using our delivery service.
3. Only products that is made out of cotton and was shipped to a company using our delivery service and payment was done before shipment.
Products got a fabtric attribute which is cotton, wool or other fabrics.
shipping type is either delivery, post, air +++
If payment received before shipping is also an attribute.
So each product can and will fall into more than one category so how can I group the product listing on the category and have a product listed more than once?
-
You should not have data solutions based on human knowledge.
Application should not rely on your memory.
You've no categories unless you've got them in database.
Create table "Category" and insert 3 lines with some names in it.
Create table "CategoryRule" and insert lines describing relations you listed in your post.
Create table "ProductCategory" and populate it from table "Product" joined to these 2 just created tables and to tables "Fabric", ShippingType", "Payment".
What you are trying to do now is to make everything I listed above in your code. it's:
1) non-manageble;
2) bad performing;
3) human-depending;
4) non-repeatable.
And of course this hardcoding solution has nothing in common with relational databases.
_____________
Code for TallyGenerator -
thanks for NOT helping dude..I know all of the above, but hey since you can't help me with a solution then don't bloddy bother
-
Sergiy is helping you, but you don't listen...
Even if you have no chance to alter anything in the database which contains the data (which it seems you can't... but maybe it is just a question of applying some pressure on the right persons), you could for example create a reporting database which would contain tables with rules for your reports. It is extremely hard to give you blueprints how to do it, not knowing anything about your situation and possibilites, so the best thing we can do is offer you some ideas which you could use as a starting point for your solution.
To be honest, I still don't understand what precisely is the problem and where are the categories in your sample report:
CompanyA product1
CompanyA product2
CompanyA product5
CompanyB product1
CompanyB product3
CompanyB product5
-
Some table defs, sample data and actual required results would be helpful
But I consulted my Oiuja board
and.....SELECT [Category],[ProductName],[CompanyName]
FROM
(SELECT 1 AS [Category],p.[ProductName],p.[CompanyID]
FROM [Product] p
WHERE p.[fabtric attribute] IN ('cotton','wool')
AND p.[shipping type] = 'delivery'
UNION
SELECT 2 AS [Category],p.[ProductName],p.[CompanyID]
FROM [Product] p
WHERE p.[fabtric attribute] = 'cotton'
AND p.[shipping type] = 'delivery'
UNION
SELECT 3 AS [Category],,p.[ProductName],p.[CompanyID]
FROM [Product] p
WHERE p.[fabtric attribute] = 'cotton'
AND p.[shipping type] = 'delivery'
AND p.[payment received before shipping] = 'Y') d
INNER JOIN [Company] c
ON c.[CompanyID] = d.[CompanyID]
GROUP BY [Category],[CompanyName],[ProductName]
ORDER BY [Category],[CompanyName],[ProductName]
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks David for your help. I've kind of made a similar solution and that would've worked, but I couldn't use it.
The system I am reporting off is a huge system and its not a relational database. It got a table for every screen it shows (I assume this is done for performance) and there is no way I can start changing it. And we don't have the budget to create a data warehouse either so I was stuck trying to create this very important report on the crap data.
And trying to use the solution David made made it clear that the app system we have to go through to get to the data does not support joins or Unions
But I solved it using crystal reports and doing some "fancy" things there. Thanks for all your help even though I couldn't use it
-
I know what you're talking about, and I feel with you. I had to work with a similar system for some time, before we managed to convince the managers that we really need to change to something more up to date than a mainframe system programmed years ago. We were receiving idiotically formatted text outputs (designed to be printed - so, including headers, footnotes and whatever) and had to parse them into a database (MS Access 2.0 it was at that time) to create something that could vaguely remember a data warehouse... but lots of data were missing because it simply wasn't included in the files we were receiving.
Good luck, and hope you're be able to get rid of this system sooner or later (rather sooner).
-
...don't have the budget to create a data warehouse... Know that as well, but if you could ever get your foot in that door, 'they' will wonder how they managed without one.
However I don't think that you need to progress to a a data warehouse straight away. I had similar problems with one of our third party systems and I built a small database, used DTS to transfer just enough data and ran a few SQL queries via Excel for reporting (all without any budget
). Now we transfer over 10GB of data daily, run several reports including Reporting Services and 3 DataMarts.From little acorns do oak trees grow
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply