SQL 2012 :: SSRS Multi-value Parameters - Total / Sum / Count
Sep 11, 2015
I have a multi-value parameter that I am having a hard time writing a COUNT expression for in SSRS. Here is the situation:
1. If the "(Select All)" in the drop down is selected, COUNT all last names for ALL of the Auditor parameter
2. If a specific or multiple auditors are selected from the drop down, COUNT all last names based on that selection for the Auditor parameter
Currently, I am having it COUNT by ALL and it works but if a specific or multiple auditors are chosen, then the COUNT doesn't work.
I have what seems to be a common problem where I have names like "Last, First" that I need to be able to select using a multi-value parameter, which of course, SSRS passes as comma-delimited and gets messed up. I need to find a way to use a different delimiter or pass CHAR(44) instead of the comma, or something. Even worse, some people have suffixes and multiple commas in one name!  Â
I've been running into this issue quite a few times, and have been unable to solve it through reading various posts/forums. Here is the issue
Software Details: Datawarehouse Database - SQL Server 2008 R2 Reporting Services Version - SSRS 2008 R2 Development Environment - Visual Studio 2008 Problem Details:
I have been writing reports based of a Relational Datamart with dimensions and facts. The report in question uses dimensions for parameters and facts for content. Multi valued parameters are enabled in the report query by using the 'IN(@Paramenter)' statement within the report query. It has a total of 6 multi-value parameters. I assign the same available values from dimension datasets for each parameter to it's default parameter. Theoretically all the values in the option lists should be selected after first render. This is not always the case.
Example: SELECT * FROM dbo.MyTable WHERE TableColumn0 IN (@Parameter0) AND TableColumn1 IN(@Parameter1) AND ....N (continue syntax to 6 multi valued parameters) Â Some of the multi-value drop down lists are defaulting to have every value selected, and some are not. The problem is I need to know why some parameters are not selecting all by default when specified.
We have an application that runs Jobs, each of which affect ## number of child objects (usually around 1M). When a thread gets to 5000 updated child objects it bulk inserts into a table called ActionLog with the child Id and JobId.
When the job is complete a sproc SUMs the children from the ActionLog table: select sum(id) from ACTIONLOG where JOBID = @JobId;
It then updates the Jobs table AffectedObjectCount column with the sum(*) from above.
Instead of writing to the ActionLog table and calculating the SUM at the end I would like to do this 'real time'. After the bulk insert I would like to update the AffectedObjectCount column with the number of rows that were just bulk inserted. I tried this in the past and ran into major contention issues. There are usually 20 threads running a job so there exists a lot of potential for deadlocks.
Is there a recommended way to handle updating one column on one row from multiple threads? What is the best practice for a counter like this?
There is a multi value parameter called  "include" in the report where "Allow Multiple Values" is checked and it has 4 Available values as shown in the attached screen shots and preview of the report is also shown .There is no data set for this parameter and the values  will get displayed on the report based on the visibility condition set in the report.Example : If first value  is selected  then 1 is passed and based on the visibility condition set in the report - the report output is displayed.None is default value and has value 4  and when the report is run with this option i.e. "None" then rest three parameter values are not applicable .
Requirement : -When the end user selects (Select All) Check box then (None) -check box must be disabled or must not appear for selection for the end user -When the end user selects check boxes either of the first three except None then also None check box must be disabled or must not appear for -selection for the end user -when the end user selects a combination of first three then also None check box must be disabled or must not appear for selection for the end user -The None is set as default with a value as 4 and is applicable only when the user does not select either of the first three values and the report will run.
I have a detailed report in ssrs in which data can come from start date and end date parameters.
but the problem is. for example i gave startdate as 01/01/2015 and end date as 09/31/2015 then the data must be in displayed in such a way that jan month in one tab and feb month data in one tab and sooo on to sep month data in new tab when i export to Excel.
I am creating simple report in ssrs and pass one parameter only. It will work perfectly (here user enter the parameter value). but i need that i should select the value in drop down box. i had tried many time and did different ways but I am unable to do it.
First i gave the parameter in my sql query in Data set (like WHERE COUNTRY = @COUNTRY) and i checked the Parameters tab in the data set. Here by default comes the Parameter Name: COUNTRY and Parameter value: [@COUNTRY].
Next i select COUNTRY Parameter in the Report Data Pane. and go to properties Here in General Tab: Name COUNTRY Prompt: COUNTRY, Select Get values from query in available values Tab (and also i tried with Select Get values from query in Default Value Tab) and Select Data set: Data Set1, Value field: COUNTRY and Label Field COUNTRY. And Click Ok
And tried to preview the report, it throwing below error
"An error Occured during local report processing. The definition of the report is invalid. The Report Parameter 'COUNTRY' has a DefaultValue or a ValidValue that depends on the report parameter "COUNTRY". Forward dependencies are not valid.
How can I achieve dropdown list.What i missed? Even i unable to do it Multi valued parameters and Cascading parameters.
Actually i am working on SQL Express 2012 version.
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition --We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately --Recovery needs to happen within 1 hour (Not sure that this is realistic -- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable 2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances 3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.
1) Do you really need DTC per instance or one for all? 2) Should DTC be in its own resource group or within the instance's group? 2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select -- count returns unit data Count(substring(m.PTNumber,3,3)) as PTCnt, -- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt, -- attempting to calculate the percent by PTCnt/TotalCnt returns 0 (Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct -- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
Does anyone know of a link or list that has all the parameters for the "rs:" section of the URL access parameter, except for the ones in the Microsoft books?
Hello, I am having problems with this query below: 1 SELECT Table1.Email AS Email, 2 Table2.UserName AS Username, 3 Table3.Members_Paid AS Paid, 4 (SELECT DISTINCT COUNT(*) 5 FROM Table3 AS e JOIN Table3 AS m 6 ON e.Members_Sponsor = m.Members_ID 7 WHERE (e.Members_Sponsor = m.Members_ID)) AS TotalRecords 8 FROM Table1 INNER JOIN 9 Table2 ON Table1.UserId = Table2.UserId INNER JOIN 10 Table3 ON Table2.UserId = Table3.UserID 11 WHERE (Table3.Members_Sponsor = @UserId)Basicly what I am trying to do is get all members that belong to a certain manager along with those members count total of members they have below them.The code above is giving me the count of the first member only, not different counts for each member.Hope you understand what I am trying to say and do here. Hope someone can help me out cause this hase been driving me crazy for a few days now.
I have to write a query to get the count() of the customer who has max sales in the last 6 months.my query is
Select Inv_Cust,Count(Inv_Cust) as Salescount From Inv_Header Group By Inv_Cust,Inv_Date Having Inv_Date Between MIN(Inv_Date) And DATEADD(MM,6,min(Inv_Date)) which gives me a result like inv_cust ' Salescount
I have a query that uses a CTE which looks similar to this
WITH cte_Products AS (SELECT SELECT ROW_NUMBER() OVER (ORDER BY ProductName ASC) AS RowNum, RANK() OVER (ORDER BY id DESC) AS rank, FROM Products WHERE SomeColumn = @SomeVariable) SELECT rownum, columns, (SELECT COUNT(*) FROM cte_Product) AS TotalRowCount FROM cte_Product AS products WHERE Rank <= @LastXItems AND RowNum BETWEEN (@StartRowIndex + 1) and (@StartRowIndex + @MaximumRows) ORDER BY RowNum
The (SELECT COUNT(*) FROM cte_Product) AS TotalRowCount is in there because I need the total row count that is queried by the CTE. But I feel like this is an inefficient way of doing this. I would just split out the query for the total row count, but then I have to do another sub query to get the rank again since rank is calculated using the Rank method. Does anyone have any ideas of how best to do this?
I'm trying to return the total records with my query, but I'm getting the following error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."
Here's my query:
set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _ & " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed, '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _ & " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _ & " Order By DateClosed DESC ")
in my procedure, I want to count the number of rows that have erroredduring an insert statement - each row is evaluated using a cursor, soI am processing one row at a time for the insert. My total count tobe displayed is inside the cursor, but after the last fetch is called.Wouldn't this display the last count? The problem is that the count isalways 1. Can anyone help?here is my code,.... cursor fetchbegin ... cursorif error then:beginINSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,CUSTINDICATOR, DT_LOADED)VALUES(@ERRORNUM, @ERRORDESC,@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY,@STATE, @POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE,@PRICELEVEL, @TERMSCODE, @DISCPERCENT, @TAXCODE,@USERCOMMENT, @CURRENCY, @EMAILADDRESS, @CUSTOMERGROUP,@CUSTINDICATOR, @DTLOADED)SET @ERRORCNT = @ERRORCNT + 1END --error--FETCH NEXT FROM CERNO_US INTO@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE,@POSTALCODE, @CONTACT,@PHONE,@SALESREPCODE, @PRICELEVEL,@TERMSCODE,@DISCPERCENT, @TAXCODE, @USERCOMMENT, @CURRENCY,@EMAILADDRESS,@CUSTOMERGROUP, @CUSTINDICATOR, @DTLOADED--IF @ERRORCNT > 0INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,STATUS)VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @ERRORCNT, 'FAILEDINSERT/UPDATE')END -- cursorCLOSE CERNO_USDEALLOCATE CERNO_US
I have report in which I have created groups base on the customer name. Can anybody please tell me how to get the total page counts for the individual group? I have page break after every new group and I am able to reset page count to 1 when new group start but I am getting the total number of pages for a particular group.
For ex, let say I have 4 groups, 1 group has 3 page, 2 group has 2 pages and 3 group has 6 page and 4th group has 7 pages I need something like,
For 1st group
'Page 1 of 3€™ when user click next page it should be 'Page 2 of 3' etc
I have a question regarding the total count of the table rows
Select count (name) from test. Lets say I have got 200 count. And Select count (lastname) from test1.I have got 200.And this counts I should store it in "There are nn name items awaiting your attention and nn pending lastname's awaiting your approval".
I want to return the total number of rows and top n records from my query. Is it possible to do this by simply running a single query. My query is dynamic so I can not create any temp table or table variables.
Bacially I want to eliminate 2 calls for sp_executesql
Set @ColumnCount = "Count(*)" Set @Columns = " Top 100 a,b,c" Set @Filter = "a = 1001"
Set @sql = "select " + @ColumnCount + " FROM TABLEA WHERE " + @Filter sp_executesql @Sql
Set @sql = "select " + @Columns + " FROM TABLEA WHERE " + @Filter sp_executesql @Sql
I have a DB of professors and information related with them. I created the cube, it consist of: Measures: Measure group Professors: Amount of projects (COUNT proj_id) Amount of pulications (COUNT pub_id) Amount of e_books (COUNT book_id) -------------- Measure group Projects: Distinct amount of projects (DISTINCT COUNT proj_id) -------------- Measure group Publications: Distinct amount of publications (DISTINCT COUNT pub_id) -------------- Measure group E_books: Distinct amount of e_books (DISTINCT COUNT book_id) -------------- Calculated measures: Amnt_Projects iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects]) Amnt_Publications (similar to the above one) Amnt_E_books (similar to the above one) --------------------------- Dimensions: dimPROFESSORS - prof_id -surname -name -gender dimPROJECTS - proj_id -type name -name dimPUBLICATIONS - pub_id -type name -name dimE_BOOKS - book_id -name Date_Projects -date_id -years Date_Publications -date_id -years Date_E_books -date_id -years
For example, when I browse the cube: prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects 1032------------------- 30 --------------------------1----------------1 1070-------------------90 --------------------------2----------------2 1111-------------------0 ---------------------------1----------------0 1137-------------------0 ---------------------------1----------------0 1234-------------------1404--------------------------9----------------9 1721-------------------504--------------------------7----------------7 2661-------------------85 --------------------------5----------------5 ...--------------------...---------------------------...----------------... 6999------------------- 20---------------------------1-----------------1 9956-------------------50---------------------------5-----------------5 Unknown----------------(empty)---------------------(empty)-----------0 Grand Total------------ 2421------------------------11-----------------11
Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?
I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of: Measures: Measure group Professors: Amount of projects (COUNT proj_id) Amount of publications (COUNT pub_id) Amount of e_books (COUNT book_id) -------------- Measure group Projects: Distinct amount of projects (DISTINCT COUNT proj_id) -------------- Measure group Publications: Distinct amount of publications (DISTINCT COUNT pub_id) -------------- Measure group E_books: Distinct amount of e_books (DISTINCT COUNT book_id) Calculated measures: Amnt_Projects iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects]) Amnt_Publications (similar to the above one) Amnt_E_books (similar to the above one) --------------------------- Dimensions: dimPROFESSORS - prof_id -surname -name -gender dimPROJECTS - proj_id -type name -name dimPUBLICATIONS - pub_id -type name -name dimE_BOOKS - book_id -name Date_Projects -date_id -years Date_Publications -date_id -years Date_E_books -date_id -years
For example, when I browse the cube: prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects 1032------------------- 30 --------------------------1----------------1 1070-------------------90 --------------------------2----------------2 1111-------------------0 ---------------------------1----------------0 1137-------------------0 ---------------------------1----------------0 1234-------------------1404--------------------------9----------------9 1721-------------------504--------------------------7----------------7 2661-------------------85 --------------------------5----------------5 ...--------------------...---------------------------...----------------... 6999------------------- 20---------------------------1-----------------1 9956-------------------50---------------------------5-----------------5 Unknown----------------(empty)---------------------(empty)-----------0 Grand Total------------ 2421------------------------11-----------------11
Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?
We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.
So some sample records of a complete package would look like this:
ItemId's 2 & 3 are items contained within the ItemId 1 package.
Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:
Name | Available Qty -------------------------- Package A | 15 Package B | 100
How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?
I need to create a stored procedure for total count of the user's. If  User from front end  invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."
But now we have changed the concept to get the ISID (name of the user) Â and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .
After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.
if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2.
Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).
Here is my sample  code:
I need to sum it up to get the total user's from both the table but I should get 1 instead of 2Â
SELECT (select distinct COUNT(*) from dbo.test where New_Values like  '%invited%' and Created_By= 'sam' + (select distinct count (*)  from dbo.test1 where invited_by ='sam'
I pulled data from several tables into temp table and I want to write a query to do count specfic data in certain columns and group it by the Owner... here is a sample table and query below, however in the real table there are more owner's, so I want to avoid using statements like - where owner = 'Smith'
Query ------ Select distinct(owner), (Select count(Color) from #table1 where Color = 'Blue') Blue, (Select count(Color) from #table1 where Color = 'Green') Green, (Select count(Color) from #table1 where Color = 'Red') Red, (Select count(Data) from #table1 where Data = 'A')A, (Select count(Data) from #table1 where Data = 'B')B, (Select count(Data) from #table1 where Data = 'C')C, (Select count(nextaction) from #table1 where nextaction = 'Level1')Level1, (Select count(nextaction) from #table1 where nextaction = 'Level2')Level2, (Select count(nextaction) from #table1 where nextaction = 'Level3')Level3, from #table1 group by owner
Does't really work... I tried putting a group by in the sub select but I get and error because I get multiple rows and that is not allowed when you use a sub query as an expression... so it said..
I have a dataadapter on my asp.net page and am having issues passing values to the sql statement. See below:
SELECT tblTemp.SensorID, tblSensor.SensorNum, tblTemp.TempTime, tblTemp.TempVal FROM tblTemp INNER JOIN tblSensor ON tblTemp.SensorID = tblSensor.SensorID WHERE (tblTemp.TempTime BETWEEN @From AND @To) AND (tblTemp.SensorID IN (@Sensor)) ORDER BY tblTemp.TempTime
@Sensor is populated in vb with the selected values from a checkboxlist control. I attempted to test this in the "Data Adapter Preview" window, but continually get an error. Is there a way to pass multiple values to a parameter? Any help would be appreciated, thanks!
I and trying to get some help with Multi-Value parameters in Reporting Services 2005 in VS 2005. I’m new to the product and struggling with the TSQL syntax.
(I have simplified my SQL statement for the purposes of this question) I have a dataset below which has 1 parameter;
="SELECT * " & " FROM dbo.Table" & Iif(Parameters!Sex.Value = "ALL",""," AND dbo.Table.Sex = '" & Parameters!Sex.Value &"'" )
The Sex parameter dataset contains F, M, U and ALL – the above parameter allows me to select on any of the 4 options. I would like to use the multi-value parameter but am struggling to grasp the syntax. Believe me I have tried a few things.
Could someone provide me with an idiots guide on how to make my basic parameter a multi-value parameter?
