Crosstab Query - Populating with Zeros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to populate with zeros where there are currrently blanks in
cells. I am using the folllowing SQL code and I am getting a Syntax error
missing operator message. Any sugguestions?

TRANSFORM Val(NZ(Sum([Qry_AIT_FSE _COGS_Summary].[SumOfMonthly Sales],0)) AS
[SumOfSumOfMonthly Sales]
SELECT [Qry_AIT_FSE _COGS_Summary].Region, [Qry_AIT_FSE _COGS_Summary].FSE
FROM [Qry_AIT_FSE _COGS_Summary]
GROUP BY [Qry_AIT_FSE _COGS_Summary].Region, [Qry_AIT_FSE _COGS_Summary].FSE
PIVOT Format([Month],"yyyy-mm");

Thanks.
 
you don't need crosstab, you need analysis services

SSAS is 100,000 times more powerful than a relational crosstab
 
you don't need crosstab, you need analysis services

SSAS is 100,000 times more powerful than a relational crosstab
Click to expand...


Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Tony;

maybe you should learn SSAS

Displaying empty rows as a 0 is a simple setting in SSAS



Tony Toews said:
"T o d o s M e n o s [ M S F T ]" <t o d o s _ m e n o s _ m (e-mail address removed)>
wrote:

you don't need crosstab, you need analysis services

SSAS is 100,000 times more powerful than a relational crosstab
Click to expand...


Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Click to expand...
 
Bob said:
I would like to populate with zeros where there are currrently blanks in
cells. I am using the folllowing SQL code and I am getting a Syntax error
missing operator message. Any sugguestions?

TRANSFORM Val(NZ(Sum([Qry_AIT_FSE _COGS_Summary].[SumOfMonthly Sales],0)) AS
[SumOfSumOfMonthly Sales]
SELECT [Qry_AIT_FSE _COGS_Summary].Region, [Qry_AIT_FSE _COGS_Summary].FSE
FROM [Qry_AIT_FSE _COGS_Summary]
GROUP BY [Qry_AIT_FSE _COGS_Summary].Region, [Qry_AIT_FSE _COGS_Summary].FSE
PIVOT Format([Month],"yyyy-mm");

Thanks.
Click to expand...

Maybe:

Sum(Val(Nz([Qry_AIT_FSE _COGS_Summary].[SumOfMonthly Sales],0)))

James A. Fortune
(e-mail address removed)
 
Back
Top