C'mon!! crosstab EXPERTS!!!

[axxo]

BAHH!! Here's the problem...

I have a crosstab. The row is [region] column is [month]
e.g.
           July August  September etc....
RegionA  3    1          6
RegionB  1    5          1
RegionC  9    1          0

Okay...
in the query that is used by the crosstab there is a data item [journal] with an expression. This data item counts the number of times a certain thing happens and turns it to a negative value (not so important how it does it). If I were to add this to the crosstab it would look like this.

           July August  September etc....
RegionA  3    1          6
RegionB  1    5          1
RegionC  9    1          0
Journal   -2   -5        -1  (however this column needs to aggregate along the way - see below!)


This is my problem... I want this row to aggregate journal columns along the way
e.g.

           July August  September etc....
RegionA  3    1          6
RegionB  1    5          1
RegionC  9    1          0
Journal   -2   -7        -8

The person who solves this for me will hold a special place in my heart forever.

[rockytopmark]

I don't have time to work on this myself, but the solution will involve using the Tuple() function, assuming you are using DMR or Powercube data source.

If not, I would suggest running-total()

[axxo]

Hi rockytopmark,

Well initially it was modelled dimensionally and I was told to go query subjects as there were no drill ups or downs for this report. I never got far enough with the dimensional model to test this... but I've already tried running-total() which gives unexpected results (larger numbers and not aggregating).

[rockytopmark]

the key to running-total is to invoke the "for" clause properly.  You also need to ensure your running-total query is sorted as you want the total to build.

This would be much easier accomplished with DMR and utilizing Dimensional Reporting Techniques, which are really reserved for Crosstabs and Graphs, where we are dealing with aggregation of data at intersections of descriptive information... just like your report!

[axxo]

Interesting comment about using DMR for crosstab type operations. I think this way, the level within the hierarchy within the dimension can be directly accessed.

I'm gonna try this at work tmrw (and maybe convince my boss at the same time about using DMR ;))

So this is an example I found on the net:
running-total([Quantity] at [Order year], [Order month] for [Order
year])

for the crosstab:

                 Order Month
Order Year

where quantity is the measure.

Thanks and will let you know if it worked.

[Rajesh_Vanam]

Try this also,

running-total( [measure] for [row_item])

Also give aggregation propoerty for this dataitem as Calculated.

[Gollapudi]

Hi Axxo,

I think I have done it, I just took the following expression to achieve it...

running-total(total([Quantity] for [Order year]) for report)

Attached is the sample report for "Go Sales and Retailers" sample Package.

Thanks,
Gollapudi.

[axxo]

Thanks Gollapudi,

I will go back and try that next week (I just rebuilt the model to be dimensions after I couldnt get it to work with query subjects)

Will post as soon as I have success.

Axxo

[Gollapudi]

Your most welcome!! Axxo, let me know once u dont it... the example which i have sent you is relational package based...

[axxo]

The problem is more complicated than I first thought here's why.

I have a column in my table with 2 things in it A and B (type of invoice). When the user runs the report they can select either A or B. The crosstab shows the count by month / region for number of invoices for either A or B - no problem however the journal line under the crosstab is a problem because, it needs to use a different measure to the one in the crosstab. Crosstabs only use one measure.

Let's say I select A from the prompt - the crosstab come out nicely. However ,the journal line needs to use a different measure - it needs to count the number of invoices allocated to B (unlike the above crosstab that counts A).

I have a small theory that I could solve it if I was able to repllicate the IIF statement in Access.
ColA: iif([col]=A, [col],null)
ColB: iif([col]=B, [col], null)
to produce:

Col   ColA   Colb
A      A   
B               B

But I can't in Report Studio :(

[axxo]

I've solved all my report problems except for the aggregation one. But at least that's all I have to work on now ;)
My crosstab: (The Journal YTD line is what I need to create but I can't using any of the above methods :()

                  Date
Region         Jul   Aug   Sep
    1             5     3      3
    2             1     4      1
    3             2     1      9
Journal         -6   -2     -1
Journal YTD   -6   -8     -9

[axxo]

This is the error I receive

Initially, in data source type(s) 'PC', function 'ces_runningtotal' is not supported in 'OlapQueryProvider'. After decomposition, in data source type(s) 'PC', function 'ces_runningtotal' is not supported in 'OlapQueryProvider'.

based on
running-total([Quantity] at [Order year], [Order month] for [Order
year])
substituting for my own data item names

[axxo]

Well I've solved the problem and will post how I did it when I have the time - shortly I hope. Now the only remaining problem is to create some total rows of non-conforming dimensions of the crosstab - I have a feeling I'll need to use tuple()


What I have is a crosstab with 3 nested nodes at the top and 3 nested nodes at the bottom. What I need to do is to add the overall total of the 3 top nodes (I have something like a [Total Node 1] column) Then grab the second nested node in the bottom section and sum the 2 together.

It doesnt like me usign a tuple from 2 different sets of nodes
The dimensions on the edge are inconsistent. A dimension is not found on the first path 'valueSet=8'