Removing empty cells in column groups

Hi All,

I'm creating a report with row and column groups. But the columns groups are 
displaying values in sperate rows instead on the same row leaving empty 
cells. I need to remove those cells and get the column gropu values in a same 
row.

Please refer to this image

http://img526.imageshack.us/img526/7139/23561415.png
0
Utf
12/15/2009 9:25:01 PM
sqlserver.reportingsvcs 542 articles. 0 followers. Follow

4 Replies
654 Views

Similar Articles

[PageSpeed] 15

Hi
What dataset query do  you have? I think SELECT MAX(CASE WHEN .... ) should 
solve the problem

"Supun" <Supun@discussions.microsoft.com> wrote in message 
news:F842A7A9-7975-438E-B2E5-40C72166DF63@microsoft.com...
> Hi All,
>
> I'm creating a report with row and column groups. But the columns groups 
> are
> displaying values in sperate rows instead on the same row leaving empty
> cells. I need to remove those cells and get the column gropu values in a 
> same
> row.
>
> Please refer to this image
>
> http://img526.imageshack.us/img526/7139/23561415.png 


0
Uri
12/16/2009 8:58:31 AM
Dear Uri,
Thanks for your quick reply.

Actually I'm using this inside a asp .net project and I've created the 
report in vs 2010 beta 2. I'm using the following query and using row and 
column grouping.

SELECT        Items.Code, Items.Name, ItemUnits.Name AS Units, 
PEItems.Quantity, Supplier.Name AS Supplier, PESupplierAmounts.Rate, 
PESupplierAmounts.Amount
FROM            PriceEnquiry INNER JOIN
                         PEItems ON PriceEnquiry.Id = PEItems.PEId AND 
PriceEnquiry.Id = PEItems.PEId AND PriceEnquiry.Id = PEItems.PEId INNER JOIN
                         Items ON PEItems.ItemId = Items.Id INNER JOIN
                         PESuppliers ON PriceEnquiry.Id = PESuppliers.PEId 
INNER JOIN
                         Supplier ON PESuppliers.SupplierId = Supplier.Id 
INNER JOIN
                         PESupplierAmounts ON PriceEnquiry.Id = 
PESupplierAmounts.PEId AND Items.Id = PESupplierAmounts.ItemId AND 
                         Supplier.Id = PESupplierAmounts.SupplierId INNER JOIN
                         ItemUnits ON Items.UnitId = ItemUnits.Id
WHERE        (PriceEnquiry.Id = @PEId)

I don't understand how to apply your solution. Please expalin further

"Uri Dimant" wrote:

> Hi
> What dataset query do  you have? I think SELECT MAX(CASE WHEN .... ) should 
> solve the problem
> 
> "Supun" <Supun@discussions.microsoft.com> wrote in message 
> news:F842A7A9-7975-438E-B2E5-40C72166DF63@microsoft.com...
> > Hi All,
> >
> > I'm creating a report with row and column groups. But the columns groups 
> > are
> > displaying values in sperate rows instead on the same row leaving empty
> > cells. I need to remove those cells and get the column gropu values in a 
> > same
> > row.
> >
> > Please refer to this image
> >
> > http://img526.imageshack.us/img526/7139/23561415.png 
> 
> 
> .
> 
0
Utf
12/16/2009 3:06:01 PM
Hi
COL1     COL2      COL3
ITEM1    ITEM2    VALUE1
ITEM1    ITEM2    VALUE2
ITEM1    ITEM2    VALUE3
ITEM1    ITEM2    VALUE4

ITEM3    ITEM4    VALUE1
ITEM3    ITEM4    VALUE2
ITEM3    ITEM4    VALUE3
ITEM3    ITEM4    VALUE4

--------desired result

COL1  COL2      COL3         COL4        COL5         COL6

ITEM1 ITEM2    VALUE1    VALUE2    VALUE3    VALUE4
ITEM3 ITEM4    VALUE1    VALUE2    VALUE3    VALUE4

select
    col1
,    col2
,    max (case when col3 = 'value1' then 'value1' else null end)    col3
,    max (case when col3 = 'value2' then 'value2' else null end)    col4
,    max (case when col3 = 'value3' then 'value3' else null end)    col5
,    max (case when col3 = 'value4' then 'value4' else null end)    col6
from
    MyTable
group by
    col1
,    col2




"Supun" <Supun@discussions.microsoft.com> wrote in message 
news:167616D7-D815-4D98-B274-F25A3F1D47F7@microsoft.com...
> Dear Uri,
> Thanks for your quick reply.
>
> Actually I'm using this inside a asp .net project and I've created the
> report in vs 2010 beta 2. I'm using the following query and using row and
> column grouping.
>
> SELECT        Items.Code, Items.Name, ItemUnits.Name AS Units,
> PEItems.Quantity, Supplier.Name AS Supplier, PESupplierAmounts.Rate,
> PESupplierAmounts.Amount
> FROM            PriceEnquiry INNER JOIN
>                         PEItems ON PriceEnquiry.Id = PEItems.PEId AND
> PriceEnquiry.Id = PEItems.PEId AND PriceEnquiry.Id = PEItems.PEId INNER 
> JOIN
>                         Items ON PEItems.ItemId = Items.Id INNER JOIN
>                         PESuppliers ON PriceEnquiry.Id = PESuppliers.PEId
> INNER JOIN
>                         Supplier ON PESuppliers.SupplierId = Supplier.Id
> INNER JOIN
>                         PESupplierAmounts ON PriceEnquiry.Id =
> PESupplierAmounts.PEId AND Items.Id = PESupplierAmounts.ItemId AND
>                         Supplier.Id = PESupplierAmounts.SupplierId INNER 
> JOIN
>                         ItemUnits ON Items.UnitId = ItemUnits.Id
> WHERE        (PriceEnquiry.Id = @PEId)
>
> I don't understand how to apply your solution. Please expalin further
>
> "Uri Dimant" wrote:
>
>> Hi
>> What dataset query do  you have? I think SELECT MAX(CASE WHEN .... ) 
>> should
>> solve the problem
>>
>> "Supun" <Supun@discussions.microsoft.com> wrote in message
>> news:F842A7A9-7975-438E-B2E5-40C72166DF63@microsoft.com...
>> > Hi All,
>> >
>> > I'm creating a report with row and column groups. But the columns 
>> > groups
>> > are
>> > displaying values in sperate rows instead on the same row leaving empty
>> > cells. I need to remove those cells and get the column gropu values in 
>> > a
>> > same
>> > row.
>> >
>> > Please refer to this image
>> >
>> > http://img526.imageshack.us/img526/7139/23561415.png
>>
>>
>> .
>> 


0
Uri
12/17/2009 6:44:40 AM
Thank you very much, the grouping from the sql level solved the problem!!!
0
Utf
12/18/2009 7:39:01 AM
Reply:

Similar Artilces:

Prevent change to refs in second worksheet when drag cells in firs
I have a complex calendar in Excel. Each week, admins Ctrl-drag cells among different locations in the primary calendar worksheet. I have a second worksheet that needs to list those cell values in a fixed configuration, e.g. the value in Sheet1!A1 needs to always appear in the cell of Sheet2!G10. Sheet2!G10 contains the formula: “Sheet1!$A$1”. If the user drags Sheet1!A1 to Sheet1!A3, the formula in Sheet2!G10 gets automatically updated to “Sheet1!$A$3”. I have tried all combos of locking and protecting to prevent this updating. Is there a way to do this? Try referencing the other shee...

