SSRS - match dataset values with hard-coded values

0
votes

I am trying to create an RDL file and I need a tablix to appear in the following format.

This is how I want the results to look

This is how I want the results to look

The values that are in bold are hard coded values. This is how the output from the SELECT statement in the datasets looks

SQL Output

SQL Output

I don't know how to make the values that output from the database match with the hard coded values in the RDL file. The 'Day' field represents a day in the month and the 'Num' field represents the number of sales that were on the day. The above example shows that on the first day of the month, there were 100 sales made. I need the tablix to output in that specific format.

If the day isn't in the SQL output (no sales made that day), I want it to output blank and/or 0.

Any idea how this could be accomplished?

2
reporting-servicesssrs-tablixrdl
what do you mean by hardcoded values in the RDL? Do you have a dataset that returns these values? if all you want to do is to return the report as 3 columns instead of one long list.. go to Report Properties - Columns- change from 1 to 3.. ( on the right had property pane) – Harry
It would appear that a "calendar" has been created and the values returned should sit in the respective number column that corresponds to the date. – JonTout
Do you have access to the SQL, how is the "day" stored in the database – JonTout
Like @JonTout is suggesting top side; use the date field in your sql server as you are clearly aggregating [Num] column based day. Get just the day part out from datepart function and your aces. You wont have to worry about 30, and 31 based month. – junketsu

2 Answers

0
votes

Use a CTE to create rows for each day you need and then join your results on. A starting point for you CTE could be:

;WITH nums AS
   (SELECT 1 AS value
    UNION ALL
    SELECT value + 1 AS value
    FROM nums
    WHERE nums.value <= 30)
SELECT *
FROM nums

You'll probably then want to modify the total days based on the month you are viewing.

0
votes

You can do this using lookups, but you would need to hard code a lookup in each cell. e.g. for day 1

=lookup(cint(1),Fields!Day.Value,Fields!Num.Value,"Dataset1")

A faster way would be to create a tablix on the dataset filtered on the first ten days:

=Switch(
Fields!DAY.Value <= 10 and Fields!DAY.Value >=1,"Include",
True,"Exclude"
)

Create a row group on days, then create a column with day and num, and columns with Fields!DAY.Value+10 and Fields!DAY.Value+20 with the following lookups:

=lookup(Fields!DAY.Value+10,Fields!DAY.Value,Fields!NUM.Value,"DataSet1")
=lookup(Fields!DAY.Value+20,Fields!DAY.Value,Fields!NUM.Value,"DataSet1")