Reporting services and filtering

Hi,

I have being trying this for quiet a while now and I just cant find a solution. Basically I have a field called Total Active. The total active indicates for example..... A project is open or closed. If the project is open, the value 1 is displayed in the database and if closed the value 0 is displayed in the database.

So I have my field in reporting services and now I just want to count all the open projects. I thought you could do this:

= Count(Fields!Active_flag.value = 1)

This does not seem to work.....or my syntax is wrong or something.

Then I tried to pass all the values in Fields!Active_flag as a object using VB custom code. Example:

=code.getTotalActive(Fields!Active_flag.value)

then receive the Object and loop through it to count all the 1's in the Object (Active_flag.value) and pass the total back. However I was not successful with this because I couldn’t get the length of the Object....which I needed to iterate through all the values.

My next attempt which I thought was a sure winner was using a group and filtering out all the 0's which left only 1's that I could count. But this brought about another problem.....the filter only looks for 1's and then counts the 1's and displays then.... But when it doesn’t find any 1's then the entire Field does not display on the report. This is very frustrating because I would rather have is say "no projects active" instead of it not displaying.

Any help will be much appreciated. I have boggled my mind for the solution.

thanks

Ziyaad

I had something similar in my report but it is not that simple because you need CASE Statement to split the column into two Case Active 1, Case Inactive 0 but SQL Server will not recognize your derived columns as valid so you need to put both in temp table and do the calculation. There maybe a more elegant solution but this will give you what you want. I have done many reports both Crystal and SQL Server in the Enterprise so I know that is not an expression problem because SQL can solve it.

So split it do Count(*) of the active column in a temp table.

|||

Not sure I follow. This active and inactive status could change any time. This is because my database pulls information from another SQL database and this process of obtaining my data is run every nigh using a SQL DTS Package.

Could you please explain your solution more?

Thanx

|||If you are using DTS package why not just create separate Active or Inactive destination columns so all you have to do is count the Active column for what you need.|||

Ok, I understand that...I just feel like it's going to be a big effort to create the package all over again. Are you sure there isnt another way?

I am sure using the custom code is easier... if you can get the length of the object passed. I am very new to Reporting Services, which means I basically just started...as well as creating the DTS Packages.

I possible could you maybe explain shortly or give some idea how I would seperate the colums using the DTS packages?

thanks

|||

If you don't want to do it in the DTS package then move the data to a staging table then do an INTO that is SELECT INTO which creates a table with the CASE 1 then Active CASE 0 then Inactive and write a stored procedure which pulls the data from the destination table with COUNT(*) for the Active column and you will get the total. I could not find a more elegant solution, expecting reporting service to filter and give you a count you will need to be an expression expert.

Another option is to create a View with all your query result with Active and Inactive as columns then run a SELECT with COUNT(*) for Active. Run a search for CASE in the BOL and check the link below for more CASE samples.

http://www.craigsmullins.com/ssu_0899.htm

|||

If in the database you have a 1 or 0 (1 being active), couldnt you just do a Sum(Fields!Active.Value) to get the total number of active projects?

Or am I oversimplifying?

BobP

|||

That sound like a good idea, using the CASE. I have done this and added it to my storedPROC which is used to populate my datasource.

However I would like to add that values now to my Report Example:

=Fields!Active.value

However I cannot find this in the expressions box. Is this right and how do i get access to the CASE field from my StoredPROC.

Thanx

|||Modify the CASE statement to add 1 AS Active and 0 AS Inactive, I am sorry I assumed you understand when I told you to do the criteria based conditional split you will know that the derived columns should be renamed. If you are still getting problem use the Case in a Create View which makes both Active and Inactive fields by default and do Select (*) from the View and do the sum or COUNT(*) with SUM.

Reporting services and filtering

Hi,

I have being trying this for quiet a while now and I just cant find a solution. Basically I have a field called Total Active. The total active indicates for example..... A project is open or closed. If the project is open, the value 1 is displayed in the database and if closed the value 0 is displayed in the database.

So I have my field in reporting services and now I just want to count all the open projects. I thought you could do this:

= Count(Fields!Active_flag.value = 1)

This does not seem to work.....or my syntax is wrong or something.

Then I tried to pass all the values in Fields!Active_flag as a object using VB custom code. Example:

=code.getTotalActive(Fields!Active_flag.value)

then receive the Object and loop through it to count all the 1's in the Object (Active_flag.value) and pass the total back. However I was not successful with this because I couldn’t get the length of the Object....which I needed to iterate through all the values.

My next attempt which I thought was a sure winner was using a group and filtering out all the 0's which left only 1's that I could count. But this brought about another problem.....the filter only looks for 1's and then counts the 1's and displays then.... But when it doesn’t find any 1's then the entire Field does not display on the report. This is very frustrating because I would rather have is say "no projects active" instead of it not displaying.

Any help will be much appreciated. I have boggled my mind for the solution.

thanks

Ziyaad

