Count records manually

Hi everyone, I'm a newbie with this Crystal Reports thing and I was wondering if there is anyone who can help me.

My table has a field named obsolete, depending on the type of "obsolete" I would like to count the number of ocurrencies in my table.

I know I could do this by grouping the field but, I need to group with other 4 fields and I've tried it like that but I guess that the only way that I'll be able to retrieve the data the way I want it is manually. So, is there any if statement or while statement that I could use?

Thanx for any repliesI guess you could try to use if statements in suppressed formulas (look up information on running totals in CR).

I had a similar requirement and found it easier to decode the values in my view (I'm using Oracle PL/SQL).

decode(classId,1,trafficcount,0) class_1,
decode(classId,2,trafficcount,0) class_2,

Ie. If the classId is 1, put the trafficcount in column called class_1, else put 0.|||Thanx for your reply kristyw. Im using informix, I'm not quite sure I can use the decode function, or is it a CR function?

Count on text fields

Hello, I works with the reporting services and is thereby reports to
providing. Now the question arises whether one can count into the
reporting services (in the report designer) also on text fields.
Super would be nice if me one help could.You need to clarify the question a little more.
Do you mean a SUM line, or just a text box that has a summation in it? etc
"TR" wrote:
> Hello, I works with the reporting services and is thereby reports to
> providing. Now the question arises whether one can count into the
> reporting services (in the report designer) also on text fields.
> Super would be nice if me one help could.
>

Count Occurances Of Given Value

Hello All,

I have a question that has been vexing me for some time now. It keeps coming up when I'm trying to write queries for SSRS reports. Lets say I have a table that has 3 columns to keep track of people's gender in a annonomys survay (very basic example):

Month (varchar) | Year (smallint) | Gender (bool)

I want to return a dataset that is grouped by Month and Year and that contains a count of each Gender which would look something like this:

Month | Year | [Male Count] | [Female Count]

January | 2006 | 100 | 120
February | 2006 | 130 | 110
March | 2006 | 120 | 145
April | 2006 | 105 | 125

How would I acheive a dataset like this? Is it possible? Do I need to join the table to itself? If so do I use an Inner Join, an Outer Join, or a Left/Right Join? Any help would be extremely appreciated.

Thanks!

Tennyson

There are several possibilities. One option:

SELECT [Month], [Year],
COUNT(CASE WHEN Gender = 0 THEN 1 END) AS MaleCount,
COUNT(CASE WHEN Gender = 1 THEN 1 END) AS FemaleCount
FROM YourSurveyTable

GROUP BY [Month], [Year]

-Sue

|||

Thank you Sue! Your help was much appreciated.

Thanks,

Tennyson

Count Occurances Of Given Value

Hello All,

I have a question that has been vexing me for some time now. It keeps coming up when I'm trying to write queries for SSRS reports. Lets say I have a table that has 3 columns to keep track of people's gender in a annonomys survay (very basic example):

Month (varchar) | Year (smallint) | Gender (bool)

I want to return a dataset that is grouped by Month and Year and that contains a count of each Gender which would look something like this:

Month | Year | [Male Count] | [Female Count]

January | 2006 | 100 | 120
February | 2006 | 130 | 110
March | 2006 | 120 | 145
April | 2006 | 105 | 125

How would I acheive a dataset like this? Is it possible? Do I need to join the table to itself? If so do I use an Inner Join, an Outer Join, or a Left/Right Join? Any help would be extremely appreciated.

Thanks!

Tennyson

There are several possibilities. One option:

SELECT [Month], [Year],
COUNT(CASE WHEN Gender = 0 THEN 1 END) AS MaleCount,
COUNT(CASE WHEN Gender = 1 THEN 1 END) AS FemaleCount
FROM YourSurveyTable

GROUP BY [Month], [Year]

-Sue

|||

Thank you Sue! Your help was much appreciated.

Thanks,

Tennyson

Couldn't connect to reports

Hi,

I'm not able to browse through http://localhost/reports.

When I'm trying to connect it gives me "Server Error in '/Reports' Application."At the same time I'm able to view Reportserver.At the same time I'm able to view Reportserver.(i.e)http://localhost/reportserver

please can anyone suggest me some solution to this problem..

Server Error in '/Reports' Application.

---

The trust relationship between the primary domain and the trusted domain failed.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.SystemException: The trust relationship between the primary domain and the trusted domain failed.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SystemException: The trust relationship between the primary domain and the trusted domain failed.

]

