Analysis :: How To Block Writing In Total Cells When Use Excel And Write Back
Nov 16, 2015
I have a cube with a partition configures in write back.
Users in Excel need to see the totals of the line. Unfornately they have the bad idea to write in this cell sometimes and not in the leaf cells.
As there is some MDX code behind in the weight expression field, we got some weird values : one is negative and others ARe 10 times the initial value in the total. So it's very dangerous.
How can we block the writing in this totals cells ?
View 2 Replies
ADVERTISEMENT
Jul 24, 2007
Hi all,
When i creat a report model with report builder, i don't know how to hide aggregate cells.
Here is a picture about my problem :
Cells aggregate (in green) are the total of my sales amount, but i dont want it, i just want the non aggregate value (cells in white)
http://img370.imageshack.us/img370/8408/ventesga4.jpg
Regards,
Erwan, Toulouse
View 1 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Jun 28, 2005
Hi
i need to sum the values in different cells of an excel sheet which im getting from data base and which is in a loop and the number of entries depends on the user.Please help me out
im adding the code please check it out:
<%
Response.ContentType = "application/vnd.ms-excel"
%>
<html>
<body>
<%
dim objConn
set objConn=server.CreateObject("ADODB.Connection")
objConn.open "provider=SQLOLEDB;data source=10.100.17.107;initial catalog=RUT;user id=test;password=test;"
iMonth = Request.QueryString ("month")
iyear = Request.QueryString ("year")
intpid = Request.QueryString ("project")
inteid = Request.QueryString ("eid")
dim fDate
DIM fmonth
Dim fi
if iMonth = "February" and iYear mod 4 <> 0 then
iTo = 28
elseif iMonth = "February" and iYear mod 4 = 0 then
iTo = 29
end if
if (iMonth = "April" or iMonth = "June" or iMonth = "September" or iMonth = "November") then iTo = 30 end if
if (iMonth = "January" or iMonth = "March" or iMonth = "May" or iMonth = "July" or iMonth = "August" or iMonth = "October" or iMonth = "December") then iTo = 31 end if
dim rsobj1,rsobj2,rsobj5,rsobj9
sql1 = "select first_name from tblUserMaster where emp_id='"&inteid&"'"
set rsobj1 = objConn.Execute (sql1)
sql2 = "select distinct activity_id from tblTimeSheet where month='"&iMonth&"' and year='"&iyear&"' and project_id='"&intpid&"' and emp_id='"&inteid&"' and bill_non = ''"
set rsobj2 = objConn.Execute (sql2)
%>
<b>Employee Name:<%=rsobj1("first_name")%></b>
<TABLE BORDER=1>
<TR>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></td>
<TD ><%=imonth%></TD>
<TD ></td>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ><%=imonth%>
</TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ><%=imonth%></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ><%=imonth%></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
</TR>
<TR bgcolor="#ffffff" style="FONT-FAMILY: fantasy; BACKGROUND-COLOR: powderblue">
<td ><b>Client/Assignment</b> </td>
<td ><b>ProjectName</b> </td>
<td ><b>Remarks</b></td>
<td><b>Total Hrs</b></td>
<%for i = 1 to iTo
fmonth=imonth
fi =i
fDate = fi & "/" & fMonth & "/" & iYear
Response.Write "<td align=center><b>" & i & "</p>" & weekdayname(weekday(fdate)) & "</b></td>"
next%>
</TR>
<%'===============================================d efault activity============================%>
<% do until rsobj2.EOF%>
<tr>
<%
dim intactid,rsobj3
intactid = rsobj2("activity_id")
sql3 = "select activity_name from tblActivityMaster where activity_id = '"&intactid&"'"
set rsobj3 = objConn.Execute (sql3)
sql4 = "select no_of_hrs,remarks,date from tblTimeSheet where month='"&iMonth&"' and year='"&iyear&"' and project_id='"&intpid&"' and emp_id='"&inteid&"' and activity_id='"&intactid&"'"
set rsobj4 = objConn.Execute (sql4)
%>
<td><%=rsobj3("activity_name")%></td>
<td></td>
<td><%=rsobj4("remarks")%></td>
<td></td>
<%do until rsobj4.eof%>
<%if (rsobj4("no_of_hrs") < 8 ) then%>
<td><%=rsobj4("no_of_hrs")%></td>// I need to add the values i get in this td and display in the td with red colour and the number of values may change according to the user
<%else%>
<td>8</td>
<%end if%>
<%
rsobj4.movenext
loop
%>
</tr>
<%
rsobj2.movenext
loop
%>
Thanks
ajith
View 1 Replies
View Related
Jul 26, 2007
Hi all,
I would to take a value of a cells in my matrix to load an other. Like we usually do in Excel
A2 = A1 * 100 for Example...
An idea ?
Regards,
Erwan, France
View 3 Replies
View Related
May 15, 2007
Hi
I'm trying to eliminate merged cells when exporting a report to Excel. My problem being my report textbox above my table.
I've searched and found that making the textbox the same width as the first column eliminates the merging. Perfect, it does. But when the report is viewed in the report viewer, the textbox can only expand vertically. So the title looks terrible because column one is not wide.
I've read that expand horizontally is not an option
I don't seem to be able to tell it not to output that text box, which would be an option if there is no other answer
I can't have the text box the size I want it, due it creating merged cells
I don't want to export it as a CSV
Are there any other options available or am missing something?
Cheers
View 5 Replies
View Related
Apr 7, 2006
Hi,
Can any tell me how to check whether any Comment been entered in a Cell in the Excel Sheet?
It is pretty urgent. Solutions are greatly appreciable.
Thanks & Regards,
Prakash Srinivasan.
View 2 Replies
View Related
Mar 25, 2008
Hi,
I created a report in list view (I have to use a subreport in it) and when I export it to excel the cells are shifted even in the header. The html is ok - the problem is only with export.
like this:
Title First Name Last Name Address
Mr John Smith Peartree Str.
I tried to fix the size and location on every cells, but this does'n work in export. Some of the cells drop at the next row and some of they are merged. I was able to receive some good export - but only with a lot of attempts of cells resizing.
Is there any way to avoid this shifting?
View 5 Replies
View Related
Dec 28, 2006
I have a report which was in RS 2003 and exported to Excel fine. Now when I export the same report using RS 2005 to Excel I get gray cells at the bottom of each table. I have the background of the body set to transparent. I have tried setting the background of the body to white and other colors. Those colors show up but the gray is still there as well. In preview mode and export to pdf I do not have those problems. Basically gray cells are showing up where they should not be. Any help would be great.
View 4 Replies
View Related
Feb 5, 2007
Anyone know why cells within a matrix that are formatted as numeric export to Excel with a cell format proprty of "General"? Cells within a table however export with an appropriate format.
Thanks
View 1 Replies
View Related
Apr 30, 2008
Hi
I have matrix, in data cells I want to perform calculation based on value in this cell and next/previous cell. Something like : "=B3-A3" in MS Excel.
How can I get the value form next/previous cell in matrix?
There is Previous function but it does'nt work in matrix. Are there workarounds?
Andriy Zhornyk
View 2 Replies
View Related
Dec 3, 2007
Hi all,
I have a basic matrix report but whenever I export this report to Excel, some of the cells end up becoming merged. Is there any way to avoid this using Reporting Services instead of formatting the cells directly in Excel?
Any help is greatly appreciated!
View 12 Replies
View Related
Feb 10, 2008
I load data from excel and sometimes I have problem with format of excel's cells. For example format of cell should be "general" but one of cell is "custom" and I get wrong data but if I just change format in excel to "general" - data is correct.
Can I change or check format of cells in SSIS ?
View 1 Replies
View Related
Dec 4, 2007
Is it possible to block updates from Excel but still allow selects? We're using SQL Server authentication for the time being and a number of users use Excel to query the database. One user has figured out that Excel can also send data to SQL Server. Is there a straightforward way to prevent this?
View 1 Replies
View Related
Nov 24, 2015
I am using
SSAS: version 2008
Excel: version 2013
I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:
DateKey:
- Type: System.Integer
- Value: yyyyMMdd
Date:
- Type: System.DateTime
DateStr0:
- Type: System.String
- Value: dd/MM/yyyy (note: I am not using US culture)
- Example: 01/11/2015
DateStr1:
- Type: System.String
- Value: %d/%M/yyyy (note: I am not using US culture)
- Example: 1/11/2015
Filtering on date is working fine:
Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:
I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:
But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.
For date values of my data coming from cube, excel consider them as 'General'
2.1. But value here is same as it was in simple test.
2.2. 'Date Filter' works just fine.
2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell.
2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.
2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.
So, I don't know, what is the puzzle piece that I am missing.
1. Date filter works, group does not work
2. Excel consider it as 'General' string.
3. SSAS does not allow to change 'NameColumn' to Date.
View 2 Replies
View Related
Sep 10, 2004
Hello everybody,
I have a dataset that i read from an xml file.
I need to import this database back in to sql..
Is there a easy way.. Or do i need to loop thorugh each record in the dataset to import it.
Thanks
Chris
View 2 Replies
View Related
Feb 27, 2008
I have around 600 worksheets that i need to import into sql server that are in a somewhat non-table like format. Data defractor seems to be able to do what i need, but i also need to check into doing it manually. I've seen the code to import cells and ranges into sql server, but can't seem to remember what it is anymore. I've also searched through the forums and can't seem to find any examples either. Could anyone post an example or a link with some examples or explaination code. Thanks in advance.
View 4 Replies
View Related
Sep 3, 2015
I have created an SSIS package which processes daily financial information to a sql server database. These processes are to be outputted to excel spreadsheets to a readable report format for management to review. Some of these reports are laid out in a way that is not just tabular output but requires customized placement of data on an excel spreadsheet to specific cells.
I am able to place an initial resultset of a query output from the database in a tabular excel template through SSIS but the issue is at the end of that placement in the spreadsheet I am required to place another output below that tabular output in a different format from the initial output which I have shown below.
View 9 Replies
View Related
Mar 12, 2008
Help,
I have a package that maps the current getdate to a variable.
I then want to be able to write that variable back to a table using a sql task using the following expression:
"INSERT INTO CONTROL_BATCH_HEADER VALUES (
" + (DT_STR, 10, 1252) @[Control::intAdtBatchId] + ",
'" + @[Control::strSubjectArea] + "',
convert(datetime, '" + (DT_STR, 30, 1252) @[Control::dteRunDate] + "',131))"
The problem is the date is being written back in the wrong format. I've tried changing the mask to 121 but the month is being written as the day.
Help, how can I make this work regardless of the locals. In oracle I would just put a to_char(date,'dd-mm-yyyy') but not sure what to do here.
Thanks for your help
Stapsey
View 1 Replies
View Related
Mar 4, 2008
Hi,
Is there a way to write to a log-table inside a transaction which is rolled back without rollback of this log-entry.
thanks in advance
Raimund
View 9 Replies
View Related
Jun 19, 2015
I'm trying to insert records into "holding" table and write back identity column value (Entry_Key) to the original table. So my setup is I have two tables; tblEWPBulk and tbleFormsUploadEWP. Users will enter records into tblEWPBulk and use BatchID to group records, once batch entry has been completed (usually less than 30 records) user will click on UploadAll button and insert records (not all fields) into tbleFormsUploadEWP. One record in tblEWPBulk can be sent multiple times to the holding table but tblEWPBulk will need to have latest Entry_Key captured. Records are sent from holding table to DB2 z/VSE using SQL stored procedure and based on certain logic records are marked uploaded or certain error capture... that part works fine.
So for example I want to send
BatchID, AccountNumber, Period, ReceiveDate, AccountType, ReturnType, NetProfitOrLoss, TaxCredit FROM tblEWPBulk to the holding table and write back Entry_Key (identity column) back to the record in tblEWPBulk (field called UploadEntryKey). As I said one record could be sent to the holding table multiple times until uploaded or deleted and UploadEntryKey always needs to be updated so that when results are processed response from the DB2 can be inserted into table and presented to the user.
No foreign key relationship exists since records in the holding table get sent to the archive table and table is truncated and entry_key starting value reset back to 2000... just some DB2 restrictions.
View 5 Replies
View Related
Jul 14, 2014
How can i get the total number of each distinct value in the column and write it to another table e.g.
MyTable
Id Fruit
1 Apple
2 Banana
3 Apple
4 Watermelon
5 Banana
6 Watermelon
7 Apple
Result
Fruit Count
Apple 3
Banana 2
Watermelon 2
View 9 Replies
View Related
May 11, 2007
I have the membership stuff up and running. I've added a field to the membership table called custnmbr. Once a user logs in, I want store his custnbmr in the session and use that to lookup data in another db.
ie: Joe logs in and his custnumbr is 001, he goes to the login success page and sees his list of service calls which is:
select top 10 * from svc00200 where custnmbr = 001 (the membership.custnmbr for the logged in user)
I know how to do this in old ASP using session variables....but I have no idea where to even start with .Net.
Many thanks
View 7 Replies
View Related
Jul 30, 2015
I've got measure a in one table and measure b in another table and calculation a*b.
a b calc
2 4 8
5 2 10
SUM 7 6 18
How to get 18 as total instead of 7*6=42. I'd like to get row level calculation to sum up for the total level...
View 3 Replies
View Related
Sep 3, 2015
i am getting error for the mdx query fortotal profitis there any other way to write??????
View 2 Replies
View Related
Jul 23, 2015
I have a requirement where I need to show the maximum value in grand totals but for the dimension members the same measure has to sum.
For ex: lets say I have a measure called Test and this is a base measure. The aggregation type set to this is SUM.
For this same measure the grand totals should not show the sum instead it should show the maximum value of the dimension members which is being analyzed across.
View 4 Replies
View Related
Jun 17, 2015
I have created cube. 1 fact table and few dimensions including dimDate
I need to create a calculated member for variance.
Variance = SUM([Measures].[Amt]) starting from financial year beginning(2015-04-01 to current date) -
SUM([Measures].[Amt]) for the same period last year(2014-04-01 to current date last year)
How can I create this calculated member?
View 5 Replies
View Related
Nov 30, 2015
I have developed a cube in my work place for analyzing current year sales with previous year sales in Time Hierarchy (Year- Quarter- Month) using Parallel period. If we want to see data for particular Quarters i.e. Q1 and Q2 then total at the year level should also get change. Currently if we only choose 2 quarters in the filter then current year data gets change, however data using parallel period is not getting change accordingly and its shows Total of full year.
View 4 Replies
View Related
Oct 27, 2015
I cannot understand why Total of calculated member is displayed incorrect. How should I change calculated member for it to work correctly?
Calculated Member:
CREATE MEMBER CURRENTCUBE.[Measures].AverageScore
AS IIF([Measures].[Distance]<2001,0,[Measures].[avgscore]/[Measures].[Date Count]),
VISIBLE = 1;
It seems that Total is calculated without checking value in "AverageScore" for that month.
View 3 Replies
View Related
Sep 28, 2003
I have to generate reports in Excel formats.I send the appropriate content type through header() function of PHP.But I can't get the <td> colors when using css sheets.
Following problem occurs:
1. Image doesn't come.
2.<td> does'nt take css style.
Following is the code snippet:
<?php
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: attachment; filename=pareto_combined.xls");
?>
<link href="../../../extra/sheets/SSheet.css" rel="stylesheet" type="text/css">
<tr colspan=8>
<td height=50> <img src='../../../extra/images/log.jpg'></td>
<td align=right><img src='../../../extra/images/mickey_sup.jpg'></td>
</tr>
<?php
$dateF=$_REQUEST['dateF'];
$dateT=$_REQUEST['dateT'];
$agentID=$_REQUEST['agentID'];
?>
<tr>
<td class='graybg'><?php echo $agentID</td>
<td class='graybg'><?php echo $dateF?></td>
<td class='graybg'><?php echo $dateT?></td>
</tr>
View 1 Replies
View Related
May 27, 2015
I have a request for being able to show ordertotals for those orders that contains a specific productgroup.
Case
Order 1 ProductGroup Value
A 20
B 40
C 40
Total 100
Order 2 ProductGroup Value
A 20
D 40
Total 60
So if i slice OrderTotal by ProductGroup the result will be
A 160
B 100
C 100
D 60
ALL 160
how to solve this.
View 9 Replies
View Related
May 22, 2015
Let's have a table to show what I mean ...
As you can see I have added only one contract (for the sake of simplicity). At any given date I want to calculate the running total for a contract (all contracts), but the aggregation must take the contract state into consideration.
You can see my expected result for queries 1 - 3. That means, with the actual state ('B') I want to aggregate all values regardless of their state in the past.
For query 2 the state is 'A' ... I hope you can follow.
Now my aim is to aggregate all contract states for any given date with the right value. The past states of each contract are not relevant. And of course future ones (with respect to the query date) shall also be irrelevant.
Maybe the solution is a combination of 'LastNonEmpty' and SUM/PERIODSTODATE ...
View 5 Replies
View Related
Dec 3, 2014
I have the following stored procedure to test scope of variables
alter proc updatePrereq
@pcntr int,
@pmax int
as
begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int
set @z = 1
declare @max int
set @max = 5
while @z <= @max
begin
exec dbo.updatePrereq @z, @max
set @z = @z + 1
end
go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
View 1 Replies
View Related
