SSRS

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have written a procedure to update SSRS subscription schedules. The procedure appears to work because the values are updated in ReportServer.dbo.Schedule. However when I view the schedule through SSRS Report Manager, the dates are not updated.

The code I am using is below -

declare 
--@SubscriptionID varchar(100) = 'E65E6D4A-A631-4C17-A8DB-001FB0AD0855'
@Path varchar(max) = '/QTS Internal/RTM/Service Disruption Report'
, @SPLC varchar(max) = '123456'
, @State varchar(max) = 'VA,NC'
, @Country varchar(max) = NULL
, @Carrier varchar(max) = NULL
, @Location varchar(max) = NULL
, @CustomerID varchar(max) = '1'
, @Disruption varchar(max) = 'Hurricane Florence'
, @StartDate datetime = '20180914 04:19:00'
, @EndDate datetime = '20181002'

create table #subs (SubscriptionID varchar(200), [Path] varchar(200))

insert into #subs

select s.SubscriptionID, c.Path

from Catalog c
join subscriptions s on c.ItemID = s.Report_OID
where c.Path = @Path

-----------------------------------------------------update schedule dates

UPDATE ReportServer.dbo.Schedule
SET StartDate = @StartDate, EndDate = @EndDate
from Catalog c
LEFT OUTER JOIN Subscriptions s WITH (NOLOCK) ON s.Report_OID = c.ItemID
LEFT OUTER JOIN ReportServer.dbo.[ReportSchedule] RS WITH (NOLOCK) ON s.[SubscriptionID] = RS.[SubscriptionID] 
LEFT OUTER JOIN ReportServer.dbo.[Schedule] SC WITH (NOLOCK) ON RS.[ScheduleID] = SC.[ScheduleID]
JOIN #subs subs on s.SubscriptionID = subs.SubscriptionID

Open in new window


Does anybody have experience with this?
0
Python 3 Fundamentals
LVL 12
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

I have been tasked with creating some reports out of VS and SSRS from an Oracle 12c database.  I am an Oracle developer but I don't know much about VS or SSRS.  I have both VS 2015 (14.0.25431.01 Update 3) and 2017 (15.7.5) installed and I have installed ODTwithODAC for 12c.  I have also installed SSDT for both VS 2015 and VS 2017.  

Using VS 2017, i have created a new project but I am unable to create a shared data source.  As soon as I select Oracle Database in the Type drop-down, the Edit button next to the connection string becomes grayed out.  

Using VS 2015, I have cloned an existing project that works fine for my colleague.  However, when I first tried to load the project I received a message that the version of the report server project is not supported and the project must be upgraded.  If I say no, then the project won't load.  If I say yes, the project loads and everything seems to be ok. However, when I try to Preview an existing report from the project, I receive the following error:
"An error has occurred during local report processing.  An error has occurred during report processing.  An attempt has been been made to use a data extension 'ORACLE' that is either not registered for this report server or is not supported in this edition of Reporting Services."  There is nothing in the Error List when this happens.  

The only difference between my colleague's setup and mine is that he has SQL Server Reporting Services 14.0.806.134 and I have …
1
Please help with writing a query to get aggregated OrderAmount for each SalesPerson for the last month (last 28-31 days). For example, if today is 09/13/2018, we need sum from 08/13/2018 to 09/12/2018.

TableOrders
----------------
SalesPerson
OrderAmount
OrderDate


Thank you in advance.
0
while exporting my report data to excel it is running 10 -15 minutes after that im getting server Runtime error in  ssrs report .I have 3 lakhs data and 49 columns in excel.
There is no exceeding limitations problem with my excel data and im trying to export it into .xlsx format only.Please suggest what i have to do
0
The following command string exports a PDF to be saved or opened.

start explorer "<http://ServerName/ReportServer$NamedSQLInstance?%2fPurchasing%2fPurchaseOrder&PoNumber=103290&rs:Format=PDF"

Need to NAME and SAVE the file Automatically, from a Delphi desktop application.
0
I'm trying to recreate a report for a client, and in that report they have three fields Night, Weekend, Holiday (0 or 1) which need to be displayed as check-boxes.
In Visual Studio, I've made the column for these values .2" wide, have set the font to Wingdings, and have the expression which looks like:

= IIF(Fields!Night.Value = 0, chr(0168), chr(0254))
& IIF(Fields!Wknd.Value  = 0, chr(0168), chr(0254))
& IIF(Fields!Holiday.Value = 0, chr(0168), chr(0254))

In VS, this displays like the following image:2018-09-05-SSRS-Checkbox1.jpgBut after I deploy the report and run it in SSRS it looks like the following, making it difficult for the client to review the data in the report.2018-09-05-SSRS-Checkbox2.jpgIf my client exports this from SSRS to a .pdf file, it renders properly, but the client needs to export the report to Word (so that it can be edited) and when they do, that column automatically resizes itself so that these stacked checkboxes display horizontally.  My client recognizes that they can simply resize the column, but would prefer not to have to do so.2018-09-05-SSRS-Checkbox3.jpgI've tried inserting a carriage return/line feed between each character in the report textbox expression, but that results in extra spacing between the checkboxes.

I've also tried using 'X' and 'o' with the wingdings font, which places an X inside the checkbox, but the formatting issue still persists.

Does anyone have any recommendations?
0
=Sum(IIF(Fields!GROUP4CODE.Value>=3, CDbl(ReportItems!Textbox132.Value), CDbl(0)))

this expression gives error: aggregate functions can be used only on report items contained in page headers and footers.
0
What is best configuration to have SQL Server 2014 Standard & SSRS component run on a normal Windows OS ( 7 or 10) and send reports by email ?
0
How to add the textbox values of columns? these textboxes are not the part of the dataset. I have tried several options but something seems to be missing.

tried #1
=sum(ReportItems!Textbox22.Value)
Error: aggregate functions can only be used on report items contained in page headers and footers

tried #2
=RunningValue(ReportItems!Textbox22.Value,Sum,"Dataset1")
same error as above:   aggregate functions can only be used on report items contained in page headers and footers

tried #3
I wrote a vb function like this given in this link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/002c459a-2260-437a-a53c-a380bc2299d0/sum-column-gives-error-aggregate-functions-can-be-used-only-on-report-items-contained-in-page?forum=sqlreportingservices

Public Total_lookup_Sum As Integer = 0
Public Function Lookup_Sum(ByVal value As Integer) As Integer
    Total_lookup_Sum = Total_lookup_Sum + value    
    Return Total_lookup_Sum
End Function

and then wrote this expression:
=Code.Lookup_Sum(Reportitems!textbox22.value)

the output shows me the last value of the textbox instead of aggregating all the textbox values.

if I write the expression
=Code.Lookup_Sum(Reportitems!textbox22.value)
=Code.Total_lookup_Sum

the output shows true a boolean value.

I tried cint also but no success.

I can't use =sum(Fields!......Value) because textbox is not the part of the dataset.
I can't hardcode like textbox1.value+textbox2.value.

I have googled a lot of …
0
I need to install SQL Server Standard 2014 , the main usage will be to activate the SSRS ( Reporting Services) and have it send automated scheduled emails to client. The main point I need t clarify is : can i install this edition of SQL server and have the SSRS running, on a normal windows OS ( Widnows 7, or Windows 10) .... or must it be a Windows Server OS ?
0
Build an E-Commerce Site with Angular 5
LVL 12
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Installation failed for sql server 2012  sp4 upgrade for one of the cluster node1.. and another node2 is working fine
see the error msg sql server 2014 sp4 upgrade failed cached msi

1. even copied  sql_engine_core_inst  from C:\sqlpatches\sp4\x64\setup\sql_engine_core_inst_msi

to %windir%\installer\
with

cached msi file 7b2f1fe.msi


2.  https://github.com/suyouquan/SQLSetupTools/releases/
FixMissingMSI_V2.2ForNET35.zip

and fixed all..

Still the same error

Not sure, what should i do
0
SSRS report,  Running Value on the 3rd group down, can’t change this…the sorting is a must.  There’s two RunningValue columns, one by the first group (works) and one by the 3rd group , will work with only two groups.  How to create a running total on a change of a specific group when there’s more than 2 groups.


This is in Microsoft Dynamics CRM using  FetchXML so I cant use SQL or VB/C# code and have a single data set.
0
Hello techies :)

With the new 2016 version, the parameters can be rearranged on its own banner, unlike the 2008 version we could only have 2 columns of parameters.
Trying to figure out if there is a way to reduce the vertical / horizontal spacing between parameters on the band.  Also, font size.
If someone can put me on the right direction then will be greatly appreciated.

The link in below is great and all but does not say anything about changing the spacing nor the font sizes.
https://docs.microsoft.com/en-us/sql/reporting-services/report-design/customize-the-parameters-pane-in-a-report-report-builder?view=sql-server-2017

thx


JohnE
0
How to export date in general format in Excel File in SSRS?
0
Please setup a filter with the following criteria and save it. Name the filter:  Tony’s tasks this month

Assigned to:   Me (Tony)
Updated date:  This month

Grouped by: State
Sorted by Parent, then by custodian

Bonus points for sharing the filter with my manager, Jonny Lee
0
I'm re-writing a few SSRS reports that were on a 2008 SSRS server and deploying those to a 2017 SSRS server.

I have visual studio 2008 on my local pc I write/modify reports and deploy to the 2008 SSRS server. I also have Visual Studio 2017 on my local PC to write/deploy SSRS reports 2017 reports to the 2017 SSRS server.

My 2008 works fine, no issues. My issues are in SSRS 2017. I can write, preview and deploy basic reports to the 2017 server except those using custom assemblies.

I have several 2008 reports using a custom dll. I have that custom dll deployed and running on the destination SSRS 2017 server. I have it configured correctly so they I can deploy a report that uses the dll and I can successfully run the report on the server.

My problem is that I can't preview it on my local machine. I get the following error:

An error occurred during local processing.
Failed to load expression host assembly. Details: Could not load file or assembly 'Zen.Barcode.Core, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b5ae55aa76d2d9de' or one of its dependencies. Failed to grant permission to execute. (Exception from HRESULT: 0x80131418)

I'm almost positive some config on my local PC is preventing the preview. Has anyone seen this before?
0
How to Authentication parameters [user,password] to SSRS reports  while calling from Angular4 Application  to avoid prompt for username and password at runtime.
0
My SSRS 2016 report is not displaying data.  The stored procedure run in management studio returns data.  When I run the report in the Query Designer within SSRS it returns data but nothing is displayed.  What could be causing this
0
I'm running Power Bi Reporting Service 2018 Version 1.2.6648.38132

The problem I am running into is when running scheduled subscriptions, only 1 subscription can be run at a time.

When starting the subscription manually through the SQL Agent (while the other one is running), the status of the subscription goes directly to: The report server has encountered a configuration error.

I notice that the SQL Agent is starting the job, but it does not seem to trigger the Reporting Service to run the subscription. (Image attached)

Do you have any suggestions?



See the error message in the attached file.
ssrs---schedules.jpg
SQL-Agent-vs-SSRS-Job.jpg
0
PMI ACP® Project Management
LVL 12
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Hi,

I have a sales report based on 3 levels: area, region, sales rep and I implemented a sorting for sales.
The sorting works well on the basic level (sales rep), but does not work on the other 2, i.e. when I hide the sales rep.-details and want to sort on regions, SSRS opens the sales rep-details again and sorts on sales rep details.

Any suggestions how to fix the issue in such way the SSRS can sort based on the level which is shown?

Thanks.
0
Hi,

I have a table with sales data from 3 levels (Area, region, sales rep.). The default setting should be that the sales numbers are shown aggregated per Area (row visibility of "region": "hidden" - can be toggled by clicking on the plus in the "Area"-field).  "Sales rep." - details are visible by clicking on the plus in the "region"-field. To sum it up: "Sales rep" depends on "region" which depends on "Area".

My problem: When i chose the "Area" to be shown in the parameter (see 1st picture), the SSRS-report shows me "Area" + "Sales rep" (but not the "Region"). When I click on the plus in "Area", the "Region" is shown and "Sales rep" is hiding.

How I want it: 1. Chose "Area" (show only aggregated numbers from the area --> "Region" and "Sales rep" hidden)
 2. Chose "Region" (show "area" + "region", hide "sales rep")
3. Click on plus of "Region" --> show all the detailed sales data per "sales rep".

Please find the setup attached.
1.pdf
0
Created an SSRS 2014 report by joining 2 table ,related by CS_ID filtered by year . Notes column is in 2nd table and for each year it is unique . I am able to display data in a tablix. Issue I am facing is ,when some of the rows  are having notes (value of which is same for all rows). How to display that note on  bottom of a report on  a textbox  ? Currently I use another dataset to do this .  I want to do away with this. But I feel there is  way to set a report variable on expression of a column ( I use status column) of the row which has that  note column value. SetValue() does not seem to work . Ensured that report variable is not ready only.
0
Hi,

how can I declare a Textbox or render it accessible in relation to the error-message below?

"Textbox313' is not declared. It may be inaccessible due to its protection level."

Thanks
0
Hi,

how is it possible to set more than 1 filter in SSRS, such that the values in the 2nd filters are shown according to the choice lade in the 1st filter?
In my example (see attachment), I have a sales area divided into regions. I would like to appear only the regions which are presented in the sales area chosen in the 1st filter
0
SSRS Report Builder - Export to CSV issue; Double Quotes exist for each record.

We have a report that is scheduled to produce a CSV file.
This file is consumed by another application and requires specific formatting in order to successfully process the file.
The following entry has been configured within the 'rsreportserver.config' file:

<Extension Name="CSV (No Header)" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
                        <OverrideNames>
                              <Name Language="en-AU"> CSV No Header</Name>
                        </OverrideNames>
                        <Configuration>
                              <DeviceInfo>
                                    <NoHeader>true</NoHeader>
                                    <Extension>CSV</Extension>
                                    <Encoding>UTF-8</Encoding>
                                    <Qualifier></Qualifier>
                              </DeviceInfo>
                        </Configuration>
                  </Extension>

The file that is being generated from this contains double quotes at the start and end of each record (see below).
"""FORMAT BATCH IMPORT, STANDARD 1.0"""
"DREF1,DDATE1,DSOURCE,DCLRENTITY,DENTITYPE,LLDGCODE,LACCNBR,LAMOUNT1,LNARR1,LNARR2,LNARR3,LPRESCSTAT,LGSTTYPE,LGSTRATECODE,BIMPNAME,BCLRENTITY"
"180603,06/06/2018,AB,123,A,AB,123456789,-1234.56,Team Journal,21/05/2018 to 03/06/2018,12345,N,I,NA,TEAMJNL,123"

The output we need is shown below:
"""FORMAT BATCH IMPORT, STANDARD 1.0"""
DREF1,DDATE1,DSOURCE,DCLRENTITY,DENTITYPE,LLDGCODE,LACCNBR,LAMOUNT1,LNARR1,LNARR2,LNARR3,LPRESCSTAT,LGSTTYPE,LGSTRATECODE,BIMPNAME,BCLRENTITY
0

SSRS

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.