I had something similar in my report but it is not that simple because you need CASE Statement to split the column into two Case Active 1, Case Inactive 0 but SQL Server will not recognize your derived columns as valid so you need to put both in temp table and do the calculation. There maybe a more elegant solution but this will give you what you want. I have done many reports both Crystal and SQL Server in the Enterprise so I know that is not an expression problem because SQL can solve it.

So split it do Count(*) of the active column in a temp table.

|||

Not sure I follow. This active and inactive status could change any time. This is because my database pulls information from another SQL database and this process of obtaining my data is run every nigh using a SQL DTS Package.

Could you please explain your solution more?

Thanx

|||If you are using DTS package why not just create separate Active or Inactive destination columns so all you have to do is count the Active column for what you need.|||

Ok, I understand that...I just feel like it's going to be a big effort to create the package all over again. Are you sure there isnt another way?

I am sure using the custom code is easier... if you can get the length of the object passed. I am very new to Reporting Services, which means I basically just started...as well as creating the DTS Packages.

I possible could you maybe explain shortly or give some idea how I would seperate the colums using the DTS packages?

thanks

|||

If you don't want to do it in the DTS package then move the data to a staging table then do an INTO that is SELECT INTO which creates a table with the CASE 1 then Active CASE 0 then Inactive and write a stored procedure which pulls the data from the destination table with COUNT(*) for the Active column and you will get the total. I could not find a more elegant solution, expecting reporting service to filter and give you a count you will need to be an expression expert.

Another option is to create a View with all your query result with Active and Inactive as columns then run a SELECT with COUNT(*) for Active. Run a search for CASE in the BOL and check the link below for more CASE samples.

http://www.craigsmullins.com/ssu_0899.htm

|||

If in the database you have a 1 or 0 (1 being active), couldnt you just do a Sum(Fields!Active.Value) to get the total number of active projects?

Or am I oversimplifying?

BobP

|||

That sound like a good idea, using the CASE. I have done this and added it to my storedPROC which is used to populate my datasource.

However I would like to add that values now to my Report Example:

=Fields!Active.value

However I cannot find this in the expressions box. Is this right and how do i get access to the CASE field from my StoredPROC.

Thanx

|||Modify the CASE statement to add 1 AS Active and 0 AS Inactive, I am sorry I assumed you understand when I told you to do the criteria based conditional split you will know that the derived columns should be renamed. If you are still getting problem use the Case in a Create View which makes both Active and Inactive fields by default and do Select (*) from the View and do the sum or COUNT(*) with SUM.

Reporting Services 2005 and Analysis Services 2000

Hello,

i have a small problem with the data sources in the reporting services, maybe you can clarify the situation... I have a MS CRM 3 solution with the reporting services 2005 installed. From an other vendor we have a arcplan solution on the 2000 analysis services. I would like to get access from the 2005 reporting services to the 2000 cube. Is this possible? Ive read that the SSMS cant connect to the 2000 cubes. Can i connect from the reporting services?

thank you

regards

Andreas

Sure you can. However, you have to use the Microsoft OLE DB Provider for Analysis Services when you set up your data source in SSRS. You may find this whitepaper useful although it's been written for SSRS 2000.

|||

thank you for the quick answer. i thought of a compatibility issue because i can choose the server and the database in the driver. but when i try to send a mdx the driver always tells me that the connection is lost. Ill try it on a second server, maybe its a computer issue...

thank you

andreas

Reporting Service

I have problem with repoting Service. When openhttp://localhost/reports it gives me below error what can be the solution for it

Error
The underlying connection was closed: An unexpected error occurred on a send.

Has reporting services ever worked on your box?

Was IIS working prior to installing SQL Server Reporting Services?

What account did you install reporting services to? Have you changed this account or has its password expired?

Look in both the Application and System event logs - you may find an expanatory message there.

|||

No before never i run reports on my localhost

Yea I am browsing many asp.net application and asp appcaltion on the same localhost and it is working very gud.

I Dont get error message in event log also

|||

You could also try posting tohttp://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=80&SiteID=17

You need to be as specific as possible as to host server, version of SQL you installed ect. Also patches applied as SP1 or Sp2 plus all security patches.

|||

Also check the HTTPSys log to see if you spot any problem.

C:\WINDOWS\system32\LogFiles\HTTPERR

This definitely sounds like an IIS issue rather than a RS issue.

|||

Their is no file like HTTPERR in C:\WINDOWS\system32\LogFiles\ Any another suggestion

|||Check the settings on the ReportingServices web site within IIS Manager. In particular check that the application name property is set.Which version of ASP.NET are you running on your web site? You may need to find and ASPNET_REGIIS -i to set all the ASP.NEt metadata.Also the temp files directory - check that the correct permissions have been applied to it. The ASP.NET set-up can fail to set set permissions to this.|||

Is your Reporting Service running on your locahost. If everthing is installed correctly, you should be able to to browse to it . You can check it Sql services are running from , right click on My computer-> manage->Sql Server Configuration Manager ->Sql Server 2005 services . check on right hand side widow if Sql Server Reporting Services are running.

hope this helps.

kushpaw