[RESOLVED] How to show empty columns at crosstab ?

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

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 .

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

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

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

ARPRINCE,

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

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

Example of the result I need:

VB Code:

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

Well,

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

Just made changes in SPs...

[]s

João Luiz