SSRS Help & Support | Experts Exchange

Question by

janhoedt

5d agoSolved

Diff date

Hi,

In ssrs I get diff from two dates.
How do I get also hours and minutes (now some results show 0 since diff is hours minutes only.

Thanks!

6 Comments

Hi,

I'd need a sql query to color code a date:
Items = status NOT equal to 'Status01', 'Status02'
For all items which have date less then a week => green
older then a week => orange
older then 2 weeks => red
Items which have Status Status03
and have date
older then 2 weeks orange
older then a month red

Thanks for helping with the approach!

J

3 Comments

Hi,

I mail my ssrs report on a daily basis. Opening it in mail (mobile device) takes 30 seconds, so people think the mail is not working and delete it.
Also loading it on Windows, it still takes like 3 seconds to load.
How can I make my report more leightweight?
Suggestions: show less items on a page (there are a few 100 rows now, howto?), leighter formatting (like I have gridelines for better visibility ... howto?) ...?

J

2 Comments

Hi,

I have an SSRS KPI in which I have totals like the following expression:

=Sum(IIf(Fields!StatusName.Value = "Status01", 1, 0))

Now, how do I make more difficult queries: like
sum of all items where statusname.value = status02 and status03 but not empty status but also status06,status07

J.

3 Comments

Hi,

I have an ssrs report for a ticketing system which has names, ticket number etc.

I d like to add several filters:
-show my tickets = filter on username, all tickets
-environment: UAT/PRD
-statuses: open/not resolved, closed

This way a user of the form can see/filter on only specific data like.his requests which are open in prd

Please advise.
J

5 Comments

Question by

Fred

2020-02-14Solved

Data set query Error

I am using this data set query that is generating error , The dated diff function requires 3 arguments(s). Incorrect syntax near Label 1. Incorrect syntax near 'Group Order'. The Query generates data for a DemographicsPar2(parameter. I am building a ssrs has board that uses this parameter.

SELECT DISTINCT 
                  'Age' DemographicGroup, 
CASE 
WHEN DATEDIFF(YY, DOB, @ReportDate) < 25 THEN '<=25'

 WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 25 AND 35 THEN '26-35' 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 36 AND   45 THEN '36-45' 
WHEN DATEDIFF(YY, DOB, @ReportDate) > 45 THEN '46+' END Label, CASE 
WHEN DATEDIFF(YY, DOB, @ReportDate) < 25 THEN 1 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 25 AND 35 THEN 2 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 36 AND 45 THEN 3 
WHEN DATEDIFF(YY, DOB, @ReportDate) > 45 THEN 4
END GroupOrder

INTO        #Demo2
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Marital Status', MaritalStatus, 1
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Race', Race, 1
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Sex', Sex, 1
FROM     [dbo].[Dim_Borrower]
                      SELECT *, DemographicGroup + ' - ' + Label RealLabel
                      FROM     #Demo2
                      WHERE  DemographicGroup IN (@Demographics1)
                      ORDER BY DemographicGroup

Open in new window

When I run this query on the SSRS query Designer, the output shows Three columns. DemographicGroup / Label / GroupOrder/ Real Label ..

1 Comment

Question by

janhoedt

2020-02-13SolvedPrivate

SQL reportingserver

Hi,

I have an ssrs report which I would like to make dynamical (like a Powershell Crusties): when you click ok a column of sorts, when you search, it filters, ideally have also search filters and some nice formatting too (now it is a boring formatting).
One more thing: can I link to another ssrs report (clickable link)?

J

7 Comments

Hi,

I have a GUI which talks to a sql db backend via powershell and sql queries.

I execute queries remotely but now discovered stored procedures and views.
Created some views and a stored procedure.

What is the specific purpose of each item, the difference and what should I use for my gui?
Also, can I use the view for ssrs report?

J

5 Comments

Hi, is there anyway to Stop SSRS from Building ALL reports when Previewing in Visual Studio ?

Many Thanks

1 Comment

I recently moved SSRS to a new server. The migration went fairly well and most of the reports are running fine on the web server. I've been going through each project to point the project to the new report server location and then updating the connection string on shared data sources to again point to the new server. Almost all of my data sources point to one of 2 catalogs (one for finances and one for fundraising) and I have successfully updated many of the projects without an issue for both catalogs. However, I have a folder that uses both data sources. I've made all the changes to point to the new server and when I test the connection string, it is successful. However, if I try and run the query for the financial data source, it errors out with the following error (last paragraph). If I turn on the old SSRS server, it works fine. I've checked and double checked that everything points to the new server and it does. Again, this data source is used in other projects and works fine there - just not in this one project. I've even deleted and recreated the data source and it still won't work. I can't figure out what the problem is... and feel the error is misleading.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - …

3 Comments

LVL 6

Question by

allanau20

2020-01-31SolvedPrivate

SSRS Expression #Error

Hi Experts!

Have a simple report that display from a sproc. The sproc is like this (the fields are varchar(16) for SomeTable:

SELECT FirstName,
       LastName,
       '0' AS MealCost,
       ISNULL(CAST(SUM(Field02) AS VARCHAR(16)), '0') AS Field02,
       ISNULL(CAST(SUM(Field03) AS VARCHAR(16)), '0') AS Field03,
       ISNULL(CAST(SUM(Field04) AS VARCHAR(16)), '0') AS Field04
FROM dbo.SomeTable

Open in new window

Here's the report layout: The Total is added after the group.

Have tried these for the MealCost and the total comes out as #ERROR:

=SUM(Fields!MealCost.Value)
=IIF(IsNothing(Fields!MealCost.Value), 0, Fields!MealCost.Value)
=IIF(SUM(Fields!MealCost.Value) IS NOTHING, 0, Fields!MealCost.Value))

Open in new window

Here's how the report should look like (I'm using Excel as an example)

any ideas?

TIA!

5 Comments

How to convert sqlserver database schema to no sql database such as Cassandra or mongodb. Is it some api can be used to convert or use database structure to do that or any other way.

1 Comment

I was having a heck of a time trying to create a report in Visual Studio 2017. I removed references to version 9 and installed 150.1400 from nuget and added the references back. I was never able to get a report viewer onto a form. It always ended up at the bottom as a component like the timer or backgroundworker. I uninstalled 2017 and installed 2019 after hours of unsuccessful google fu trying to fix it. 2019 allows me to drop a report viewer onto a form but I am missing the add new item - report option. So now I can drop a report viewer onto a form but I can't add an RDLC to my winforms project. I may go back to VS 2010 where things just worked...This is crazy frustrating.

Any ideas on getting the report creation option into the menu?

Thanks

3 Comments

Hi,

I d like to have a daily or weekly sql ssrs report of ALL software users have on their pc (add/remove programs?) by querying our sccm 2012 server (latest version) plus number of installs per item/preferably also clickable that it shows the list of pcs per software.
Nice would also be to map it to sccm.itself: do.we have it in sccm (or was it installed nu user manually).

I d like to be able to exctact the data in Powershell so I can work with it. F.e. $alllsoft | get top 10 foreach ...

Thanks!
J

1 Comment

I moved my SSRS application to a new server. My last step was to get my SSL certificate working for the new server. I’ve done quite a few, but cannot get this one to work. It is through Godaddy and I’ve talked to them several times. Today I was told that they aren’t training in certificates for IIS so they can’t help me.

I’ve followed the instructions in this article:
https://www.godaddy.com/help/manually-install-an-ssl-certificate-on-my-iis-8-server-4951

But there is some misleading steps and I don’t know if that is why it isn’t working? Step 14 says to upload the intermediate (.p7b) to MMC. I’ve done that. The next steps are in IIS and on step 22 it says to find your primary certificate that you previously uploaded. There aren’t any steps that say to import it on MMC so I’m not quite sure what that means. I complete the install of the primary certificate (.crt) on IIS and then do the bindings. I then restart the services. But the website never works. On IIS everything looks ok. I’ve actually started the old server and compared what I see in the 2 servers and they look the same. I’m not sure what to do? Any thoughts??

30 Comments

I have a SQL 2017 SSRS report that is grouped by state by month. There are two lines per state with the 12 months spread across each line. The first line is total invoice amount and the second line is total sales tax amount. I am now trying to configure the footer to show the total for all states with one being the total for the sales amount and the other being the total for the sales tax amounts. I do have a column called Type where Sales = sales amount and Tax = sales tax amount. I tried creating two groups at the bottom based on Type with filters but both lines keep giving me to total of both. Underneath this report is a SQL query with a union between the sales amounts and the sales tax amounts. How should I structure the totals to present the two separate totals?

2 Comments

I recently moved my Windows 2008 R2 / SQL 2008 R SSRS install to a new server (Windows 2016) and SQL (2014). I thought everything was working fine - we are getting subscriptions emailed internally every day and all of the reports are running fine. However I just discovered that subscriptions to external emails are not being sent out. I'm getting one of two errors:

unable to send to a recipient (happens when I have both an internal email and an external email)

failure sending email – mailbox unavailable with a server response of 5.7.54 unable to relay recipient in a non-accepted domain (happens if it is just external email)

Our exchange server does not have relay/connectors set up for the old SSRS server so I didn't set up relay/connectors for the new one. The SSRS configuration app has the same settings for email on the old and new server. The config file is also identical for email. I'm using the same login to send reports on both servers. I've run a trace on the email from that account and it shows all of the reports sent to internal emails addresses but doesn't show a thing for those sent to external email addresses. I have no clue what is causing the error and don't know if it is because of a difference in Windows versions or SQL versions or ??

Our email is 365 but we have a local exchange server that syncs active directory with 365.

Can anyone help?

8 Comments

LVL 41

Question by

Vadim Rapp

2020-01-03Solved

Javascript: open popup, wait till loaded, close

In SSRS report showing pending invoices, in the last column I have a hyperlink that marks the invoice as sent. This is accomplished by setting Action to the following:

="javascript:w=window.open('http://myserver/MarkInvoiceAsSent/?invoice_id=12345');w.close();document.location.reload(true);"

"MarkInvoiceAsSent" is an asp page that quietly performs the requested database transaction, with no output. "12345" is for this example only, in fact it substitutes the correct value from this row.

The problem is that w.close closes the page before it has opened, so the transaction is not performed.

Is there a way to modify javascript so that it would wait until the page is complete before closing it? Or perhaps some other way to accomplish this.

1 Comment

HELP!

I can't I seem to add the appropriate grouping, to simply allow for all records to be seen..
My SSRS report is returning only the 1st record..
The query within SSRS is returning 276 records in query designer..
e.g. right click Dataset, "Query", and feed the query the two parameters it needs.
I have a number of fields that are all inside of a tablix control..
And so I dragged my field to group on, down to "Row Groups"
I went into that Group Properties, and chose to insert a Page Break "Between each instance..."
Same result.
I then right clicked that group, and chose "Add group / Child Group"
I chose "Show Detailed Data", hit OK, re-ran report, and am getting only 1 record...every time

I cannot figure out what I'm doing wrong...

Thanks,
Rich

12 Comments

I have sql server 2012 ssrs subscriptions that send reports in excel format

I have been asked if there is a way within ssrs to password encrypt these

2 Comments

I'm trying to design a report based on a function query in our ERP. (as in, under Database/Programmability/Functions/Table-valued Functions)

The FROM clause is this:

FROM p21_fnt_invoice_hdr('COMPNAME', @ls_begin_invoice_no,@ls_end_invoice_no, 0, 9999, '0', 'ZZZZZZZZZZ', 0, 999999999, ' ', 'ZZZZZZZZZZ', ' ', 'ZZZZZ', 0,999999999, {ts '1900-01-01 00:00:00.000'}, {ts '2019-12-05 23:59:59.000'}, 'Y', 'N', 'N','B','Y', 'N','','N')

Open in new window

If I run this in SSMS, providing the two parameters (@ls_begin_invoice_no and @ls_end_invoice_no), it returns the data as expected.

But, I cannot figure out how to make SSRS work?

I get no results if I try & apply parameters..
No errors, just no results

thanks,

Rich

2 Comments

When a client purchases SQL 2019 by Core which version of SQL 2019 should I install?
SQL Standard Edition - Per Core
SQL Standard Edition - Server/CAL

I installed it per core but then when I try to install SSRS and use the product ID it tells me that it is not a valid product ID. Plus when I download off the MS VLSC there appears to be only generic downloads. When I click on Key it says no keys are required for this product. I have entered the client's license key and authorization number but no where else do I see anything else specific to this client.

5 Comments

Hi,

I have an ssrs report in which I show software items in a table.
F.e. Mozilla Firefox 61 Detail1 Detail2 ...
Google Chrome 70 Detail1 Detail2 ...

I have a query which gives all details of these software items but that wouldn t fit on the page.

Can.I make a software item clickable (f.e. plus next to it) so it expands and shows all. the data?

If yes, how do I do this?

2 Comments

I have a SQL Server Reports database (mdf & ldf) that survived the crash of a SQL server which had to be rebuilt. The tables are valid. I can do queries on them, such as

select * from catalog

the most important table, as I understand it.

But I cannot use the database as a data source in my newly built SQL Server. I get an error trying that.

So I built

3 Comments

Hi,

I have an SSRS report (SSRS 2016) which I would like to refresh every minute.
Found this link https://www.mssqltips.com/sqlservertip/5916/sql-server-reporting-services-ssrs-auto-refresh-report/ but I do not see the option of refresh.

Have changed the cached setting to expire after 1 minute but the execution time on the report doesn't change after one minute.
If I refresh the page, it does show new execution time, but the html should refresh itself every minute.

Adapted the rdl file with <AutoRefresh>60</AutoRefresh> but still no luck.
Please advise.
J.

3 Comments

SSRS

Related Topics

SSRS

Related Topics