How to make a simple Totals Chart in SQL Server Mobile Report Publisher?

Question

I am trying to make a bar graph that displays the total units a sales person made and display it in Mobile Report Publisher; EX:

Tim -----

Blake ---

Chad -

Greg --------

My query is simple:

SELECT t.Sales_rep, COUNT(t.Sales_rep) as counted
FROM table as t
GROUP BY t.Sales_rep

I am having no problem getting data into mobile report publisher.

And this is the result: layout of the graph

As one can see, none of the numbers are being put in.

If I change the query to:

SELECT t.Sales_rep, 1 as counted
FROM table as t

And let Mobile Report Publisher do the aggregation, I still get the same result.

If I try to switch Data Structure from By Rows to By Columns I get this: with data structure as by columns

If I add any other columns, I get the same results posted above.

Answer 1

Are you simply using the wrong query? I've created a table in SQL Server:

CREATE TABLE [dbo].[Sales] (
    [Id] INT NOT NULL,
    [Sales_rep] VARCHAR (50) NULL,
    [Units]     INT   NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

and populated it as follows:

and then created a data set using the following TSQL:

SELECT 
    t.[Sales_rep], 
    SUM(t.[Units]) 
FROM 
    [dbo].[Sales] t 
GROUP BY 
    t.[Sales_rep]

I then created a Mobile Report adding a Totals Chart to the main area and setting its data to use the above data set and its properties as follows:

Which gives me the following chart output, which I think is what you want:

Hope this helps.

Martin