Create Monthly Difference Report from One Table

Question

Create Monthly Difference Report from One Table

phina_el

SSC-Addicted

Points: 408
More actions
May 24, 2010 at 3:33 am

#224938

HI All...
I have a table as follows:
Model Date QTY_MayQTY_JuneQTY_July QTY_Aug QTY_Sept QTY_Oct
FLM-CD1-1D05/21/2010194000194000194000194000194000194000
FLM-CD1-1D05/14/2010454000454000454000454000454000454000
FLM-CD2-1D05/21/2010257900257900257900257900257900257900
FLM-CD2-1D05/14/2010249900249900249900249900249900249900
FLM-CD5-1D05/14/2010243720243720243720243720243720243720
FLM-CD5-1D05/21/2010259200259200259200259200259200259200
And I want to have the Monthly Difference Report from One Table of QTY that comes from the same records in table, and it is control by Model and Date.
As the results below:
Model Date QTY_MayQTY_JuneQTY_July QTY_Aug QTY_Sept QTY_Oct
FLM-CD1-1D05/21/2010194000194000194000194000194000194000
FLM-CD1-1D05/14/2010454000454000454000454000454000454000
Difference--------------260000-260000-260000-260000-260000-260000
FLM-CD2-1D05/21/2010257900257900257900257900257900257900
FLM-CD2-1D05/14/2010249900249900249900249900249900249900
Difference-------------800080008000800080008000
FLM-CD5-1D05/14/2010243720243720243720243720243720243720
FLM-CD5-1D05/21/2010259200259200259200259200259200259200
Difference--------------15480-15480-15480-15480-15480-15480
Are there any ideas to generate the report as I want?
Thanks,
Fin,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

kpsr_kpsr 63487 SSCommitted Points: 1875 More actions · Answer 1

Hi

i don't no how u r data was available, but if u want to find out the varinace of two,i think u need to pick up matrix report.

check with matrix(to create data set for variance)

cheers

sank

kpsr_kpsr 63487 SSCommitted Points: 1875 More actions · Answer 2

Hey did u get the solutions for that man?, if u not plz ping me, then i will give idea,whether it works or not, I tried same scenario then i got the result.that's way

sank

LutzM SSC Guru Points: 107049 More actions · Answer 3

kpsr_kpsr 63487 (6/25/2010)
Hey did u get the solutions for that man?, if u not plz ping me, then i will give idea,whether it works or not, I tried same scenario then i got the result.that's way
sank

If you have a solution please post it so others can benefit, too.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

kpsr_kpsr 63487 SSCommitted Points: 1875 More actions · Answer 4

Here i m taking sample data:

here i m finding the difference b/w two same fields in the table.first create one data set and run the query like

select EnglishProductName,Color,ReorderPoint,SafetyStockLevel from dbo.DimProduct

In layout drag one table place the fileds REORDERPOINR,SAFETYSTOCKLEVEL,

take second data set which call variance data set

there run the query:

select

(

(case when [EnglishProductName]= [EnglishProductName] then [ReorderPoint]end )-

((case when [EnglishProductName]=[EnglishProductName] then [SafetyStockLevel] end )

) )as var_Blade

from dbo.DimProduct

drag onther table ,place next to the first table.

delete two coulmns in the table, leave on and drag second data set field into second table, u will get difference b/w reorderpoint and saftystocklevel.

i think it's not possible to display the difference in one table so better to take two tables.

I think this one u r looking for

i hope it's useful for u

sank

phina_el SSC-Addicted Points: 408 More actions · Answer 5

Hi All,

Thank you for all your attention.

I already have solution, for make the report as I want;

there is solution:

1) Insert a table group on Model

2) Insert Group footer

3) In the expression for the columns of May I can give as

=First(Fields!QTY_May.Value)-Last(Fields!QTY_May.Value)

and for June

=First(Fields!QTY_June.Value)-Last(Fields!QTY_June.Value)

and for July

=First(Fields!QTY_July.Value)-Last(Fields!QTY_July.Value)

...

and for Oct

=First(Fields!QTY_Oct.Value)-Last(Fields!QTY_Oct.Value)

Cheers,

fina_el