.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

calculating field in ssrs

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
I have a dataset which brings in sales, transcast, transbilling, and cogs in by group. I want to calculate net spend as a percent to sales and gross spend as a percent to sales. but can't seem to figure it out since sales, cost, transbilling, and cogs are all in one column(entry_type)...   my dataset looks like this   group | entry_type| year | jan | feb | mar | apr | may | etc...

View Complete Post

More Related Resource Links

I need help with the syntax for passing a record field value from SSRS to an asp page

I have tried several variations without success.  Here is where I am now.  The goal is from inside Report Manager report, link to an asp page with a filed value. This is from the expression line of the Jump to URL entry. Also I am trying to open a new page http://www.whereever.com/CSSurvey01.asp?RecordID=" & Fields!respid.Value            

SSRS 2008 R2 Dataset Field Value Property Data Type not numeric

We're building SSRS 2008 R2 reports using SSAS as data sources.  There are several occaissions where the data types of certain dataset field values that represent cube measures are not numeric.  As such, when these fields are used in an aggregate function they throw an error.  Is there a way to remedy this in the dataset? Thanks for your help.

How do I stop line feeds in a Hierarchy field from a SharePoint list to prevent wrapping in a SSRS r

I'm developing a Reporting Services 2005 report that includes a Hierarchy field from a SharePoint 2007 list.  The Data Source for the report is an XML type. I would like to display the Hierarchy field on 1 line.  However, it is displaying in 2 or more lines in Visual Studio 2005. The field also wraps when rendering to Acrobat (PDF) file.  How can I get rid of the embedded line feed (I believe it's "&#xA").  I've tried using the Replace function in an expression, other functions and changed the properties, e.g., the CanGrow property.  Nothing seems to work.   Here's what I get: LAW>Administrative Law LAW>Criminal Law LAW>International Law   I'd like to see something like this (I'm able to add the "/"): LAW>Administrative Law /LAW>Criminal Law /LAW>International Law/

Pass parameter to Hidden Field in SSRS Report

Hi, I have Two Columns in a Table Say As Table1 Column1 = Text Column2= id My SSrs Report has two parameter values as Column1 and Column2. Column2 should be hidden SELECT DISTINCT COlumn1 FROM Table1. i have passed this query dataset to Column1 parameter. Now My requirement is that, when ever column1 drop is selected. the hidden field column2 should fecth the corresponding ID. this column2 has Storeprocedure which will Display the Output when View Report button is clicked Thanks, Saraswathy

SSRS 2005 Render Format Excel Issue with Time Field


I have a report that uses a datetime field but gets populated only with the time. So, in SQL db, it gets stored with a default date value of 1899-12-30 12:01:01 with the correct time filled in. Report in SSRS Preview mode displays the value just fine when I use the Time format in Layout mode. I build and deploy and able to view the report in a web browser with no problem displaying the time field. But when I subscribe to the report and choose Excel render format, when I open up the Excel file from the email that was sent, I get negative decimal values. I have tried every possible Time format in SSRS but I get the same result. I also tried just the default and still gives me negative decimal values that show up as ####### in Excel.

Any help will be greatly appreciated. Thanks

SSRS Change field properties dependent on the render type


I've built a report that has background images with text over the top, which when rendered as Word or Excel the images don't show and because the client has requested the text is white against the image, the text isn't visible.

What I'd like to do is run code within the report that identifies the render type and if xls will change the text color through an expression.

Any ideas would be greatful.


Problem with calculated field in SSRS

i have the foll scenario:
col1 col2 col3 col4 
A   1  100 
   2  250 150 
   3  500 250 
   4  600 100 
   5  900 300 

For col4 i need to calculate the difference b/w (the value in current row in col3 i.e. 250) - (the value in the previous row in col 3 i.e.100). i need to do that for all rows of col4. I need help with the expression to do this in SSRS.

Getting SSRS Report DataSet Field list from C#


Hi All,


1. We have requirement where users can build there own SSRS Reports and integrate it with the existing system dynamically.

2. We have an Existing Application (Called APP1), our approach is user will build the report as per there requirement and from APP1 we have report configuration screen when user will select the report file to import to our system.

3. While doing importing, we have special requirement like

