0

For this report, I need one field (Sales Code) to only show if it has been updated. Periodically they change Sales code and I want a report to show the prior code along side the new code when it changes. Along with its part number and so on.

I was wondering the best way to go about tackling this request.

I tried to do _add_days -1 and compare the Sales code --> Sales code1. I dont think that will give me what I am looking for.

For example Sales code changes from AA --> AB. I want to see New CODE OLD Code Part Number and so on... AB AB 12345

FlexCog
  • 1
  • 1
  • How would you know what the old code was? Is it in the data? This may be a database design question, not a Cognos question. – dougp Mar 27 '19 at 15:30
  • In the AS400 we have setup a field that looks at prior data and stores that code if it notices that the two fields are not the same(SalesCode /SalesCode1). The report works fine in the AS400, I am just trying to replicate it in cognos. – FlexCog Apr 02 '19 at 18:04

2 Answers2

0

The pattern you are encountering is called a slowly changing dimension.

Here's a wee free primer.

https://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/

You don't mention the structure of the data you're working with so it would be quite difficult for me to say what type you have other than the fact that you're trying to track historical data and seem to have it captured somehow rules out type 0.

Because of that, I can't come down from Mount Sinai with the solution but this can help you start to think through the problem.

C'est Moi
  • 326
  • 1
  • 2
  • 8
0

In Framework manager have the modeler design the fields for SalesCode and SalesCode1

To only show if there was a change Add a detail filter:

SalesCode <> SalesCode1

To control the context of time, have a separate filter like:

[Sales Date] between ?FromDate? and ?ToDate?
VAI Jason
  • 534
  • 4
  • 14