Problem with Excel Pivot Table - Microsoft SQL Server

Bajal Mohamed via SQLMonster.com

I am facing problem like;

We generate reports thru Excel Pivot table. The data is coming from sales
cube. Let?s assume that there are 2 products A and B. The user wants to
filter for each product and view ?stock in hand? and ?total sales? for
particular month (as two columns).

From the Pivot table filtering, when we choose product A and month ?Jan?,
it is showing values for ?stock in hand? and ?total sales? columns for that
month.

But when we choose product B and month ?Jan?, it is only showing ?stock in
hand? value and the ?total sales? column was suppressed/unseen. We know
that there is no sales transaction for ?Jan? month. But we still want to
show the column title even if there are no records in the database / cube.

I am not using any scripting, this pivot table was generated just thru
Pivot table wizard.

Any way to achieve this, please advise me.

Advanced thanks for u r help.

--
Message posted via http://www.sqlmonster.com

Jul 23 '05 #1

Subscribe Reply

2079

Erland Sommarskog

Bajal Mohamed via SQLMonster.com (fo***@SQLMonst er.com) writes:

I am facing problem like;

We generate reports thru Excel Pivot table. The data is coming from sales
cube. Let?s assume that there are 2 products A and B. The user wants to
filter for each product and view ?stock in hand? and ?total sales? for
particular month (as two columns).

From the Pivot table filtering, when we choose product A and month
?Jan?, it is showing values for ?stock in hand? and ?total sales?
columns for that month.

But when we choose product B and month ?Jan?, it is only showing ?stock in
hand? value and the ?total sales? column was suppressed/unseen. We know
that there is no sales transaction for ?Jan? month. But we still want to
show the column title even if there are no records in the database / cube.

I am not using any scripting, this pivot table was generated just thru
Pivot table wizard.

I'm not really sure why you are asking this in a newsgroup for
SQL Server. It sonds like an Excel issue to me.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #2

Similar topics

11935

Code for query to "Export to Excel" as pivot

by: John | last post by:

Is there a way to code the button that's available in the query window--microsoft excel icon that exports to excel. I know transferspreadsheet will do this---but I want the query, which is in a pivot table view, to be exported as a pivot, not just a data list. Does this code exist? Is it transferspreadsheet but with a twist?

Microsoft Access / VBA

2355

start client excel application from web

by: Grey | last post by:

My requirement is The user has input data in the asp.net application and the data has been stored in MSDE. After the user click the save data in MSDE, the web application will start the client's excel application file with pre-defined privot table template. when the excel application start, the data in pivot table will downloaded from MSDE and finally display the up-to-dated data in the excel pivot table. The user need to do nothing to...

ASP.NET

3230

export data into excel file

by: Grey | last post by:

How to export a dataset to excel file. I need the exported data should be named with variable in excel as I need to do pivot table in the excel. So do I need to created pivot table before and only export differenet to the excel from time to time or created pivot table dynamically with code after adding the data into the excel?? if the later one, where should I program the code, in ASP.NET or excel?? and how to code that?? Million thanks

ASP.NET

1511

OWC Pivot Table export to excel problem

by: richi | last post by:

Hello I have a funny one that I'm hoping somebody can help me out with. I have populated my OWC 11 Pivot Table hosted in a browser with XML data and set up the pivot table to disable details and expanders. The user is allowed to filter the data in the normal way but when they export to excel they get the filtered data they wanted but other data as well. How do I get the export to show only the details that the user wants i.e.

ASP.NET

4877

trying to copy an Access 2003 pivot table to excel or word .... or something

by: Jerome Ranch | last post by:

Okay So I've got pivot tables setup in Access 2003. Only about 30K records in the current 2005 databases...the pivots summarize the info in a number of nice ways. I need to get the pivot tables into a document so I can distribute the tables, as is, as an electronic report (without the detailed data) So I export to rtf and xls, and I get an error that there is a too much information. I don't want all the data, just the summary table!

Microsoft Access / VBA

2401

Converting excel to Access

by: Decreenisi | last post by:

Hi, I have to convert an existing spreadsheet for rejects into an access database. My problem is I use a load of lookup tabels in excel. How do I approch this in access. Also, just a general question, can you do pivot tables in access. Thanks

Microsoft Access / VBA

8494

Excel Pivot tables in vb.net

by: nikila | last post by:

Hi, I have to create excel pivot tables from vb.net. Already I am creating excel file using oledb connection. I want to use the same to create the excel pivot tables. Can anyone please help me on this. It is very urgent. Please reply immediately Thanks for the help!!!! nikila

Visual Basic .NET

2638

Data From Access Pivot In Excel

by: benchpolo | last post by:

I have data extracted from Access db to Excel with a pivot table. Somehow, I am having issues with the pivot table were it doesnt update the totals. For example, the first extract i did in Access has 6000 data rows and pivot table in excel should refresh on open and reflect the 6000 data rows. 2nd extract in Access has 10000 data rows when pivot table opens it doesn't refresh the summary count on the pivot table based on the 10000 data...

Microsoft Access / VBA

9425

Using .Net to create Excel Pivot table

by: Gary Dunne | last post by:

Hi All, I'm struggling to find the correct syntax for creating a Pivot table in an excel file via VB . Net 2005 The Excel help file is not particularly helpful where parameters are concerned (It rarely mentiones the required data types etc) The following is as far as I can get; Dim excelApp As Excel.Application

Visual Basic .NET

9714

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9594

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10600

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10351

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

10096

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

9174

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

5673

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3834

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

3002

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General