3.1 Retrieve the all fields from the Report DataSource and display to the user

3.2 User configures that what values they want to filter when the report is shown from the application. ex: employee information is      displayed in the report, DataSet query is "select empid,empname,..... * from employee" 

        3.3 from APP1 when user gives the report we have to dynamically load the report and get the fields from the dataset, so user can                configure like this, when the report is shown report must ask for parameter value for empid .


SSRS ..Problem with SubTotal and Calculating Percentage


I am having problem to calculating % on base of subtotal value in Matrix Report.
                  R1   %           R2    %         GrandTot  %          
A       1     20     40%          10  50%         30  34%
         2     30     60%          10  50%         40  66%
       Total  50     100%        20  100%      60  100%
B       1     20   33%            10    20%       30     30%
         2     10   16%           10    20%       20     20%
         3     30    51%          30    60%       50     50%
       Total  60   100%       50     100%   100 100%

The Bold % i nee

What is the Expression for calculating Sum of percentages in SSRS report.


Hi All,

I want to calculate Sum of percentages in my SSRS report.

I have two fields :

Field1   Field2

For Field1 I am using the following formula --- Fields!suminsured/Sum(Fields!no.ofRisks) * 100

The  Field1  formula is working correctly....

Now I want to calculate Field2 from  Field1 ....

My Field2 Formula is Sum(Fields!suminsured/Sum(Fields!no.ofRisks)*100) --- But when I am doing this way its giving error...

Can any body let me know how to acheive Field2 ....??

Field2 is nothing but Sum(Field1) ....But for Field1 I am using Percentage Forumula....So field2 should be Sum(Field1) percentages....

Any ideas in this regard will be appreciated...

Waiting for the responses..




Retrieve a hidden flag/field/ textbox/ variable from SSRS in C# with reportExecutionService



I want to set a HIDDEN flag in my reports (SSRS 2008) and retrieve in my C# code. If it's possible please share your suggestions.


Thank you,


Problems Creating Age Bands After Calculating The Age From DOB Field


SELECT     CASE WHEN Client_Details_New.CliDOB IS NULL THEN 0 WHEN dateadd(year, datediff(year, Client_Details_New.CliDOB, getdate()),
                      Client_Details_New.CliDOB) > getdate() THEN datediff(year, Client_Details_New.CliDOB, getdate()) - 1 ELSE datediff(year, Client_Details_New.CliDOB,
                      getdate()) END AS Age, CASE WHEN Age BETWEEN 17 AND 25 THEN '17 - 25' ELSE 'Other' END AS AgeBands
FROM         Client_Details_New

My query is above ^^... when I execute the query it just says "Invalid Column Name 'Age'" - this error is showing for the 2nd case statement... not sure why im getting this error when I have set the column name "age" when doing the age calculation on the DOB field :S

any help is much apprecited!

option type field value shows ";#" in SSRS

I had created a one report in SSRS from the list in sharepoint.

In sharepoint list the column name status has values save,approved

When I see that status column in SSRS it shows "2;#approved"

How can I remove these 2;# from that column value in SSRS report?

calculation, field and map traverse adjustment, and coordinate transformation

Free Pocket PC land surveying software -- COGO calculation, field and map traverse adjustment, and coordinate transformation -- for students and professionals.

Dropdown Event (Template Field)



I have grid view control that includes a dropdown control in a template field.

I wish to execute some code when the value is changed in the dropdown list.  Can't figure out how to capture this event though?

Any suggestions?

How to take value of bound field of grid in a variable or in a textbox.


How to take value of bound field of grid in a variable or in a textbox.

 I m using this code in .aspx page for grid.

<asp:GridView ID="Grd_Mprocess" runat="server" AutoGenerateColumns="False"

                      Width="100%" AllowPaging="True"


                      AllowSorting="True" PageSize="5"  >

                 <RowStyle CssClass="odd" />


<asp:BoundField HeaderText="DOC_TYPE_PK" DataField="DOC_TYPE_PK"  Visible="true" >


  <asp:TemplateField HeaderText="DOC TYPE ID" Visible="true">  &nbs

I need help about nvarchar field



how can specified nvarchar data type without N''?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend