[RESOLVED] How to show empty columns at crosstab ?

[Jlarini]

Hi, there!

I need to show some colums at crosstab, but I've had some problems, the description of the situation is:

1) I execute the CR report from an asp page which sends me the parameters used to execute a SP at SQL Server;

2) The result from SP execution has values this respective months...

Here starts the problem :rooleyes:

This result has some months absent to some groups....

For example:

Group 1: Jan, Feb, Mar, Apr, Mai, Jun, Jul, Aug, Sep, Oct, Nov, Dez (all months)

Group 2: Jun, Jul, Oct, Dez (only this months)

So the Reports shows me:

Group 1: Jan Feb, Mar, Apr, Mai, Jun, Jul, Aug, Sep, Oct, Nov, Dez
1 2 4 6 7 3 1 3 4 5 6 7
Group 2: Jun, Jul, Oct, Dez
19 3 1 1
That's ok, but it isn't fine....

I need that report shows me:

Group 1: Jan, Feb, Mar, Apr, Mai, Jun, Jul, Aug, Sep, Oct, Nov, Dez
1 2 4 6 7 3 1 3 4 5 6 7

Group 2: Jan, Feb, Mar, Apr, Mai, Jun, Jul, Aug, Sep, Oct, Nov, Dez
19 3 1 1

or

Group 1: Jan, Feb, Mar, Apr, Mai, Jun, Jul, Aug, Sep, Oct, Nov, Dez
1 2 4 6 7 3 1 3 4 5 6 7

Group 2: Jan, Feb, Mar, Apr, Mai, Jun, Jul, Aug, Sep, Oct, Nov, Dez
0 0 0 0 0 19 3 0 0 1 0 1

How can I do that, without any change on the SP ? The table has no values at the absent months, better saying, the table doesn't have these months...

I'm using CR 8.

Thanks in advance,

João Luiz

[ARPRINCE]

Fix your SP and not the report since it is your data which is missing the information. Using SQL Server BOL, search for CROSS TAB REPORTs .

[Jlarini]

ARPRINCE,

The problem is, when I don't have values at the month, this month doesn't exist... So I have no information about this month, no value and no month...

As I said before...

I think this solution doesn't apply for my problem...

[]s

João Luiz

[ARPRINCE]

Here's a more "CONVINCING" example. The middle part is the data. You could see that you have missing MONTHS for both groups 1 and 2.

The last part is the result when running the SQL statement (first part).

HTH

[Jlarini]

I didn't try it yet, but by reading, it looks fine...

I'll try it and let you know the results!

thanks!

João Luiz

In time...

Do you have any clue about this ?

http://www.vbforums.com/showthread.php?t=432267

[Jlarini]

ARPRINCE,

Now I have the same problem, but, a little different...

I have four fields... (see attach...)

Example of the result I need:

Group: 1
 
      Jan    Feb    Mar   Apr .... and so on... With 0 as I have no values for these months...
 a   100      0       10     0
 b     22      0     121     0 
 c     22      0     212     0
 d     15      0       22     0
 
Group: 2
 
      Jan    Feb    Mar   Apr .... and so on... With "0" as I have no values for these months...
 a      0      5     121    12
 b      0     57      12    21 
 c      0     74      21    22
 d      0      7     111    22

Important: I'm using SQL Server's Stored Procedure...

Tks,

João Luiz

[Jlarini]

Well,

It's resolved, by myself, after a lot of work...

Just made changes in SPs...

[]s

João Luiz