System.Security.Principal.NTAccount.TranslateToSids(IdentityReferenceCollection sourceAccounts, Boolean& someFailed) +1167

System.Security.Principal.NTAccount.Translate(IdentityReferenceCollection sourceAccounts, Type targetType, Boolean& someFailed) +42

System.Security.Principal.NTAccount.Translate(IdentityReferenceCollection sourceAccounts, Type targetType, Boolean forceSuccess) +46

System.Security.Principal.WindowsPrincipal.IsInRole(String role) +205

System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection roles, IPrincipal principal) +120

System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal user, String verb) +300

System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal user, String verb) +191

System.Web.Security.UrlAuthorizationModule.OnEnter(Object source, EventArgs eventArgs) +3403086

System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92

System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

Hi,

What are the security settings in IIS for both sites?

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi,

we had given full control to the administrators as well as system users.

And read and execute permission to reportserveruser as well as webservice user.

Thanks

Anandhi

|||

That is for NTFS security. What are the permissions set on the virtual directory?

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi,

Execute Scripts only permission for both

Thanks

Anandhi

|||

I mean in the Directory Security Tab.

There you can see whether the site is accessed via:

anonymous (+ which account)|||

Sorry for misunderstanding,

We have chosen for Integrated Windows Authentication

Anonymous is not enabled.

Thanks,

Anandhi

|||

Is this the same as for the report server?

By default this virtual directory is mapping to a folder in your C:\Program Files\... (for me it is C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportManager). This means that the users of the http://localhost/reports url, must have read permissions to it.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Permissions for both reports and reportserver are one and the same.Read permission is there.And it is mapped to the folder as you had specified.

Thanks,

Anandhi

|||

Did you migrate from one domain to another?

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

No we havn't migrated to any other domain.

Thanks,

Anandhi

Could not start the SQL Server Reporting Services (REPORTS) service on local computer.

Error 1069. The service did not start due to login failure.

I can't get a domain login account with domain admin rights to start the service. The only way I can start the service is with the LocalSystem account, which isn't a good solution because it's not networkable.

Thank you for any advice you have.

Does your domain account has "Log on as a service" right?|||Yes. My domain account has it in Active Directory and on the server under Local Security Policy.|||Interesting ... can you run any other service with your domain account? Is this problem specific to RS service?|||I just tested stopping and starting another service with the same domain account and it ran that service successfully. It appears that the problem is just related to SQL Server Reporting Services.|||Perhaps the password changed and your RS service still has the old password? Sorry I can't think of anything else.|||

How do I know what password the RS Service has or should be? I know the password hasn't changed for our domain account. I verified it works for other services.

Here is more information:

I have two servers:

One database server: SQL Server 2005 SP1

One web server with IIS running multiple websites with ASP 1.1 and ASP 2.0 and SQL Server 2005 Reporting Services. I configured Reporting Services from my web server where my SQL Server Reporting Services account is that won't login as a domain administrator account with administrator rights and log on as a service rights.

Is there some other type of rights that may be missing related to SQL Server Reporting services?

|||Also, I found that the WebServiceAccount was not set in the RSreportserver.config file in Program Fiels\Microsoft SQL Servre\MSSQL.1\Reporting Services\ReportManager so I set it to IWAM_ComputerName with the ComputerName as the name of my computer. Then I tried restarted the Reporting Services service, but it didn't start. I also can't get the ASP.NET service to start. I tried to start it using the IWAM_ComputerName account, but I need to figure out what the password is for that account to start the service.|||

rsreportserver.config file should be in ...\MSSQL.1\Reporting Services\ReportServer directory. To set web service account, please use the configuration tool. Setting it to IWAM_ account is incorrect. On XP/Win2K, the web service account is by default <MachineName>\ASPNET. On Win2K3 it is the app pool identity of your report server virtual directory.

When you say you can't get ASP.NET to start, what did you try? You shouldn't do anything. The ASP.NET worker process should start when the first request is received.

|||

The configuration tool doesn't grab the ASP.NET account web service. It won't pick it up or let me type it in. The service is listed in Services, but wasn't started. The only way it would start was with a login of LocalSystem.

In another website, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17756&SiteID=1, a Microsoft Lead Reporting Services person, Tudor Trufinescu, recommended added the Web Service Account if it was missing in the config file.

I'll remove it though.

The article also said that if I'm using IIS 5, which I assume I'm using since I have Windows 2000 Advanced Server, that the ASP.NET account should login using MACHINE\ASPNET account. This never worked. I had to change it to the LocalSystem login to work. I thought this problem may be related to the Reporting Services problem since it's an option in the Configuration Tools that isn't configured.

