EzDevInfo.com

How to get named excel sheets while exporting from SSRS

Whenever a single page report is exported to excel, sheet in excel is named by the report name. If a report has multiple pages, the sheets are named as sheet1, sheet2,.... Is there any way to specify sheet names in SSRS 2005 ?

solution: Found this after some googleing: Changing the Sheet names in SQL Server RS Excel: QnD XSLT

Will try out and post an update if it works.

How do I format date and time on ssrs report?

on SSRS report I need to show todays date and current time

i tried this =FormatDateTime(Now,"MM/dd/yyyy hh:mm tt") but this is not working for me giving an error.

Anyone please help me for expression ?

I want output display like 4/12/2013 12:05 PM

Row color and Alternate Row color for Table in RDLC Report

How do I give row color and alternate row color for a Table in RDLC report? When I googled I found most of the result says something like = iif(RowNumber(Nothing) mod 2, "Red", "White") OK! But where should I place this stuff? Any help will be appreciated.

Implementing Target lines, in SSRS column Charts

I have a column graph, that shows a trend of consumption over time, The y-axis being consumption and x-axis being time in month,

I have to implement a target consumption.

I implemented a target, by adding data field with a Line chart type, this is a constant, and will just display a horizontal line.

The problem i am facing is, if there is only one month, the line disappears? is there a way not to have it disappear, or is there a better way to add line target in column charts in SSRS

I am using SSRS 2008

SSRS the definition of the report is invalid

How can I resolve the below error message that I receive when I try to deploy my report to our our Reporting Server using BIDS?

Message:

Error : The definition of the report '/OrderAnalysis' is invalid.

Report Element:

 <Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">

Cannot view SSRS 2008 R2 - SSRS 2012 Reports in Safari/Chrome but works fine in FireFox/IE8... why?

I have some simple reports in SSRS 2008 R2 but they won't display at all in Safari or Chrome. According to Microsoft's Books Online, these browsers are supported in limited fashion. However, I can't see anything after the data "Loading" clock completes. The parameter bar and bread crumb navigation section at the top of the page are all there. Also, I can Save/Export to any format on Safari and Chrome. It just won't display the report section itself, which is just blank.

Am I suppose to use certificates and secured connections (currently not setup with https, only http)? Are there any server side configurations that need to be tweaked? Has anyone had success displaying ANY reports on Safari/Chrome using previous SSRS versions (2005)?

I'm using Safari 5.0.4 and Chrome 10.0.648.151. I know the similarity for these two browsers is they both are based on WebKit.

The report renders successfully on IE8 (of course) and FireFox 4.0.

Would really appreciate it if someone can shed some light.

Thanks in advance. - Greg

SSRS chart does not show all labels on Horizontal axis

My SSRS report does not show all the labels on the horizontal axis. Please see below.

Note how the red arrows point to the few that do show. So my question is, where are the rest of the labels? Each bar should have one.

For the chart I'm adding number data for the bars, and another field for the Category Groups. That field in Category Group is for the labels.

Does anyone know why some are missing?

Also, a second but less important question, why is the order of the bars not the same order as the dataset?

Reporting Services permissions on SQL Server R2 SSRS

I'm getting the error below when I try and access SSRS on SQL Server 2008 R2

I'm not sure how many others have started using SQL 2008 R2 SSRS, but I am having an issue with getting the error below when I try and access the reports server url

User does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed

I can access the url if I run IE as an administrator

rdlc report in vs2010, how do i repeat column header on every page?

How do I repeat column header (captions) in RDLC reports?

It says here http://msdn.microsoft.com/EN-US/library/735D1EE7-3C89-46D8-A346-504DB10F33E1.aspx#TableNoGroups:

To repeat or freeze column headings for the tablix member, select the static row that is labeled (Static). The properties pane displays the properties for the selected tablix member. By setting properties for this tablix member, you can control how the first row repeats or stays in view.

However I've tried turning "RepeatOnNewPage" and "FixedData" on and off, and nothing happened (the column header still showed up only in first page). I'm using VS2010, thanks!

Inserting a Page break into an SSRS report

I have a report in SSRS that contains 12 subreports. After each subreport, I need to insert a page break so that each subreport starts on a new page and doesn't share pages with the other subreports. Does anyone know how to do this? Thanks

SSRS 2008: error message saying my parameter doesn't exist but it clearly does?

I have a report that has about 10 parameters and about 10 datasets and one datasource. I typed alot but I really need help so I figured I would try and provide as much info about the problem as I could right from the start.

Let's simplify it all and pretend these are the relevant names of everything and that the report gets basic employee information:

• Parameters
  • facilityID: text in the format of a guid or uniqueidentifier, this is hidden and passed in so that the end user of the report can only view info for their own facility; Single valued parameter, not blank not null, hidden, text
  • startDate: start date range, let's say I want my report to include data starting Jan 1 this year; Single valued parameter, not blank not null, visible, datetime
  • endDate: end of the date range, let's say it's today/now; Single valued parameter, not blank not null, visible, datetime
  • viewOption: three hard-coded values (label, value) ("Current Employees",1) and ("Past Employees",2) and ("Employees that moved facilities",3); Multi-valued Parameter (so select all is an option), not blank, not null, visible, text
  • personID: based on the dataset getListOfNames where the label is the employee name and the value is their personID (guid/uniqueidentifier); Single select, not null, not blank, text and visible
    
• My Data Sets
  • getListOfNames: this gets a list of people that were at the company in the provided date range at the given facility and where their employee status is in the viewOption parameter value (so it can be just one number or up to three). This is a stored procedure but I have set up it to handle the multi-valued parameter by passing it join(parameter, "~") and then separating it out in the stored proc. This works on 5 other reports.
  • getReportInfoOnSelectedPerson: main body query; after the user selects the employee they want data on, this takes that personID and the date range and fills the main table.

The problem:
SSRS says viewOption doesn't exist. But I see it, right there, in the Parameters folder on the left hand side. I see it when I go to enter an expression under Parameters. There is no squiggly red line under Parameters!viewOption.value. But, when I try and put it as a value for a parameter used by the getListOfNames dataset, it errors. If I put it in the getReportInfoOnSelectedPerson dataset and use it in the exact same way, SSRS is okay with that. Wtf? So... I have checked the rdl and everything is fine (where the actual parameter is declared, where it is used in the dataset reference, everything). It's just this one dataset. And I have a similar report that uses the same data set, same basic parameters and that report is fine. I try setting the value of the dataset parameter to 1 or something and that is fine but when I try and set it to Parameter!viewOption.value it errors..... Now, above I said i normally pass the stored proc a Join on the paramater with a tilde ~ but I am trying to keep it simple and either get it to just work in general (either by passing it the first value of the multivalue viewOption parameter or turning that parameter into a single select and just passing the .value) but the join doesnt work either. All of those things work for the other dataset, which is also a stored proc.

This is my error:
An error has occurred during report processing. (rsProcessingAborted) The Value expression for the query parameter ‘@viewOption’ contains an error: The expression referenced a non-existing parameter in the report parameters collection. (rsRuntimeErrorInExpression)

Which is clearly saying my parameter doesn't exist but I can SEE it... everywhere. And if I assign one of the other datasets' parameter values to the viewOption parameter it works without an error. I've checked the rdl.

I've had this problem before and it was fixed by deleting both the parameter and dataset and creating them again (for safety I renamed both of them). That didn't work this time.

I am so frustrated. Please help....

Code?

      <DataSet Name="getListOfNames">
      <Fields>
          <Field Name="personID">
              <DataField>PersonId</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
          </Field>
          <Field Name="name">
              <DataField>name</DataField>
              <rd:TypeName>System.String</rd:TypeName>
          </Field>
      </Fields>
      <Query>
          <DataSourceName>myDataSource</DataSourceName>
          <CommandType>StoredProcedure</CommandType>
          <CommandText>getListOfNames</CommandText>
          <QueryParameters>
              <QueryParameter Name="@fac">
                  <Value>=join(Parameters!fac.Value,"~")</Value>
              </QueryParameter>
              <QueryParameter Name="@bldg">
                  <Value>=join(Parameters!bldg.Value,"~")</Value>
              </QueryParameter>
              <QueryParameter Name="@unit">
                  <Value>=join(Parameters!unit.Value,"~")</Value>
              </QueryParameter>
              <QueryParameter Name="@station">
                  <Value>=join(Parameters!station.Value,"~")</Value>
              </QueryParameter>
              <QueryParameter Name="@startDate">
                  <Value>=Parameters!startDate.Value</Value>
              </QueryParameter>
              <QueryParameter Name="@endDate">
                  <Value>=Parameters!endDate.Value</Value>
              </QueryParameter>
              <QueryParameter Name="@viewOption">
                  <Value>=Join(Parameters!viewOption.Value, "~")</Value>
              </QueryParameter>
          </QueryParameters>
          <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
  </DataSet>



<ReportParameter Name="viewOption">
  <DataType>String</DataType>
  <DefaultValue>
    <Values>
      <Value>1</Value>
    </Values>
  </DefaultValue>
  <Prompt>View</Prompt>
  <ValidValues>
    <DataSetReference>
      <DataSetName>viewOptionQuery</DataSetName>
      <ValueField>value</ValueField>
      <LabelField>label</LabelField>
    </DataSetReference>
  </ValidValues>
  <MultiValue>true</MultiValue>
</ReportParameter>



<DataSet Name="viewOptionQuery">
  <Fields>
    <Field Name="label">
      <DataField>label</DataField>
      <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="value">
      <DataField>value</DataField>
      <rd:TypeName>System.Int32</rd:TypeName>
    </Field>
  </Fields>
  <Query>
    <DataSourceName>flamingo</DataSourceName>
    <CommandText>select 'Other Facility' as label, 3 as value union select 'Past' as label, 2 as value union select 'Current' as label, 1 as value order by value</CommandText>
    <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
  </Query>
</DataSet>

This is how it is set up and getListOfNames is why the error is throw, if I change

              <QueryParameter Name="@viewOption">
                  <Value>=Join(Parameters!viewOption.Value, "~")</Value>
              </QueryParameter>

to

              <QueryParameter Name="@viewOption">
                  <Value>="1~2"</Value>
              </QueryParameter>

then it works.... or the value can be just 1 or 1~2~3

However... when I try and put join(Parameters!viewOption.value,"~") as the value for another dataset's query parameter it works and there is no error.

  <DataSet Name="getReportInfoOnSelectedPerson">
  <Fields>
    <Field Name="name">
      <DataField>name</DataField>
      <rd:TypeName>System.String</rd:TypeName>
    </Field> 
    <Field Name="Building">
      <DataField>Building</DataField>
      <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="Unit">
      <DataField>Unit</DataField>
      <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="desc">
      <DataField>desc</DataField>
      <rd:TypeName>System.String</rd:TypeName>
    </Field> 
  </Fields>
  <Query>
    <DataSourceName>myDataSource</DataSourceName>
    <CommandType>StoredProcedure</CommandType>
    <CommandText>Reports_BasicInfo</CommandText>
    <QueryParameters>
      <QueryParameter Name="@personID">
        <Value>=Parameters!personID.Value</Value>
      </QueryParameter>
      <QueryParameter Name="@numberINeedToAggregateData">
        <Value>=Join(Parameters!viewOption.Value,"~")</Value>
        <rd:UserDefined>true</rd:UserDefined>
      </QueryParameter>
    </QueryParameters>
    <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
  </Query>
</DataSet>

And I already said this, but the stored proc/dataset that throws the error uses a parameter with the same set up in about 5 other reports (I have tried copying and pasting the relevant code sections from working reports but I still get the error). So, what's wrong with this one?

Visual Studio for SSRS 2008 - How to organize reports into subfolders in Solution Explorer?

Right now I have a project called reports with several reports. In solution explorer it looks like this:

Shared Data Sources
 -- DEV
Reports
 -- Report1
 -- Report2
 -- Report3

I want to make it look like this and have the same structure carry over to the report manager website when I click deploy.

Shared Data Sources
 -- DEV
Folder A
 -- Report1
Folder B
 -- Report2
 -- Report3

Anyone know how to do this?

Reporting Services 2008 - The report server installation is not initialized

I am trying to set up Report Server 2008 on a separate machine to the Reporting Services databases which are on a SQL Server 2008 R2 machine.

I configure everything in the Reporting Services Configuration Manager (identical to a set up we have working on a different pair of servers). The database connection test works fine, and the final "Progress and Finish" and screen shows "Success". However when I browse to the report server I get the error:

"The report server installation is not initialized. (rsReportServerNotActivated)"

I have seen a number of solutions online for this but they suggest using the Initialization tab on the Reporting Services Configuration Manager to initialize it. Or alternatively to use the rsactivate command. Now in 2008 I can't seem to find either of these so I'm a bit stuck.

When I use the Reporting Services Configuration Manager I get the following errors in the Event Viewer:

The application domain WindowsService_1 failed to initialize. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized. at Microsoft.ReportingServices.Library.ConnectionManager.b_2() at Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.<>c_DisplayClass1.b__0(Object state) at System.Security.SecurityContext.Run(SecurityContext securityContext, ContextCallback callback, Object state) at Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.Run(ContextBody callback) at Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey() at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage() at Microsoft.ReportingServices.Library.ServiceController.ServiceStartThread(Object firstStart).

followed by:

The application domain WindowsService_1 failed to initialize. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized. at Microsoft.ReportingServices.Library.ConnectionManager.b_2() at Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.<>c_DisplayClass1.b__0(Object state) at System.Security.SecurityContext.Run(SecurityContext securityContext, ContextCallback callback, Object state) at Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.Run(ContextBody callback) at Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey() at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage() at Microsoft.ReportingServices.Library.ServiceController.ServiceStartThread(Object firstStart).

Is there another way I can initialize the report server installation?

In SSRS, why do I get the error "item with same key has already been added" , when I'm making a new report?

I'm getting the following error in SSRS, and it's been puzzling me a while now:

An error occurred while the query design method was being saved. 
An item with the same key has already been added

What does an "item" denote, though? I even tried editing the RDL and deleting all references to the Stored Procedure I need to use called prc_RPT_Select_BI_Completes_Data_View.

Could this possibly have to do with the fact that the Stored Procedure uses Dynamic SQL (the N' notation)?

By the mornin, in the stored procedure I have:

SET @SQL += N' SELECT bi.SupplierID as ''Supplier ID'' ,bi.SupplierName as ''Supplier Name''

        ,bi.PID as ''PID''
        ,bi.RespondentID as ''Respondent ID''

        ,lk_slt.Name as ''Entry Link Type''

        ,ts.SurveyNumber as ''Initial Survey ID'''

Is there an easy way to rearrange tablix columns in SSRS design mode?

I have an SSRS report which contains more than 20 columns in a tablix. Our users have decided that the data is OK, but they want the columns moved around (sigh!).

It seems like it should be easy to re-arrange the columns (move column 3 to column 1, swap columns 4 & 5 etc.). But, drag and drop does not seem to work and the only solution seems to be deleting the original column and re-inserting it in the correct place (and re-applying any expressions & formatting already created for the column).

Is there any easier way of doing this? Please note that I don't want a programmatic solution, but just need to change it once in design mode.