[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Good Day Experts!

I am trying to add functionality to a query used in an SSRS dataset.  The table I am working with has 15 bit fields which are True/False parameters at the top of my SSRS report.
The User will select True on the parameters for those that are required for each record to have.  For example, the User selects Teacher,Advisor,Counselor.   I would need to then
get the records that have Teacher = 1 or Advisor = 1 or Counselor = 1.  

I have all of the parameters setup as variables at the top of my query in the SMS(building query here so I don't have to keep running the report).  I have all of the parameters(variables)
that are set to 1 in a table variable called Fields.  The variable names are also the names of the bit field defined columns in the table.  I thought I was on my way to finishing this up, but then I became "stuck".  

In the where clause of my query, I am having trouble figuring out how to code where "at least one of the bit fields in my table variable are set to 1 on the record".

"Talking" this through,  I was thinking I would need to have my where like --> ...Where Fields(1st value) = 1 OR Fields(2nd value) = 1 OR Fields(3rd value) = 1 OR Fields(4th value) =1...all the way to the end of the table variable.

If it comes down to it, I will probably just have to run this through a loop up top before the query and build a Where string for all of the Or conditions.

Thanks,
jimbo99999
0
Become a Microsoft Certified Solutions Expert
LVL 12
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

I am trying to convert height in inches to feet and inches to display on a SSRS 2012 report  like 5"8'  from 69.00 inches. I have the formula below that isn't showing up correctly the digits to the right of the decimal. For 69.00, it shows 5"0'.

HGT = concat(convert(int,min(HGTTO)/12),  '"', convert(int,min(HGTTO)/12) % 1,'''')

Thanks
0
Hello - I am a Microsoft Access developer trying to learn SQL Server Reporting Services.

I already have SQL Server 2017 Express installed on my laptop, as a backend for some Access applications; everything is fine there, no problems.

I have downloaded and installed SSRS 2017, but am having a hard time working my way through the Report Server Configuration Manager.

I don't have a clue about these settings, and the articles I have found do not go into enough detail for me to know if I am setting this up right or not.

Can anyone point me to a more comprehensive guide to getting SSRS up & running on a laptop (W10) as a stand-alone environment (if that is even possible or advisable)?

Also, I don't see a Report Builder version for 2017; would it be better if I fell back to SQL Server and SSRS 2016, and worked with those versions?

Thanks
0
query performance question.  I have a query that uses multiple databases(the databases are all on the same instance of sql).  The query takes about a minute to complete.  This is acceptable, but when ssrs runs off this dataset, it is painfully slow.   4 to 5 minutes to render.  

1) is it normal for there to be a drop off in the speed of ssrs displaying the results as compared to the actual speed of the query in ssms.  I suspect there should be but, is 4 to 5 times something that i should be wary of?
0
Visual Studio 2015, SSDT, Report Authoring, Windows 10

I am having a very difficult time getting my VS2015 installation to include SSDT and Reporting Authoring Extension (for Fetxchxml).  I have tried suggestions from the web, Stack Overflow and downloaded several versions of SSDT and Report Authoring Extensions.  I have uninstalled versions that didnt work, rebooted and tried again.

I am thinking that if I can get the versions from someone where all three components are working on Windows, I should be able to get this working.  I have had this working on other laptops, older versions of VS and other operating systems.

My environment is:
Dell Latitude i7 | 64-bit processor | x64 processor
Windows 10  | Version 1803  |Build 17134.167
Visual Studio Professional 2015  | Version 14.0.25431.01 Update 3

As for SSDT, not having a lot of problems installing this; it is where I try to install the Report Authoring Extensions that I encounter problems.  I am able to get the RAE wizard to run (to failure) using the advice from this link: http://meganvwalker.com/creating-fetch-xml-reports-using-visual-studio/

When the RAE install fails it tells me that SSDT is not installed; I have tried many versions of SSDT, SSDT-BI, BIDS and Report Authoring starting with the VS2015 versions (and earlier versions).

I am just spinning my wheels.

Thank you.
0
Good Day Experts!

I have quite the dilemma here.  I have designed my query and it works fine in SMS.  I am trying to go some grouping on the report but perhaps it is not possible.  So can you give me your feedback please?

I have my query in an embedded dataset.  Now I want to run several different grouping patterns over the resulting Student data.

1)  Group by grade with the count of students in each grade
2) Group by teacher with a count of how many students for each teacher
3) Group by gender with a count of how many male or female

I was think it would be "neat" if I could have multiple tables on the report for each Item. One table for the Grade counts, one table for the teacher counts and one table for the gender counts.

If this is not possible, I am open to other design solutions as I am new to SSRS reporting.

Thanks,
jimbo99999
0
I'm needing to find when an order is on time.  The issue is some orders can have items that are considered on time while other items are not.  I need to find when the whole order is on time.  

select order_no, item_id, CASE WHEN (DATEDIFF(dd, dateadd(day,1, requested_date), ship_date)) <= 0 THEN '1' END AS item_on_time
from sales_history
GROUP BY order_no, item_id, requested_date, ship_date

So below is what I'm needing which would be column D
 2.png
0
I have an SSRS data set which returns columns: ClientName, Date, Description, and Miles based on a number of parameters.  I have assigned this dataset as the source for a tablix in my report but cannot figure out how to configure the tablix to get the output to look similar to:

grouping
0
I had to change my login in active directory.  After doing that all of my SSRS (2008 R2) subscriptions wouldn't run because the previous AD entry didn't exist.  I had a tool to make those changes, but I still have the same issue when I create a new subscription.  It says my AD login isn't recognized.  How do I change that?  I've googled it and found some hits, but nothing quite explains how to fix the issue for new subscriptions.  Can I just go to the users table and change the old login to the new login?
0
Hi all,
I'm having a problem reading in an RDLC xml file.  My purpose is to collect information about the dataset(s), e.g. Dataset Name and Table name.
I've been dealing with xml projects for decades and for some reason every piece of code I try returns nothing.
Try
            Dim xmlDOC As New XmlDocument()
            xmlDOC.Load(XMLFILE)

            Dim nodeList As XmlNodeList = xmlDOC.SelectNodes("/Report/DataSets/DataSet")
            MsgBox(nodeList.Count)
            For Each node As XmlNode In nodeList
                MsgBox("DataSet Name: " & node("Name").InnerText)
            Next



        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Open in new window

0
CompTIA Network+
LVL 12
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Hi,

is there's a way to consume JSON API in SSRS?  I did some research and I landed to a 3rd party ODBC driver for ZappySys, is there's a free way, as my company won't be paying for a license.

thanks
0
I have report that lists sales documents. There are several different types of documents so I created a parameter field for each type. In this case, the user will most likely opt to see Invoices so I want that parameter to default to True and the other 4 to default to False. When I try to set the default value of each parameter to either True or False when I run the report those values are completely ignored. At first I tried typing =true in the formula field and then I even tried =Parameter.Field=true. How do I get these default values to be assigned properly?
0
SSRS 2012 - Visual Studio 2012 - SQL 2012
I have a field called "Trend" and a field called "Multiplier" that I was to multiple together.  Then I need to take the sum of those two fields and roundup to a multiple of a field called "Inv_Max"

For example:  Trend is 1572 and multiplier is 3.  (1572 * 3 = 4716)  The Inv_Max field is 2400 so I want to roundup the 4716 to 4800, which is a multiple of Inv_max.

I want to be able to add a calculated field and can't figure out the formula.
0
Good Day Experts!

As a previous other current question I have open, I am working on SSRS reporting of which I am relatively new to.  The query in question I have working in SQL Management Studio.
Knowing that I will need to be able to handle multiple values for a parameter to be processed by the query, I tested my query with a variable and the string 'Q1,Q2,Q3,Q4'. I could have used 'Q1,Q2' or 'Q4,Q3' to simulate the User selection from the dropdown list in the report.  At the top of my query, I first needed to build a function to process each value(Q1 Q2 Q3 Q4) in the list and retrieve the start and end dates for the value from a database table.  After doing that for each of the values in the list, I then built a string for my where clause that checks if a field, which happens to be a date, lies between the retrieved dates.  So, needless to say it was a bit tricky to generate the string but it is working now fine in Management Studio.  

The issue I am having now is that apparently the passing of multiple values to the query in the report is not in the format 'Q1,Q2,Q3,Q4' and my parsing apart and processing is failing.

I have found some information online but it is centered on the query using a "where in" clause in the query.  The other unique thing about this, is that I am specifying the values for the available values for the drop down list.  

So, I am not sure how a multiple value list of specified available values is passed to the query to know how to modify…
0
Good Day Experts!

I am hoping you can help me understand my options for resolving a dilemma.  I have developed a SQL query that runs fine and the desired output is achieved in ManagementStudio.
A parameter to the query(used to simulate User selection from an SSRS report) is a comma separated list.  Using that comma separated list,  I retrieve values from a table in my database and then build a string.  Here is the output of that string for the 4 comma separated values:

(incidentDate >= '2017-08-17' and incidentDate <= '2017-10-12') OR
(incidentDate >= '2017-10-16' and incidentDate <= '2017-12-19') OR
(incidentDate >= '2018-01-03' and incidentDate <= '2018-03-16') OR
(incidentDate >= '2018-03-19' and incidentDate <= '2018-05-31')

I set this to a variable to use in my query string which is then set to @cmdString. I then exec(@cmdString).  Works great no issues.

The issue I am having is when I try to use this in my SSRS report.  The editor does not recognize the fields to display on the report since they are in the string I am executing.  
Then when I try to create that query the "regular" way, I run into trouble because of trying to use a variable in the Where clause. I am not sure what to do.

Please ask questions as I probably did not explain well,
jimbo99999
0
I'm needing to find the DateDiff between two dates in the same column.  B is when an order goes on backorder.   So I'm basically look for how long an order was on backorder.  The difference between when (old_value is NULL and new_value = 'B') and (old_value = 'B' and new_value is NULL).

SELECT        column_changed, key1_cd, key1_value, old_value, new_value, date_created
FROM            p21_view_audit_trail_oe_hdr_1319
WHERE        (item_id = 'BWTREG-DF-1') AND (column_description = 'disposition') AND (key1_value = '2732473')
ORDER BY date_created DESC

Open in new window


results:
  1. column_changed        key1_cd        key1_value       old_value       new_value                  date_created
  2. disposition                     order_no      2732473              B                       NULL                    2018-01-18 18:58:31.633
  3. disposition                     order_no      2732473              D                       B                            2018-01-11 19:10:40.503
  4. disposition                     order_no      2732473              B                       D                            2018-01-11 19:07:57.397
  5. disposition                     order_no      2732473              NULL               B                            2018-01-11 19:07:42.213
0
ssrs question with a user submitting a subscription.  Attached is a screenshot of the subscription filters.  The focus is on these two filters for now
Start Date
End Date

The default for the report is set to Start date = today   end date = today + 1 (tomorrow)

This user wants a subscription configured that sets the start date to "Today - "1  and end date set to "today"

is it possible to submit this kind of logic through the subscription interface provided?  if not, best way to tackle this....
ssrs-subscription-question.jpg
0
I have the following query that executes in SSMS in 1 minute 28 sec. However when I attempt to run it in SSRS it times out. I have increased the Time out to 600 and it still fails. I would use query analyzer to place some indexes however this is our ERP database and each time they work on the DB they restore the database as it was deployed meaning indexes, tables, views, etc. are dropped and I have to rescript them.

The query returns 37557 rows. It began to time out in SSRS when I added the following Join LEFT OUTER JOIN [dbo].[KLL Customer Projections] C ON C.customer = ORD.customer

This is a summary amount that needs added.  Specifically this column C.projection_amt , So at the summary level I have 40 customers. Each with a sales projection amount. So part of my issue is that I'm linking thousands of records to 1

Is there anyway to tweak this query so that it runs more efficiently?
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT  ORD.customer ,
        zzxcustr.cust_name ,
        ORD.Style_Season ,
        zzxseasr.seas_name ,
        ORD.style ,
        ORD.color_code ,
        ORD.lbl_code ,
        ORD.dimension ,
        ORD.MAINLBL ,
        ORD.SCALEFIN ,
        ORD.CLASS ,
        ORD.TYPE ,
        ORD.slsperson1 ,
        SUM(CONVERT(MONEY, CASE WHEN ( ( YEAR(ORD.ship_date_cascaded) = @CurYear
                                         - 2 )
                                       AND ( ORD.line_status = 'I' )
                   

Open in new window

0
In an SSRS report I want to hide the tablix/table when there are no rows appearing. Problem is there are rows, but I hide the rows when column 1 is the same as column2. So my problem is that I don't want the column headers / table to appear when no other row appears. Is this possible I think there are scope issues doing this.

At moment I have two datasets. One retrieves current values, the other dataset retrieves historical data from a specific point in time. So I am using the lookup value '=Lookup(Fields!LabelLookup.Value, Fields!LabelLookup.Value, Fields!Value.Value,"Date2")' to show in one cell what the value was at a specific date and in the other cell what is is now on the same row.  When the two values are the same I hide the row because there was no change. This works fine, but sometimes I am left with just the table header showing no rows. However, there might be lots of rows but are actually duplicate, as the value has not changed between the two dates.

Is there a way to check if there are no rows appearing in the table after the hide rows has been applied? I'm not sure it is possible? If not is there another way I might be able to do this?

Using sql server and SSRS instance 2014
0
Become a CompTIA Certified Healthcare IT Tech
LVL 12
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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
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
Visual Studio 2010

I have an SSRS report that looks like:2018-08-30-Contact-Overlap-Repeat-He.jpgThe report is correctly grouping on Program and County and each new occurance is going to a new page.
But I cannot seem to get any of the column headers (the yellow is hidden in the report view)  to repeat on new pages.

I've tried various combinations of Group Properties and "static" properties but cannot seem to get this right.
Any help would be greatly appreciated.
0
I have an SSRS report that in design view looks like:contact overlapand in report view looks like:with dataWhat I would like to do is reformat this so that the Program Name and County show up on the same line, and the Staff Name value would show up right below (not offset) the Program and County.

I assume I can modify my group to group on both ProgramName and CountyName, but how would I reformat this so that the Staff name falls immediately below the Primary Group heading?
0
How do I  get current logged in user to be used as parameter in a report so I can implement row level security on dataset?
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.