Thank you.

|||

The configuration tool should pick up ASP.NET account from machine.config file. It will not let you type in if you are on IIS5. Are you seeing blank "ASP.NET service account" box?

I am still confused when you talk about "starting the service". Which service is this? (What is the name of the service in service controller manager?) RS has a windows service that is named "SQL Server Reporting Services". On IIS side, you don't control ASP.NET worker process identity since you are on Win2K/IIS5.

|||

Yes. I am seeing a blank ASP.NET service account box.

The two services I was referencing were:

SQL Server Rerporting Service - will only start with a logon of LocalSystem account - not a good solution because I need to use a domain account that will login across the network into a database.

ASPNET service - will only start with the LocalSystem account. I just mentioned this to note it. Wasn't sure if it also needed to start using some other type of login.

Could not load file or assembly 'Microsoft.SqlServer.Express.Reports, ....

freshly installed SSMSE and upon opening get the following error...

Could not load file or assembly 'Microsoft.SqlServer.Express.Reports, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Strong name validation failed. (Exception from HRESULT: 0x8013141A)

Any ideas how to get past it?

This is really odd. Could you try the following...

1) Uninstall everything you have right now

2) Download the latest version directly from Microsoft

3) Install the latest version

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server

http://blogs.msdn.com/sqlrem/

Could not connect to http://localhost/Reports

Hi,
I have a problem when I try to connect to Report Server which is installed
locally. (http://localhost/Reports). When I enter this address in the
address bar, the progress bar starts to grow but the page will not be
opened. Eventually, my PC will be frozen and I will have to restart my
machine.
The installation went without any problems. SQL server 2000 is installed
locally and the OS is Windows XP SP1. What confuses me the most is that
everything was working ok, several months ago. Exactly the same behavior is
on the other PC. Report Server installed locally is not responding.
I suppose that maybe some security patch caused this strange behavior but
this is only guessing.
What might be the reason for this?
Any idea will be appreciated.
Thanks in advanced,
Mladen
--
Message posted via http://www.sqlmonster.comCheck out the Event Viewer.
Also check the log files under the LOG folder. c:\program files\SQL
Server...
Do a search for LOG...
"Mladen via SQLMonster.com" wrote:
> Hi,
> I have a problem when I try to connect to Report Server which is installed
> locally. (http://localhost/Reports). When I enter this address in the
> address bar, the progress bar starts to grow but the page will not be
> opened. Eventually, my PC will be frozen and I will have to restart my
> machine.
> The installation went without any problems. SQL server 2000 is installed
> locally and the OS is Windows XP SP1. What confuses me the most is that
> everything was working ok, several months ago. Exactly the same behavior is
> on the other PC. Report Server installed locally is not responding.
> I suppose that maybe some security patch caused this strange behavior but
> this is only guessing.
> What might be the reason for this?
> Any idea will be appreciated.
> Thanks in advanced,
> Mladen
> --
> Message posted via http://www.sqlmonster.com
>|||I have tried everything (that is why I did not answer earlier).
I have reinstalled Reporting Services using Domain Admin account and still
is not working.
I used built-in account (NTAUTHORITY\SYSTEM) for ReportServer Windows
service and xxx\ASPNET (xxx is the name of my machine where I try to
install it) for auto start service account. Since nothing was working
before, now I used domain admin account to connect to the Report server
database. Of course, it can connect to Report Server database.
There are no errors in the Reporting Services logs and the last thing that
can be seen in the log is "Polling started".
NT Event log does not contain errors as well.
This is all frustrating and I don't know what else can I do to solve this
(or where to look).
--
Message posted via http://www.sqlmonster.com|||What is more "interesting" is that my colleagues all have the same problem.
Everything was working ok some time ago but one day it stopped functioning.
--
Message posted via http://www.sqlmonster.com|||What is more "interesting" is that my colleagues all have the same problem.
Everything was working ok some time ago but one day it stopped functioning.
--
Message posted via http://www.sqlmonster.com

Cost of using Analysis Service

We have purchased SQL Server 2005, and we are planning to use the Reporting Services and Analysis Services to generate reports, KPIs and perform some OLAP analysis.

My question is that apart from the server licence of SQL Server, is there any other licence cost for the solution deployment, like developer licence, client access licence, etc.?

Thanks,

Chris

No, if these are all part of your SQL Server license. So if you have approriate licensing for your SQL Server you are also licensed to use SSRS and SSAS. But note that the SQL Licensing is per server, so if you want to put SSRS or SSAS on different hardware, they will need their own licenses at that point.|||

Darren Gosbell wrote:

No, if these are all part of your SQL Server license. So if you have approriate licensing for your SQL Server you are also licensed to use SSRS and SSAS. But note that the SQL Licensing is per server, so if you want to put SSRS or SSAS on different hardware, they will need their own licenses at that point.

Do you mean the end-users do not need any additional software to view the reports and OLAP cubes?

How? Please advise.

Thanks,

Chris

corrupted values in database

I just started working on reports on an SQL 2000 database.
Many field contain numerical values stored as varchar. To make matters worse these strings contain both '.' and ',' as decimal sign.
So far this has really been a great pain :-(

The database and its application are bought as is, I cannot modify fieldtypes or anything else.

Can anyone give a good strategy to convert the strings back to numerical so I can calculate revenue's and such??

P.S.

In extreme cases string contain both ',' AND '.' , like:

5.608,42Use

convert (float, replace(column_name,',',''))

or convert (int,replace(replace(column_name,',',''),'.','') if you are sure the value is an integer|||Note quite what I need,

The string can contain either a . or a ,
With the conversion you give all figures with a ',' come out 100 fold higher than they should be, while those with a '.' come out right.

Some sort of validation seems to be needed to get all them right.

Right now I am focussing on prices which means the highest value is just 900.00 (or 900.00) so I have no problem in this column with both a ',' and a '.'|||The following seems to work:

SELECT
CASE
WHEN
SUBSTRING(REVERSE(RTRIM(LTRIM(PRIJS))),3,1) = ',' THEN
convert(float,replace(PRIJS,',',''))/100
WHEN
SUBSTRING(REVERSE(RTRIM(LTRIM(PRIJS))),3,1) = '.' THEN
convert(dec(9,2),PRIJS)
ELSE 0 END

not getting very good feelings though on having to do these king of conversions :-(|||No, me either! You should consider to clean your data before reporting them. You are not allowed to change the field type, but you should be allowed to update your data. So, define your required format, and update everything, that does not match your format, first. Your format may be "#.###,##" or "####.##"; I would prefer the last, because that can be converted into a number directly. You may even consider to convert everything to cents, which makes it easy for you to detect new values, that are not yet converted.

Another approach would be to identify your different formats, and make a union query, handling each of your formats in a separate branch.|||Can't do that.

Some of the fields are filled by procedures from the AS400 system.
THere is something fundamentaly wrong with the way the data is handled on the SQL database. (For which I just mailed an angry mail to all involved) ,

but doctor maybe you can help me with this one:

I have all the differnent flavors in the DB:

1.203,56
1,203.56
456,67
456.67

You see replacing the comma is okay if it comes as first one in the string.
If there is just a comma replace it by a '.'
If a '.' precedes a ',' replace ','by '.' and get rid of the '.'

Nice challenge ain't it?

Next private message next week, gotta cycle to the north this afternoon
:-)|||No, me either! You should consider to clean your data before reporting them. You are not allowed to change the field type, but you should be allowed to update your data. So, define your required format, and update everything, that does not match your format, first. Your format may be "#.###,##" or "####.##"; I would prefer the last, because that can be converted into a number directly. You may even consider to convert everything to cents, which makes it easy for you to detect new values, that are not yet converted.

I dont uderstand the format "#.###,##" . What does it exactly mean ?|||Originally posted by blom0344
Can't do that.

Some of the fields are filled by procedures from the AS400 system.
THere is something fundamentaly wrong with the way the data is handled on the SQL database. (For which I just mailed an angry mail to all involved) ,


I understand, that you don't have control of the import process, but who is preventing you from cleaning your data afterwards?

Originally posted by blom0344
I have all the differnent flavors in the DB:

1.203,56
1,203.56
456,67
456.67

You see replacing the comma is okay if it comes as first one in the string.
If there is just a comma replace it by a '.'
If a '.' precedes a ',' replace ','by '.' and get rid of the '.'

Nice challenge ain't it?


As I said, determine first your possible flavors. What is imported when your price is 456.- or 456.50? Is is 456[,|.]00 or just 456? So, actually I'm asking whether your decimal point is always on the 3rd last position? Your algorithm depends on that. Another point is whether you can expect a maximum number of digits, or not. So, can you also have, for example 1,234,567.89?

Assuming, your possible flavors are those 4 you gave me, a query like I propose would look like (your price field is called p)

SELECT p FROM T WHERE len(p)=6 AND SubString(p, 4,1)="."
UNION
SELECT replace(p,',','.') FROM T WHERE len(p)=6 AND SubString(p, 4,1)=","
UNION
SELECT replace(p,',','') FROM T WHERE len(p)=8 AND SubString(p, 6,1)="."
UNION
SELECT replace(replace(p,'.',''), ',','.') FROM T WHERE len(p)=8 AND SubString(p, 6,1)=","

Calling this union query U you can do your accumulation like

SELECT sum(cast(p as decimal(10,2))) from (<U>)|||-- replace(replace(p,'.',''), ',','.') --

Yep,

I am working with the replace on replace in my solution as well.
Does not seem to work like it should.
I want to use the CASE instead of UNION solution , cause I am going to assign it to a BO object

We'll continue next week...|||If you know you have only two decimal places, how about something along the lines of eliminating all commas/periods, then dividing by 100?

select convert(numeric (10, 2), replace(replace(value, ',', ''), '.', '')))/100.00

A bit pressed for time here, so I have not tested that code snippet. Hope it helps.|||Elegant solution, MCrowley. :cool:

...but make sure the values don't just only have two decimal places, but that the ALWAYS have two decimal places.

blindman|||Originally posted by blom0344
I just started working on reports on an SQL 2000 database.
Many field contain numerical values stored as varchar. To make matters worse these strings contain both '.' and ',' as decimal sign.
So far this has really been a great pain :-(

The database and its application are bought as is, I cannot modify fieldtypes or anything else.

Can anyone give a good strategy to convert the strings back to numerical so I can calculate revenue's and such??

P.S.

In extreme cases string contain both ',' AND '.' , like:

5.608,42

Dumb question ... but, any chance you are dealing with software that handles multi-currency? Do you have the CCSID translation turned on from the AS400 to the SQL server?|||Most certainly not a dumb question, but in this case we are talking about E-commerce orders from two euro countries, so every order amount is always just in one currency.

McCrowleys solution does indeed work for me, cause every order is calculated down to the euro-cent

Corrupted PDF in subscriptions

Hi,
I'm actually in trouble with some of my reports. Those reports are randomly
producing corrupted PDF files in subscriptions.
The only way for me to avoid those errors is to remove report caching.
Here is my original post which explains tests i have made and patches i have
applied
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=5e36216a-766a-40db-b230-997cdcba97a0
I'm really stuck right now and desesperatly looking for help.
Thanks in advance.
Grégory.What happens if you use the export feature to export the report? Does tha
produce a corrupted file?
What is the error that the Acrobat reader outputs when reading the file?
What version of the report server are you running - are you at SP1?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg - NEOS" <GregNEOS@.discussions.microsoft.com> wrote in message
news:E14E2070-1887-4F69-955A-B1AD0CE8D3FD@.microsoft.com...
> Hi,
> I'm actually in trouble with some of my reports. Those reports are
> randomly
> producing corrupted PDF files in subscriptions.
> The only way for me to avoid those errors is to remove report caching.
> Here is my original post which explains tests i have made and patches i
> have
> applied:
> http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=5e36216a-766a-40db-b230-997cdcba97a0
> I'm really stuck right now and desesperatly looking for help.
> Thanks in advance.
> Grégory.|||Hi Lukasz,
When I use export feature, the file isn't corrupted.
The error produced by Acrobat is: "An error happened when opening documents.
The file is corrupted and can't be repared".
I'm actually using Reporting Services SP1.
Best Regards,
Grégory.
"Lukasz Pawlowski [MSFT]" wrote:
> What happens if you use the export feature to export the report? Does tha
> produce a corrupted file?
> What is the error that the Acrobat reader outputs when reading the file?
> What version of the report server are you running - are you at SP1?
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Greg - NEOS" <GregNEOS@.discussions.microsoft.com> wrote in message
> news:E14E2070-1887-4F69-955A-B1AD0CE8D3FD@.microsoft.com...
> > Hi,
> >
> > I'm actually in trouble with some of my reports. Those reports are
> > randomly
> > producing corrupted PDF files in subscriptions.
> > The only way for me to avoid those errors is to remove report caching.
> >
> > Here is my original post which explains tests i have made and patches i
> > have
> > applied:
> > http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=5e36216a-766a-40db-b230-997cdcba97a0
> >
> > I'm really stuck right now and desesperatly looking for help.
> >
> > Thanks in advance.
> > Grégory.
>
>