Build model in report builder for paginated report

[QCLab]

Hello,

I am looknig to recreate a semantic model in Power BI desktop report builder

The model looks like this and I would like to be able to print it out to display on a wall however when I export to PDF it only prints the current view

I know report builder can be used to generate paginated reports however I cannot get the model to display the lot no. on the Y axis. I need the lot no. as we get multiple a day so needs to be seperated rather than date

 

Am I doing something wrong here?

Thanks

Sean

[QCLab]

Hi Dinesh,

Thank you for the response 

Ive done as you suggested and added the lot no. to the catagories group but I cant get it to be treated as an individual lot

Thanks

Sean

[v-dineshya]

Hi @QCLab ,

Please try below things.

1. Check the Field value in Category Groups, Select Tropos_Lot_Number group --> Group Properties --> Under "Group on" , make it as =Fields!Tropos_Lot_Number.Value. If any aggregation function, remove it.

2. In Group Properties --> Sorting tab, add a new sort by =Fields!Tropos_Lot_Number.Value.

3. In Values section, make it as =Fields!Spec_430nm_Colour.Value. check your plot actual values per lot, not a summary.

4. Select X-axis --> In properties, Set Interval=1 & Set Label Rotation as 45 or 90 degrees, if lots are too dense.

5. Check your dataset has GROUP BY or any Aggregation logic in the SQL or DAX, make sure it returns one row per lot number, not aggregated.

6. Place the Tropos_Lot_Number field in both Category Groups and Series Groups, it will treat each lot as an individual category and avoid series-level grouping.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[QCLab]

Hi Dinesh,

I have added the settings as described

For point 5 I have pulled the data straight from power BI and there isnt any DAX in it there. The dataset looks like this:

Unfortunately it now fails to generate a report

Thanks

Sean

[v-dineshya]

Hi @QCLab ,

Please remove the "." dot symbol at last. Please refer below.

1.  "=Fields!Tropos_Lot_Number.Value." to " =Fields!Tropos_Lot_Number.Value"

2. "=Fields!Spec_430nm_Colour.Value." to "=Fields!Spec_430nm_Colour.Value"

Note: Replace above all. And retry to validate the report.

 Please do let us know if you have any further queries.

Regards,

Dinesh

[QCLab]

Hi Dinesh,

Thats worked now and I have the lot no. at the bottom

I have tried to also include date in here as well but it will give me date and time

I have changed the formatiing in the horizontal axis properites but that doesnt seem to work

Also what are these TBC data points here?

I dont have any missing lot no.s in my data and they apear even after I have republished the report?

Thanks

Sean

[v-dineshya]

Hi @QCLab ,

Please try below things to fix the issues.

1. Date/Time format issue: Right click on "Category Group" or X-axis field in the chart, select "Category Group Properties" --> "Label" or In chart --> Series --> Category Field.
Click the expression (fx) next to the field. Replace =Fields!DateField.Value with =Format(Fields!DateField.value, "dd/MM/yyyy") or you can try this =Format(Fields!DateField.value, "dd MM yyyy")

Note: This forces the display format directly in the label.

2. Unexpected TBC data points: In Left Pane --> Datasets --> Right click on your data --> Query. Run the query manually to check for any rows with LotNumber = "TBC" or NULL or invalid dates like 01/01/1900. Edit your dataset query or add a filter, Right click the chart --> Properties --> Filters, add filter like =IsNothing(Fields!Tropos_Lot_Number.Value) =False

3. Clear cache: Delete and re-add the report instead of overwrite or clear dataset cache from the Report server or Power BI service if published.

4. If you want to show both lot and date in the label, = Fields!Tropos_Lot_Number.Value & " (" & Format(Fields!DateField.Value, "dd/MM/yyyy") & ")"   use this code.

 Please do let us know if you have any further queries.

Regards,

Dinesh

[QCLab]

Hi Dinesh,

Thats perfect thank you

Sean

[v-dineshya · Accepted solution]

Hi @QCLab ,

Thank you for reaching out to the Microsoft Community Forum.

You want to display Lot No. on the Y-axis or use it to separate data points.

Please try the below things.

1. Check Lot number is on Category Groups, In Report Builder --> Open Chart Data panel --> Drag Trops_Lot_Number into "Category Groups" not in values or series. Also check each lot is treated as individual category on the axis.

2. To increase space while exporting, Go to Report --> Report properties --> Page setup. Set to Landscape and increase width based on your requirement. And increase Margins.

3. If you want horizontal scroll or auto fit, Right click on the chart --> properties. under the Chart axis --> Interval, set interval =1 or use Auto option.

4. Better to use matrix for more control or split the chart by date or batch group and use a parameter or filter to control.

5. Export directly from report builder using File --> Export --> PDF. if chart gets cut off, Re check page width and avoid placing the chart inside a fixed-size container.

4. If you are using a scatter plot, it may not support categorical Y-axis values. Consider switching to a bar chart or column chart.

Please refer below link.

Power BI Report Builder - Power BI | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh