Sunday, March 25, 2012
Calculated Measure (Previous Act Value) Problem
I am having trouble with a calculation that on the surface seems to be working until it is opened up in excel.
The measure calculates the Actual Value as of last year and is as follows - ([Measures].[Act Val],ParallelPeriod([Time].[Year],1))
This works fine until I look at the totals within EXCEL 2002/2003
1)If I choose to view a whole year then the total shows me a whole year. That is fine and as expected.
2)If I choose part of the year the total shows me the whole year. This is wrong and am wondering if it is an Excel probem or my calculation? But here is the weird part -
3)If I choose another year and select the same months as I have already chosen (ie Year 20023 Jan, Feb and Year 2003 Jan and Feb) then the total for the year that is already selected works!!(shows only the total of the two months - whereas the extra year I have selected (remember - Jan Feb only) shows me a total for the whole year.
WAIT - There's more
4)If I add another month to the 2002 selection (ie add March) then the total for 2003 (Jan Feb only) includes the March value also (even though it is not selected)!!!!!!
Any Ideas??
Does it make sense??
Thanks
MattWell...
ParallelPeriod
Is a udf someone wrote...
How are you connecting to sql server?
Are you connecting to sql server?
Sorry...should've looked first...
I have GOT to start playing with that stuff...never had any real need...|||No, ParallelPeriod is an Analysis Services function. I don't know anything more about it than that.
From the Holy Book:
ParallelPeriod
Returns a member from a prior period in the same relative position as a specified member.
Syntax
ParallelPeriod([Level[, Numeric Expression[, Member]]])
Remarks
This function is similar to the Cousin function, but is more closely related to time series. It takes the ancestor of Member at Level (call it ancestor); then it takes the sibling of ancestor that lags by Numeric Expression, and returns the parallel period of Member among the descendants of that sibling.|||You should it's a powerful addition to SQL Server - only sometimes you can get lost in translation (so to speak) - the MDX statements (language for writing the calcs) sometimes take a while to understand. Hopefully you take a crash course and help me with my problem.
Calculated fields on olap pivottable in excel 2007
Is there a way to add calculated fields in excel 2007 to a pivot table, connected to a SSAS cube?
Meaning that is calculated on the fly automatically while drilling up/down...
I do not think so. The formulas button is disabled. Formulas have to be server side MDX calculated members. Same in Excel 2003
You will also note some other diffences like Grouping may require security access to cube metadata in order to work.
Philippe
|||This is quite a disadvantage! Hopefully the kpi wizard and the set selector of ProClarity will be integrated into excel!Calculated field when export to excel > number as text
shows up as text with the smart tag message 'number as text'. I have
tried all formatting strings as part of the calculated expression
CDbl, Cdec, Int, FormatCurrency etc ... but the 'number as text' does
not seem to go away.
How can I export the calculated field results to excel 2007 from SSRS
as number and not as text.
Please help.
Thank you for your time!I have this same issue! Have you determined how to fix this? dhughes@.cogc.com
"avididy" wrote:
> In my report, I have a calculated field which when exported to excel
> shows up as text with the smart tag message 'number as text'. I have
> tried all formatting strings as part of the calculated expression
> CDbl, Cdec, Int, FormatCurrency etc ... but the 'number as text' does
> not seem to go away.
> How can I export the calculated field results to excel 2007 from SSRS
> as number and not as text.
> Please help.
> Thank you for your time!
>|||Hi,
Did you find a solution to the problem of calculated fields appearing as
text in Excel?sql
Thursday, March 22, 2012
Calculated cells in Excel?
We're evaluating Reporting Services for a client. I like the functionality,
but it's unclear whether we can manipulate Excel output. For example, our
client will want to generate or populate a spreadsheet that has multiple
worksheets, with calculated values that pull values from one or more of
those sheets.
Does RS support this level of complexity?
Thanks in advance,
Ian LurieI don't know about creating calculated fields, but you can put tables
onto different sheets by setting the page break after table property
to true. The only reporting product I have seen that allows you to
export formulas is actuate. Should you find a way to do it with
reporting services, *please* let me know! We are interested in it
too-
Thanks,
John Hennesey
johnhennesey82@.hotmail.com
"Ian Lurie" <ian@.portentinteractive.com> wrote in message news:<#eXuLqRXEHA.1356@.TK2MSFTNGP09.phx.gbl>...
> Hi,
> We're evaluating Reporting Services for a client. I like the functionality,
> but it's unclear whether we can manipulate Excel output. For example, our
> client will want to generate or populate a spreadsheet that has multiple
> worksheets, with calculated values that pull values from one or more of
> those sheets.
> Does RS support this level of complexity?
> Thanks in advance,
> Ian Lurie|||We export formulas as along as they contain only report item references.
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Hennesey" <johnhennesey82@.hotmail.com> wrote in message
news:9f049b3b.0406281103.54baf474@.posting.google.com...
> I don't know about creating calculated fields, but you can put tables
> onto different sheets by setting the page break after table property
> to true. The only reporting product I have seen that allows you to
> export formulas is actuate. Should you find a way to do it with
> reporting services, *please* let me know! We are interested in it
> too-
> Thanks,
> John Hennesey
> johnhennesey82@.hotmail.com
> "Ian Lurie" <ian@.portentinteractive.com> wrote in message
news:<#eXuLqRXEHA.1356@.TK2MSFTNGP09.phx.gbl>...
> > Hi,
> >
> > We're evaluating Reporting Services for a client. I like the
functionality,
> > but it's unclear whether we can manipulate Excel output. For example,
our
> > client will want to generate or populate a spreadsheet that has multiple
> > worksheets, with calculated values that pull values from one or more of
> > those sheets.
> >
> > Does RS support this level of complexity?
> >
> > Thanks in advance,
> >
> > Ian Luriesql
Friday, February 24, 2012
C# code or document for loading excel sheet into an sql table
I am trying to find some document or code that will load an excel spreadsheet into an sqlserver database.
Can anyone please point me in the right direction.http://www.databasejournal.com/features/mssql/article.php/3331881|||Thanks a lot for your help I will take a look at this now.
C# code for saving data from excel to mssql database
I am trying to find some code or documentation that I can use to create a web page that will save data from an excel file to a mssql databaseAfter you save the Excel file, open it and read records from it using this article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934
And then use normal ADO.NET procedures to update the SQL Server database.|||Thanks a lot I will take a look at this article now|||Thanks for the tip all worked well on my local machine but when I uploaded it to an external server and tested I keep getting this error:
System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data. at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider() at System.Data.OleDb.OleDbConnection.Open() at summitPortal.controls.bulk.insertdata(String sSheetPath) at summitPortal.controls.bulk.ImageButton1_Click(Object sender, ImageClickEventArgs e) at System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) at System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain()
The file is definately not in use because I even restarted the computer and tried again.
I also checked the permissions and they seem to be ok.
Any ideas anyone?|||I'm not so sure about this but did you check yourExcel file is read-only or not? sometimes I get some error when I open read-only files. anyway, I will try what you're doin right now.|||Check out this Microsoft troubleshooting article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q306269
Tuesday, February 14, 2012
button to export to excel
(instead of having to choose the format from the toolbar and then press
"export").
Is there a way to do it? I know that there is a parameter that I can
add to the url for this but i don't know exactly how to add it to the
current url from my report).
Thanks.Yes it can be done.
Put a text box with text something like "Save as Excel" you can Underline
the text to look more like a hyperlink in the "Action" option of the text box
give the URL as
http://servername/reportserver?/SampleReports/Employee Sales
Summary&rs:Command=Render&rs:format=EXCEL. You can pass the parameters as
well.
PS: You need to create the replica of the original report and point in the
URL which takes it to excel otherwise you will have "Save as Excel" text will
also get exported.
Just to avoid that you can create the same replica (copy paste the report)
and remove the "save as Excel" text from the copy of the report.
Amarnath
"nicknack" wrote:
> Hi, I would like to have a button on my report to export it to excel
> (instead of having to choose the format from the toolbar and then press
> "export").
> Is there a way to do it? I know that there is a parameter that I can
> add to the url for this but i don't know exactly how to add it to the
> current url from my report).
> Thanks.
>|||Hi Amarnath,
Thanks for your reply.
As i understand, The only solution is to have two reports (for example
ORIGINAL and COPY_ORIGINAL).
1) My original report with a 'button' that have a URL of the
COPY_ORIGINAL and in that url i'll add the "rs:format=3DEXCEL" at the
end).
2) My copy that is the same as the first one (just copy&paste) but
without the 'button'.
Is that right?
Its look like it my be a solution exept for the problem that every time
i'll make a change in the original report i'll have to delete the copy
and create it again.
Did I got it right?
Thank.
Amarnath =D7=9B=D7=AA=D7=91:
> Yes it can be done.
> Put a text box with text something like "Save as Excel" you can Underline
> the text to look more like a hyperlink in the "Action" option of the text= box
> give the URL as
> http://servername/reportserver?/SampleReports/Employee Sales
> Summary&rs:Command=3DRender&rs:format=3DEXCEL. You can pass the parameter=s as
> well.
> PS: You need to create the replica of the original report and point in the
> URL which takes it to excel otherwise you will have "Save as Excel" text =will
> also get exported.
> Just to avoid that you can create the same replica (copy paste the report)
> and remove the "save as Excel" text from the copy of the report.
> Amarnath
>
> "nicknack" wrote:
> > Hi, I would like to have a button on my report to export it to excel
> > (instead of having to choose the format from the toolbar and then press
> > "export").
> >
> > Is there a way to do it? I know that there is a parameter that I can
> > add to the url for this but i don't know exactly how to add it to the
> > current url from my report).
> > > > Thanks.
> > > >