Excel Data Querry using criteria from Cell
Hello, I have created a data query in Excel using DATA - IMPORT EXTERNAL DATA - NEW DATABASE QUERY and then selecting Access and browsing to an query within an Access database. This query returns all data into excel, but I would like to limit the criteria based on what a user has typed into a cell in the Excel workbook. I thought this could be accomplished by clicking DATA - IMPORT EXTERNAL DATA then PARAMETERS but the PARAMETERS option is grayed out. I can select EDIT CURRENT QUERY, but this just brings me into the Microsoft query, where I don't see any options to query based on...

can I print selected cells only within a worksheet
I have designed a simple template using text only for a unique advice note. I want to be able to add variable information within this template but need only for the variable information to print onto a pre-printed advice note. I do not want to hide the template as I need to see exactly where to insert the variables but I do not want it to print. Anyone help? Thanks. Maybe you can use this ? http://www.rondebruin.nl/print.htm#Hide -- Regards Ron de Bruin http://www.rondebruin.nl "martin hanley" <martin hanley@discussions.microsoft.com> wrote in message news:AA062677-3...

How do I make two lines in one cell?
since you cant hit enter to make 2 lines in one cell how do you do it? Press alt + enter together. It's easy when you know how! :-) Alan P. "kpeters" <kpeters@discussions.microsoft.com> wrote in message news:CAEFD0EB-26F1-4AAB-9D4F-86DF4E013B9F@microsoft.com... > since you cant hit enter to make 2 lines in one cell how do you do it? if you mean that you want to add more text into the cell, then increase the row height and "wrap the text" "kpeters" <kpeters@discussions.microsoft.com> wrote in message news:CAEFD0EB-26F1-4AAB-9D4F-86DF...

Autofill of row or column
Greetings, Let's say I have a min, a max, and an increment, all decimal values. I want to autofill or autopopulate a row or column -- without manually dragging cels -- over the range [min,max] incrementing by the increment value each cell. Can this be done somehow? Also, what if you don't know how many cells you will need initially (you return some variable integer from a calculation)--can you tell Excel to fill only that number of cells each time? For example, let's say I compute some number and get a 7 one time and a 10 the next, and I want 7 cells filled with data and then ...

Absolute Values and Column Insertions
I've created a formula in column A that adds up some cells in column D. Ex: =sum(D3:D6). If I insert one column in front of column D, the formula changes to =sum(E3:E6). This happens even if I change the formula to =sum($D$3:$D$6). I also tried creating a range name called TEST referring to the original $D$3:$D$6 range and the column insertion changed the TEST range definition as well. Is there a way to keep a formula from NOT adjusting to column insertions or deletions? Thanks in advance! Please don't multi-post See answer in other newsgroup -- Kind regards, Niek Otten Mic...

how can I split a single cell diagonally in Excel 2000
Anyone out there know a way to split a single excel cell diagonally in order to have it contain 2 pieces of information? Forrest, As far as I know, you can not split a cell diagonally so that it can contain 2 pieces of information. However, you can merge cells which might give you the effect that you want. To do so, go to the standard toolbar and hit Format -> Cells -> Alignment Tab -> Text Control and work with the merged cells option. ---- Regards, John Mansfield http://www.pdbook.com "Forrest" wrote: > Anyone out there know a way to split a single excel ce...

How to average a column, but exclude zero AND negative values?
I used the formula of =AVG(IF(A1:A10)<>0,A1:A10)) to exclude just the zero values, but what about zero AND Negative? Thanks!! =AVerage(IF(A1:A10)>0,A1:A10)) Still array entered. KMHarpe wrote: > > I used the formula of =AVG(IF(A1:A10)<>0,A1:A10)) to exclude just the zero > values, but what about zero AND Negative? > > Thanks!! -- Dave Peterson ...

print column labels with one row of data at a time.
I am a teacher. I have created my gradebook in excel. (I don't have access). I have 7 worksheets (one for each class that I teach). The worksheets are not exactly the same. I would like to be able to print out the top two rows (column labels) with each students data (row). This way the student can see row 1 (name of assignment), row 2 (points), their row (their personal data for each assignment). Any help?? Go to File > Page Setup > Sheet Then in the "Rows to repeat at top" highlight the label headings row(s). HTH -- _______________________ Naz, London...

Calculated Columns Help
FIND/SEARCH functions both return #DEFAULT! if string is not found. If it is not found, I'd like to display a different value (one of the other columns). All my attempts to check if the calculated column = "#DEFAULT!" fail. It is as if it is not really a string. How can I test for #DEFAULT! value? ...

How to Link to the next blank row after the last entry in a column?
Cell BM6 Sheet1, has a formula that gives me a numeric total. The total belongs in Column D1 Sheet2 after the last entry, and there will be cells that will remain blank in that column before the last entry. Sheet1 is a work sheet, which will be saved with a new file name at the end of the month after the totals have been sent to Sheet2. Sheet2 is a permanent, ongoing record. The numbers on original Sheet1 will then be erased and the worksheet reused for the new month. :confused: How do I get the values into the appropriate row of Sheet2 and have them remain there when I reuse the workshee...

Setting column width on different computers
I have a program which checks workbooks and corrects those errors which it can correct. One check is on the column widths: Column A should be 1.43 characters wide and column G should be 8.86 characters wide. This works fine on 99% of the computers where it is used but we have one or two where if you change the width of column A to 1.43 it goes to 1.44, and for column G if you set it to 8.86 it goes to 8.89. This means that the next time the check is run, the workbook is deemed a failure. The difference appears to be the screen resolution: on the computers where it works the...

Removing the default root attributes when serializing
In an application I'm making I've got a config object which I would like to serialize to the XML format in a .config file which would then be loaded the next time the application starts up using Configuration class. I've got all the elements and attributes correctly named but the default namespaces that get added when an object is serialized are causing the program to crash when it loads the .config file. These are the attributes I mean - xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" I know there are easier ...

Copy/Paste with Hidden Rows/Columns Excel 2007
I am working with rather large & detailed spreadsheets. I have hidden rows and columns with supporting details that feed into the viewable results (formulas). I need to take the “zipped up” data and copy/paste to a new worksheet. When I paste the data, it appears with all of the hidden columns and rows visible one again. Is there any way to paste the zipped up data without the reappearance of the previously hidden rows and columns? Can you paste a link without all of the hidden data reappearing? Select range to copy which includes hidden rows and columns. F5>Special...

Cell Reference for the Color of a cell?
Hello to all you beautiful Excel users hope you are grand! Reason I write today is this: Is it possible to create a Cell Reference to another Cell so tha information such as the Border of the Cell Border OR the Color of i will transfer to another Cell? For instance: Cell A2 is red and has an underline border and I would like to create reference that makes D4 red and with an underline without having to us the tool bar. Then if I change A2 to green D4 will turn green. Please respond. Thanks Ti -- Message posted from http://www.ExcelForum.com You can't do this kind of thing with a f...

How can i create a 2-D stacked column within a 4 series stock char
I am trying to create an excel chart that combines the features of a 100% 2-D stacked column (for comparison of 2 variables) and the 4 series stock chart. I would desire to have my ideal chart being a 5-series stock chart with the data variables arranged as 'Volume1-Volume2 (stacked up as in 2-D column)-High-Low-Close'. This is slightly different from the current 5-series stock chart available in the excel spreadsheet. Can anybody help please? Thank you. Sincerely, Samuel See http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html http://peltiertech.com/Utility/ClusterStackU...

removing exchange2003
Following artical 839356 step 6, in which you delete the exchange container in active directory using ADSI. After doing so I restarted the server. Then went to my GC/DC and tried to access the Users and Computers snapin; it gave me a error that it could not connect to active directory,this was the same for sites and services etc. I restarted the server and tried again. Everything worked fine. I have two questions. What happened? Would the exchange 2003 setup.exe /removeorg do the same thing as the above mentioned artical. thx jason ...

Event 6398
I got the error 6298 below every 5 minutes on my Sharepoint server (MOSS 2007) Tried couple of things to no avail. Your help is greatly appreciated. Bill --------------------------------------- Log Name: Application Source: Windows SharePoint Services 3 Date: 12/11/2009 11:00:04 AM Event ID: 6398 Task Category: Timer Level: Error Keywords: Classic User: N/A Computer: WEBPORTAL.jacooil.com Description: The Execute method of job definition ScheduleAlertJob.ScheduleAlertJobDefinition (ID 2d1ee7c5-0ee5-46b9-9047-b35bf78afda2) threw an exception. More information is...

Removing the title bar
Hello, I am developing an SDI application, based CFormView. I would like to prevent the user from moving the window around. How do I do that? Eitan Override OnNcHitTest; when you see the superclass return HTCAPTION, change it to HTNOWHERE (or some similar name, I'm trusting memory here). Be prepared to have your users searching you out with barrels of tar and plenty of feather pillows. joe On Thu, 29 Sep 2005 19:34:02 -0700, "Eitan" <Eitan@discussions.microsoft.com> wrote: >Hello, > >I am developing an SDI application, based CFormView. I would like t...

split text in cell into 2
I am needing help with formula. I have a cell that takes values from several other cells to create one long line text (for a command used in other app). For ex: a1=jack b1=ran c1="There was a person named"&a1&"who was hungry and"&b1&" to Mcdonalds." This process works great except when the line is greater than 72. I need an if statment that says if c1 length is greater than 72 then to split / replace line AFTER name(a1). Where the split occurs a +, and carriage return needs to be added. So in the example above (pretend length will be over 72,...

Display nothing in a cell if value = 0
I have a range of cells formatted to display numbers in currency format. They have a formula applied to them so if the value of the Cell is nothing I get �0.00 showing. I would rather that nothing was shown in these instances. How is this achieved? Thanks Dom Couple of ways, use conditional formatting and display with a white font if the value is zero change the display option to suppress zeroes, Tools>Options>General and uncheck the Zero Values option. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mail...

Outlook links unavailable with configured group policy
we have a strange behavior with Outlook 2003 on Windows Server 2008 Terminal Server. if you click on an embedded link the installed Internet Explorer 7 opens trying to follow the link but ends in an 'unavaible' error message. if you refresh the page (F5)the webpage will be displayed. through group policies the IE connection (preference mode) is configured to use our usual proxy server through the automatic browser configuration and configured auto-proxy URL using a .pac file I have investigated the user config and found that through this policy the HKEY_CURRENT_USER\S...

Profile can not be removed.
There is a profile in my outolook whicj I wanto to removed and I can not do it. I reistall outlook 2003 and the problem presist. can somebody help me out? Jorge How are you trying to remove the profile? "Jorge" <jorgezalles@hotmail.com> wrote in message news:fa7401c43e5f$5c2f3770$a401280a@phx.gbl... > There is a profile in my outolook whicj I wanto to > removed and I can not do it. I reistall outlook 2003 and > the problem presist. can somebody help me out? > > Jorge you can remove the profile by deleting all the outlook's files and at the registry...

remove white space without changing plot size
Now I know the white space can be removed by draging plot area but without changing the size of plot I like to remove the white space. Is that impossible ? Not impossible, but it is hard to maintain the plot area size. You have to first make the outer chart area rectangle as small as you need, then make the plot area as large as you need. You'll probably have to cycle through resizing of the two rectangles a few times. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ pm wrote: > Now I know t...

Cell shading display area
Hi This is an issue on a friends PC. I have looked it over and could not find anything that would cause the following issue. In Excel when they color or shade a cell it does not show up on the screen. It does show up in preview and when it is printed. On another PC the same sheet will show up with color or shading. Its a Gateway desktop, Windows XP and Office XP. Any ideas? The following MSKB article may help: OFF: Changes to Fill Color and Fill Pattern Are Not Displayed http://support.microsoft.com/default.aspx?scid=kb;en-ca;Q320531 -- Regards Ken.................