August 15, 2012 at 11:01 am
SQLKnowItAll (8/15/2012)
Matt Miller (#4) (8/15/2012)
Lynn Pettis (8/14/2012)
rVadim (8/14/2012)
I think Jared is right. The whole thing boils down to incorrect formation of @Subnet parameter. That is why asked what is in it. If it's like below, you will get the "invalid syntax" error:
SELECT count(IP_Address)
FROM #Device_Information
WHERE (IP_Address LIKE ('192.168.1.%','192.168.2.%'))
Again, the pertinent part of the OP's question:
What I am trying to achieve is a report by Building of the total number of devices in each Building.
I read this to mean that I need a query that will return the number of devices by building.
Does this not do that?
SELECT
bi.Building_Name, -- Building
COUNT(di.DeviceNum) NumberOfDevices -- Number of devices in the building
FROM
#Building_Information bi
INNER JOIN #Device_Information di
ON (di.IP_Address LIKE bi.Subnet)
GROUP BY
Building_Name;
+1
Ok, we can argue this all day. You showed him a better way of getting the data set. I showed him how to understand reporting services. 2 birds.
Lynn, even though I agree that your query is better structured...the issue was
However, on the multiple value pass it gives an error because of the like statement (Incorrect syntax ","). When I try to substitute the LIKE for IN, it will only match the value if it is an exact match.
Passing multiple-values parameters in the report itself will still cause the error and does not solve the problem which is solved by understanding how SSRS handles a multi-value pass to the parameter. If the OP still wants a report where the user can select 1, 2, 3, 4, etc. buildings and should only receive a report based on those choices, the supplied query does not change that. The multi-value pass still needed to be modified.
First, I don't see anything in the post to indeicate that this is an SSRS report passing parameters to a SQL query (stored procedure or what ever).
I see an OP attempting to use two separate queries to generate a simple report. Nothing in post indicated that the results were to be isolated to a selected set (1 or more) buildings. Again, the pertinent part of the post was to "...to achieve is a report by Building of the total number of devices in each Building." If you see anything here that indicates that this report was to be restricted by a selection criteria, please show me.
Instead of trying to make the original queries work to generate the dataset required for the report, I saw this as an opportunity to provide the OP with a better solution.
August 15, 2012 at 11:25 am
First, I don't see anything in the post to indeicate that this is an SSRS report passing parameters to a SQL query (stored procedure or what ever).
Not to beat a dead horse...
I am new to the forums and overall new to SQL and SRSS. I have two 2008 SQL databases that I am trying to generate a report from two different data sets.
(Now this is for people new to SSRS, not directed at Lynn) A data set is the term in SSRS for a set of results generated by a stored procedure, query, or even hard-coded. A dynamic parameter list can be populated with a second data set that is loaded asynchronously in the report that is not directly tied to the data set used in the report itself. This is done to generate a distinct list of options separate form the potential duplicates for that value in the report data set. A multi-value option can be set for a parameter, but returns the supplied error when not configured properly. I have had several people under me select the multi-value option and expect proper results. Well, it doesn't give it to them and when they naturally change the query to IN because they don't understand how SSRS combines the multiple values, they get the syntax error with the comma. Why, because it uses a comma to separate values. Taking all of this into consideration, I hope I solve someone's issue with multi-value parameter pass in SSRS.
Jared
CE - Microsoft
August 15, 2012 at 11:39 am
SQLKnowItAll (8/15/2012)
First, I don't see anything in the post to indeicate that this is an SSRS report passing parameters to a SQL query (stored procedure or what ever).
Not to beat a dead horse...
I am new to the forums and overall new to SQL and SRSS. I have two 2008 SQL databases that I am trying to generate a report from two different data sets.
(Now this is for people new to SSRS, not directed at Lynn) A data set is the term in SSRS for a set of results generated by a stored procedure, query, or even hard-coded. A dynamic parameter list can be populated with a second data set that is loaded asynchronously in the report that is not directly tied to the data set used in the report itself. This is done to generate a distinct list of options separate form the potential duplicates for that value in the report data set. A multi-value option can be set for a parameter, but returns the supplied error when not configured properly. I have had several people under me select the multi-value option and expect proper results. Well, it doesn't give it to them and when they naturally change the query to IN because they don't understand how SSRS combines the multiple values, they get the syntax error with the comma. Why, because it uses a comma to separate values. Taking all of this into consideration, I hope I solve someone's issue with multi-value parameter pass in SSRS.
Okay, I think this dead horse likes being beaten.
I am new to the forums and overall new to SQL and SRSS. I have two 2008 SQL databases that I am trying to generate a report from two different data sets.
(Highlighting modified by me)
+
What I am trying to achieve is a report by Building of the total number of devices in each Building.
A single report detailing BY building the number of devices in EACH building.
Still not seeing where any selection is occuring to restrict the report to a specific Building or Buildings.
Even if there were, the original query the OP posted for the device information fails as it stands. There is a variable in the where clause, but how is it getting populated? If there were multiple subnets passed, and if it worked, it would return a single value, not a count based on each building.
And even if the report needs to be selectable by Building or Buildings, I would use the query I wrote as the basis for the report. It would be easy to add the DelimitedSplit8K function to the query and enhance the query to return only the counts for the Building or Buildings selected (a comma delimited string passed as a parameter).
August 15, 2012 at 11:52 am
Lynn Pettis (8/15/2012)
SQLKnowItAll (8/15/2012)
First, I don't see anything in the post to indeicate that this is an SSRS report passing parameters to a SQL query (stored procedure or what ever).
Not to beat a dead horse...
I am new to the forums and overall new to SQL and SRSS. I have two 2008 SQL databases that I am trying to generate a report from two different data sets.
(Now this is for people new to SSRS, not directed at Lynn) A data set is the term in SSRS for a set of results generated by a stored procedure, query, or even hard-coded. A dynamic parameter list can be populated with a second data set that is loaded asynchronously in the report that is not directly tied to the data set used in the report itself. This is done to generate a distinct list of options separate form the potential duplicates for that value in the report data set. A multi-value option can be set for a parameter, but returns the supplied error when not configured properly. I have had several people under me select the multi-value option and expect proper results. Well, it doesn't give it to them and when they naturally change the query to IN because they don't understand how SSRS combines the multiple values, they get the syntax error with the comma. Why, because it uses a comma to separate values. Taking all of this into consideration, I hope I solve someone's issue with multi-value parameter pass in SSRS.
Okay, I think this dead horse likes being beaten.
I am new to the forums and overall new to SQL and SRSS. I have two 2008 SQL databases that I am trying to generate a report from two different data sets.
(Highlighting modified by me)
+
What I am trying to achieve is a report by Building of the total number of devices in each Building.
A single report detailing BY building the number of devices in EACH building.
Still not seeing where any selection is occuring to restrict the report to a specific Building or Buildings.
lol Well, the idea that there is a parameter at all, multi-value, makes me pretty sure that the OP wants the user to select 1 or more buildings to be in the report.
Even if there were, the original query the OP posted for the device information fails as it stands. There is a variable in the where clause, but how is it getting populated?
That's why I assumed an SSRS parameter from the first dataset 🙂
If there were multiple subnets passed, and if it worked, it would return a single value, not a count based on each building.
Ahh... but people designing a report think that if they select "multi-value" that SSRS is smart enough to simply change their query to item1 OR item2 OR item3. They think it should work with the multiple as it stands because the option exists. However, it concatenates the values into a comma delimited string and passes them all as 1 parameter. That's what "I" see as the problem in this case.
And even if the report needs to be selectable by Building or Buildings, I would use the query I wrote as the basis for the report. It would be easy to add the DelimitedSplit8K function to the query and enhance the query to return only the counts for the Building or Buildings selected (a comma delimited string passed as a parameter).
I agree. I was just trying to help the op Understand why their query worked when passed with 1 parameter and not with the multi-value (comma delimited). Don't get me wrong, I agree with your query whole-heartedly. However, lets say you let SSRS do the counting and had the data set listing each ip address which means duplicate building values in the set. In order to offer a distinct parameter list in SSRS, you must have a second data set that does just that. Having been in the position described, I took all of that information and assumed it was in fact the exact same position I had been in when I first started writing reports.
Jared
CE - Microsoft
August 15, 2012 at 12:07 pm
Okay, I think we have now beaten the dead horse enough. My exposure to SSRS is helping people writing the reports to get the data sets they need, I haven't had to actually write any reports (yet). With that, my goal is to provide the data they need in the format they need to simplify the report they are writing. (Did any of that make sense?)
I think we both understand where the other is coming from, and anything further really would be wasted effort on both sides. Agree?
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply